properly support formulas (in xlsx at least)

3 files changed, 16 insertions, 6 deletions

diff --git a/lib/Spreadsheet/Template/Generator/Parser/Excel.pm b/lib/Spreadsheet/Template/Generator/Parser/Excel.pm
index 0e12e66..40f2744 100644
--- a/lib/Spreadsheet/Template/Generator/Parser/Excel.pm
+++ b/lib/Spreadsheet/Template/Generator/Parser/Excel.pm
@@ -82,17 +82,13 @@ sub _parse_cell {
 
     my $contents = $cell->unformatted;
     my $type = $cell->type;
+    my $formula = $cell->{Formula}; # XXX
 
     if ($type eq 'Numeric') {
         $type = 'number';
     }
     elsif ($type eq 'Text') {
-        if ($contents =~ /^=/) {
-            $type = 'formula';
-        }
-        else {
-            $type = 'string';
-        }
+        $type = 'string';
     }
     elsif ($type eq 'Date') {
         $type = 'date_time';
@@ -104,6 +100,7 @@ sub _parse_cell {
     my $data = {
         contents => $self->_filter_cell_contents($contents, $type),
         type     => $type,
+        ($formula ? (formula => $formula) : ()),
     };
 
     return $data;
diff --git a/lib/Spreadsheet/Template/Generator/Parser/XLS.pm b/lib/Spreadsheet/Template/Generator/Parser/XLS.pm
index 8ac14ef..eb5c949 100644
--- a/lib/Spreadsheet/Template/Generator/Parser/XLS.pm
+++ b/lib/Spreadsheet/Template/Generator/Parser/XLS.pm
@@ -25,6 +25,8 @@ sub make_excel {
         ];
     }
 
+    # XXX no formula support yet
+
     return $excel;
 }
 
diff --git a/lib/Spreadsheet/Template/Generator/Parser/XLSX.pm b/lib/Spreadsheet/Template/Generator/Parser/XLSX.pm
index 904c04b..cf7c997 100644
--- a/lib/Spreadsheet/Template/Generator/Parser/XLSX.pm
+++ b/lib/Spreadsheet/Template/Generator/Parser/XLSX.pm
@@ -1,6 +1,7 @@
 package Spreadsheet::Template::Generator::Parser::XLSX;
 use Moose;
 
+use POSIX;
 use Spreadsheet::XLSX;
 use XML::Entities;
 use XML::Twig;
@@ -48,6 +49,16 @@ sub make_excel {
         $sheet->{ColWidth} = [
             map { defined $_ ? 0+$_ : 0+$default_column_width } @column_widths
         ];
+
+        for my $formula ($root->find_nodes('//f')) {
+            my $cell_id = $formula->parent->att('r');
+            my ($col, $row) = $cell_id =~ /([A-Z]+)([0-9]+)/;
+            $col =~ tr/A-Z/0-9A-P/;
+            $col = POSIX::strtol($col, 26);
+            $row = $row - 1;
+            my $cell = $sheet->get_cell($row, $col);
+            $cell->{Formula} = "=" . $formula->text;
+        }
     }
 
     return $excel;