CODEWITHSUNDEEP
windowsperlexcelencoding
VolatileRig
VolatileRig

Reputation: 2847

Putting UTF-8 characters into Excel with Perl?

I have XML documents that have many characters that are UTF-8 such as "É" and "é", and I am trying to put these into Excel, but there is something with the encoding that I am just not getting. I am using Win32::OLE to put the data in Excel.

I have tried this:

use Unicode::String qw(utf8 latin1 utf16le);
my $u = utf8($content);
$output = $u->utf16le;

but the only thing that shows in the Excel cells is the first character of the string (correctly encoded). What am I doing wrong here?

Upvotes: 1

Views: 2601

Answers (2)

bvr
bvr

Reputation: 9697

You need to enable utf8 coding using Win32::OLE->Option call. The code below works for me (for Eastern-Europe characters):

use utf8;
use Win32::OLE qw(CP_UTF8);

Win32::OLE->Option(CP => CP_UTF8);      # set utf8 encoding

my $excel = Win32::OLE->new('Excel.Application') or die $!;
$excel->{Visible} = 1;

my $wb    = $excel->Workbooks->Add;
my $sheet = $wb->Sheets(1);

$sheet->Range('A1')->{Value} = 'Nějaký český text ďťň';

Upvotes: 4

VolatileRig
VolatileRig

Reputation: 2847

I seem to have found out why. I simply tried:

$output = $u->latin1;

and that worked perfectly, so I assume that is what encoding Excel uses. The only-showing-first-character part was probably because in utf-16, each character is ended with a null char \0 telling Excel that that's the entire string. (that's just an assumption)

Upvotes: 2

Related Questions