From f79aa6392589db518f7292b89a4c8978f5c7c6a5 Mon Sep 17 00:00:00 2001 From: Jesse Luehrs Date: Wed, 28 Aug 2013 17:24:32 -0400 Subject: fix up the merge and autofilter stuff (#1) --- lib/Spreadsheet/Template.pm | 36 ++++-- lib/Spreadsheet/Template/Helpers/Xslate.pm | 66 +++++++---- lib/Spreadsheet/Template/Processor/Xslate.pm | 17 ++- lib/Spreadsheet/Template/Writer/Excel.pm | 163 +++++++++++++++------------ t/data/merge.json | 16 +-- 5 files changed, 177 insertions(+), 121 deletions(-) diff --git a/lib/Spreadsheet/Template.pm b/lib/Spreadsheet/Template.pm index 2e4fe61..4b879c8 100644 --- a/lib/Spreadsheet/Template.pm +++ b/lib/Spreadsheet/Template.pm @@ -45,7 +45,9 @@ been preprocessed is a JSON file, with a structure that looks like this: "name" : "Sheet1", "row_heights" : [ 18, 18, 18 ], "selection" : [ 0, 0 ], - "autofilter" : [ 0, 0, 0, 3], + "autofilter" : [ + [ [0, 0], [0, 2] ] + ], "cells" : [ [ { @@ -106,15 +108,16 @@ been preprocessed is a JSON file, with a structure that looks like this: } ] ], - merge : [ - ["contents":"Merged Contents","format":{"color":#000000"}, - "type":"string", - "first_row": 0, - "first_col": 0, - "last_row" : 0, - "last_col" : 3 - ] - ] + "merge" : [ + { + "range" : [ [1, 0], [1, 2] ], + "contents" : "Merged Contents", + "format" : { + "color" : "#000000" + }, + "type" : "string" + } + ] } ] } @@ -163,11 +166,24 @@ spreadsheet. An array of two numbers corresponding to the (zero-based) row and column of the cell that should be selected when the worksheet is first displayed. +=item autofilter + +Enables autofilter behavior for each range of cells listed. Cell ranges are +specified by an array of two arrays of two numbers, corresponding to the row +and column of the top left and bottom right cell of the autofiltered range. + =item cells An array of arrays of cell objects. Each innermost array represents a row, containing all of the cell data for that row. +=item merge + +An array of merge objects. Merge objects are identical to cell objects, except +that they contain an additional C key, which has a value of an array of +two arrays of two numbers, corresponding to the row and column of the top left +and bottom right cell of the range to be merged. + =back =head2 cell diff --git a/lib/Spreadsheet/Template/Helpers/Xslate.pm b/lib/Spreadsheet/Template/Helpers/Xslate.pm index e611f1d..8712656 100644 --- a/lib/Spreadsheet/Template/Helpers/Xslate.pm +++ b/lib/Spreadsheet/Template/Helpers/Xslate.pm @@ -3,6 +3,7 @@ use strict; use warnings; use JSON; +use POSIX; my $JSON = JSON->new; @@ -31,30 +32,6 @@ sub format { return ''; } -sub merge { - my ( - $package, $contents, $format, $type, $first_row, - $first_col, $last_row, $last_col, %args - ) = @_; - - return $JSON->encode( - { - contents => "$contents", - format => _formats( $package, $format ), - type => $type, - first_row => $first_row, - first_col => $first_col, - last_row => $last_row, - last_col => $last_col, - ( - defined $args{formula} - ? ( formula => $args{formula} ) - : () - ), - } - ); -} - sub c { my ($package, $contents, $format, $type, %args) = @_; @@ -70,9 +47,50 @@ sub c { }); } +sub merge { + my ($package, $range, $contents, $format, $type, %args) = @_; + + $type = 'string' unless defined $type; + + return $JSON->encode({ + range => _parse_range($range), + contents => "$contents", + format => _formats($package, $format), + type => $type, + (defined $args{formula} + ? (formula => $args{formula}) + : ()), + }); +} + sub true { JSON::true } sub false { JSON::false } +sub _parse_range { + my ($range) = @_; + + $range = [ split ':', $range ] + if !ref($range); + + return [ map { _cell_to_row_col($_) } @$range ] +} + +sub _cell_to_row_col { + my ($cell) = @_; + + return $cell if ref($cell) eq 'ARRAY'; + + my ($col, $row) = $cell =~ /([A-Z]+)([0-9]+)/; + + (my $ncol = $col) =~ tr/A-Z/1-9A-Q/; + $ncol = POSIX::strtol($ncol, 27); + $ncol -= 1; + + my $nrow = $row - 1; + + return [ $nrow, $ncol ]; +} + sub _formats { my ($package, $format) = @_; diff --git a/lib/Spreadsheet/Template/Processor/Xslate.pm b/lib/Spreadsheet/Template/Processor/Xslate.pm index 38803c6..e8f3cd4 100644 --- a/lib/Spreadsheet/Template/Processor/Xslate.pm +++ b/lib/Spreadsheet/Template/Processor/Xslate.pm @@ -39,15 +39,14 @@ C<"date_time">, and C<%args> contains any other parameters (such as C, for instance) to declare for the cell. C<$type> is optional, and if not passed, defaults to C<"string">. -=item merge($content, $format, $type, $first_row, $first_col, $last_row, $last_col, %args) - -Returns representation of a range of cells to be merged. C<$content> is the -content to be placed in the merged cell.C<$format> is the name of a format -declared with the C helper or a hashref of format options. -C<$type> is either C<"string">, C<"number">, or C<"date_time">. -C<$first_row>, C<$first_col>, C<$last_row>, C<$last_col> are zero-indexed -Excel row, column numbers. C<%args> contains any other parameters (such as C, -for instance) to declare for the cell. +=item merge($range, $content, $format, $type, %args) + +Returns representation of a range of cells to be merged. C<$content>, +C<$format>, C<$type>, and C<%args> are identical to the parameters listed above +for the C helper, and C<$range> describes the range of cells to be merged. +The range can be specified either by an array of two arrays corresponding to +the row and column indices of the top left and bottom right cell, or by an +Excel-style range (like C). =item true diff --git a/lib/Spreadsheet/Template/Writer/Excel.pm b/lib/Spreadsheet/Template/Writer/Excel.pm index 99f1b7e..59c4553 100644 --- a/lib/Spreadsheet/Template/Writer/Excel.pm +++ b/lib/Spreadsheet/Template/Writer/Excel.pm @@ -153,39 +153,21 @@ sub _write_worksheet { } } - 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}; - } - else { - $format_obj = $self->excel->add_format(%$format); - $self->_formats->{$format} = $format_obj; - } - - $merge->{type} = 'formula' if defined $merge->{formula}; - - $sheet->merge_range_type( - $merge->{type}, - $merge->{first_row}, - $merge->{first_col}, - $merge->{last_row}, - $merge->{last_col}, - defined $merge->{formula} - ? $merge->{formula} - : $merge->{contents}, - $format_obj, - defined $merge->{formula} ? $merge->{contents} : () - ); + if (exists $data->{merge}) { + for my $merge (@{ $data->{merge} }) { + $self->_write_merge($merge, $sheet); } } - if ( exists $data->{autofilter} ) { - my @autofilter = @{ $data->{autofilter} }; - $sheet->autofilter( $autofilter[0], $autofilter[1], $autofilter[2], - $autofilter[3] ); + + if (exists $data->{autofilter}) { + for my $autofilter (@{ $data->{autofilter} }) { + $sheet->autofilter( + $autofilter->[0][0], + $autofilter->[0][1], + $autofilter->[1][0], + $autofilter->[1][1], + ); + } } } @@ -200,46 +182,7 @@ sub _write_cell { my $format; if (exists $data->{format}) { - my %border = ( - thin => 1, - ); - - my $properties = { %{ $data->{format} } }; - - if (my $border = delete $properties->{border}) { - $properties = { - left => $border->[0], - right => $border->[1], - top => $border->[2], - bottom => $border->[3], - %$properties, - }; - } - - if (my $border_color = delete $properties->{border_color}) { - $properties = { - left_color => $border_color->[0], - right_color => $border_color->[1], - top_color => $border_color->[2], - bottom_color => $border_color->[3], - %$properties, - }; - } - - $properties = { - map { - my $v = $properties->{$_}; - $_ => JSON::is_bool($v) ? ($v ? 1 : 0) - : $_ eq 'left' ? $border{$v} - : $_ eq 'right' ? $border{$v} - : $_ eq 'top' ? $border{$v} - : $_ eq 'bottom' ? $border{$v} - : $_ =~ /color/ ? $self->_color($v) - : $v - } keys %$properties - }; - - $format = $self->_format($properties); + $format = $self->_munge_format($data->{format}); } if (defined $data->{formula}) { @@ -261,6 +204,84 @@ sub _write_cell { } } +sub _write_merge { + my $self = shift; + my ($data, $sheet) = @_; + + my $format; + if (exists $data->{format}) { + $format = $self->_munge_format($data->{format}); + } + + if (exists $data->{formula}) { + $sheet->merge_range_type( + 'formula', + @{ $data->{range}[0] }, + @{ $data->{range}[1] }, + $data->{formula}, + (defined $format ? ($format) : (undef)), + (defined $data->{contents} + ? ($data->{contents}) + : ()), + ); + } + else { + $sheet->merge_range_type( + $data->{type}, + @{ $data->{range}[0] }, + @{ $data->{range}[1] }, + $data->{contents}, + (defined $format ? ($format) : ()), + ); + } +} + +sub _munge_format { + my $self = shift; + my ($format) = @_; + + my %border = ( + thin => 1, + ); + + my $properties = { %$format }; + + if (my $border = delete $properties->{border}) { + $properties = { + left => $border->[0], + right => $border->[1], + top => $border->[2], + bottom => $border->[3], + %$properties, + }; + } + + if (my $border_color = delete $properties->{border_color}) { + $properties = { + left_color => $border_color->[0], + right_color => $border_color->[1], + top_color => $border_color->[2], + bottom_color => $border_color->[3], + %$properties, + }; + } + + $properties = { + map { + my $v = $properties->{$_}; + $_ => JSON::is_bool($v) ? ($v ? 1 : 0) + : $_ eq 'left' ? $border{$v} + : $_ eq 'right' ? $border{$v} + : $_ eq 'top' ? $border{$v} + : $_ eq 'bottom' ? $border{$v} + : $_ =~ /color/ ? $self->_color($v) + : $v + } keys %$properties + }; + + return $self->_format($properties); +} + sub _color { my $self = shift; my ($color) = @_; diff --git a/t/data/merge.json b/t/data/merge.json index 7f384ab..b38d2e0 100644 --- a/t/data/merge.json +++ b/t/data/merge.json @@ -29,13 +29,15 @@ [] ], "merge" : [ - [% merge('Merged Header', 'basic', 'string', 0,0,0,3) %], - [% merge($rows.map(-> $a {$a.value1}).reduce(-> $a,$b {$a + $b}), 'basic', - 'number', - 3,0,3,3, - formula => 'SUM(A2:A3)' - ) %] - ] + [% merge('A1:D1', 'Merged Header', 'basic', 'string') %], + [% merge( + 'A4:D4', + $rows.map(-> $a {$a.value1}).reduce(-> $a,$b {$a + $b}), + 'basic', + 'number', + formula => 'SUM(A2:A3)' + ) %] + ] } ] } -- cgit v1.2.3-54-g00ecf