Export to Excel Using PHP and Mysql


Below simple program will export mysql data values to excel sheet without using any PHP libraries.

Export to Excel Using PHP and Mysql

<!--?php &lt;/p&gt;
&lt;p&gt;//write your db connection&lt;/p&gt;
&lt;p&gt;$xlsFile= 'file_'.date('Y-m-d').'.xls'; &lt;br ?-->$separator = "t";</pre>
$sql = "Select * from emp";
$Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password) or die("Failed to connect:
" . mysql_error() . "
");

$Db = @mysql_select_db($DB_DBName, $Connect) or die("Failed to select db:
" . mysql_error(). "
" );

$res = @mysql_query($sql,$Connect) or die("Failed to execute :
" . mysql_error(). "
" );

header("Content-Type: application/xls");
header("Content-Disposition: attachment; filename=$xlsFile");
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Cache-Control: private", false);
header("Content-Transfer-Encoding: binary");

for ($i = 0; $i&lt;mysql_num_fields($res); $i++) {
echo mysql_field_name($res, $i) . "t";
}
print("n");

while($row = mysql_fetch_row($res))
{

$insert = "";

for($j=0; $j&lt;mysql_num_fields($res); $j++)

{

if(!isset($row[$j])) {

$insert .= "NULL".$separator;

}

elseif ($row[$j] != "") {

$insert .= "$row[$j]".$separator;

}

else {

$insert .= "".$separator;

}
}
$insert = str_replace($separator."$", "", $insert);
$insert = preg_replace("/rn|nr|n|r/", " ", $insert);
$insert .= "t";
print(trim($insert));
print "n";
}
?&gt;

Note: IF you get errors or in excel all the data is in one column, then instead t tab space use, comma ‘,’.

Share me!