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
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
X-Mailer: MIME-tools 5.427 (Entity 5.427)
Content-Disposition: inline
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
Content-Length: 307
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
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
X-Mailer: MIME-tools 5.427 (Entity 5.427)
Content-Disposition: inline
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
Content-Length: 803
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}; }
MIME-Version: 1.0
X-Mailer: MIME-tools 5.427 (Entity 5.427)
Content-Disposition: inline
Content-Type: text/plain; charset="UTF-8"
Message-ID: <rt-3.8.HEAD-23811-1345115679-1252.79016-0-0@rt.cpan.org>
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
Content-Length: 1042
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}; }
MIME-Version: 1.0
In-Reply-To: <rt-3.8.HEAD-23811-1345115679-1252.79016-0-0@rt.cpan.org>
X-Mailer: MIME-tools 5.504 (Entity 5.504)
Content-Disposition: inline
X-RT-Interface: Web
References: <rt-3.8.HEAD-23811-1345115679-1252.79016-0-0@rt.cpan.org>
Content-Type: text/plain; charset="utf-8"
Message-ID: <rt-4.0.18-31424-1424871788-593.79016-0-0@rt.cpan.org>
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
X-RT-Encrypt: 0
X-RT-Sign: 0
Content-Length: 1324
#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}; }
MIME-Version: 1.0
In-Reply-To: <rt-4.0.18-31424-1424871788-593.79016-0-0@rt.cpan.org>
X-Mailer: MIME-tools 5.504 (Entity 5.504)
Content-Disposition: inline
X-RT-Interface: Web
References: <rt-3.8.HEAD-23811-1345115679-1252.79016-0-0@rt.cpan.org> <rt-4.0.18-31424-1424871788-593.79016-0-0@rt.cpan.org>
Content-Type: text/plain; charset="utf-8"
Message-ID: <rt-4.0.18-8160-1447854525-1863.79016-0-0@rt.cpan.org>
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
X-RT-Encrypt: 0
X-RT-Sign: 0
Content-Length: 158
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
MIME-Version: 1.0
In-Reply-To: <rt-4.0.18-8160-1447854525-1863.79016-0-0@rt.cpan.org>
X-Mailer: MIME-tools 5.504 (Entity 5.504)
Content-Disposition: inline
X-RT-Interface: Web
References: <rt-3.8.HEAD-23811-1345115679-1252.79016-0-0@rt.cpan.org> <rt-4.0.18-31424-1424871788-593.79016-0-0@rt.cpan.org> <rt-4.0.18-8160-1447854525-1863.79016-0-0@rt.cpan.org>
Content-Type: text/plain; charset="utf-8"
Message-ID: <rt-4.0.18-21143-1453378963-880.79016-0-0@rt.cpan.org>
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
X-RT-Encrypt: 0
X-RT-Sign: 0
Content-Length: 1465
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.