how to export data to the excel sheet without using any plugin in PHP and MySQL.
Here in this quick tutorial I will let you know how to export grid data into excel file. Export/Import is very common functionality for web development, some time we need to export all grid data into excel file that time we need below method to export data into excel file. It’s very easy and simple steps in PHP, we need to set header information to force browser to open download window.
We often need to generate reports in excel sheet, but sometime we have our data stored in MySQL database. It is quiet easy to fetch records from MySQL and export the result in .csv file or .xls file and let user to force download it. We will make use of PHP and MySQL to do this functionality. This downloading option is very important in web application. Using this code we can generate a report from our MySQL data.
This tutorial will explain about downloading the MySQL data into excel sheet format using PHP MySQL. Here we will select all the data as per our MySQL query and generate a excel file.
We often need to generate reports in excel sheet, but sometime we have our data stored in MySQL database. It is quiet easy to fetch records from MySQL and export the result in .csv file or .xls file and let user to force download it. We will make use of PHP and MySQL to do this functionality. This downloading option is very important in web application. Using this code we can generate a report from our MySQL data.
This tutorial will explain about downloading the MySQL data into excel sheet format using PHP MySQL. Here we will select all the data as per our MySQL query and generate a excel file.
Create a mysql database : test
CREATE DATABASE databasename;
Create a mysql database table :demo
create table demo(
id INT NOT NULL AUTO_INCREMENT,
country VARCHAR(100) NOT NULL,
state VARCHAR(40) NOT NULL,
city VARCHAR(40) NOT NULL,
pin VARCHAR(40) NOT NULL,
submission_date DATE,
PRIMARY KEY ( id )
);
Create button for export data
<a href="export.php?export=true">Export</a>
Create a php file :export.php
// Database Connection
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "test";
$conn = mysqli_connect($servername, $username, $password, $dbname);
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
if(isset($_GET['export'])){
if($_GET['export'] == 'true'){
$query = mysqli_query($conn, 'select * from demo'); // Get data from Database from demo table
$delimiter = ",";
$filename = "significant_" . date('Ymd') . ".csv"; // Create file name
//create a file pointer
$f = fopen('php://memory', 'w');
//set column headers
$fields = array('ID', 'Country', 'State', 'City', 'Pin');
fputcsv($f, $fields, $delimiter);
//output each row of the data, format line as csv and write to file pointer
while($row = $query->fetch_assoc()){
$lineData = array($row['id'], $row['country'], $row['state'], $row['city'], $row['pin']);
fputcsv($f, $lineData, $delimiter);
}
//move back to beginning of file
fseek($f, 0);
//set headers to download file rather than displayed
header('Content-Type: text/csv');
header('Content-Disposition: attachment; filename="' . $filename . '";');
//output all remaining data on a file pointer
fpassthru($f);
}
}