“How to Export MySQL data to Excel in PHP ?”
This script will used to export data into .xls format from mysql database.You need to just copy this code and update database connection details.
Below code will export mysql data into excel (.xls format) with every column name and value from your database .
<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('Could not connect: ' . mysql_error());
}
$sql = 'SELECT * FROM myTable';
mysql_select_db('MyDatabase');
$result_query = mysql_query( $sql, $conn );
if(! $result_query )
{
die('Could not get data: ' . mysql_error());
}
$sample_data_array=array();
while($row = mysql_fetch_array($result_query , MYSQL_ASSOC))
{
array_push($sample_data_array, $row);
}
//header info for browser
header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=Export_XLS_By_Mysql.xls");
//clear the cache
header("Pragma: no-cache");
header("Expires: 0");
/*******Start of Define Formatting for Excel*******/
//define separator (defines columns in excel & tabs in word)
$sep = "\t"; //tabbed character
//start of printing column names as names of MySQL fields or header of the sheet
foreach ($sample_data_array[0] as $key=>$rows)
{
echo $key. "\t";
}
print("\n");
//end of printing column names or header of the sheet
foreach($sample_data_array as $key=>$rows)
{
$schema_insert = "";
foreach ($rows as $key=>$row)
{
if(!isset($row))
$schema_insert .= "NULL".$sep;
elseif ($row != "")
$schema_insert .= "$row".$sep;
else
$schema_insert .= "".$sep;
}
$schema_insert = str_replace($sep."$", "", $schema_insert);
$schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
$schema_insert .= "\t";
print(trim($schema_insert));
print "\n";
}
exit;
?>
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 |
<?php $dbhost = 'localhost'; $dbuser = 'root'; $dbpass = 'password'; $conn = mysql_connect($dbhost, $dbuser, $dbpass); if(! $conn ) { die('Could not connect: ' . mysql_error()); } $sql = 'SELECT * FROM myTable'; mysql_select_db('MyDatabase'); $result_query = mysql_query( $sql, $conn ); if(! $result_query ) { die('Could not get data: ' . mysql_error()); } $sample_data_array=array(); while($row = mysql_fetch_array($result_query , MYSQL_ASSOC)) { array_push($sample_data_array, $row); } //header info for browser header("Content-Type: application/vnd.ms-excel"); header("Content-Disposition: attachment; filename=Export_XLS_By_Mysql.xls"); //clear the cache header("Pragma: no-cache"); header("Expires: 0"); /*******Start of Define Formatting for Excel*******/ //define separator (defines columns in excel & tabs in word) $sep = "\t"; //tabbed character //start of printing column names as names of MySQL fields or header of the sheet foreach ($sample_data_array[0] as $key=>$rows) { echo $key. "\t"; } print("\n"); //end of printing column names or header of the sheet foreach($sample_data_array as $key=>$rows) { $schema_insert = ""; foreach ($rows as $key=>$row) { if(!isset($row)) $schema_insert .= "NULL".$sep; elseif ($row != "") $schema_insert .= "$row".$sep; else $schema_insert .= "".$sep; } $schema_insert = str_replace($sep."$", "", $schema_insert); $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert); $schema_insert .= "\t"; print(trim($schema_insert)); print "\n"; } exit; ?> |
