For many years I assumed generating a CSV file is the best option if you asked to implement an “export to Excel” in your PHP web application. Writing CSV files with PHP is plain simple and does not involve using complicated or proprietary libraries, the format could be also read by many applications other than Excel and after all the output is a plain-text file, which you can review with any text editor if something goes wrong. Basically, all you need is fputcsv PHP function, that takes a provided data array and writes it as well-formatted CSV row to the output file.
That all worked fine for me, until one day a client told me that CSV files we produce does not really import to Microsoft Excel. It turns out that Excel has two ways to import CSV file:
- Text Import Wizard, which is invoked by clicking From Text on the Data tab of modern Excel versions. In this case you’re prompted to specify file encoding, delimiters and other settings.
- Regular file import, which is performed if you double click a file in Explorer or used File -> Open menu. In this case Excel tries to auto-recognize file format details.
Our problem was that the regular file import didn’t work for files we generated with PHP. Text import wizard worked with correct settings but users didn’t want to go into file format details and after all I found it also doesn’t work right for some files.
After a lot of research, trials and errors, I found that was caused by few limitations MS Excel applies to CSV format, which are not supported by original PHP fputcsv function:
- Excel expects all rows ended by CR and LF characters, while fputcsv on Linux etc places LF character only.
- Excel expects multiline values to use CR character as a line break.
- Excel has a limit of 32767 characters per single cell.
First two points I learned from this answer at Stack Overflow, which for some reason is not marked as correct one.
Still, the issues above is not a reason to switch from CSV to a native MS Excel format, writable via some library or PHP extension. With a little work, you can get your CSV files compatible with MS Excel:
- Replace all LF characters or CR LF combinations with CR characters in the array items you’re passing to fputcsv function.
- Ensure all array items you’re passing to fputcsv function do not exceed 32767 characters or trim them in any way possible.
- Replace LF characters fputcsv put at line ends with CRLF.
Unfortunately, fputcsv function still does not support any way of altering line break characters so you only can rewrite them as following:
fputcsv($fp, $line); fseek($fp, -1, SEEK_CUR); fwrite($fp, "\r\n");
Or, you can use a function from this comment. However, it does not incorporate first two tips, so you may need to alter it or find another alternative function. There are some available.
After fixing the issues your CSV files will open in MS Excel w/o any problem.