October 2011 Archives

Spreadsheet::WriteExcel is dead. Long live Excel::Writer::XLSX.

Last week I released a new version of Excel::Writer::XLSX to CPAN that was 100% API compatible with Spreadsheet::WriteExcel. This marked a milestone as I am now able to move past WriteExcel's feature set and move on to new features that I wasn't able to support previously.

This was achieved with 30 CPAN releases in almost exactly a year. By comparison, WriteExcel took almost 10 years. This gives a fair indication of the disparity of effort required to implement a feature in the pre-Excel 2007 binary xls format as opposed to the new XML based xlsx format.

So, from now on, all new features will go into Excel::Writer::XLSX and Spreadsheet::WriteExcel will be in maintenance mode only.

The first of the new features, conditional formatting, was added yesterday. For a long time has been the most frequently requested feature for WriteExcel but it was always too big a feature to implement in the available time that I had.

With Excel::Writer::XLSX you can now add a format like the following:

$worksheet1->conditional_formatting( 'B3:K12',
    {
        type     => 'cell',
        format   => $light_red,
        criteria => '>=',
        value    => 50,
    }
);

This will result in output like the following, full example here:

conditional_format.jpg

Excel::Writer::XLSX has also been designed differently from Spreadsheet::WriteExcel to allow it to implement some features that previously weren't possible (or at least easy). One of these is the separation of the data and the formatting.

It was a common assumption with new users of WriteExcel that you could write data to a spreadsheet and then apply the formatting afterwards. However, for design reasons related to performance and Excel's file format this wasn't easily implemented. With Excel::Writer::XLSX the back-end architecture is different and this type of feature is not only possible but will be added soon.

If you are a user of Spreadsheet::WriteExcel then now is probably a good time to try out Excel::Writer::XLSX so you can get the new features when them come on-line.

About John McNamara

user-pic Just another Perl hacker