Spreadsheet::WriteExcel with UTF-8 text

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

I have some UTF-8 text (primarily a mixture of English and Japanese) which I am trying to write out to a spreadsheet. This gives me acceptable text for English, but garbage for Japanese.

According to the Spreadsheet::WriteExcel documentation, I need to encode in UTF-16BE. I have tried using Unicode::String's utf16 method but this also does not give me the correct results.

Is there someone who has dealt with this problem who can offer some advice?

Thank you.

Comment on Spreadsheet::WriteExcel with UTF-8 text

Replies are listed 'Best First'.
Re: Spreadsheet::WriteExcel with UTF-8 text by jmcnamara (Monsignor) on Mar 03, 2005 at 10:04 UTC
With the latest versions of Spreadsheet::WriteExcel and Perl 5.8 you can use the UTF-8 text directly. See this Japanese example in the distro and the text file that it reads. With older versions of Perl you will have to use a module to convert the UTF-8 to UTF-16BE. See also this example. Note you may also have to specify a Unicode font (although this is generally the default with newer versions of Excel). If you dealing with UTF-8 data I would strongly recommend using Perl 5.8 if you aren't already. -- John.	[reply]
Re^2: Spreadsheet::WriteExcel with UTF-8 text by Anonymous Monk on Mar 03, 2005 at 10:56 UTC
Thank you very much for your help. I believe I have identified the problem...not having much experience with Windows, I didn't consider the question of whether the Excel version would be able to display Japanese characters. As your example spreadsheet gives the same type of results as my spreadsheet, I think the problem lies with interpretation, not writing. Once again, thank you.	[reply]
Re^3: Spreadsheet::WriteExcel with UTF-8 text by jmcnamara (Monsignor) on Mar 03, 2005 at 11:20 UTC
Some versions of Excel aren't installed with Unicode fonts by default. If you have one installed but Excel isn't associating it with your strings you tell it to by adding something like this to your program: `... # Set a Unicode font. my $uni_font = $workbook->add_format(font => 'Arial Unicode MS'); $worksheet->write('A1', $utf8_str, $uni_font); ...` [download] -- John.	[reply] [d/l]
Re^4: Spreadsheet::WriteExcel with UTF-8 text by Anonymous Monk on Mar 03, 2005 at 11:42 UTC
Re^5: Spreadsheet::WriteExcel with UTF-8 text by dragonchild (Archbishop) on Mar 03, 2005 at 13:27 UTC
Re^2: Spreadsheet::WriteExcel with UTF-8 text by Anonymous Monk on Mar 03, 2005 at 10:46 UTC
I should have specified: I am using Spreadsheet::WriteExcel version 2.11 and Perl version 5.8.5. As this appears to be the most recent WriteExcel version, I intend to test your example to see if the fault might lie elsewhere. Thank you for your suggestions.	[reply]