Generating XLSX spreadsheets

spreadsheets Sat 10 December 2016

At work we often have to generate spreadsheets, for which I usually turn to John McNamara's excellent Excel::Writer::XLSX module, which gives you access to most features supported by Excel. But often we just need a basic spreadsheet, with a standard format, so I created Spreadsheet::GenerateXLSX to make our life easier. I'll show how you use it, and then look at other modules for generating XLSX format spreadsheets.

For the examples below we're going to generate a single sheet from the following data:

my @sheet1 = (
    ['Pokemon',    'Type',      'Number'],
    ['Charizard',  'Flying',    6],
    ['Pikachu',    'Electric',  25],
    ['Vulpix',     'Fire',      37],
    ['Ditto',      'Normal',    132],
);

Spreadsheet::GenerateXLSX

To generate the spreadsheet, you just need to write:

use Spreadsheet::GenerateXLSX qw/ generate_xlsx /;

generate_xlsx('pokemon.xlsx', \@sheet1);

This will create a single sheet titled Sheet1, but you can specify the name you want for the sheet, and can pass multiple arrays to get multiple sheets:

generate_xlsx('pokemon.xlsx', Favourites => \@sheet1, 'Wish List' => \@sheet2);

This does the following things:

For our example above, you'll end up with a spreadsheet that looks something like this:

Spreadsheet generated with Spreadsheet::GenerateXLSX

At some point I plan to do some kind of auto-formatting of columns, so that numeric columns are right aligned, etc.

XLS::Simple

When I was first looking at spreadsheet modules, I didn't look very closely at this one, because what minimal documentation it currently has is in Japanese, and the relevant function is called write_xls. It turns out the function is misnamed, as it generates XLSX format.

Here's how you generate a spreadsheet from our example data:

use XLS::Simple qw/ write_xls /;

my $header_row = shift @sheet1;
write_xls(\@sheet1, 'pokemon.xlsx', header => $header_row);

The generated spreadsheet looks like this:

Spreadsheet generated with XLS::Simple

The header cells are highlighted differently, all cells have grid lines around them, but the top row isn't frozen, filters aren't added, and column widths aren't adjusted.

The module also provides a read_xls() function, but I haven't tried that.

Data::Table::Excel

The Data::Table class is used to hold grids of data, intended to "make it easy for manipulating spreadsheet data". Data::Table::Excel provides functions for mapping between data tables and spreadsheets.

First we need to construct a data table from our example data, and then we'll generate an XLSX spreadsheet from that:

use Data::Table;
use Data::Table::Excel qw/ tables2xlsx /;

my $header_row = shift @sheet1;
my $table      = Data::Table->new(\@sheet1, $header_row, 0);

tables2xlsx('pokemon3.xlsx', [$table], ['Pokemon']);

The second argument is an array of data tables, each of which will be a sheet in the generated spreadsheet, and the third argument is an array of names to use for the generated sheets. Here's the resulting spreadsheet:

Spreadsheet generated with XLS::Simple

You can provide additional arguments to tables2xlsx(), for example to specify what colours to use for the header row, and for the alternating odd and even rows.

Spreadsheet::WriteExcel::Styler

This module is used in conjunction with Excel::Writer::XLSX (or Spreadsheet::WriteExcel if you want the XLS file format), and gives you a richer model for specifying formatting of cells.

It stills means you're using a lower-level API for creating the spreadsheet, so I'm not going to cover it here.

Spreadsheet::Template

This module is part of a distribution that provides a powerful engine for generating spreadsheets from templates. You can create a spreadsheet based on a JSON format, and the JSON can be generated from a sample Excel spreadsheet, and then edited by hand.

Again, this doesn't meet my needs for a simple DWIM interface, so I'm not going to show the workflow.

Summary

There are a number of options for generating XLSX spreadsheets, and even more if you don't mind producing XLS format spreadsheets (I listed the ones I know of in the SEE ALSO section of my module's doc). Some of the XLS modules are interesting, so I may look at getting them to support XLSX output as well.

comments powered by Disqus