reading Unicode from Excel

[nagilum]

I'm trying to read Unicode strings from an Excel(tm) sheet, unfortunately all I'm getting is '???' instead of the the 3 letter Unicode string. I'm using ActiveState Perl 5.6.1 and in the beginning of my script you will find:

use strict;
use Spreadsheet::ParseExcel;
use Spreadsheet::WriteExcel;
use utf8;

then I use the usual

my $cell = $oWkS->{Cells}[$iR][0];
my $section=$cell->Value;

as this didn't work I also tried:

use strict;
use Spreadsheet::WriteExcel;
use Cwd;
use Win32::OLE;
use Win32::OLE::Variant;
use Win32::OLE::Const 'Microsoft Excel';
use utf8;

but $worksheet->Cells($iR,1)->{'Value'}

returns the same '???' as did the previous version. (And yes, I did {use bytes; print length($value) } to see if it's not just a terminal problem. )
Looks like I'm forgetting about something :-\ .

[bart]

Actually it's quite easy to get UTF-8 through Win32::OLE out of Excel. All you have to do is tell Excel (or OLE, I'm not sure) that you want UTF-8. Let me pull out some old project code...

use Win32::OLE 'CP_UTF8';
Win32::OLE->Option(CP => CP_UTF8);

With that, all data you get out of Excel should be in UTF-8.

[nagilum]

Thanks! That was exactly it!
Where can I find stuff like that, it's not in the docs I've read, that's for sure :-{ .

[Anonymous Monk]

Hi i tried with this code but still ????? comes can you help; [e-mail] use OLE; use Win32::OLE 'CP_UTF8'; use utf8; Win32::OLE->Option(CP => CP_UTF8); $xlfile ="c:\\Akruti Tamil Unicode Test Vectors.xls"; ##### OLE - Excel Connection # Create OLE object - Excel Application Pointer $xl_app = CreateObject OLE 'Excel.Application' || die $!; # Set Application Visibility # 0 = Not Visible # 1 = Visible $xl_app->{'Visible'} = 0; # Open Excel File $workbook = $xl_app->Workbooks->Open($xlfile); # setup active worksheet $worksheet = $workbook->Worksheets(1); #///////////////////////////////////////////opened xls file for reading. # retrieve value from worksheet print $worksheet->Range("B2")->{'Value'};