fix up the merge and autofilter stuff (#1)

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<range> 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<formula>,
 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<format> 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<formula>,
-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<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<A1:C3>).
 
 =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)'
+               ) %]
+         ]
       }
    ]
 }