#!/usr/bin/perl -w
###############################################################################
#
# A test for Spreadsheet::ParseExcel.
#
# Tests for default number format handling using FmtExcel(). See note below.
#
# reverse('�'), January 2009, John McNamara, jmcnamara@cpan.org
#
use strict;
use Spreadsheet::ParseExcel::Utility 'ExcelFmt';
use Test::More tests => 47;
###############################################################################
#
# Test cases for default Excel formats. The hex "Index" number is the internal
# index used by Excel. The tests are sorted by category rather than index.
#
my @testcases = (
# No, Index,Number, Expected, Format string, TODO note (if any).
[ 1, 0x00, 1234.567, '1234.567', 'General' ],
[ 2, 0x01, 1234.567, '1235', '0' ],
[ 3, 0x02, 1234.567, '1234.57', '0.00' ],
[ 4, 0x03, 1234.567, '1,235', '#,##0' ],
[ 5, 0x04, 1234.567, '1,234.57', '#,##0.00' ],
[ 6, 0x05, 1234.567, '$1,235', '($#,##0_);($#,##0)' ],
[ 7, 0x05, -1234.567, '-$1,235', '($#,##0_);($#,##0)' ],
[ 8, 0x06, 1234.567, '$1,235', '($#,##0_);[Red]($#,##0)' ],
[ 9, 0x06, -1234.567, '-$1,235', '($#,##0_);[Red]($#,##0)' ],
[ 10, 0x07, 1234.567, '$1,234.57', '($#,##0.00_);($#,##0.00)' ],
[ 11, 0x07, -1234.567, '-$1,234.57', '($#,##0.00_);($#,##0.00)' ],
[ 12, 0x08, 1234.567, '$1,234.57', '($#,##0.00_);[Red]($#,##0.00)' ],
[ 13, 0x08, -1234.567, '-$1,234.57', '($#,##0.00_);[Red]($#,##0.00)' ],
[ 14, 0x25, 1234.567, '1,235', '(#,##0_);(#,##0)' ],
[ 15, 0x25, -1234.567, '-1,235', '(#,##0_);(#,##0)' ],
[ 16, 0x26, 1234.567, '1,235', '(#,##0_);[Red](#,##0)' ],
[ 17, 0x26, -1234.567, '-1,235', '(#,##0_);[Red](#,##0)' ],
[ 18, 0x27, 1234.567, '1,234.57', '(#,##0.00_);(#,##0.00)' ],
[ 19, 0x27, -1234.567, '-1,234.57', '(#,##0.00_);(#,##0.00)' ],
[ 20, 0x28, 1234.567, '1,234.57', '(#,##0.00_);[Red](#,##0.00)' ],
[ 21, 0x28, -1234.567, '-1,234.57', '(#,##0.00_);[Red](#,##0.00)' ],
[ 22, 0x29, 1234.567, '1,235', '_(* #,##0_);_(* (#,##0);_(* "-"_);_(@_)' ],
[ 23, 0x29, -1234.567, '-1,235', '_(* #,##0_);_(* (#,##0);_(* "-"_);_(@_)' ],
[ 24, 0x2A, 1234.567, '$ 1,235', '_($* #,##0_);_($* (#,##0);_($* "-"_);_(@_)' ],
[ 25, 0x2A, -1234.567, '-$ 1,235', '_($* #,##0_);_($* (#,##0);_($* "-"_);_(@_)' ],
[ 26, 0x2B, 1234.567, '1,234.57', '_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)' ],
[ 27, 0x2B, -1234.567, '- 1,234.57', '_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)', 'TODO: Minor difference.' ],
[ 28, 0x2C, 1234.567, '$ 1,234.57', '_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)' ],
[ 29, 0x0E, 37653.521, '2/1/03', 'm/d/yy' ],
[ 30, 0x0F, 37653.521, '1-Feb-03', 'd-mmm-yy' ],
[ 31, 0x10, 37653.521, '1-Feb', 'd-mmm' ],
[ 32, 0x11, 37653.521, 'Feb-03', 'mmm-yy' ],
[ 33, 0x12, 37653.521, '12:30 PM', 'h:mm AM/PM' ],
[ 34, 0x13, 37653.521, '12:30:14 PM', 'h:mm:ss AM/PM' ],
[ 35, 0x14, 37653.521, '12:30', 'h:mm' ],
[ 36, 0x15, 37653.521, '12:30:14', 'h:mm:ss' ],
[ 37, 0x16, 37653.521, '2/1/03 12:30', 'm/d/yy h:mm' ],
[ 38, 0x2D, 37653.521, '30:14', 'mm:ss' ],
[ 39, 0x2E, 3.0141204, '72:20:20', '[h]:mm:ss'],
[ 40, 0x2F, 37653.521, '30:14.4', 'mm:ss.0' ],
[ 41, 0x30, 1234.567, '1.2E+3', '##0.0E+0' ],
[ 42, 0x31, 1234.567, '1234.567', '@' ],
[ 43, 0x09, 0.567, '57%', '0%' ],
[ 44, 0x0A, 0.567, '56.70%', '0.00%' ],
[ 45, 0x0B, 1234.567, '1.23E+03', '0.00E+00' ],
[ 46, 0x0C, 0.75, '3/4', '# ?/?' ],
[ 47, 0x0D, 0.3125, '5/16', '# ??/??' ],
);
###############################################################################
#
# Run tests.
#
for my $test_ref (@testcases) {
my $number = $test_ref->[2];
my $expected = $test_ref->[3];
my $format = $test_ref->[4];
my $got = ExcelFmt( $format, $number );
local $TODO = $test_ref->[5] if defined $test_ref->[5];
is( $got, $expected, " \tFormat = $format" );
}
__END__