The following is by John McNamara. If you do anything with getting data to users that winds up in Excel, you owe it to yourself to look at John’s module.
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:
type => 'cell',
format => $light_red,
criteria => '>=',
value => 50,
This will result in output like the following, full example here:
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.
John McNamara is a software engineer living in Ireland. Once a month he drinks a pint of Guinness with Tim Bunce to discuss Perl and open source. He spends a lot of time looking at hexdumps so that you don’t have to.