merge now supports formulas, and adjusted test

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;