/ 
Excel-ValueWriter-XLSX-0.4
River stage one • 1 direct dependent • 1 total dependent
/ Excel::ValueWriter::XLSX

NAME

Excel::ValueWriter::XLSX - generating data-only Excel workbooks in XLSX format, fast

SYNOPSIS

my $writer = Excel::ValueWriter::XLSX->new;
$writer->add_sheet($sheet_name1, $table_name1, [qw/a b tot/], [[1, 2, '=[a]+[b]'],
                                                               [3, 4]
                                                              ]);
$writer->add_sheet($sheet_name2, $table_name2, \@headers, $row_generator);
$writer->save_as($filename);

DESCRIPTION

The common way for generating Microsoft Excel workbooks in XLSX format from Perl programs is the excellent Excel::Writer::XLSX module. That module is very rich in features, but quite costly in CPU and memory usage. By contrast, the present module Excel::ValueWriter::XLSX is aimed at fast and cost-effective production of data-only workbooks, containing nothing but plain values and formulas, without any formatting. Such workbooks are useful in architectures where Excel is used merely as a local database, for example in connection with a Power Pivot architecture.

METHODS

new

my $writer = Excel::ValueWriter::XLSX->new(%options);

Constructor for a new writer object. Currently the only option is :

date_regex

A compiled regular expression for detecting data cells that contain dates. The default implementation recognizes dates in dd.mm.yyyy, yyyy-mm-dd and mm/dd/yyyy formats. User-supplied regular expressions should use named captures so that the day, month and year values can be found respectively in $+{d}, $+{m} and $+{y}.

add_sheet

$writer->add_sheet($sheet_name, $table_name, [$headers,] $rows);

Adds a new worksheet into the workbook.

  • The $sheet_name is mandatory; it must be unique and between 1 and 31 characters long.

  • The $table_name is optional; if not undef, the sheet contents will be registered as an Excel table. The table name must be unique, of minimum 3 characters, without spaces or special characters.

  • The $headers argument is optional; it may be undef or may even be absent. If present, it should contain an arrayref of scalar values, that will be used as column names for the table associated with that worksheet. Column names should be unique (otherwise Excel will automatically add a discriminating number). If $headers are not present, the first row in $rows will be treated as headers.

  • The $rows argument may be either a reference to a 2-dimensional array of values, or a reference to a callback function that will return a new row at each call, in the form of a 1-dimensional array reference. An empty return from the callback function signals the end of data (but intermediate empty rows may be returned as []). Callback functions should typically be closures over a lexical variable that remembers when the last row has been met. Here is an example of a callback function used to feed a sheet with 500 lines of 300 columns of random numbers:

    my @headers_for_rand = map {"h$_"} 1 .. 300;
my $random_rows = do {my $count = 500; sub {$count-- > 0 ? [map {rand()} 1 .. 300] : undef}};
$writer->add_sheet(RAND_SHEET => rand => \@headers_for_rand, $random_rows);

Cells within a row must contain scalar values. Values that look like numbers are treated as numbers. String values that match the date_regex are converted into numbers and displayed through a date format. String values that start with an initial '=' are treated as formulas; but like in Excel, if you want regular string that starts with a '=', put a single quote just before the '=' -- that single quote will be removed from the string. Everything else is treated as a string. Strings are shared at the workbook level (hence a string that appears several times in the input data will be stored only once within the workbook).

save_as

$writer->save_as($target);

Writes the workbook contents into the specified $target, which can be either a filename or filehandle opened for writing.

ARCHITECTURAL NOTE

Âlthough I'm a big fan of Moose and its variants, the present module is implemented in POPO (Plain Old Perl Object) : since the aim is to maximize cost-effectiveness, and since the object model is extremely simple, there was no ground for using a sophisticated object system.

SEE ALSO

Excel::Writer::XLSX

BENCHMARKS

Not done yet

TO DO

- options for workbook properties : author, etc.
- support for 1904 date schema

AUTHOR

Laurent Dami, <dami at cpan.org>

COPYRIGHT AND LICENSE

Copyright 2022 by Laurent Dami.

This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself.