This queue is for tickets about the Spreadsheet-XLSX CPAN distribution.

Report information
The Basics
Id:
79016
Status:
open
Priority:
Low/Low

People
Owner:
Nobody in particular
Requestors:
EDAVIS [...] cpan.org
ftl [...] dnv.com
Cc:
AdminCc:

BugTracker
Severity:
Normal
Broken in:
0.13-withoutworldwriteables
Fixed in:
(no value)



Subject: #n/a generates a lot of warnings
these breaks http requests under some conditions. recommend changing line 96 in Fmt2007.pm from } elsif( int($oCell->{Val}) != $oCell->{Val}) { to } elsif($oCell->{Val} =~ /\d/ && int($oCell->{Val}) != $oCell->{Val}) { ie check that it may be a number at all, before using int.
Subject: Patch to fix warnings when #N/A appears in cells
Many spreadsheets produce lots of warnings 'Argument "#N/A" isn't numeric in int'. This fixes it. --- Spreadsheet-XLSX-0.13/lib/Spreadsheet/XLSX.pm 2012-08-16 12:02:49.047772667 +0100 +++ Spreadsheet-XLSX-0.13-new/lib/Spreadsheet/XLSX.pm 2012-08-16 12:02:24.778332419 +0100 @@ -210,7 +210,7 @@ ); $cell->{_Value} = $oBook->{FmtClass}->ValFmt($cell, $oBook); - if ($type eq "Date" && $v<1){ #then this is Excel time field + if ($type eq "Date" && $v ne "#N/A" && $v<1){ #then this is Excel time field $cell->{Type}="Text"; $cell->{Val}=$cell->{_Value}; }
Actually Fmt2007.pm needs fixing too, here is a more comprehensive patch: --- lib/Spreadsheet/XLSX/Fmt2007.pm 2010-05-16 10:07:42.000000000 +0100 +++ lib/Spreadsheet/XLSX/Fmt2007.pm 2012-08-16 12:10:44.586385956 +0100 @@ -93,7 +93,9 @@ if ($oCell->{Type} eq 'Numeric') { if($oCell->{Format}){ $sFmtStr=$oCell->{Format}; - } elsif(int($oCell->{Val}) != $oCell->{Val}) { + } elsif($oCell->{Val} eq "#N/A" or $oCell->{Val} eq "#VALUE!") { + $sFmtStr = '0'; + } elsif(int($oCell->{Val}) != $oCell->{Val}) { $sFmtStr = '0.00'; } else { --- lib/Spreadsheet/XLSX.pm 2012-08-16 12:02:49.047772667 +0100 +++ lib/Spreadsheet/XLSX.pm 2012-08-16 12:02:24.778332419 +0100 @@ -210,7 +210,7 @@ ); $cell->{_Value} = $oBook->{FmtClass}->ValFmt($cell, $oBook); - if ($type eq "Date" && $v<1){ #then this is Excel time field + if ($type eq "Date" && $v ne "#N/A" && $v<1){ #then this is Excel time field $cell->{Type}="Text"; $cell->{Val}=$cell->{_Value}; }
#DIV/0! should also be recognized. New patch: --- lib/Spreadsheet/XLSX/Fmt2007.pm 2010-05-16 10:07:42.000000000 +0100 +++ lib/Spreadsheet/XLSX/Fmt2007.pm 2012-08-16 12:10:44.586385956 +0100 @@ -93,7 +93,9 @@ if ($oCell->{Type} eq 'Numeric') { if($oCell->{Format}){ $sFmtStr=$oCell->{Format}; - } elsif(int($oCell->{Val}) != $oCell->{Val}) { + } elsif($oCell->{Val} eq "#N/A" or $oCell->{Val} eq "#VALUE!" or $oCell->{Val} eq "#DIV/0!") { + $sFmtStr = '0'; + } elsif(int($oCell->{Val}) != $oCell->{Val}) { $sFmtStr = '0.00'; } else { --- lib/Spreadsheet/XLSX.pm 2012-08-16 12:02:49.047772667 +0100 +++ lib/Spreadsheet/XLSX.pm 2012-08-16 12:02:24.778332419 +0100 @@ -210,7 +210,7 @@ ); $cell->{_Value} = $oBook->{FmtClass}->ValFmt($cell, $oBook); - if ($type eq "Date" && $v<1){ #then this is Excel time field + if ($type eq "Date" && $v ne "#N/A" && $v<1){ #then this is Excel time field $cell->{Type}="Text"; $cell->{Val}=$cell->{_Value}; }
To make a test case spreadsheet containing the values #DIV/0!, #N/A, and #VALUE!, start Microsoft Excel and enter in the first three cells =0/0 =NA() ="a"+0
Here is a new patch against 0.15: diff -ru Spreadsheet-XLSX-0.15/lib/Spreadsheet/XLSX/Fmt2007.pm Spreadsheet-XLSX-0.15-na/lib/Spreadsheet/XLSX/Fmt2007.pm --- Spreadsheet-XLSX-0.15/lib/Spreadsheet/XLSX/Fmt2007.pm 2015-10-28 20:37:16.000000000 +0000 +++ Spreadsheet-XLSX-0.15-na/lib/Spreadsheet/XLSX/Fmt2007.pm 2016-01-21 12:19:38.411744947 +0000 @@ -98,6 +98,8 @@ if ($oCell->{Type} eq 'Numeric') { if ($oCell->{Format}) { $sFmtStr = $oCell->{Format}; + } elsif($oCell->{Val} eq "#N/A" or $oCell->{Val} eq "#VALUE!" or $oCell->{Val} eq "#DIV/0!") { + $sFmtStr = '0'; } elsif (int($oCell->{Val}) != $oCell->{Val}) { $sFmtStr = '0.00'; } else { diff -ru Spreadsheet-XLSX-0.15/lib/Spreadsheet/XLSX.pm Spreadsheet-XLSX-0.15-na/lib/Spreadsheet/XLSX.pm --- Spreadsheet-XLSX-0.15/lib/Spreadsheet/XLSX.pm 2015-10-31 02:33:19.000000000 +0000 +++ Spreadsheet-XLSX-0.15-na/lib/Spreadsheet/XLSX.pm 2016-01-21 12:20:15.643415239 +0000 @@ -146,7 +146,7 @@ $cell->{_Value} = $self->{FmtClass}->ValFmt($cell, $self); if ($type eq "Date") { - if ($v < 1) { #then this is Excel time field + if ($v ne "#N/A" and $v < 1) { #then this is Excel time field $cell->{Type} = "Text"; } $cell->{Val} = $cell->{_Value};


This service runs on Request Tracker, is sponsored by The Perl Foundation, and maintained by Best Practical Solutions.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.