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: 127829
Status: open
Priority: 0/
Queue: Spreadsheet-XLSX

People
Owner: Nobody in particular
Requestors: franz.fasching [...] drfasching.com
Cc:
AdminCc:

Bug Information
Severity: (no value)
Broken in: (no value)
Fixed in: (no value)



Subject: [Warning] Argument "#N/A" isn't numeric in int
Date: Wed, 28 Nov 2018 17:01:55 +0100
To: bug-Spreadsheet-XLSX [...] rt.cpan.org
From: Franz Fasching <franz.fasching [...] drfasching.com>
Download (untitled) / with headers
text/plain 1.8k
Dear Maintainers of Spreadsheet::XLSX! I recently ran into an issue of Spreadsheet::XLSX when reading an Excel .xlsx file containing numerical cells, where the cell value is computed by an erroneous formula, leading to a non-numeric cell value of "#N/A". Parsing the spreadsheet with the Spreadsheet::XLSX module then results in the following warning from Perl: Argument "#N/A" isn't numeric in int at /usr/local/share/perl/5.26.1/Spreadsheet/XLSX/Fmt2007.pm line 101.  at /usr/local/share/perl/5.26.1/Spreadsheet/XLSX/Fmt2007.pm line 99. Looking at the source code of Fmt2007.pm in line 101 reveals the following code in sub FmtString:      97     unless (defined($sFmtStr)) {      98         if ($oCell->{Type} eq 'Numeric') {      99             if ($oCell->{Format}) {     100                 $sFmtStr = $oCell->{Format};     101             } elsif (int($oCell->{Val}) != $oCell->{Val}) {     102                 $sFmtStr = '0.00';     103             } else {     104                 $sFmtStr = '0';     105             } It looks like the value of cells with numeric format is numerically compared to their integer value in line 101, which seems to trigger this warning, when the cell actually contains an "#N/A" value as a result of an erroneous formula evaluation in Excel. Checking if there's actually a numerical value contained in the cell value before doing the numerical comparison might avoid this warning, you might want to include one of the tests mentioned here in the elsif-clause: https://stackoverflow.com/questions/12647/how-do-i-tell-if-a-variable-has-a-numeric-value-in-perl Thanks for considering! Environment: Perl 5.26.1 on Ubuntu 18.04.1 LTS "bionic", using Spreadsheet::XLSX version 0.15 Thanks for this great module and best regards, Franz
Download (untitled) / with headers
text/plain 2.2k
I humbly offer https://github.com/soldemuth/Spreadsheet-XLSX-0.16 https://github.com/soldemuth/Spreadsheet-XLSX-0.16/commit/efd53be7eb28321f6261ba2c5f91d38e9fe39959 On Wed Nov 28 11:11:26 2018, franz.fasching@drfasching.com wrote: Show quoted text
> Dear Maintainers of Spreadsheet::XLSX! > > I recently ran into an issue of Spreadsheet::XLSX when reading an > Excel > .xlsx file containing numerical cells, where the cell value is > computed > by an erroneous formula, leading to a non-numeric cell value of > "#N/A". > > Parsing the spreadsheet with the Spreadsheet::XLSX module then results > in the following warning from Perl: > > Argument "#N/A" isn't numeric in int at > /usr/local/share/perl/5.26.1/Spreadsheet/XLSX/Fmt2007.pm line 101. >  at /usr/local/share/perl/5.26.1/Spreadsheet/XLSX/Fmt2007.pm line 99. > > Looking at the source code of Fmt2007.pm in line 101 reveals the > following code in sub FmtString: > >      97     unless (defined($sFmtStr)) { >      98         if ($oCell->{Type} eq 'Numeric') { >      99             if ($oCell->{Format}) { >     100                 $sFmtStr = $oCell->{Format}; >     101             } elsif (int($oCell->{Val}) != $oCell->{Val}) { >     102                 $sFmtStr = '0.00'; >     103             } else { >     104                 $sFmtStr = '0'; >     105             } > > It looks like the value of cells with numeric format is numerically > compared to their integer value in line 101, which seems to trigger > this > warning, when the cell actually contains an "#N/A" value as a result > of > an erroneous formula evaluation in Excel. > > Checking if there's actually a numerical value contained in the cell > value before doing the numerical comparison might avoid this warning, > you might want to include one of the tests mentioned here in the > elsif-clause: > https://stackoverflow.com/questions/12647/how-do-i-tell-if-a-variable- > has-a-numeric-value-in-perl > > Thanks for considering! > > Environment: > Perl 5.26.1 on Ubuntu 18.04.1 LTS "bionic", using Spreadsheet::XLSX > version 0.15 > > Thanks for this great module and best regards, > Franz
Download (untitled) / with headers
text/plain 2.2k
Cell contents should be checked before all of the cell operations in FmtString. Perhaps Scalar::Uti::looks_like_number can handle the numeric checks. I'm not sure on the best way to do the date checks. On Wed Nov 28 11:11:26 2018, franz.fasching@drfasching.com wrote: Show quoted text
> Dear Maintainers of Spreadsheet::XLSX! > > I recently ran into an issue of Spreadsheet::XLSX when reading an > Excel > .xlsx file containing numerical cells, where the cell value is > computed > by an erroneous formula, leading to a non-numeric cell value of > "#N/A". > > Parsing the spreadsheet with the Spreadsheet::XLSX module then results > in the following warning from Perl: > > Argument "#N/A" isn't numeric in int at > /usr/local/share/perl/5.26.1/Spreadsheet/XLSX/Fmt2007.pm line 101. >  at /usr/local/share/perl/5.26.1/Spreadsheet/XLSX/Fmt2007.pm line 99. > > Looking at the source code of Fmt2007.pm in line 101 reveals the > following code in sub FmtString: > >      97     unless (defined($sFmtStr)) { >      98         if ($oCell->{Type} eq 'Numeric') { >      99             if ($oCell->{Format}) { >     100                 $sFmtStr = $oCell->{Format}; >     101             } elsif (int($oCell->{Val}) != $oCell->{Val}) { >     102                 $sFmtStr = '0.00'; >     103             } else { >     104                 $sFmtStr = '0'; >     105             } > > It looks like the value of cells with numeric format is numerically > compared to their integer value in line 101, which seems to trigger > this > warning, when the cell actually contains an "#N/A" value as a result > of > an erroneous formula evaluation in Excel. > > Checking if there's actually a numerical value contained in the cell > value before doing the numerical comparison might avoid this warning, > you might want to include one of the tests mentioned here in the > elsif-clause: > https://stackoverflow.com/questions/12647/how-do-i-tell-if-a-variable- > has-a-numeric-value-in-perl > > Thanks for considering! > > Environment: > Perl 5.26.1 on Ubuntu 18.04.1 LTS "bionic", using Spreadsheet::XLSX > version 0.15 > > Thanks for this great module and best regards, > Franz
Download (untitled) / with headers
text/plain 152b
Adding this at line 97, solved it for me: # Check for formula error before evaluating format return '@' if ( $oCell->{Val} =~ m/^#/ );


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.