jmcnamara

Macros are a powerful feature of Excel and are a frequently requested feature for Spreadsheet::WriteExcel and Excel::Writer::XLSX.

It is also a feature that I've wanted to add for some time but it wouldn't be feasible, with current human lifespans, to re-implement Excel's VBA in Perl. So instead I implemented a scheme to "borrow" the VBA code from Excel and add it to an Excel::Writer::XLSX file.

At its simplest an Excel 2007+ file is a collection of XML files in a zip container. Here is a look inside a sample file that contains macros:

$ unzip -qql Book1.xlsm | awk '{print $4}'
[Content_Types].xml
_rels/.rels
xl/_rels/workbook.xml.rels
xl/workbook.xml
xl/drawings/vmlDrawing1.vml
xl/worksheets/_rels/sheet1.xml.rels
xl/theme/theme1.xml
xl/styles.xml
xl/vbaProject.bin
xl/worksheets/sheet1.xml
docProps/core.xml
docProps/app.xml

In amongst the .xml files you can see there is a vbaProject.bin file. This is an OLE Compound Document file, or rather a collection of files, that contain the VBA macros associated with the Excel workbook.

So, I wrote a small Perl application using Archive::Zip to extract the VBA project from an existing Excel file:

$ extract_vba Book1.xlsm
Extracted 'vbaProject.bin' successfully

Then I added a function to Excel::Writer::XLSX so that it could be added to a new file:

#!/usr/bin/perl

use strict;
use warnings;
use Excel::Writer::XLSX;

my $workbook  = Excel::Writer::XLSX->new( 'macros.xlsm' );
my $worksheet = $workbook->add_worksheet();

$workbook->add_vba_project( './vbaProject.bin' );

# Add the usual Excel::Writer::XLSX code to populate the workbook.
# ...

__END__

So, now we have a new Excel::Writer::XLSX file that contains macros and all the other data we want to add. This is kind of cool but not very useful. It is possible to use complex functions contained in the VBA but calling the macros is a more manual process.

So the next step was to add some form buttons to link the macros to actions on the worksheet. Unfortunately, form buttons in Excel 2007+ are implemented in VML which is a messy XML format that isn't very well documented and which contains lots of quirky backward compatibility features.

Nevertheless, a few late nights later I added form buttons. The process was too painful to add any other form types but at least I was able to add the most common Excel form element. So now you can do something like this:

#!/usr/bin/perl

use strict;
use warnings;
use Excel::Writer::XLSX;

my $workbook  = Excel::Writer::XLSX->new( 'macros.xlsm' );
my $worksheet = $workbook->add_worksheet();

# Widen the column for clarity.
$worksheet->set_column( 'A:A', 30 );

# Add the VBA project binary.
$workbook->add_vba_project( './vbaProject.bin' );

# Show text for the end user.
$worksheet->write( 'A3', 'Press the button to say hello.' );

# Add a button tied to a macro in the VBA project.
$worksheet->insert_button(
    'B3',
    {
        macro   => 'say_hello',
        caption => 'Press Me',
        width   => 80,
        height  => 30
    }
);

__END__

And when you run it you get something like this:

This is now available in version 0.60 of Excel::Writer::XLSX on CPAN and as it says in the release notes: The portal to the dungeon dimensions is now fully open.

I've added Sparklines to the latest version Excel::Writer::XLSX.

Sparklines are small charts showing trends that fit in a single cell. They were invented (or at least named) by Edward Tufte.

In Excel they look something like this:

Excel::Writer::XLSX now provides an interface to Sparklines in Excel and to all of their options to allow worksheets like the following (taken from the output of one of the example programs):

On a related note, I also recently added Excel "Tables" to Excel::Writer::XLSX. Excel Tables are a way of grouping a range of cells into a single entity that has common formatting or that can be referenced from formulas:

When I started Excel::Writer::XLSX I had, more or less, a clean slate to start with. So I chose to use perl 5.10.

It was mainly because I wanted to use the defined-or operator //. There were other reasons as well but Excel::Writer::XLSX is an API heavy module and, although it may sound trivial, defined-or saved me a lot of time.

I also wanted to use some of the other Modern Perl features. In fact I would really have liked to have used perl 5.14 but I thought that was probably a step too far.

Perl 5.10 i…

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:

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.

I've released a new version of Excel::Writer::XLSX to CPAN with support for charts.

The output looks something like this:

The example program that generated it is here.

I'll be adding more features in the near future such as chart sub-types, formatting, hi-lo bars and droplines.

If you are already using the Spreadsheet::WriteExcel charting features, or are interested in using this feature, try it out and let me know what you think.