Exporting to Excel

[PeDa]

I export php data to Excel using the following script:

$db = mysqli_connect(HOST, USER, PASS) or die(mysqli_error($db));
mysqli_select_db($db, DBNAME);
$q="SELECT * FROM foo";
$result = mysqli_query($db,$q) or die();
$tsv = array();
while ($row = mysqli_fetch_array($result, MYSQLI_NUM))
{
   $tsv[] = implode("\t", $row);
}
$tsv = implode("\r\n", $tsv);
$fileName = 'myfile.xls';
header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=$fileName");
echo $tsv;
mysqli_free_result($result);
mysqli_close($db);

This give a pop-up message 'The file format and extension of ... do not match ... Do you want to open it anyway?' If, however, I select 'yes' here the file opens normally, so what is the problem?

 

[spamjim]

It looks like you're dumping out tab separated data that would be better suited as TXT or CSV format. MS Excel recognizes this is what you're actually feeding it. However, it is generously allowing you to open the alleged XLS file anyway.

The XLS (XLSX) format is a bit more advanced than what you are feeding it. See these other options to better prepare XLS/XLSX data.

https://learncodeweb.com/web-development/export-an-excel-file-with-phpexcel/

https://www.phpclasses.org/blog/post/253-30-Top-Excel-PHP-Classes.html

... or just live with the warning message when Excel opens the file.

 

[PeDa]

Yes, thank you Spamjim; I realise that now. I shall implement your last suggestion whilst investigating the other ones.