Jump to content


Photo

php mysql and excel


  • Please log in to reply
2 replies to this topic

#1 DarkReaper

DarkReaper
  • Members
  • PipPipPip
  • Advanced Member
  • 36 posts

Posted 08 March 2006 - 06:28 PM

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";
else{
$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 );

?>

#2 DarkReaper

DarkReaper
  • Members
  • PipPipPip
  • Advanced Member
  • 36 posts

Posted 08 March 2006 - 06:43 PM

errr now it works ... dunno how or why but it does :)

#3 lessthanthree

lessthanthree
  • Members
  • PipPipPip
  • Advanced Member
  • 85 posts
  • LocationUK

Posted 08 March 2006 - 07:55 PM

hehe.

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.
call me a safe bet, i'm betting i'm not




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users