通过php问题将数据导出到.xls

Question

I am exporting data to a .xls file through php and having the following issue: when I export few rows, it's working ok, but when I export a large amount of rows, the spreadsheet is downloaded as a .php file.

A summary of my code (named export.php):

header("Content-type: application/vnd.ms-excel;");

echo 'Column1' . "\t" . 'Column2' . "\t" . 'Column3' . "
";

while($data = $sql_data->fetch(PDO::FETCH_ASSOC)){
   echo $data['column1'] . "\t" . $data['column2'] . "\t" . $data['column3'] . "
";
}

header("Content-disposition: attachment; filename=file.xls");

When the exported spreadsheet is very large, it comes with the filename and extension of the php script (export.php) and yet I can open it with Microsoft Excel. So, I only wish it to come with the right filename and extension (file.xls) regardless of file size.

Any idea?

Answer 1

You should put your header calls at the top. If your code accidentally outputs anything, all your headers will get flushed. And that's why it's downloading as php. The header call that defines the "file.xls" filename and download-as-attachment behavior is below the while loop.

Header calls need to come before any data is output to the screen.

You can read more about php's Output Controls here.

header("Content-type: application/vnd.ms-excel;");
header("Content-disposition: attachment; filename=file.xls");

echo 'Column1' . "\t" . 'Column2' . "\t" . 'Column3' . "
";
while($data = $sql_data->fetch(PDO::FETCH_ASSOC)){
   echo $data['column1'] . "\t" . $data['column2'] . "\t" . $data['column3'] . "
";
}

As far as why your code does this on large files, is probably because you're exceeding your maximum buffer size, and it just dumps whatever it has until that point. Either increase your memory limits in PHP, or create smaller spreadsheets ;)