Excel-ValueWriter-XLSX
view release on metacpan or search on metacpan
0.9 03.01.2023
- fix missing dependencies for tests
0.8 02.01.2023
- added method "add_sheets_from_database"
0.7 04.11.2022
- added parameter "compression_level"
0.6 02.08.2022
- escape XML entities in formulas
0.5 02.08.2022
- bug fix : $VERSION was declared with 'my' instead of 'our'
0.4 01.08.2022
- save_as() can also take a filehandle instead of a filename
0.3 31.07.2022
- support for formulas
0.2 15.01.2022
- fix UTF8 support
- new optional parameter: $headers
0.1 14.01.2022
- initial release
lib/Excel/ValueWriter/XLSX.pm view on Meta::CPAN
max => Int(-optional => 1))))
}),
)->meth_signature;
my $sig_for_add_sheets_from_database = List(-items => [Obj (-isa => 'DBI::db'),
String(-default => "S.")],
-all => String,
)->meth_signature;
my $sig_for_add_defined_name = List(String(-name => "name"),
String(-name => "formula"),
String(-name => "comments", -optional => 1),
)->meth_signature;
my $sig_for_save_as = One_of(String,
Whatever(-does => 'IO'),
)->meth_signature;
lib/Excel/ValueWriter/XLSX.pm view on Meta::CPAN
sub new {
my ($class, %self) = &$sig_for_new;
# initial values for internal data structures (constructor args cannot initialize those)
$self{sheets} = []; # array of sheet names
$self{tables} = []; # array of table names
$self{shared_string} = {}; # ($string => $string_index)
$self{n_strings_in_workbook} = 0; # total nb of strings (including duplicates)
$self{last_string_id} = 0; # index for the next shared string
$self{defined_names} = {}; # ($name => [$formula, $comment])
# immediately open a Zip archive
$self{zip} = Archive::Zip->new;
# return the constructed object
bless \%self, $class;
}
#======================================================================
lib/Excel/ValueWriter/XLSX.pm view on Meta::CPAN
my $n_days; # in case we need to parse a date
# choose XML attributes and inner value
# NOTE : for perl, looks_like_number( "INFINITY") is TRUE! Hence the test $val !~ /^\pL/
( my $tag, my $attrs, $val)
# ==== ========= ====
= looks_like_number($val) && $val !~ /^\pL/ ? (v => "" , $val )
: $date_regex && $val =~ $date_regex
&& is_valid_date(\%+, \$n_days) ? (v => qq{ s="$DATE_STYLE"}, $n_days )
: $bool_regex && $val =~ $bool_regex ? (v => qq{ t="b"} , $1 ? 1 : 0 )
: $val =~ /^=/ ? (f => "", escape_formula($val) )
: (v => qq{ t="s"} , $self->_add_shared_string($val));
# add the new XML cell
my $cell = qq{<c r="$col_letter$row_num"$attrs><$tag>$val</$tag></c>};
push @cells, $cell;
}
# generate the row XML and add it to the sheet
my $row_xml = join "", qq{<row r="$row_num" spans="1:$last_col">}, @cells, qq{</row>};
$xml .= $row_xml;
lib/Excel/ValueWriter/XLSX.pm view on Meta::CPAN
);
# insert into the zip archive
$self->add_string_to_zip(encode_utf8(join "", @xml), "xl/tables/table$table_id.xml");
return $table_id;
}
sub add_defined_name {
my ($self, $name, $formula, $comment) = &$sig_for_add_defined_name;
not exists $self->{defined_names}{$name} or croak "add_defined_name(): name '$name' already in use";
$self->{defined_names}{$name} = [$formula, $comment];
}
sub worksheet_rels {
my ($self, $table_id) = @_;
my @rels;
push @rels, "officeDocument/2006/relationships/table" => "../tables/table$table_id.xml" if $table_id;
return $self->relationships(@rels);
}
lib/Excel/ValueWriter/XLSX.pm view on Meta::CPAN
# Convert control character to the _xHHHH_ escape.
$string =~ s/([\x00-\x08\x0B-\x1F])/sprintf "_x%04X_", ord($1)/eg;
my $maybe_preserve_space = $string =~ /^\s|\s$/ ? ' xml:space="preserve"' : '';
my $node = qq{<si><t$maybe_preserve_space>$string</t></si>};
return $node;
}
sub escape_formula {
my ($string) = @_;
$string =~ s/^=//;
$string =~ s/($entity_regex)/$entity{$1}/g;
return $string;
}
sub is_valid_date {
my ($named_captures, $n_days_ref) = @_;
lib/Excel/ValueWriter/XLSX.pm view on Meta::CPAN
my $writer = Excel::ValueWriter::XLSX->new;
$writer->add_sheet($sheet_name1, $table_name1, [qw/a b tot/], [[1, 2, '=[a]+[b]'],
[3, 4],
['TRUE', 'FALSE'],
]);
$writer->add_sheet($sheet_name2, $table_name2, \@headers, sub {...});
$writer->add_sheet($sheet_name3, $table_name3, $sth); # DBI statement handle
$writer->add_sheet($sheet_name4, $table_name4, $statement); # DBIx::DataModel::Statement object
$writer->add_sheets_from_database($dbh);
$writer->add_defined_name($name, $formula, $comment);
$writer->save_as($filename);
$writer = Excel::ValueWriter::XLSX->new(bool_regex => qr[^(?:(VRAI)|FAUX)$]);
$writer->add_sheet($sheet_name1, $table_name1, [qw/a b/], [['I like Perl:', 'VRAI']]);
=head1 DESCRIPTION
The present module is aimed at fast and cost-effective
production of "data-only" Excel workbooks, containing nothing but plain values
and formulas.
CPU and memory usage are much lower than with the well-known L<Excel::Writer::XLSX>;
however the set of features is also much more restricted : there is no support
for formats, colors, figures or other fancy Excel features.
Such workbooks with plain data are useful for example :
=over
=item *
lib/Excel/ValueWriter/XLSX.pm view on Meta::CPAN
=back
=back
Cells within a row must contain scalar values. Values that look like numbers are treated
as numbers. String values that match the C<date_regex> are converted into numbers and
displayed through a date format, but only if the date is valid and is above 1900 January 1st --
otherwise it is treated as a string, like in Excel.
String values that start with an initial '=' are treated
as formulas; but like in Excel, if you want a plain 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).
=head2 add_sheets_from_database
$writer->add_sheets_from_database($dbh, $sheet_prefix, @table_names);
Gets data from database tables and adds them as sheets into the Excel workbook.
lib/Excel/ValueWriter/XLSX.pm view on Meta::CPAN
=item C<@table_names>
The list of tables to be read from the database.
If empty, table names are retrieved automatically from the database
through the L<DBI/table_info> method.
=back
=head2 add_defined_name
$writer->add_defined_name($name, $formula, $comment);
Adds a "defined name" to the workbook. Defined names can be used
in any formula within the workbook, and will be replaced by
the corresponding content.
=over
=item *
C<$name> is mandatory and must be unique
=item *
C<$formula> is mandatory and will be interpreted by Excel like a formula.
References to ranges should include the sheet name and use absolute coordinates;
for example for concatenating two cells in sheet 's1', the formula is :
$writer->add_defined_name(cells_1_and_2 => q{'s1'!$A$1&'s1'!$A$2});
If the intended content is just a constant string, it must be enclosed in double quotes, i.e.
$writer->add_defined_name(my_string => q{"my_constant_value"});
=item *
C<$comment> is optional; it will appear when users consult the
$writer->add_sheet(With_header => t_header => [qw/col1 col2/], [[33, 44], [11, 22]]);
# empty sheets, with and without table
$writer->add_sheet(Empty1 => t_empty => []);
$writer->add_sheet(Empty2 => (undef) => []);
# a table without data
$writer->add_sheet(Dataless => t_dataless => [qw/col1 col2/] => []);
# defined names
$writer->add_defined_name(my_formula => q{'s1'!$A$1&'s1'!$A$2}, "no comment");
$writer->add_defined_name(my_constant => q{"constant_value"});
# save the worksheet
$writer->save_as($filename);
# some stupid regex checks in various parts of the ZIP archive
my $zip = Archive::Zip->new($filename);
my $content_types = $zip->contents('[Content_Types].xml');
like $content_types, qr[<Override PartName="/xl/worksheets/sheet1.xml"], 'content-types';
my $workbook = $zip->contents('xl/workbook.xml');
like $workbook, qr[<sheets><sheet name="s1" sheetId="1" r:id="rId1"/>.+</sheets>], 'workbook';
like $workbook, qr[<definedName name="my_formula" comment="no comment">'s1'!\$A\$1&'s1'!\$A\$2</], 'defined name';
my $sheet1 = $zip->contents('xl/worksheets/sheet1.xml');
like $sheet1, qr[<sheetData><row r="1" spans="1:4"><c r="A1" t="s"><v>0</v></c><c r="B1" t="s"><v>1</v>], 'sheet1';
like $sheet1, qr[<f>\Q[foo]+[bar]&[bar]\E</f>], 'formula';
my $table1 = $zip->contents('xl/tables/table1.xml');
like $table1, qr[<tableColumn id="1"], 'table1';
my $strings = $zip->contents('xl/sharedStrings.xml');
like $strings, qr[<si><t>foo</t></si><si><t>bar</t></si>], 'shared strings';
like $strings, qr[<si><t>\Q=[foo]+[bar]&[bar]\E</t></si>], 'escaped formula';
like $strings, qr[<si><t>INFINITY</t></si>], 'INFINITY treated as a string';
like $strings, qr[<si><t>ctrl_x0001__x0002__x0003_</t></si>], 'control chars';
like $strings, qr[<si><t xml:space="preserve"> space </t></si>], 'preserve space';
like $strings, qr[<si><t>02.03.1824</t></si>], 'old date treated as string';
like $strings, qr[<si><t>32.01.2021</t></si>], 'invalid date treated as string';
unlike $strings, qr[<si><t>01.01.2022</t></si>], 'valid date not treated as string';
my $sheet7 = $zip->contents('xl/worksheets/sheet7.xml');
like $sheet7, qr[<row r="2"], 'sheet 7 has an empty data row';
t/filehandle.t view on Meta::CPAN
my $zip = Archive::Zip->new($filename);
my $content_types = $zip->contents('[Content_Types].xml');
like $content_types, qr[<Override PartName="/xl/worksheets/sheet1.xml"], 'content-types';
my $workbook = $zip->contents('xl/workbook.xml');
like $workbook, qr[<sheets><sheet name="s1" sheetId="1" r:id="rId1"/>.+</sheets>], 'workbook';
my $sheet1 = $zip->contents('xl/worksheets/sheet1.xml');
like $sheet1, qr[<sheetData><row r="1" spans="1:4"><c r="A1" t="s"><v>0</v></c><c r="B1" t="s"><v>1</v>], 'sheet1';
like $sheet1, qr[<f>\Q[foo]+[bar]\E</f>], 'formula';
my $table1 = $zip->contents('xl/tables/table1.xml');
like $table1, qr[<tableColumn id="1"], 'table1';
my $strings = $zip->contents('xl/sharedStrings.xml');
like $strings, qr[<si><t>foo</t></si><si><t>bar</t></si>], 'shared strings';
like $strings, qr[<si><t>\Q=[foo]+[bar]\E</t></si>], 'escaped formula';
# end of tests
done_testing;
( run in 0.393 second using v1.01-cache-2.11-cpan-26ccb49234f )