Convert MySQL Query Result To Excel
Using PHP to convert MySQL query result to Excel format is also common especially in web based finance applications. The finance data stored in database are downloaded as Excel file for easy viewing. There is no special functions in PHP to do the job. But you can do it easily by formatting the query result as tab separated values or put the value in an HTML table. After that set the content type to application/vnd.ms-excel
<?php
include ‘library/config.php’;
include ‘library/opendb.php’;$query = “SELECT fname, lname FROM students”;
$result = mysql_query($query) or die(‘Error, query failed’);$tsv = array();
$html = array();
while($row = mysql_fetch_array($result, MYSQL_NUM))
{
$tsv[] = implode(“\t”, $row);
$html[] = “<tr><td>” .implode(“</td><td>”, $row) . ”</td></tr>”;
}$tsv = implode(“\r\n”, $tsv);
$html = “<table>” . implode(“\r\n”, $html) . “</table>”;$fileName = ‘mysql-to-excel.xls’;
header(“Content-type: application/vnd.ms-excel”);
header(“Content-Disposition: attachment; filename=$fileName”);echo $tsv;
//echo $html;include ‘library/closedb.php’;
?>