Beefy Boxes and Bandwidth Generously Provided by pair Networks
more useful options
 
PerlMonks  

Re: Issue while reading decimal number from a Spreadsheet using perl

by poj (Abbot)
on Oct 21, 2019 at 09:13 UTC ( #11107751=note: print w/replies, xml ) Need Help??


in reply to Issue while reading decimal number from a Spreadsheet using perl

Add this to your program to show what versions are you using. Also what version of Excel was used to create myxls.xls ?

#!perl use Spreadsheet::ParseExcel::SaveParser; printf "OS %s Perl %s ParseExcel %s\n",$^O,$^V,$Spreadsheet::ParseExcel::VERSION;

Running your scripts I get

OS MSWin32
Perl v5.16.1
ParseExcel 0.65
-10.1-  -10.2-  -10.3-  -10.4-  -10.5-  -10.6-  -10.7-  -10.8-  -10.9-
poj
  • Comment on Re: Issue while reading decimal number from a Spreadsheet using perl
  • Download Code

Replies are listed 'Best First'.
Re^2: Issue while reading decimal number from a Spreadsheet using perl
by rjt (Curate) on Oct 21, 2019 at 10:07 UTC

    It was surprising to me that your system doesn't show any apparent floating point error. This prompted me to (finally) try the OP's the code myself, and sure enough, I had perfect rounding, too! Yet the OP's output corresponds to what I would actually expect from a double precision IEEE-754 float:

    perl -le 'printf "%.18f", 10.1' 10.099999999999999645

    Suspecting skulduggery, I dug into the Spreadsheet::ParseExcel version 0.65 code, and I was not disappointed. See Utility.pm, around line 800. Indeed, the module does its own rounding because of RT#45626:

    # Fix for Perl and sprintf not rounding up like Excel. # http://rt.cpan.org/Public/Bug/Display.html?id=45626 if ( $data =~ /^${number_result}5/ ) { $number_result = sprintf "%0${str_length}.${after_decimal}f", $data . '1'; }

    This section was last changed ten years ago, suggesting OP may have a very old version, so your request for the version number was apropos.

    Aside: The sub that's in is called ExcelFmt and is almost 900 lines long. The entire package is 45000 lines. I'm pretty sure the section around line 800 is responsible for the rounding, but that's a lot of code to grok in five minutes. I'd be happy to be corrected on the specifics, but the point is, something is doing the rounding. :-)

    And yes, OP, I'd still suggest doing your own rounding for the reasons stated in my comment, to ensure you are rounding to your own specifications.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://11107751]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others pondering the Monastery: (6)
As of 2019-11-21 09:13 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Strict and warnings: which comes first?



    Results (104 votes). Check out past polls.

    Notices?