| Subject: | Bug in Utility::ExcelFmt with 3-Part Formats (Patch) |
Utility.pm is throwing warnings when a cell contains a three-part format
([pos];[neg];[zero]) and when the value of the cell is zero.
Furthermore the formatted values produced by Cell::value() are wrong.
The attached Excel file fmtest.xls contains 1 sheet with the following
values:
A1: 5
A2: 0
A3: -3
The number format in all three cells is 0.00;(0.0);0% so Excel displays
them as:
A1: 5.00
A2: 0%
A3: (3.0)
The attached program utilwarn.pl produces this output (removed the
output from cluck() for brevity):
(0, 0) : (5.0)
Use of uninitialized value in substitution (s///) at
/usr/local/share/perl/5.8.8/Spreadsheet/ParseExcel/Utility.pm line 183.
Use of uninitialized value in substitution (s///) at
/usr/local/share/perl/5.8.8/Spreadsheet/ParseExcel/Utility.pm line 188.
Use of uninitialized value in length at
/usr/local/share/perl/5.8.8/Spreadsheet/ParseExcel/Utility.pm line 209.
(1, 0) : 0
(2, 0) : -300%
Workbook contains 1 sheets
Completed with 3 cells and 3 warnings
After applying the attached patch to Utility.pm the output is:
(0, 0) : 5.00
(1, 0) : 0%
(2, 0) : (-3.0)
Workbook contains 1 sheets
Completed with 3 cells and 0 warnings
fmtest.xls was produced with MS Excel 2003. info.txt contains my
relevant system info.
| Subject: | fmtest.xls |
Message body not shown because it is not plain text.
| Subject: | info.txt |
Perl version : 5.008008
OS name : linux
Module versions: (not all are required)
Spreadsheet::ParseExcel 0.49
Scalar::Util 1.18
Unicode::Map (not installed)
Spreadsheet::WriteExcel 2.25
Parse::RecDescent 1.94
File::Temp 0.21
OLE::Storage_Lite 0.18
IO::Stringy 2.110
| Subject: | Utility.pm.diff |
--- Utility.pm 2009-04-13 13:01:48.000000000 -0400
+++ Utility.pm.new 2009-04-13 13:02:36.000000000 -0400
@@ -151,13 +151,13 @@
}
elsif ( @formats == 3 ) {
if ( $number == 0 ) {
- $section = 3;
+ $section = 2;
}
elsif ( $number < 0 ) {
- $section = 2;
+ $section = 1;
}
else {
- $section = 1;
+ $section = 0;
}
}
else {
| Subject: | utilwarn.pl |
#!/usr/bin/perl
use warnings;
use strict;
use Spreadsheet::ParseExcel;
use Carp qw(cluck);
my ($c, $w) = (0, 0);
$SIG{__WARN__} = sub { cluck $_[0]; $w++; };
my $filename = "fmtest.xls";
my $parser = Spreadsheet::ParseExcel->new(CellHandler => \&parser_cb, NotSetCell => 1);
my $workbook = $parser->Parse($filename) or die "Could not open file!";
my @sheets = $workbook->worksheets();
printf "Workbook contains %d sheets\n", scalar @sheets;
print "Completed with $c cells and $w warnings\n";
exit 0;
sub parser_cb
{
my ($workbook, $sheet_index, $row, $col, $cell) = @_;
my $v = $cell->value();
print "($row, $col) : $v\n";
$c++;
}