From 2d06ee894a3e5cd139bde1a88095c33772283e99 Mon Sep 17 00:00:00 2001 From: Jason Date: Wed, 28 Aug 2013 15:16:28 +0000 Subject: merge now supports formulas, and adjusted test --- lib/Spreadsheet/Template/Helpers/Xslate.pm | 22 +++++++++++++------- lib/Spreadsheet/Template/Writer/Excel.pm | 30 +++++++++++++++++++--------- t/data/merge.json | 32 +++++++++++++++++++++--------- t/merge.t | 31 ++++++++++++++++++++++++++--- 4 files changed, 87 insertions(+), 28 deletions(-) diff --git a/lib/Spreadsheet/Template/Helpers/Xslate.pm b/lib/Spreadsheet/Template/Helpers/Xslate.pm index d4238e9..bf9b435 100644 --- a/lib/Spreadsheet/Template/Helpers/Xslate.pm +++ b/lib/Spreadsheet/Template/Helpers/Xslate.pm @@ -32,13 +32,21 @@ sub format { } sub merge { - my ($package, $range, $contents, $format) = @_; - - return $JSON->encode({ - range => "$range", - contents => "$contents", - format => _formats($package, $format) - }); + my ($package, $contents, $format, $type, $range, %args) = @_; + + return $JSON->encode( + { + contents => "$contents", + format => _formats( $package, $format ), + type => $type, + range => "$range", + ( + defined $args{formula} + ? ( formula => $args{formula} ) + : () + ), + } + ); } sub c { diff --git a/lib/Spreadsheet/Template/Writer/Excel.pm b/lib/Spreadsheet/Template/Writer/Excel.pm index da54cea..d24b3ae 100644 --- a/lib/Spreadsheet/Template/Writer/Excel.pm +++ b/lib/Spreadsheet/Template/Writer/Excel.pm @@ -153,24 +153,36 @@ sub _write_worksheet { } } - if (exists $data->{merge}) { - for my $i (0..$#{ $data->{merge} }) { - my $merge = $data->{merge}[$i]; + if ( exists $data->{merge} ) { + for my $i ( 0 .. $#{ $data->{merge} } ) { + my $merge = $data->{merge}[$i]; my $format = $merge->{format}; my $format_obj; - if (exists $self->_formats->{$format}) { - $format_obj = $self->_formats->{$format}; + if ( exists $self->_formats->{$format} ) { + $format_obj = $self->_formats->{$format}; } else { $format_obj = $self->excel->add_format(%$format); $self->_formats->{$format} = $format_obj; } - $sheet->merge_range($merge->{range}, $merge->{contents}, $format_obj); + + $merge->{type} = 'formula' if defined $merge->{formula}; + + $sheet->merge_range_type( + $merge->{type}, + $merge->{range}, + defined $merge->{formula} + ? $merge->{formula} + : $merge->{contents}, + $format_obj, + defined $merge->{formula} ? $merge->{contents} : () + ); } } - if (exists $data->{autofilter}) { - my @autofilter = @{$data->{autofilter}}; - $sheet->autofilter($autofilter[0], $autofilter[1], $autofilter[2], $autofilter[3]); + if ( exists $data->{autofilter} ) { + my @autofilter = @{ $data->{autofilter} }; + $sheet->autofilter( $autofilter[0], $autofilter[1], $autofilter[2], + $autofilter[3] ); } } diff --git a/t/data/merge.json b/t/data/merge.json index 2a86bdf..5dc32f3 100644 --- a/t/data/merge.json +++ b/t/data/merge.json @@ -1,5 +1,5 @@ %% my $default = { color => '#000000', size => 14 }; -%% format(basic => $default.merge({bold => true()})); +%% format(basic => $default.merge({bold => true(),align => 'left'})); { "selection" : 0, @@ -10,18 +10,32 @@ "selection" : [ 1, 9 ], "row_heights" : [ 25, 18, 18, 18 ], "cells" : [ + %% for $headers -> $header { + [ + [% c($header.value1, "basic") %], + [% c($header.value2, "basic") %], + [% c($header.value3, "basic") %], + [% c($header.value4, "basic") %] + ], + %% } %% for $rows -> $row { [ - [% c($row.value1, "basic") %], - [% c($row.value2, "basic") %], - [% c($row.value3, "basic") %], - [% c($row.value4, "basic") %] - ] + [% c($row.value1, "basic", "number") %], + [% c($row.value2, "basic", "number") %], + [% c($row.value3, "basic", "number") %], + [% c($row.value4, "basic", "number") %] + ], %% } + [ + [% c(0, "basic", "number" ) %] + ] ], - "merge" : [ - [% merge('A1:D1', 'Merged Cells', 'basic') %] - ] + "merge" : [ + [% merge('Merged Header', 'basic', 'string','A1:D1') %], + [% merge($rows.map(-> $a {$a.value1}).reduce(-> $a,$b {$a + $b}), 'basic', + 'number', + 'A4:D4', formula => 'SUM(A2:A3)') %] + ] } ] } diff --git a/t/merge.t b/t/merge.t index c04c0ab..0d270db 100644 --- a/t/merge.t +++ b/t/merge.t @@ -15,12 +15,26 @@ my $data = do { local $/; local @ARGV = ('t/data/merge.json'); <> }; my $excel = $template->render( $data, { - rows => [ + headers => [ { value1 => "Merge 1", value2 => "Merge 2", value3 => "Merge 3", value4 => "Merge 4" + }, + ], + rows => [ + { + value1 => "1", + value2 => '0', + value3 => '0', + value4 => '0' + }, + { + value1 => "2", + value2 => '0', + value3 => '0', + value4 => '0' } ], } @@ -33,15 +47,26 @@ my $data = do { local $/; local @ARGV = ('t/data/merge.json'); <> }; my $ws = $wb->worksheet(0); is($ws->get_name, 'Merge Report 1'); - # In the template, the 4 columns are merged + # In the template, the 4 columns in row 1 are merged # with contents = "Merged Cells" for my $col (0..3) { if ($col == 0) { - is($ws->get_cell(0, $col)->value, 'Merged Cells'); + is($ws->get_cell(0, $col)->value, 'Merged Header'); } else { is($ws->get_cell(0, $col)->value, ''); } } + is($ws->get_cell(1,0)->value, 1); + is($ws->get_cell(2,0)->value, 2); + my $value1 = $ws->get_cell(1,0)->value; + my $value2 = $ws->get_cell(2,0)->value; + my $sum = $value1 + $value2; + is($ws->get_cell(3,0)->value, $sum); + + open my $fh2, '>', 'out.xlsx'; + binmode $fh2; + $fh2->print($excel); + $fh2->close; } done_testing; -- cgit v1.2.3