Jump to content

php mysql and excel


Recommended Posts

I am trying to export some mysql data to an xls file. In theory its done but i dont get any "download file" dialog and everything is outputed in the browser window. My guess is that i am messing up the headers but i cant uderstand where. Help!
Here is what i got at the moment:

function xls_format_row( $field ) {
$line = '';
foreach($field as $value){
if(!isset($value) || $value == "") $value = "\t";
$value = str_replace('"', '""', $value);
$value = '"' . $value . '"' . "\t";
$line .= $value;
return trim($line)."\n";

function xls_send( $headers, $rows ) {
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=excelfile.xls");
header("Pragma: no-cache");
header("Expires: 0");
echo $headers."\n".$rows;

mysql_connect('localhost', 'test', '123123');
mysql_select_db('music') or die(exit('no database'));

$result = mysql_query('select artist, track from songinfo');
$count = mysql_num_fields($result);

$headers= '';
for ($i = 0; $i < $count; $i++)
$headers .= mysql_field_name($result, $i)."\t";

$data = array();
while($row = mysql_fetch_row($result)) {
array_push($data, $row);
$xls_rows = '';
foreach( $data as $row )
$xls_rows .= xls_format_row( $row );

xls_send( $headers, $xls_rows );

Link to post
Share on other sites

you'll find that happens sometimes.

In the future...if you;re trying it again, i find the following headers work better

header("Content-Type: application/vnd.ms-excel");
header("Expires: 0);
header("Cache-Control: must-revalidate, post-check=0, pre-check=0);
header("Content-Disposition: attachment; filename=excelfile.xls");

Also, make sure that the default application on your machine for xls files is Excel. I had the same problem because xls was set to open with openoffice. Took me a while to figure that one out.
Link to post
Share on other sites


This topic is now archived and is closed to further replies.

  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.