From 1b404926df08ac4bbe6cbd2c7ada14de6cebda63 Mon Sep 17 00:00:00 2001 From: Jesse Luehrs Date: Thu, 18 Jul 2013 17:16:39 -0400 Subject: add some tests --- t/basic.t | 375 +++++++++++++++++++++++++++++++++++++++++++++++++++ t/data/Test.json | 151 +++++++++++++++++++++ t/data/Test.xlsx | Bin 0 -> 35554 bytes t/data/template.json | 50 +++++++ t/template.t | 96 +++++++++++++ 5 files changed, 672 insertions(+) create mode 100644 t/basic.t create mode 100644 t/data/Test.json create mode 100644 t/data/Test.xlsx create mode 100644 t/data/template.json create mode 100644 t/template.t 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 Binary files /dev/null and b/t/data/Test.xlsx differ 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; -- cgit v1.2.3-54-g00ecf