0

I'm using PHPExcel on a restricted server running PHP 5.6.

I have some Japanese characters that I'm trying to include in a CSV and Excel output. Seeing as there's potential to have the same output also in HTML or PDF I'm using PHPExcel to build all the data and just changing the writer depending on the request.

I've managed to get CSV export to open successfully in Excel by using $objWriter->setUseBOM(true) however exporting as an Excel file I'm getting the Japanese characters replaced with nonsense, such as:

éÂÂä¿¡ãÂÂãÂÂãÂÂＩＤã¡ã¼ã«ã«ＩＤã®è¨Âè¼ÂãÂÂãªãÂÂ

From what I've read my best bet is to convert the string to UTF-16LE, however, I don't have access to mb_string or iconv and I'm struggling to see how else I can solve this. I wondered whether Excel2007 would have the same issues, but I don't have access to php_zip or to zlib in order to use PCLZip. I think I'm screwed.

Does anyone have any idea how I can get Excel to correctly interpret these characters?

CyberneticianDave
  • 162
  • 1
  • 9
  • PHPExcel works purely with UTF-8; setting anything to UTF-16LE will not work – Mark Baker Jun 06 '17 at 09:34
  • Fair enough, I must have misunderstood some of the other answers I've seen on SO. Some seem to suggest converting between UTF-8 and UTF-16LE and some also seem to suggest going the other way around (but those are all relating to CSV files and not Excel files). – CyberneticianDave Jun 06 '17 at 09:37
  • Forgot to tag you @Mark, thanks for your clarification. If I save the CSV as an XLS within Excel it's working fine. Also `PHPExcel_Shared_String::IsUTF8` says that my string is already UTF-8, so why would they be getting garbled? Is there some equivalent of the `$objWriter->setUseBOM(true)` that I need to use for Excel5? – CyberneticianDave Jun 06 '17 at 09:49
  • There is no equivalent to use BOM for Excel5, because it's explicitly defined as UTF-8 and PHPExcel doesn't allow this to be changed. All conversion between UTF8 and BIFF8 Unicode is handled internally – Mark Baker Jun 06 '17 at 09:51
  • So why/how are my japanese characters getting garbled? There must be something I'm missing. Do you have any tips where I should look or what I should search for in Google to help me find the problem @MarkBaker? At some point the UTF-8 encoded string is ceasing to be UTF-8 or at least not _handled_ as UTF-8. – CyberneticianDave Jun 06 '17 at 11:15
  • That I don't know for certain.... internally, the Excel5 Writer converts that UTF-8 to MS Excel's dual-byte BIFF8 encoding using `PHPExcel_Shared_String::UTF8toBIFF8UnicodeLong()`, which does require mb_string or iconv to be enabled, and can have problems with Asian phonetics – Mark Baker Jun 06 '17 at 11:31
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/145968/discussion-between-cyberneticiandave-and-mark-baker). – CyberneticianDave Jun 06 '17 at 12:12

1 Answers1

0

In my case, the problem is the lack of mb_convert_encoding or iconv on the server. Without those, the encoding from UTF-8 to Biff8 (see PHPExcel_Shared_String::UTF8toBIFF8UnicodeLong) doesn't happen correctly.

In this case PHPExcel_Shared_String::ConvertEncoding simply returns the original character, which will not make sense in Biff8 - hence the issues experienced.

A standalone converter would need to be written, which is possible but is excessive for this particular situation where we can simply fall back to CSV. If anyone else gets this problem I would first check for the presence of mb_convert_encoding or iconv and, if possible, install those.

CyberneticianDave
  • 162
  • 1
  • 9