Skip Menu |
 
rt.cpan.org will be shut down on March 1st, 2021.

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

Report information
The Basics
Id: 79016
Status: open
Priority: 0/
Queue: Spreadsheet-XLSX

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

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



Subject: #n/a generates a lot of warnings
Download (untitled) / with headers
text/plain 307b
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
Download (untitled) / with headers
text/plain 803b
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}; }
Download (untitled) / with headers
text/plain 1.2k
#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}; }
Download (untitled) / with headers
text/plain 158b
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
Download (untitled) / with headers
text/plain 1.4k
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 is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

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