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

Example : convert.php
Source : convert.php, students.txt

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[] = "" .implode("", $row) . "";
}

$tsv = implode("\r\n", $tsv);
$html = "

" . implode("\r\n", $html) . "";

$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';
?>

In the above example $tsv is a string containing tab separated values and $html contain an HTML table. I use implode() to join the values of $row with tab to create a tab separated string.

After the while loop implode() is used once again to join the rows using newline characters. The headers are set and the value of $tsv is then printed. This will force the browser to save the file as mysql-to-excel.xsl

Try running the script in your own computer then try commenting echo $tsv and uncomment echo $html to see the difference.