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

Hallo all ,
Greetings to you ,
I am trying to write french accented characters é è ê in
Excel worksheet using my perl script , But I am stuck
here as I couldnt find a way of writing it !:

My code:

use strict;
use warnings;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
my $Excel = Win32::OLE->GetActiveObject('Excel.Application')|| Win32::OLE->new('Excel.Application', 'Quit'); my $excel_file = 'd:/excelfile.xls';
And it goes on, where I am reading the french text from an
xml file and I am writing it into my Excel worksheet
using this :

$sheet->Cells($i,8)->{'Value'}="$temp"; Where $temp has the french text .. I see that It reads
correctly from the xml file.
my $xmlsource3 = "LAX03fr.xml";
open IN3, "<:utf8", $xmlsource3 or die "Can't Read Source
file $xmlsource3: $!\n";
There was a problem before with reading also but when I
added this "<:utf8" tag in there the problem got solved and
now it reads the characters correctly..

Also i printed the $temp in the output window and it shows
the accented characters correctly , only when it
writes to excel i cannot see the accented characters
and instead I see ,something like this in my excel ! ---> Unité
How can I solve this problem ?
Please help me guys , any help would be greatly appreciated.
thanks a lot, in advance,
shintu

  • Comment on Problem with Writing French Accented Characters in Excel using Perl!

Replies are listed 'Best First'.
Re: Problem with Writing French Accented Characters in Excel using Perl!
by almut (Canon) on Sep 06, 2007 at 13:57 UTC

    Looks like your Excel doesn't understand UTF-8 encoded strings. So, you could try to convert your $temp into one of the other likely candidate encodings, which are "UCS-2LE" (or "UTF-16LE" for that matter... though Windows more often uses UCS-2), or "cp1252" (the Windows variant of the legacy encoding "iso-8859-1").

    In other words, before you set your cell's value, insert a line 

    $temp = encode("UCS-2LE", $temp);

    [download]

    or 

    $temp = encode("cp1252", $temp);

    [download]

    and see if that helps...

    The routine encode() is from the module Encode, so you'll need to add "use Encode qw(encode);" somewhere near the beginning of the script.

[reply]
[d/l]
[select]
      Wow , Thanks a lot almut,if you were here I can give you a big
      hug! :) Tried lot of things for two days without success
      , but this totally works and now the problem is solved :)
      Love you monks !
      P.S Sorry , it was me above who thanked you , and never
      noticed that I havent logged in !
      Have a nice evening,
      Shintu
[reply]
      Wow , Thanks a lot almut,if you were here I can give you a big hug! :) Tried lot of things for two days without success , but this totally works and now the problem is solved :) Love you monks ! Have a nice evening, Shintu
[reply]
Re: Problem with Writing French Accented Characters in Excel using Perl!
by jmcnamara (Monsignor) on Sep 06, 2007 at 14:21 UTC

    This has come up before, see Re: Re: Using Win32::OLE and Excel - Tips and Tricks.

    Here is a small working example, using the CP_UTF8 directive, that doesn't require any further conversion after reading in the data in utf8 format: 

        #!/usr/bin/perl -w


    use strict;
    use Cwd;
    use Win32::OLE qw(CP_UTF8);
    use Win32::OLE::Const 'Microsoft Excel';


    my $application = Win32::OLE->new("Excel.Application");
    my $workbook    = $application->Workbooks->Add;
    my $worksheet   = $workbook->Worksheets(1);


    # Make Excel handle UTF8.
    $Win32::OLE::CP = CP_UTF8;


    my $file = 'french_utf8.txt';

    open FH, '<:encoding(utf8)', $file  or die "Couldn't open $file: $
+!\n";


    my $row = 1;

    while (<FH>) {
        chomp;
        $worksheet->Cells($row++,1)->{Value} = $_;
    }

    $workbook->SaveAs({
                        FileName   => cwd() . '/test.xls',
                        FileFormat => xlNormal,
                      });
    $workbook->Close;

    [download]

    Spreadsheet::WriteExcel also handles utf8 text, see the example here.

    --
    John.

[reply]
[d/l]

Back to Seekers of Perl Wisdom