How to output euro sign with Spreadsheet::WriteExcel from a MSSQL Server nvarchar column

I have text with € signs in a nvarchar column on a MSSQL 2008 Server and want开发者_Python百科 to dump that text via Spreadsheet::WriteExcel into an Excel file but I always get some wired sign out instead of €.

Until now I put my string together in perl directly and used always something like

my $euro = chr 0x20AC;
$worksheet->write($currentline, $currentcolumn, "Some text here".$euro ,$format);

which always worked.

Any hints?

• ActivePerl v5.10.0
• Windows
• MSSql Server 2008
• Access per ADO => Win32::OLE->new("ADODB.Recordset");

Unicode handling in Spreadsheet::WriteExcel is relatively straightforward: if the string you are trying to write is in utf8 format then it gets written to the Excel file exactly as you would expect.

Ensuring the strings you are writing are in utf8 format isn't always as straightforward.

To resolve the issue you just need to figure out what encoding the strings coming from MSSQL are in and then use the Encode module to convert them to utf8. For example:

use Encode 'decode';

my $string = 'some string with koi8-r characters';
   $string = decode('koi8-r', $string);

$worksheet->write('A1',  $string);

Have a look at the unicode perldocs linked in the WriteExcel section above for some background.

It is entirely possible that this is a charset issue. The euro sign is not well-supported, and Microsoft formats can be a crapshoot when it comes to charsets.

I don't have an actual solution, but figuring out what WriteExcel is trying to do with your charset and what Excel actually supports would be a good start. For what it's worth, Perl stores strings as Unicode by default, and if you want UTF-8 you need to do the conversion explicitly.

For info about Perl and charsets, check out the man page for JSON::XS which is oddly the best resource out there. http://search.cpan.org/~mlehmann/JSON-XS-2.3/XS.pm