Skip Menu |

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)

History Show all quoted text
  Wed Nov 28 11:11:26 2018 franz.fasching [...] drfasching.com - Ticket created
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>
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
  Mon Dec 03 18:12:55 2018 sdemuth [...] fairbanksllc.com - Correspondence added
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
  Mon Dec 03 18:12:56 2018 The RT System itself - Status changed from 'new' to 'open'
  Fri Jul 05 11:53:35 2019 delskorch [...] gmail.com - Correspondence added
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
  Tue May 12 16:47:45 2020 vector_flys [...] hotmail.com - Correspondence added
Adding this at line 97, solved it for me: # Check for formula error before evaluating format return '@' if ( $oCell->{Val} =~ m/^#/ );