Creating an Excel file with PHP is not so simple as many online tutorials claim it is, because it requires a certain basic knowledge of how an Excel file is structured. First of all, an Excel file is an XML file. This implies that all the parsing rules of XML apply to this kind of files. In fact, an Excel file must be:
- well-formed and
- with all the XML entities properly encoded
Some tutorials propose to serve Excel files as text/xml in order to benefit of the native support of Internet Explorer to these files. If you serve an Excel file as an XML file (of course we're talking about its content type), IE will automatically open it as the user were viewing the document in Excel. That's fine. However, you must be aware that Excel validates internally its files, so you should create a file that honor its rules. For example, data types inside Cell elements must be in the correct format (Number, String etc.). Excel is really strict with data types. Another thing to bear in mind is the number of columns and rows in a Table element: they must match the number of column and rows you're generating from your database.
Another ugly thing is encoding. As said above, all XML entities must be encoded and, what's more, correctly expanded. Generally, UTF-8 is the best choice, provided that:
- database data is in the correct encoding
- your PHP files are encoded in UTF-8 (without BOM)
Before creating an Excel file, you should make sure that all data in your database is properly encoded. If this is not the case, you must perform a conversion /filtering with PHP, using all entities-related functions (such as iconv() and utf8-encode()) provided by PHP. If you don't take all these details into account, you'll probably bang your head on the desk watching Excel generating an infinite sequence of error logs.