Skip Menu |

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)

History Show all quoted text
  Mon Aug 08 07:27:42 2011 ftl [...] dnv.com - Ticket #70129: Ticket created
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.
  Thu Aug 16 07:04:47 2012 EDAVIS [...] cpan.org - Ticket created
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}; }
  Thu Aug 16 07:14:39 2012 EDAVIS [...] cpan.org - Correspondence added
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}; }
  Thu Aug 16 07:14:40 2012 EDAVIS [...] cpan.org - Status changed from 'new' to 'open'
  Wed Feb 25 08:43:08 2015 EDAVIS [...] cpan.org - Correspondence added
#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}; }
  Wed Oct 21 21:10:08 2015 mikeb [...] cpan.org - Ticket #70129: Merged into ticket #79016
  Wed Oct 21 21:10:08 2015 mikeb [...] cpan.org - Merged into ticket #79016
  Wed Nov 18 08:48:45 2015 EDAVIS [...] cpan.org - Correspondence added
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
  Thu Jan 21 07:22:43 2016 EDAVIS [...] cpan.org - Correspondence added
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};