Excel-Template-XLSX
view release on metacpan or search on metacpan
v1.1.1 10-Dec-2019
added command to upload to CPAN
v1.1.0 10-Dec-2019
Changed build.pl to require Excel::Writer::XLSX version 1.01
v1.0.9 07-Oct-2019
Removed references to Mojo::Util which were used for development.
v1.0.8 24-Sep-2019
Fixed bug in handling of array formulas
v1.0.7 02-Jun-2016
Changed build script to require Excel::Writer::XLSX 0.90
v1.0.6 02-Jun-2016
Ticket #114543: Changed XLSX.pm and test scripts to use EWX public api $worksheet->get_name()
issue arose from release of Excel::Writer::XLSX 0.90
v1.0.5 01-Mar-2016
Ticket #105683: Loosing format information in merged cells (rich strings)
t/01-object.t
t/02-workbook.t
t/03-worksheet.t
t/04-styles.t
t/05-shared_strings.t
t/06-cell.t
t/06-cell.xlsx
t/07-two_templates.t
t/08-open_output.t
t/09-template_callback.t
t/10-array_formulas.t
t/11-formulas.t
t/12-merged_ranges.t
t/99-todo.t
t/boilerplate.t
t/manifest.t
t/pod-coverage.t
t/pod.t
lib/Excel/Template/XLSX.pm view on Meta::CPAN
}
} # return hashref
}
###############################################################################
sub _parse_sheet_pass2 {
=head2 _parse_sheet_pass2
Parses cell contents (first by row, then by column). Cells can contain
inline strings, string references, direct string values, formulas,
and hyperlinks. Each cell may also contain formatting information.
The format is in an index to formatting for borders, shading, alignment,
font, and number formats.
=cut
my $self = shift;
my ($sheet) = @_;
return {
lib/Excel/Template/XLSX.pm view on Meta::CPAN
# In-line string (not seen in practice)
elsif ( $t eq 'str' ) {
$val = '=' . $cell->first_child('f')->text();
}
# Formulas
elsif ( $t eq 'n' ) {
if ( my $form_child = $cell->first_child('f') ) {
my $is_array = $form_child->att('t');
my $ref = $form_child->att('ref');
my $formula = $form_child->text() // q[="No Formula Found"];
if ($is_array and $ref) {
$sheet->write_array_formula( $ref, "=${formula}", $format, $val );
}
else {
if ( my $ref = $self->{MERGED_RANGES}{$sheet_idx}{$a1} ) {
$sheet->merge_range_type('formula', $ref, "=${formula}", $format, $val);
$self->{MERGED_RANGES}{$sheet_idx}{$a1} = 0; # Flag it as used. Handle unused ranges at end
} else {
$sheet->write_formula( $a1, "=${formula}", $format, $val );
}
}
next;
}
}
elsif ( $t eq 'b' ) {
$val = $val ? "TRUE" : "FALSE";
}
elsif ( $t eq 'e' ) {
t/04-styles.t view on Meta::CPAN
$sheet->write( 3, 0, 4050.1, $num_format1 );
# Zip codes:
# string tested in shared strings
my $zip_format = $wbk->add_format( num_format => '00000' );
$sheet->write( 4, 0, '01111', $zip_format );
$sheet->keep_leading_zeros(); # Just converts number to a string
$sheet->write( 5, 0, '01111' );
my $prot_format = $wbk->add_format( 'hidden' => 1, 'locked' => 1 );
$sheet->write_formula( 6, 0, '="Locked" & " Hidden"', $prot_format );
$wbk->close();
# Get workbook content as a template
my ( $gfh, $gfilename ) = tempfile( SUFFIX => '.xlsx' );
my ( $self, $twbk ) = Excel::Template::XLSX->new( $gfilename, $efilename );
$self->parse_template();
# Get the format object referenced by cell 0,0
my $got_sheet = $twbk->get_worksheet_by_name('Sheet1');
t/05-shared_strings.t view on Meta::CPAN
$center->set_align( 'vcenter' );
my $right = $wbk->add_format();
$right->set_align( 'right' );
my $red = $wbk->add_format();
$red->set_color('red');
my $wksheet = $wbk->add_worksheet();
$wksheet->write( 'A1', 'A1A1' );
$wksheet->write_rich_string( 'A2', 'Some ', $red, 'red ', 'text' );
$wksheet->merge_range_type('rich_string', 'A3:B4', 'Some ', $red, 'red ', 'text', $center);
$wksheet->merge_range_type('formula', 'A5:B7', q[=A1 & "B1B1"], $right);
$wbk->close();
# Get workbook content as a template
my ( $gfh, $gfilename ) = tempfile( SUFFIX => '.xlsx' );
my ( $self, $twbk ) = Excel::Template::XLSX->new( $gfilename, $efilename );
$self->parse_template();
is_deeply($twbk->{_sheetnames}->{"Sheet1"}->{_merge}, $wbk->{_sheetnames}->{"Sheet1"}->{_merge}, "Sheet Data Structure");
TODO: {
t/06-cell.t view on Meta::CPAN
use Excel::Writer::XLSX;
use Excel::Template::XLSX;
use Test::More;
use File::Temp qw(tempfile);
# Can be set to 1 to see the created template and output file during debugging
$File::Temp::KEEP_ALL = 0;
###############################################################################
# Issue with using EWX to create array formulas
# Expected spreadsheet developed by hand.
my $efilename = q[./t/06-cell.xlsx];
TODO: {
local $TODO = "Restore programmitically generated expected Excel worksheet";
ok(0, 'Add ability to programmatically generate Excel Worksheet');
}
=for nothing
# Create expected workbook content
my ( $efh, $efilename ) = tempfile( SUFFIX => '.xlsx' );
my $wbk = Excel::Writer::XLSX->new($efilename);
t/10-array_formulas.t view on Meta::CPAN
my ( $self, $twbk ) = Excel::Template::XLSX->new( $gfilename, $efilename );
$self->parse_template();
# Get string table lookup by ID, not value
my $hstring = $twbk->{_str_table};
# warn "hstring is ", dumper( $hstring );
TODO: {
local $TODO = <<"EOF";
Find a way to read output file, and see if array formula survives.
Consider is_deeply on sheet XML.
For now, set KEEP_ALL to 1, and examine output file by hand.
EOF
is( $hstring->{A1}, '0', "Sheet 1 A1 in row 0" );
is( $hstring->{A2}, '1', "Sheet 1 A2 in row 1" );
}
# is( get_cell( $twbk, $hstring->{0}, 0, 0 ), 'A1', "Sheet 1 A1" );
# is( get_cell( $twbk, $hstring->{1}, 0, 1 ), 'A2', "Sheet 1 A2" );
my $sheet = $twbk->sheets(0);
( run in 0.314 second using v1.01-cache-2.11-cpan-3cd7ad12f66 )