Download Mysql Table Data into Excel Sheet Format in PHP | Aheadpoints

This tutorial will explain about exporting the mysql table data into excel sheet format using PHP and mysql. Here we will select all the data from table by our mysql query and generate a excel file according to that data and we can set a selected column name as the excel sheet header. We will do this functionality by using PHP and Mysql. This downloading option is very important in web application. Using this code we can generate a report from our Mysql data. We can generate different type of Report from User Report, Employee Report or Student Report etc. When anyone needs to generate any types of report, this code is very helpful.

To Do this First we will have a Mysql Table with some data like table structure below.
After making table in database we will make a mysql connectivity using this code.
$con=mysql_connect("localhost", "root", "") or die("error"); mysql_select_db("dbname", $con) or die("error");
Here dbname is your database name. And after this the final code is....
include(config.php);
$Counter = 0;
$ExcelName = "exceldata";
$Sql = "select * from city";
$Rec = mysql_query($Sql);
$Counter = mysql_num_fields($Rec);
for ($i = 0; $i < $Counter; $i++) {
$MainHeader .= mysql_field_name($Rec, $i)."t";
}
while($rec = mysql_fetch_row($Rec)) {
$rowLine = '';
foreach($rec as $value) {
if(!isset($value) || $value == "") {
$value = "\t";
} else {
//It escape all the special charactor, quotes from the data.
$value = strip_tags(str_replace('"', '""', $value));
$value = '"' . $value . '"' . "t";
}
$rowLine .= $value;
}
$Data .= trim($rowLine)."\n";
}
$Data = str_replace("\r", "", $Data);
if ($Data == "") {
$Data = "\nno matching records found\n";
}
$Counter = mysql_num_fields($Rec);

header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=".$ExcelName."_Reoprt.xls");

header("Pragma: no-cache");
header("Expires: 0");
echo ucwords($MainHeader)."\n".$Data."\n";



Done...Enjoy..

Read More