FreezePanes for Desperados

| No Comments | No TrackBacks
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.

 

Installing X11::GUITest on Ubuntu Linux

| No Comments | No TrackBacks

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.

Hard Disk Head Crash

| No Comments | No TrackBacks

One month ago, shortly after the YAPC::EU 2009, my MacBook's hard disk died due to a head crash with only 2 years and 2 months. I used it intensively, but didn't expect such a short life.

 head_crash.jpg

                                                          Farewell, Seagate ST9160821AS

It could have been worse (e.g., a battery blast on my lap... ouch! I always use a lapdesk since I considered this possibility, not as remote as you might think, given the latest news related to explosions of Apple products), but I lost a lot of info, mainly blog posts related to programming. Fortunately, I had a backup of the most important stuff. The really good news is that now I will only need another two hundred years to process what's left (the disk failure trimmed around half a millennium from my backlog).

Anyway, I've already installed a new HD, a Seagate ST9500420ASG with 500 GB capacity and I should receive Snow Leopard anytime soon.

 

WWW::Translate::Apertium Update

| No Comments | No TrackBacks
WWW::Translate::Apertium v0.10 is now available on CPAN.
This update fixes a bug due to a recent change in the I/O format of the Apertium web service.
The developers changed the deformatter from txt to html, so the results contained html entities
instead of the actual extended characters. 
Since there's a lot of activity going on in the Apertium project (it's one of the organizations accepted
into Google Summer of Code 2009), I will set up a daily cron job to check for any changes that may
require an update, to avoid getting caught off guard again.

This update also includes support for new language pairs:
Occitan < > Spanish, Portuguese < > Catalan, Portuguese < > Galician, 
and English > Esperanto (single-direction).

10th Anniversary

| No Comments | No TrackBacks

Ten years ago, I quit a permanent Project Manager position in a software localization company to start a freelance career, with the hope of being able to devote more time to pursue other interests. I don't mind working long hours on my own projects, but a workaholic life was definitely not for me (although, occasionally, I still eat a 'late night pizza' in remembrance of the good ol' karoshi-candidate days.)

I have not fully achieved the downshifting I planned originally, but at least my work-life balance is quite better, and I ended up living a peaceful life close to the beach, in Cambrils, a nice Mediterranean village. That's something to celebrate... Today I will let my wife defeat me again at the tennis court.

Hot Books Basket(s)

| No Comments | No TrackBacks
I got tired of wading through piles of books and papers in my home office.
A little "domestic pressure" and my own sense of uneasiness made me think of possible strategies to turn the cave back into, well, a plain office.
As a first measure, I selected some books I'm not using anymore to give away. I then thought that it would be a good idea to put my 'hot books' (a term I use to refer to the books I'm reading or plan to read soon, and the books I use frequently as a reference) in a basket, next to my reading chair. And so I did.

'Hot Books' Basket #1:

hotbooks1.jpg

It soon became clear that one basket wouldn't be enough. Besides, I still had the left flank available. So I bought another basket and quickly filled it with more books.

'Hot Books' Basket #2:

hotbooks2.jpg
I couldn't fit in all of them... Anyway, the room is so tidy now that I won't have to leave the usual trail of breadcrumbs to find my way back anymore. And the great thing is that now I keep (almost) all of my 'hot books' at my fingertips.

UTF-16 Processing Issue in Perl

| No Comments | No TrackBacks
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.

Code Syntax Highlighting

| No Comments | No TrackBacks
I found an easy way of adding code samples with syntax highlighting to my blog entries, based on a "conversion to html" procedure in Vim. This method is valid for all the languages supported by Vim.

Select the code in the editor and type :TOhtml. This command opens a new buffer containing your code converted to html (including the colors). Now you only have to copy the html code and paste it to your entry.
As an example, here's my solution to Project Euler's #2:

#!/usr/bin/perl -w
use strict;
use feature 'say';
use Memoize;

memoize('fib');

my $n = 1;
my $fib = fib($n);
my $sum = 0;

while ($fib < 1_000_000) {
    if ($fib % 2 == 0) {
        say $fib;
        $sum += $fib;
    }
    $n++;
    $fib = fib($n);
}

say "Result: $sum";


sub fib {
    my $n = shift;
    
    return 1 if $n < 2;
    fib ($n-1) + fib($n-2);
}

As you can see, the code coloring functionality of a default setup of MacVim doesn't recognize the new Perl 5.10 features (like the say function), but the result is quite readable.

NOTE: For an MT4 blog, the entry format should be None (pure HTML).

There is a similar solution for Emacs/XEmacs users. You can find the details here: htmlize.

Movable Type

| No Comments | No TrackBacks
After a slow start, I have resumed work on my blog. The free weblogs provided by Wordpress.com are nice, but I wanted a more flexible solution, so today I installed Movable Type (which is written in Perl) on my server and migrated the blog. So far so good.
Now I'm checking the different options to add code syntax-highlighting functionality to my posts. It seems that the available plugins are outdated and won't work well with MT4, but there are other possibilities...