What that code actually does is create a “tab separated values” file (a CVS, with tabs) and lie about the file type, which causes Excel to open it. It isn’t really an .xls until Excel imports it into a spreadsheet. That is why you get the message about the file format not matching. It probably works fine, unless your data has tabs in it.
I don’t think you are going to get this to work using exec-php though. You need to send http headers at the beginning of the transmission and exec-php operates in the post body, long after the headers have been sent.
Hmm, interesting, so this code doesn’t seem like it’ll work out. Can you point me to some other code that will let me generate an excel file without this header trick?
I seem to have solved my own issue…somewhat.
It appears that there are two ‘hacks’ going on in these PHP->Excel scripts. The first is as you described, changing the header so that the browser thinks it’s downloading an Excel file instead of an HTML file or something like that. The second hack is that the file that’s generated is not, in fact, an Excel file, but instead a .csv (again, as you described) which Excel will open, albeit with that message.
To solve the first issue, I just have WordPress call a different .php, so that the headers will be sent again. In other words, instead of copying the contents of test.php into WordPress, I have a link in the WordPress page to test.php. Clicking on that link launches a download of the Excel file. I can now modify test.php to use my real data instead of the test data. The second issue of the pop up message about the incorrect format remains, although I’ve just added a line on my site that says “Note: you will receive a message saying that this file may not be in the correct format, simply click ‘OK'”.
Through my research, I’ve found that the ‘correct’ way to generate an Excel file is through a COM add-in. It requires Excel to be installed on the server, however. There is an OpenOffice alternative called PUNO, which requires Java. Both of these options would seemingly solve both issues, as neither one is a header/filetype hack, but I haven’t messed with either.
Through my research, I’ve found that the ‘correct’ way to generate an Excel file is through a COM add-in. It requires Excel to be installed on the server…
I’d argue that that is not the ‘correct’ way to do it. You are vendor-locking your code. There are PHP libraries that will create proper .xls or .ods files. I’ve been using a rather old on called ExcelExport but I need to move to .ods since that is what the office uses now. (Yay! Me!)
