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:

The header row is darker than the body

The header row is frozen

Autofilters are enabled on all columns

It tries to set a sensible width on all columns

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

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:

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:

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.

Please enable JavaScript to view the comments powered by Disqus.

Disqus