Recently in Perl Category

UPDATE: Spreadsheet::WriteExcelXML is now deprecated in favor of Excel::Writer::XLSX, which contains a freeze_panes() method since v0.10 (February 17, 2011).

So there I was, working on a program that generates an Excel report. Whenever I have to write a program that interfaces with Excel (or, more generally, when I need COM automation), I work with Win32::OLE, and I have quite a deal of experience doing so. However, I have always found VBA's object model too counterintuitive and often find myself trying things based on hunches rather than common sense; the combination of Excel's macro recorder and ActiveState's VBScript Converter helps to ease the pain, but... Anyway, I didn't want to rely on a local Excel installation to get the job done. On the other hand, I wanted to take a multiplatform approach this time, so I decided to try Spreadsheet::WriteExcel. This module works fine and has an impressive documentation, chock-full of examples (screenshots and all!), but I got stuck when I tried to find a way of adding several different formatting features to the cells' content (say, words in different colors). I contacted the module's author, who replied quickly and informed me that this feature was not supported for technical reasons, but suggested that I could use a related module, Spreadsheet::WriteExcelXML, which provides the convenient write_html_string function for this purpose. The idea of generating XML output instead of the typical binary format seemed sexy enough, so I adapted the code to the new module.

But then I found another problem. I wanted to freeze the headers row and the freeze_panes() method didn't seem to work as it worked with Spreadsheet::WriteExcel. I read again the documentation and found out to my dismay that it's not yet supported in the XML version (although it will eventually be implemented). I could try to implement it myself, but I don't know well enough this XML vocabulary and it would take me far more time than I can afford right now. So I created a poor man's freeze panes functionality --I always wanted to create a poor man's version of something (just for the sake of using that buzzword); actually, I have done it many times, but never on purpose. So, here is the procedure I followed: I froze panes manually on a sample report open in Excel, and saved it. Then I compared the new file and the original one to get the lines added, and modified my code to insert that patch in a second pass into every automatically generated report:

my $patch = <<'END';
    <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
        <Selected/>
        <FreezePanes/>
        <FrozenNoSplit/>
        <SplitHorizontal>1</SplitHorizontal>
        <TopRowBottomPane>1</TopRowBottomPane>
        <ActivePane>2</ActivePane>
        <Panes>
         <Pane>
          <Number>3</Number>
         </Pane>
         <Pane>
          <Number>2</Number>
          <ActiveRow>2</ActiveRow>
         </Pane>
        </Panes>
        <ProtectObjects>False</ProtectObjects>
        <ProtectScenarios>False</ProtectScenarios>
    </WorksheetOptions>
    <x:FreezePanes x:Range="A2">   </x:FreezePanes>    
END

open my $in, '<:encoding(UTF-8)', $log_path;
my $text = do { local $/; <$in> };
close $in;

chop($patch);
$text =~ s/<\/Table>/<\/Table>\n$patch/;

open my $out, '>:encoding(UTF-8)', $log_path;
print $out $text;
close $out;

Yes, it's a dirty trick, but it works!

The latest update of the documentation of one of my CPAN modules, String::CaseProfile, includes sample text in alphabets other than the Roman alphabet (namely, Armenian, Greek, and Cyrillic).

So I had to convert the encoding to UTF-8 and add the use utf8 pragma, but while checking the doc of several CPAN modules containing non-Latin1 Unicode characters, I noticed that for many of them, these characters were not displayed correctly. I wanted to make sure that everything was OK before the release, so I asked Joaquín Ferrero, a walking encyclopedia of Perl and administrator of the Spanish Perl Forum (sort of PerlMonks for the Spanish-speaking community), and he spoke with the wisdom that can only come from experience: Adding the use utf8 pragma is not enough. You also have to add the following line at the beginning of the Pod text:

=encoding utf8

This way, CPAN's Pod processor knows what is the encoding used in the module and transforms accordingly these Unicode characters into the corresponding HTML entities that will be displayed correctly with the ISO-8859-1 encoding used by default for CPAN documentation in HTML format.

When you think about it, it's quite logical, but the fact is that there are many CPAN modules that don't get this right.

 

When you try to install X11::GUITest on a fresh installation of Ubuntu Linux, you will get the following error:

GUITest.xs:41:27: error: X11/Intrinsic.h: No such file or directory

GUITest.xs:42:28: error: X11/StringDefs.h: No such file or directory

make: *** [GUITest.o] Error 1

 

To fix it, you must install first the X.Org X Window System development libraries: 

 

sudo apt-get install xorg-dev

 

And then issue again the usual installation commands:

 

perl Makefile.PL

make

make test

make install

I have notified this issue to the module author, and asked him to update the Readme.

WWW::Translate::Apertium v0.10 is now available on CPAN.

Here is an interesting piece of information on how to avoid problems while editing UTF-16|32 files using Perl.
Some time ago I wanted to read an UTF-16LE-encoded XML file, modify some strings, and then write out the
updated version. I followed the guidelines to transform the encoding to Perl's internal format using PerlIO:

open my $in, "<:encoding(UTF-16LE)", $in_path;
# some code goes here
open my $out, ">:encoding(UTF-16LE)", $out_path;

but couldn't make it work right this way. The extended characters weren't displayed correctly. After doing some research on the Internet, I found a solution in this thread of the perl-unicode list. Dan Kogai mentions that BOMed UTF files are not suitable for streaming models, and they must be slurped as binary files instead:

# read file
open(my $in, '<:raw', $in_path) || die "Couldn't open file: $!";
my $text = do { local $/; <$in> };
close $in;

# now decode to use character semantics (no need to specify LE or BE when reading)
my $content = decode('UTF-16LE', $text);
my @lines = split /\n/, $content;

# some code that turns @lines into @processed goes here

# write file
my $output_str = join "\n", @processed;

open my $out, '>:raw', $out_path;
print $out encode("UTF-16LE", $output_str);
close $out;

and that did the trick.