/ 
Excel-ValueWriter-XLSX-0.1
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/], [1, 2], [3, 4]]);
$writer->add_sheet($sheet_name2, $table_name2, $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. Such workbooks are useful in architectures where Excel is used merely as a local database, for example in connection with a PowerBI architecture.

VERSION

This is version 0.1, the first release. Until version 1.0, slight changes may occur in the API.

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, $rows);

Adds a new worksheet into the workbook.

  • The $sheet_name 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. Values in the first row will become the headers of the table.

  • 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 []).

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, all other strings are treated as shared strings 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($filename);

Writes the workbook contents into the specified $filename.

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

- tests (use LibXML for checking schema validity)
- options for workbook properties : author, etc.
- support for 1904 date schema
- easier API for headers when using a row callback function

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.