Ever wondered how to make Microsoft Excel open UTF8 encoded CSVs properly? Me, too.
Recently we had a problem with a client whereby their MySQL DB was UTF8, all controllers, models and views were UTF8, the Content-Transfer-Encoding was UTF8, but still Excel converted them to ISO Latin 1.
The solution took me 90 minutes of Googling and trial and error, but I got there.
MS Excel will not open UTF8 documents as such, but does recognise the Little Endian BOM. So let’s convert the UTF8 data to UTF16LE:
$out = iconv ( 'UTF-8', 'UTF-16LE//IGNORE', $out ); // your data needs to be UTF-8 encoded...
Where $out is the data you wish to display in Excel.
This was the step that took a while. Playing around with declared character encodings and file character encodings, but what we have is the following:
header("Content-Disposition: attachment; filename=$xlstitle.xls");
echo chr(255).chr(254);echo $content_for_layout;
Where $content_for_layout is your Excel data (I am using CakePHP here).
The two first characters are the UTF16 Little Endian BOMs (Byte Order Marks). Make sure you save your file as a UTF8 file and the job is done.