问题将数据库查询结果导出到Excel

Question

I need to export the results of a MySQL query to an Excel file. I picked up some code to achieve this, but there is one issue. The code sends the header information to the browser with the .xls content type and the query results following the headers. The code for the called page is as follows:

<?php
function cleanData(&$str)
{
$str = preg_replace("/\t/", "\\t", $str);
$str = preg_replace("/?
/", "\
", $str);
if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"';
}

// filename for download
$filename = "customer_inventory_" . date('Ymd') . ".xls";

header("Content-Disposition: attachment; filename=\"$filename\"");
header("Content-Type: application/vnd.ms-excel");

$flag = false;
$query = "SELECT customer_id,sku,qtyoh,date FROM sims_customer_inventory WHERE   customer_id=$customer_id and date=(select max(date) from sims_customer_inventory)";
$result = mysql_query($query, $conn) or die(mysql_error());
while(false !== ($row = mysql_fetch_assoc($result))) {
if(!$flag) {
  // display field/column names as first row
  echo implode("\t", array_keys($row)) . "
";
  $flag = true;
}
array_walk($row, 'cleanData');
echo implode("\t", array_values($row)) . "
";
?>

The problem is that when the page is called in this form:

<form action="inventory_download.php" method="get" onsubmit="return validateInventoryform();">

The file does not automatically download. However, when I refresh the page the file downloads. My problem is that when the page is called this should automatically execute the code to download the file. It does not do that. I have looked all over the internet to solve the problem, but I can't find out why it does not work.

Answer 1

This problem is caused by the passing of ajax data to the external page that creates the .xls data. The answer is to turn off the ajax data in the link:

<a href="something.php" data-ajax="false">

That will allow the php code to work properly. There is still the problem that this is not actually a valid Excel file. This code creates a header and follows it with data that is interpreted as an Excel file. It is not an Excel file, but it does open in Excel and then it can be saved as an Excel file. That is an important distinction. If you are in need of an actual Excel file then PHPExcel is what you should use. That is probably the best option.