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

Report information
The Basics
Id:
127829
Status:
open
Priority:
Low/Low

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

BugTracker
Severity:
(no value)
Broken in:
(no value)
Fixed in:
(no value)



MIME-Version: 1.0
X-Spam-Status: No, score=-1.234 tagged_above=-99.9 required=10 tests=[BAYES_00=-1.9, HTML_MESSAGE=0.001, SPF_SOFTFAIL=0.665] autolearn=no
X-Cpan.org: This message routed through the cpan.org mail forwarding service. Please use PAUSE pause.perl.org to configure your delivery settings.
X-Spam-Flag: NO
Content-Language: en-GB
Content-Type: multipart/alternative; boundary="------------782369C1D6424C2D7438BC02"
Message-ID: <0b63b9a1-cec7-dddf-e2d1-e904f235cef6@drfasching.com>
X-Virus-Scanned: Debian amavisd-new at bestpractical.com
X-Virus-Scanned: Debian amavisd-new at drfasching.com
Autocrypt: addr=franz.fasching@drfasching.com; prefer-encrypt=mutual; keydata= xsFNBFpzFx0BEACl9B8YNNLzfWQWxRWQdo5HH2v6LRX+I5Z6RbCjj1QHpVDdl3wUw8wgqQMK btWoyfkZeegn8XbQoNtBH6IpQQ93WMhsFjlAHGIHfx7F4gmUDomBlyg2YMFbMM2Vvq9aq4zE ZddMAN5mRdjlI1FxBB6exem5lq17xSfFPwYdW528sE0tu/etAs1mPheLoC/YXRn3CiLgQBnL Zt/kr7rwTsjqRbYs18bj1Dn2I8lsA09TVLB+BJTY1+eIgxuehlvfHIH1qHFrZcDrAljWNvNs rqYXOdDyBH/ZddYv614MXWcCJAgHk0senA82cnk+HfKFLqfaZ3n12ynW7CJsvZynJMo/dQp3 wFRV6mN+n3bxr4PWPH/NM/usTFd4eUzYFvIMD7Wne5dx3tcpNNWNZlYCUEeDfvF4UrVZhqHi mt38nsTj1qWYGhKuG0JRqYjJ7GKOUupHDtEpcsxjR7yQaZNZEkknzZpgWyp+q2dvW+P1CYVS Fo0kt5r1T4AIRitdAjrqCFmSWQv/GoX4SdN9MInDkxoHOIAJ2vFGtSKJnYlYF9/NW474zIPe LCWciRWaB1JRorjIK07F3ZaJVGCyBvUVrnWYpA2opOD+X7YHeeHZHLOu0gZ7Q/bYQgk0Ac6x kXJDjCqjfFhmt2kbouuxYPz7eEuju84x/ZDW3/Y4Lg1izojdpwARAQABzS5GcmFueiBGYXNj aGluZyA8ZnJhbnouZmFzY2hpbmdAZHJmYXNjaGluZy5jb20+wsF3BBMBCAAhBQJacxcdAhsj BQsJCAcCBhUICQoLAgQWAgMBAh4BAheAAAoJEDDGpsQ64IR5FT4P/AmzWv+E7YedUG8W9BNM kUUaTPCPlp/oTwur5VgfH3RMhMcHcCD0u2xjZSMjHNcpBUYFVQMtVmqrz4hs/7cg1iIJjBT9 NtK3pa5Wk/LyelxxRJk02wo4ubUM/CqpW7qEBg06leOvfag3ws59CaWekogIp5QbYHNeVqRD /cii00/BCWaESkBbPhqcDh/EPHDEb9QOXM3yrpbIbBwp2pHlj8UBRsDBDDepPxKLS8ETV+4r hfTnEcpw292PbyrGRZ/yaq9bX8R7gCcceOCxcAi9K55cLk3AZ8dZqGz42eIMk94IJsWhTR/T CXxSaPWX/bihtz1hpgN6x2YqekXCIszHVFEx7chF92jq8HJm9iiSyaaG0A6joWLTv2u9/ncw Tf3Sn26jawYCx05TFSmSZHt7yyBtemccvTYhVZKzhAADInhhqhAQ1Ap0M8DqabX5erk2vCYq sH85/MQ5Sr0dIWNZjuOcE/diSHR7Rh30EEkQMLrlORGt8nsqr3oZGKNmy3H5Zg2Ls/K+IFY0 bPz/y+tryED/UMiKMlMaFythgebdiBG5H5Qe/Iw8yuxbwE84LkX2mdyH09qb0mrpSAnR9scQ ALCfFqpihOkAToUFjEHUdZLgoVBbzmuVhQKnT4NomRd6mcVLojd3VuWf4ZA7fixt6AslMoMV BMs05yLOWWKo55lkzsFNBFpzFx0BEADm2/ZcCgfKiE1yAkhW9CjpXtzUMg+VuTNVpKmPc5Il hKJ3JPQJGziDFGiJg8o7mBhqoscMGE+Whf+ELEcbQsnMc7EFnW9X2eiMJhb0rf+8RnAm3eYx cuZvFo7MWYp9qpa5hgrHlUz6CogPwSsQsdBjPUZFNzfLU+duiNKXw3r5tW1zPMzd/kmGTvLb kkbv+4NjX5UYRG5GaYoylMCcr5dFmVx2aEFzLyI9URDC9WZ1dlC6k3ZwqHtflSJc7mBOcHm7 k1X2g2E01+6/B4Skt6MghrUSjJuRbja4cv+nUvBHx8DkMo6lhc30awfRjVN8PVb7VyNp2COs a33pHMoc+us0Qx6iiAMpIvfQDKmmRLsa5PsQ4/dnnckRm2DGrGRR9ck+K2/08wVHOqfeJbsv YKYGMhdCiPZ0LCPecn6qziqK5F8/soY/XwuB/8UhlBG/swfbcejMhiUtpBLF9WiA2GKLiXIK rjQoC7l3Nort/TKY+FehEY+2/XceHce4mXcO6CA4MW7wWR6MvPPkGHta6zygiMxVQH+rSbLY cXH8I9WeHK+FdfiLwJyRrkobGbVoL2F8qrmtnhxNw4nEMxR73aKQbKxx8kCZOflKHyrIXS99 wHDLjqIk3iGUw+LGb6yKJpnfi1j0uHVe4copAlfzvEkWbDfizzY01iZct2iBesRX9QARAQAB wsFfBBgBCAAJBQJacxcdAhsMAAoJEDDGpsQ64IR5JUYP/REs4757J+CgkE9a6EFH37w5eZ11 wu8loRi/TDw1/y0ATFCebPyHak9FY5G9Ct3QPNx3xO2NT/HDAqD2dveDs2akpEnyEUCkIykx 9IFxr/QQGF+OcCtwcIRdbjA3nFCSmVeIa/f6kvZOTkGJTktb3Z9cqlb9zHucKFrfHqROlkLF hivf+l6uVD05yg33vDg9Uc1XbME+FInnFLraJ5TK8ycArOLAf5PqNZW5DCibMqphm3CWpIoW XJEZuT04GcMR7pcvt2WKd246Zz34SjqlGakrfPgVvrCkgUPluzJ5QYGVIlDtsmOMaT+b+nGv FGcLh7P7TBMrQAfF0kzGwDzPd7FcSIHopYcJdRq3f1CbV6FbvfBblIVp0PsIXiD/GO7tdXW8 xOJOtOnXxcHd8aDTGmiJ44PZrA4Ux490c7AoAQP6d4tIfjgX3iM67sI0npkFCF/EpyZ7+ELc AVhS9iYkmdWCp4wQ1+P+rd6W3yNsfdCkLALdsdaXHjnAKt/t1AxtzePJJidIHcJK6zCdk8Yv C2YADsDrjc6AuEBceJj4vqhaZ4alor5DHwoVdMOP9+3jrx8RRVgwIXtr79Gor5cpp1Kh1AMC GkkfyGiSb9v/XrwvPXy88C76qyW9dLDyNeP6vHRIp9rFwJKUYxW3YmKruDaLM7/i2DJWKx70 g6KhC3xE
Organization: Dr. Franz Fasching
X-Spam-Score: -1.234
Received: from localhost (localhost [127.0.0.1]) by hipster.bestpractical.com (Postfix) with ESMTP id 6C52D24025D for <cpan-bug+Spreadsheet-XLSX@hipster.bestpractical.com>; Wed, 28 Nov 2018 11:11:25 -0500 (EST)
Received: from hipster.bestpractical.com ([127.0.0.1]) by localhost (hipster.bestpractical.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id H6ijSS0SKPMB for <cpan-bug+Spreadsheet-XLSX@hipster.bestpractical.com>; Wed, 28 Nov 2018 11:11:23 -0500 (EST)
Received: from xx1.develooper.com (xx1.develooper.com [207.171.7.115]) by hipster.bestpractical.com (Postfix) with ESMTPS id AE50C2401BD for <bug-Spreadsheet-XLSX@rt.cpan.org>; Wed, 28 Nov 2018 11:11:22 -0500 (EST)
Received: from localhost (xx1.develooper.com [127.0.0.1]) by localhost (Postfix) with ESMTP id 1AE2F11F02E for <bug-Spreadsheet-XLSX@rt.cpan.org>; Wed, 28 Nov 2018 08:11:21 -0800 (PST)
Received: from xx1.develooper.com (xx1.develooper.com [127.0.0.1]) by localhost (Postfix) with SMTP id 99E8A11F064 for <bug-Spreadsheet-XLSX@rt.cpan.org>; Wed, 28 Nov 2018 08:11:18 -0800 (PST)
Received: from drfasching.com (drfasching.com [88.198.36.7]) by xx1.develooper.com (Postfix) with ESMTP id 74ABC11F02E for <bug-Spreadsheet-XLSX@rt.cpan.org>; Wed, 28 Nov 2018 08:11:16 -0800 (PST)
Received: from localhost (localhost [127.0.0.1]) by drfasching.com (Postfix) with ESMTP id 731C24250CC for <bug-Spreadsheet-XLSX@rt.cpan.org>; Wed, 28 Nov 2018 17:02:02 +0100 (CET)
Received: from drfasching.com ([127.0.0.1]) by localhost (drfasching.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id Y3jl9VEHL_wN for <bug-Spreadsheet-XLSX@rt.cpan.org>; Wed, 28 Nov 2018 17:01:56 +0100 (CET)
Delivered-To: cpan-bug+Spreadsheet-XLSX@hipster.bestpractical.com
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101 Thunderbird/60.2.1
Subject: [Warning] Argument "#N/A" isn't numeric in int
Return-Path: <franz.fasching@drfasching.com>
X-RT-Mail-Extension: spreadsheet-xlsx
X-Original-To: cpan-bug+Spreadsheet-XLSX@hipster.bestpractical.com
Openpgp: preference=signencrypt
X-PMX-Spam: Gauge=IIIIIIII, Probability=8%, Report=' SUPERLONG_LINE 0.05, BODYTEXTH_SIZE_10000_LESS 0, BODYTEXTP_SIZE_3000_LESS 0, BODY_SIZE_5000_5999 0, BODY_SIZE_7000_LESS 0, SINGLE_URI_IN_BODY 0, SPF_PASS 0, URI_WITH_PATH_ONLY 0, __ANY_URI 0, __BAT_BOUNDARY 0, __CP_URI_IN_BODY 0, __CT 0, __CTYPE_HAS_BOUNDARY 0, __CTYPE_MULTIPART 0, __CTYPE_MULTIPART_ALT 0, __FRAUD_MONEY_CURRENCY 0, __FRAUD_MONEY_CURRENCY_DOLLAR 0, __HAS_FROM 0, __HAS_HTML 0, __HAS_MSGID 0, __HIGHBITS 0, __HTML_AHREF_TAG 0, __HTTPS_URI 0, __MIME_HTML 0, __MIME_TEXT_H 0, __MIME_TEXT_H1 0, __MIME_TEXT_H2 0, __MIME_TEXT_P 0, __MIME_TEXT_P1 0, __MIME_TEXT_P2 0, __MIME_VERSION 0, __MOZILLA_USER_AGENT 0, __MULTIPLE_URI_TEXT 0, __PHISH_SPEAR_SUBJ_ALERT 0, __SANE_MSGID 0, __SINGLE_URI_MPART_BOTH 0, __SUBJ_ALPHA_END 0, __TAG_EXISTS_HTML 0, __TO_MALFORMED_2 0, __TO_NO_NAME 0, __URI_IN_BODY 0, __URI_NOT_IMG 0, __URI_NO_MAILTO 0, __URI_NO_WWW 0, __URI_NS , __URI_WITH_PATH 0, __USER_AGENT 0, __zen.spamhaus.org_ERROR '
Date: Wed, 28 Nov 2018 17:01:55 +0100
X-Spam-Level:
X-Greylist: delayed 552 seconds by postgrey-1.34 at xx1.develooper.com; Wed, 28 Nov 2018 08:11:17 PST
X-PMX-Version: 5.6.1.2065439, Antispam-Engine: 2.7.2.376379, Antispam-Data: 2018.11.28.160018
To: bug-Spreadsheet-XLSX@rt.cpan.org
From: Franz Fasching <franz.fasching@drfasching.com>
X-RT-Interface: Email
Content-Length: 0
content-type: text/plain; charset="utf-8"
Content-Transfer-Encoding: base64
X-RT-Original-Encoding: utf-8
Content-Length: 1920
content-type: text/html; charset="utf-8"
Content-Transfer-Encoding: 8bit
X-RT-Original-Encoding: utf-8
Content-Length: 2811
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

MIME-Version: 1.0
In-Reply-To: <0b63b9a1-cec7-dddf-e2d1-e904f235cef6@drfasching.com>
X-Mailer: MIME-tools 5.504 (Entity 5.504)
Content-Disposition: inline
X-RT-Interface: Web
References: <0b63b9a1-cec7-dddf-e2d1-e904f235cef6@drfasching.com>
Content-Type: text/plain; charset="utf-8"
Message-ID: <rt-4.0.18-20322-1543878775-1686.127829-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: 2258
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
MIME-Version: 1.0
In-Reply-To: <0b63b9a1-cec7-dddf-e2d1-e904f235cef6@drfasching.com>
X-Mailer: MIME-tools 5.504 (Entity 5.504)
Content-Disposition: inline
X-RT-Interface: Web
References: <0b63b9a1-cec7-dddf-e2d1-e904f235cef6@drfasching.com>
Content-Type: text/plain; charset="utf-8"
Message-ID: <rt-4.0.18-16286-1562342015-157.127829-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: 2293
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
MIME-Version: 1.0
In-Reply-To: <0b63b9a1-cec7-dddf-e2d1-e904f235cef6@drfasching.com>
X-Mailer: MIME-tools 5.504 (Entity 5.504)
Content-Disposition: inline
X-RT-Interface: Web
References: <0b63b9a1-cec7-dddf-e2d1-e904f235cef6@drfasching.com>
Content-Type: text/plain; charset="utf-8"
Message-ID: <rt-4.0.18-16303-1589316465-822.127829-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: 152
Adding this at line 97, solved it for me: # Check for formula error before evaluating format return '@' if ( $oCell->{Val} =~ m/^#/ );


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.