add some tests

5 files changed, 672 insertions, 0 deletions

diff --git a/t/basic.t b/t/basic.t
new file mode 100644
index 0000000..d85ff06
--- /dev/null
+++ b/t/basic.t
@@ -0,0 +1,375 @@
+#!/usr/bin/env perl
+use strict;
+use warnings;
+use Test::More;
+
+use Spreadsheet::ParseXLSX;
+use Spreadsheet::Template;
+use Spreadsheet::Template::Generator;
+
+my $data;
+
+{
+    my $generator = Spreadsheet::Template::Generator->new;
+    $data = $generator->generate('t/data/Test.xlsx');
+    my $expected = do { local $/; local @ARGV = ('t/data/Test.json'); <> };
+    is($data, $expected, "got the correct serialization");
+}
+
+{
+    my $template = Spreadsheet::Template->new;
+    my $excel = $template->render($data, {});
+    open my $fh, '<', \$excel;
+
+    my $wb = Spreadsheet::ParseXLSX->new->parse($fh);
+    isa_ok($wb, 'Spreadsheet::ParseExcel::Workbook');
+
+    is($wb->worksheet_count, 1);
+    # is($workbook->get_filename, $filename); # XXX
+
+    my $ws = $wb->worksheet(0);
+    isa_ok($ws, 'Spreadsheet::ParseExcel::Worksheet');
+    is($ws->get_name, 'Sheet1');
+    is_deeply([$ws->row_range], [0, 5]);
+    is_deeply([$ws->col_range], [0, 4]);
+
+    {
+        my $cell = $ws->get_cell(0, 0);
+        isa_ok($cell, 'Spreadsheet::ParseExcel::Cell');
+        is($cell->unformatted, "Colored Cell");
+        is($cell->value, "Colored Cell");
+        is($cell->type, 'Text');
+        is($cell->{Formula}, undef);
+
+        my $format = $cell->get_format;
+        isa_ok($format, 'Spreadsheet::ParseExcel::Format');
+        is($format->{AlignH}, 0);
+        is($format->{AlignV}, 2);
+        ok(!$format->{Wrap});
+        is_deeply($format->{Fill}, [1, '#9BBB59', '#FFFFFF']);
+        is_deeply($format->{BdrStyle}, [(0) x 4]);
+        is_deeply($format->{BdrColor}, [(undef) x 4]);
+        is_deeply($format->{BdrDiag}, [0, 0, undef]);
+
+        my $font = $format->{Font};
+        isa_ok($font, 'Spreadsheet::ParseExcel::Font');
+        is($font->{Name}, 'Calibri');
+        is($font->{Height}, 12);
+        is($font->{Color}, '#FFFF00');
+    }
+
+    {
+        my $cell = $ws->get_cell(0, 1);
+        isa_ok($cell, 'Spreadsheet::ParseExcel::Cell');
+        is($cell->unformatted, "Wide Cell (25.00)");
+        is($cell->value, "Wide Cell (25.00)");
+        is($cell->type, 'Text');
+        is($cell->{Formula}, undef);
+
+        my $format = $cell->get_format;
+        isa_ok($format, 'Spreadsheet::ParseExcel::Format');
+        is($format->{AlignH}, 0);
+        is($format->{AlignV}, 2);
+        ok(!$format->{Wrap});
+        is_deeply($format->{Fill}, [0, undef, undef]);
+        is_deeply($format->{BdrStyle}, [(0) x 4]);
+        is_deeply($format->{BdrColor}, [(undef) x 4]);
+        is_deeply($format->{BdrDiag}, [0, 0, undef]);
+
+        my $font = $format->{Font};
+        isa_ok($font, 'Spreadsheet::ParseExcel::Font');
+        is($font->{Name}, 'Calibri');
+        { local $TODO = "default font heights don't work well";
+        is($font->{Height}, 12);
+        }
+        is($font->{Color}, '#000000');
+    }
+
+    {
+        my $cell = $ws->get_cell(0, 2);
+        isa_ok($cell, 'Spreadsheet::ParseExcel::Cell');
+        is($cell->unformatted, "Bordered Cell w/ Text Wrap");
+        is($cell->value, "Bordered Cell w/ Text Wrap");
+        is($cell->type, 'Text');
+        is($cell->{Formula}, undef);
+
+        my $format = $cell->get_format;
+        isa_ok($format, 'Spreadsheet::ParseExcel::Format');
+        is($format->{AlignH}, 0);
+        is($format->{AlignV}, 2);
+        ok($format->{Wrap});
+        is_deeply($format->{Fill}, [0, undef, undef]);
+        is_deeply($format->{BdrStyle}, [(1) x 4]);
+        is_deeply($format->{BdrColor}, [('#000000') x 4]);
+        is_deeply($format->{BdrDiag}, [0, 0, undef]);
+
+        my $font = $format->{Font};
+        isa_ok($font, 'Spreadsheet::ParseExcel::Font');
+        is($font->{Name}, 'Calibri');
+        { local $TODO = "default font heights don't work well";
+        is($font->{Height}, 12);
+        }
+        is($font->{Color}, '#000000');
+    }
+
+    {
+        my $cell = $ws->get_cell(0, 3);
+        isa_ok($cell, 'Spreadsheet::ParseExcel::Cell');
+        is($cell->unformatted, "Middle Valigned");
+        is($cell->value, "Middle Valigned");
+        is($cell->type, 'Text');
+        is($cell->{Formula}, undef);
+
+        my $format = $cell->get_format;
+        isa_ok($format, 'Spreadsheet::ParseExcel::Format');
+        is($format->{AlignH}, 0);
+        is($format->{AlignV}, 1);
+        ok(!$format->{Wrap});
+        is_deeply($format->{Fill}, [0, undef, undef]);
+        is_deeply($format->{BdrStyle}, [(0) x 4]);
+        is_deeply($format->{BdrColor}, [(undef) x 4]);
+        is_deeply($format->{BdrDiag}, [0, 0, undef]);
+
+        my $font = $format->{Font};
+        isa_ok($font, 'Spreadsheet::ParseExcel::Font');
+        is($font->{Name}, 'Calibri');
+        { local $TODO = "default font heights don't work well";
+        is($font->{Height}, 12);
+        }
+        is($font->{Color}, '#000000');
+    }
+
+    {
+        my $cell = $ws->get_cell(0, 4);
+        isa_ok($cell, 'Spreadsheet::ParseExcel::Cell');
+        is($cell->unformatted, "Right Aligned and text wrapped");
+        is($cell->value, "Right Aligned and text wrapped");
+        is($cell->type, 'Text');
+        is($cell->{Formula}, undef);
+
+        my $format = $cell->get_format;
+        isa_ok($format, 'Spreadsheet::ParseExcel::Format');
+        is($format->{AlignH}, 3);
+        is($format->{AlignV}, 2);
+        ok($format->{Wrap});
+        is_deeply($format->{Fill}, [0, undef, undef]);
+        is_deeply($format->{BdrStyle}, [(0) x 4]);
+        is_deeply($format->{BdrColor}, [(undef) x 4]);
+        is_deeply($format->{BdrDiag}, [0, 0, undef]);
+
+        my $font = $format->{Font};
+        isa_ok($font, 'Spreadsheet::ParseExcel::Font');
+        is($font->{Name}, 'Calibri');
+        { local $TODO = "default font heights don't work well";
+        is($font->{Height}, 12);
+        }
+        is($font->{Color}, '#000000');
+    }
+
+    for my $i (0..4) {
+        is($ws->get_cell(1, $i), undef);
+    }
+
+    {
+        my $cell = $ws->get_cell(2, 0);
+        isa_ok($cell, 'Spreadsheet::ParseExcel::Cell');
+        is($cell->unformatted, 10);
+        is($cell->value, 10);
+        is($cell->type, 'Numeric');
+        is($cell->{Formula}, undef);
+
+        my $format = $cell->get_format;
+        isa_ok($format, 'Spreadsheet::ParseExcel::Format');
+        is($format->{AlignH}, 0);
+        is($format->{AlignV}, 2);
+        ok(!$format->{Wrap});
+        is_deeply($format->{Fill}, [0, undef, undef]);
+        is_deeply($format->{BdrStyle}, [(0) x 4]);
+        is_deeply($format->{BdrColor}, [(undef) x 4]);
+        is_deeply($format->{BdrDiag}, [0, 0, undef]);
+
+        my $font = $format->{Font};
+        isa_ok($font, 'Spreadsheet::ParseExcel::Font');
+        is($font->{Name}, 'Calibri');
+        { local $TODO = "default font heights don't work well";
+        is($font->{Height}, 12);
+        }
+        is($font->{Color}, '#000000');
+    }
+
+    for my $i (1..4) {
+        is($ws->get_cell(2, $i), undef);
+    }
+
+    {
+        my $cell = $ws->get_cell(3, 0);
+        isa_ok($cell, 'Spreadsheet::ParseExcel::Cell');
+        is($cell->unformatted, 20);
+        is($cell->value, 20);
+        is($cell->type, 'Numeric');
+        is($cell->{Formula}, undef);
+
+        my $format = $cell->get_format;
+        isa_ok($format, 'Spreadsheet::ParseExcel::Format');
+        is($format->{AlignH}, 0);
+        is($format->{AlignV}, 2);
+        ok(!$format->{Wrap});
+        is_deeply($format->{Fill}, [0, undef, undef]);
+        is_deeply($format->{BdrStyle}, [(0) x 4]);
+        is_deeply($format->{BdrColor}, [(undef) x 4]);
+        is_deeply($format->{BdrDiag}, [0, 0, undef]);
+
+        my $font = $format->{Font};
+        isa_ok($font, 'Spreadsheet::ParseExcel::Font');
+        is($font->{Name}, 'Calibri');
+        { local $TODO = "default font heights don't work well";
+        is($font->{Height}, 12);
+        }
+        is($font->{Color}, '#000000');
+    }
+
+    for my $i (1..2) {
+        is($ws->get_cell(3, $i), undef);
+    }
+
+    {
+        my $cell = $ws->get_cell(3, 3);
+        isa_ok($cell, 'Spreadsheet::ParseExcel::Cell');
+        is($cell->unformatted, 2.5);
+        is($cell->value, "\$2.50");
+        is($cell->type, 'Numeric');
+        is($cell->{Formula}, undef);
+
+        my $format = $cell->get_format;
+        isa_ok($format, 'Spreadsheet::ParseExcel::Format');
+        is($format->{AlignH}, 0);
+        is($format->{AlignV}, 2);
+        ok(!$format->{Wrap});
+        is_deeply($format->{Fill}, [0, undef, undef]);
+        is_deeply($format->{BdrStyle}, [(0) x 4]);
+        is_deeply($format->{BdrColor}, [(undef) x 4]);
+        is_deeply($format->{BdrDiag}, [0, 0, undef]);
+
+        my $font = $format->{Font};
+        isa_ok($font, 'Spreadsheet::ParseExcel::Font');
+        is($font->{Name}, 'Calibri');
+        { local $TODO = "default font heights don't work well";
+        is($font->{Height}, 12);
+        }
+        is($font->{Color}, '#000000');
+    }
+
+    {
+        my $cell = $ws->get_cell(3, 4);
+        isa_ok($cell, 'Spreadsheet::ParseExcel::Cell');
+        is($cell->unformatted, "<< currency cell");
+        is($cell->value, "<< currency cell");
+        is($cell->type, 'Text');
+        is($cell->{Formula}, undef);
+
+        my $format = $cell->get_format;
+        isa_ok($format, 'Spreadsheet::ParseExcel::Format');
+        is($format->{AlignH}, 2);
+        is($format->{AlignV}, 2);
+        ok(!$format->{Wrap});
+        is_deeply($format->{Fill}, [0, undef, undef]);
+        is_deeply($format->{BdrStyle}, [(0) x 4]);
+        is_deeply($format->{BdrColor}, [(undef) x 4]);
+        is_deeply($format->{BdrDiag}, [0, 0, undef]);
+
+        my $font = $format->{Font};
+        isa_ok($font, 'Spreadsheet::ParseExcel::Font');
+        is($font->{Name}, 'Calibri');
+        is($font->{Height}, 12);
+        is($font->{Color}, '#4BACC6');
+    }
+
+    {
+        my $cell = $ws->get_cell(4, 0);
+        isa_ok($cell, 'Spreadsheet::ParseExcel::Cell');
+        is($cell->unformatted, 30);
+        is($cell->value, 30);
+        is($cell->type, 'Numeric');
+        is($cell->{Formula}, undef);
+
+        my $format = $cell->get_format;
+        isa_ok($format, 'Spreadsheet::ParseExcel::Format');
+        is($format->{AlignH}, 0);
+        is($format->{AlignV}, 2);
+        ok(!$format->{Wrap});
+        is_deeply($format->{Fill}, [0, undef, undef]);
+        is_deeply($format->{BdrStyle}, [(0) x 4]);
+        is_deeply($format->{BdrColor}, [(undef) x 4]);
+        is_deeply($format->{BdrDiag}, [0, 0, undef]);
+
+        my $font = $format->{Font};
+        isa_ok($font, 'Spreadsheet::ParseExcel::Font');
+        is($font->{Name}, 'Calibri');
+        { local $TODO = "default font heights don't work well";
+        is($font->{Height}, 12);
+        }
+        is($font->{Color}, '#000000');
+    }
+
+    for my $i (1..4) {
+        is($ws->get_cell(4, $i), undef);
+    }
+
+    {
+        my $cell = $ws->get_cell(5, 0);
+        isa_ok($cell, 'Spreadsheet::ParseExcel::Cell');
+        is($cell->unformatted, 60);
+        is($cell->value, 60);
+        is($cell->type, 'Numeric');
+        is($cell->{Formula}, 'SUM(A3:A5)');
+
+        my $format = $cell->get_format;
+        isa_ok($format, 'Spreadsheet::ParseExcel::Format');
+        is($format->{AlignH}, 0);
+        is($format->{AlignV}, 2);
+        ok(!$format->{Wrap});
+        is_deeply($format->{Fill}, [0, undef, undef]);
+        is_deeply($format->{BdrStyle}, [(0) x 4]);
+        is_deeply($format->{BdrColor}, [(undef) x 4]);
+        is_deeply($format->{BdrDiag}, [0, 0, undef]);
+
+        my $font = $format->{Font};
+        isa_ok($font, 'Spreadsheet::ParseExcel::Font');
+        is($font->{Name}, 'Calibri');
+        { local $TODO = "default font heights don't work well";
+        is($font->{Height}, 12);
+        }
+        is($font->{Color}, '#000000');
+    }
+
+    {
+        my $cell = $ws->get_cell(5, 1);
+        isa_ok($cell, 'Spreadsheet::ParseExcel::Cell');
+        is($cell->unformatted, "<< formula cell");
+        is($cell->value, "<< formula cell");
+        is($cell->type, 'Text');
+        is($cell->{Formula}, undef);
+
+        my $format = $cell->get_format;
+        isa_ok($format, 'Spreadsheet::ParseExcel::Format');
+        is($format->{AlignH}, 3);
+        is($format->{AlignV}, 2);
+        ok(!$format->{Wrap});
+        is_deeply($format->{Fill}, [1, '#EEECE1', '#FFFFFF']);
+        is_deeply($format->{BdrStyle}, [(0) x 4]);
+        is_deeply($format->{BdrColor}, [(undef) x 4]);
+        is_deeply($format->{BdrDiag}, [0, 0, undef]);
+
+        my $font = $format->{Font};
+        isa_ok($font, 'Spreadsheet::ParseExcel::Font');
+        is($font->{Name}, 'Calibri');
+        is($font->{Height}, 12);
+        is($font->{Color}, '#F79646');
+    }
+
+    for my $i (2..4) {
+        is($ws->get_cell(5, $i), undef);
+    }
+}
+
+done_testing;
diff --git a/t/data/Test.json b/t/data/Test.json
new file mode 100644
index 0000000..64fc6e1
--- /dev/null
+++ b/t/data/Test.json
@@ -0,0 +1,151 @@
+{
+   "selection" : 0,
+   "worksheets" : [
+      {
+         "cells" : [
+            [
+               {
+                  "contents" : "Colored Cell",
+                  "format" : {
+                     "bg_color" : "#9BBB59",
+                     "color" : "#FFFF00",
+                     "pattern" : "solid",
+                     "size" : 12
+                  },
+                  "type" : "string"
+               },
+               {
+                  "contents" : "Wide Cell (25.00)",
+                  "type" : "string"
+               },
+               {
+                  "contents" : "Bordered Cell w/ Text Wrap",
+                  "format" : {
+                     "border" : [
+                        "thin",
+                        "thin",
+                        "thin",
+                        "thin"
+                     ],
+                     "border_color" : [
+                        "#000000",
+                        "#000000",
+                        "#000000",
+                        "#000000"
+                     ],
+                     "text_wrap" : true
+                  },
+                  "type" : "string"
+               },
+               {
+                  "contents" : "Middle Valigned",
+                  "format" : {
+                     "valign" : "vcenter"
+                  },
+                  "type" : "string"
+               },
+               {
+                  "contents" : "Right Aligned and text wrapped",
+                  "format" : {
+                     "align" : "right",
+                     "text_wrap" : true
+                  },
+                  "type" : "string"
+               }
+            ],
+            [
+               {},
+               {},
+               {},
+               {},
+               {}
+            ],
+            [
+               {
+                  "contents" : 10,
+                  "type" : "number"
+               },
+               {},
+               {},
+               {},
+               {}
+            ],
+            [
+               {
+                  "contents" : 20,
+                  "type" : "number"
+               },
+               {},
+               {},
+               {
+                  "contents" : 2.5,
+                  "format" : {
+                     "num_format" : "\"$\"#,##0.00"
+                  },
+                  "type" : "number"
+               },
+               {
+                  "contents" : "<< currency cell",
+                  "format" : {
+                     "align" : "center",
+                     "color" : "#4BACC6",
+                     "size" : 12
+                  },
+                  "type" : "string"
+               }
+            ],
+            [
+               {
+                  "contents" : 30,
+                  "type" : "number"
+               },
+               {},
+               {},
+               {},
+               {}
+            ],
+            [
+               {
+                  "contents" : 60,
+                  "formula" : "SUM(A3:A5)",
+                  "type" : "number"
+               },
+               {
+                  "contents" : "<< formula cell",
+                  "format" : {
+                     "align" : "right",
+                     "bg_color" : "#EEECE1",
+                     "color" : "#F79646",
+                     "pattern" : "solid",
+                     "size" : 12
+                  },
+                  "type" : "string"
+               },
+               {},
+               {},
+               {}
+            ]
+         ],
+         "column_widths" : [
+            10.83203125,
+            25.83203125,
+            10,
+            15,
+            18.1640625
+         ],
+         "name" : "Sheet1",
+         "row_heights" : [
+            45,
+            15,
+            15,
+            15,
+            15,
+            15
+         ],
+         "selection" : [
+            3,
+            4
+         ]
+      }
+   ]
+}
diff --git a/t/data/Test.xlsx b/t/data/Test.xlsx
new file mode 100644
index 0000000..7c1f8ad
--- /dev/null
+++ b/t/data/Test.xlsx
diff --git a/t/data/template.json b/t/data/template.json
new file mode 100644
index 0000000..a57b33e
--- /dev/null
+++ b/t/data/template.json
@@ -0,0 +1,50 @@
+%% my $default = { color => '#000000', size => 14 };
+%% format(normal     => $default.merge({}));
+%% format(date       => $default.merge({ num_format => 'd-mmm' }));
+%% format(money      => $default.merge({ num_format => '$#,##0.00_);[Red]($#,##0.00)' }));
+%% format(title      => $default.merge({ bg_color   => '#c6d9f0', pattern => 'solid' }));
+%% format(bold       => $default.merge({ bold       => true() }));
+%% format(bold_money => $default.merge({ bold       => true(), num_format => '$#,##0.00_);[Red]($#,##0.00)' }));
+{
+   "selection"  : 0,
+   "worksheets" : [
+      {
+         "column_widths" : [ 14.6, 18.5, 15.8, 12.2 ],
+         "name"          : "Report 1",
+         "selection"     : [ 1, 9 ],
+         "row_heights"   : [ 25, 18, 18, 18 ],
+         "cells"         : [
+            [
+               [% c("Descriptions", "title") %],
+               [% c("Numbers",      "title") %],
+               [% c("Dates",        "title") %],
+               [% c("Money",        "title") %]
+            ],
+            %% for $rows -> $row {
+            [
+               [% c($row.description, "normal")              %],
+               [% c($row.number,      "normal", "number")    %],
+               [% c($row.date,        "date",   "date_time") %],
+               [% c($row.money,       "money",  "number")    %]
+            ],
+            %% }
+            [
+               [% c("Totals:", "bold") %],
+               [% c(
+                  $rows.map(-> $a { $a.number }).reduce(-> $a, $b { $a + $b }),
+                  "bold",
+                  "number",
+                  formula => 'SUM(B2:B' ~ ($rows.size() + 1) ~ ')',
+               ) %],
+               [% c("", "bold") %],
+               [% c(
+                  $rows.map(-> $a { $a.money }).reduce(-> $a, $b { $a + $b }),
+                  "bold_money",
+                  "number",
+                  formula => 'SUM(D2:D' ~ ($rows.size() + 1) ~ ')',
+               ) %]
+            ]
+         ]
+      }
+   ]
+}
diff --git a/t/template.t b/t/template.t
new file mode 100644
index 0000000..fd581dd
--- /dev/null
+++ b/t/template.t
@@ -0,0 +1,96 @@
+#!/usr/bin/env perl
+use strict;
+use warnings;
+use Test::More;
+
+use Spreadsheet::ParseXLSX;
+use Spreadsheet::Template;
+
+my $data = do { local $/; local @ARGV = ('t/data/template.json'); <> };
+{
+    my $excel = Spreadsheet::Template->new->render(
+        $data,
+        {
+            rows => [
+                {
+                    description => "Row 1",
+                    number      => 26,
+                    date        => '2013-03-21T00:00:00',
+                    money       => 3.50,
+                },
+                {
+                    description => "Row 2",
+                    number      => 83,
+                    date        => '2013-06-25T00:00:00',
+                    money       => 84.28,
+                },
+            ],
+        }
+    );
+
+    open my $fh, '<', \$excel;
+    my $wb = Spreadsheet::ParseXLSX->new->parse($fh);
+    is($wb->worksheet_count, 1);
+
+    my $ws = $wb->worksheet(0);
+    is($ws->get_name, 'Report 1');
+    is_deeply([$ws->row_range], [0, 3]);
+    is_deeply([$ws->col_range], [0, 3]);
+
+    my @values = (
+        ["Descriptions", "Numbers", "Dates",  "Money"  ],
+        ["Row 1",        "26",      "21-Mar", "\$3.50" ],
+        ["Row 2",        "83",      "25-Jun", "\$84.28"],
+        ["Totals:",      "109",     "",       "\$87.78"],
+    );
+    for my $row (0..3) {
+        for my $col (0..3) {
+            is($ws->get_cell($row, $col)->value, $values[$row][$col]);
+        }
+    }
+}
+
+{
+    my $excel = Spreadsheet::Template->new->render(
+        $data,
+        {
+            rows => [
+                {
+                    description => "Another Row",
+                    number      => 42,
+                    date        => '2012-12-25T00:00:00',
+                    money       => 1.22,
+                },
+                {
+                    description => "Yet Another Row",
+                    number      => 0,
+                    date        => '2011-03-09T00:00:00',
+                    money       => 1001.01,
+                },
+            ],
+        }
+    );
+
+    open my $fh, '<', \$excel;
+    my $wb = Spreadsheet::ParseXLSX->new->parse($fh);
+    is($wb->worksheet_count, 1);
+
+    my $ws = $wb->worksheet(0);
+    is($ws->get_name, 'Report 1');
+    is_deeply([$ws->row_range], [0, 3]);
+    is_deeply([$ws->col_range], [0, 3]);
+
+    my @values = (
+        ["Descriptions",    "Numbers", "Dates",  "Money"     ],
+        ["Another Row",     "42",      "25-Dec", "\$1.22"    ],
+        ["Yet Another Row", "0",       "9-Mar",  "\$1,001.01"],
+        ["Totals:",         "42",      "",       "\$1,002.23"],
+    );
+    for my $row (0..3) {
+        for my $col (0..3) {
+            is($ws->get_cell($row, $col)->value, $values[$row][$col]);
+        }
+    }
+}
+
+done_testing;