Day 22: exporting data as .xls file

Spreadsheet::ParseExcel and Spreadsheet::WriteExcel are two mature modules for reading and writing Microsoft Excel files.

Sometimes you just want to export some columns, Spreadsheet::WriteExcel::Simple is a perfect fit for that:

#!/usr/bin/perl
use strict;
use warnings;
use Spreadsheet::WriteExcel::Simple;

my $xls = Spreadsheet::WriteExcel::Simple->new;
$xls->write_bold_row([qw/Date Time Thing/]);
$xls->write_row(['12/22/10', '10:00', 'buy presents']);
$xls->write_row(['12/22/10', '16:00', 'wrap presents']);
$xls->write_row(['12/24/10', '18:00', 'give presents']);
$xls->save('todo.xls');

Besides from saving the file, this module has mainly two methods: write_bold_row and write_row. I use the first one for headings and the latter for every row of data.

Spreadsheet::WriteExcel::Simple ist just a wrapper around Spreadsheet::WriteExcel. book and sheet give you access to the underlying objects. So you can adjust the settings:

$xls->sheet->keep_leading_zeros

will allow you to write data like '01234'. (For example some German zip codes have a leading zero.)

The counterpart for reading is (you propably guessed it already) Spreadsheet::ParseExcel::Simple:

#!/usr/bin/perl
use strict;
use warnings;
use Data::Dump qw/pp/;
use Spreadsheet::ParseExcel::Simple;

my $xls = Spreadsheet::ParseExcel::Simple->read('todo.xls');
my $sheet = ($xls->sheets)[0];

my @output;
my @headlines = $sheet->next_row;
while ($sheet->has_data) {
    my @data = $sheet->next_row;
    my %item;
    foreach (@headlines) {
        $item{$_} = shift @data;
    }
    push @output, \%item;
}
print pp(\@output);

# Output:
[
  { Date => "12/22/10", Thing => "buy presents", Time => "10:00" },
  { Date => "12/22/10", Thing => "wrap presents", Time => "16:00" },
  { Date => "12/24/10", Thing => "give presents", Time => "18:00" },
]

If you want to do more complex stuff, have a look at Spreadsheet::ParseExcel and Spreadsheet::WriteExcel.

Links:

• Spreadsheet::ParseExcel (distribution)
• Spreadsheet::ParseExcel (documentation)
• Spreadsheet::ParseExcel::Simple (distribution)
• Spreadsheet::ParseExcel::Simple (documentation)
• Spreadsheet::WriteExcel (distribution)
• Spreadsheet::WriteExcel (documentation)
• Spreadsheet::WriteExcel::Simple (distribution)
• Spreadsheet::WriteExcel::Simple (documentation)