As you could have read in the documentation, addressing a cell using the name, like in $ss->[1]{A1}, will give you the formatted value. If you want the original unformatted value, you need to address the cell using $ss->[1]{cell}[1]]1].

Use that value and format it yourself, like with the already suggested sprintf.

But how come the same piece of code is giving me .9888 in the other environment. Also you could see that the unformatted value is '0.98880000000000001' (raw data). How can i extract this 0.9888 from that?

But how come the same piece of code is giving me .9888 in the other environment. Also you could see that the unformatted value is '0.98880000000000001' (raw data). How can i extract this 0.9888 from that?

See http://floating-point-gui.de/ for the 0000000000001 part. Apart from that, if you want a certain precision, use sprintf, as you were told in other replies.

Alexander

--
Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)

"Also you could see that the unformatted value is '0.98880000000000001' (raw data). How can i extract this 0.9888 from that?"

In Re: Spreadsheet::Read module is rounding the decimals in XLSX file, I provided:

• A fully functional script.
• The output from that script.
• Links to documentation.
• Advice on issues should you choose a different, but closely related, function.

My apologies. Clearly that wasn't sufficient, free work for you!

If, at some point, you want to extract a digit and change 0.9888 to 0.98880000000000001 in my code, you'll find out for yourself. If you're too lazy to do that, stay ignorant!

-- Ken

G'day char_perl,

Welcome to the Monastery.

Use the sprintf function to specify the precision you want:

#!/usr/bin/env perl -l

use strict;
use warnings;

my $value = 0.9888;

print '"%.2f" --> ', sprintf '%.2f', $value;
print '"%.4f" --> ', sprintf '%.4f', $value;
print '"%.8f" --> ', sprintf '%.8f', $value;
[download]

Output:

$ pm_1131769_output_precision.pl
"%.2f" --> 0.99
"%.4f" --> 0.9888
"%.8f" --> 0.98880000
[download]

Also note that there is a printf function, but this has a number of gotchas. They're described in the documentation; although, you may just want to follow the advice on the last line:

"Don't fall into the trap of using a printf when a simple print would do. The print is more efficient and less error prone."

What versions of Spreadsheet::Read and Spreadsheet::XLSX do you have on each environment? From the surface, I can see at least two potential causes:

• one of the platforms uses a different representation for floating point, and
• there is a difference in reading/writing based on different versions of those two modules.

DEMO-time!

I had a friend with Windows and some recentish MS-Office create me an xlsx with just one single cell filled with the value 0.9888 and then put that in sandbox/x-0.9888.xlsx

$ perl -Mblib -MDP -MSpreadsheet::Read -we'DDumper ReadData ("sandbox/
+x-0.9888.xlsx")'
[
    {   error            => undef,
        parser           => 'Spreadsheet::ParseXLSX',
        sheet            => {
            Blad1            => 1
            },
        sheets           => 3,
        type             => 'xlsx',
        version          => '0.17'
        },
    {   A1               => '0.98880000000000001',
        attr             => [],
        cell             => [
            [],
            [   undef,
                '0.98880000000000001'
                ]
            ],
        label            => 'Blad1',
        maxcol           => 1,
        maxrow           => 1,
        merged           => []
        }
    ]
$ env SPREADSHEET_READ_XLSX=Spreadsheet::XLSX perl -Mblib -MDP -MSprea
+dsheet::Read -we'DDumper ReadData ("sandbox/x-0.9888.xlsx")'
[
    {   error            => undef,
        parser           => 'Spreadsheet::XLSX',
        sheet            => {
            Blad1            => 1
            },
        sheets           => 3,
        type             => 'xlsx',
        version          => '0.13'
        },
    {   A1               => '0.99',
        attr             => [],
        cell             => [
            [],
            [   undef,
                '0.98880000000000001'
                ]
            ],
        label            => 'Blad1',
        maxcol           => 1,
        maxrow           => 1,
        merged           => []
        }
    ]
$ env SPREADSHEET_READ_XLSX=Spreadsheet::XLSX::Reader::LibXML perl -Mb
+lib -MDP -MSpreadsheet::Read -we'DDumper ReadData ("sandbox/x-0.9888.
+xlsx")'
XLSX parser cannot parse data:  at -e line 1.
[download]

I expected that last error: it is the formatter for that module that currently causes integration problems.

I used the development folder, as choosing your own parser is new and not yet released.

This clearly shows the influence of the formatter used in the underlying parser.

I get consistent results for Spreadsheet::ParseXLSX on Linux, AIX and HP-UX. On Windows, I get:

C:\Tmp> perl -MDP -MSpreadsheet::Read -we"DDumper ReadData ('x-0.9888.
+xlsx')"
[
    {   error            => undef,
        parser           => 'Spreadsheet::ParseXLSX',
        sheet            => {
            Blad1            => 1
            },
        sheets           => 3,
        type             => 'xlsx',
        version          => '0.17'
        },
    {   A1               => '0.9888',
        attr             => [],
        cell             => [
            [],
            [   undef,
                '0.9888'
                ]
            ],
        label            => 'Blad1',
        maxcol           => 1,
        maxrow           => 1,
        merged           => []
        }
    ]
[download]

If I unzip the file and then show the stored data:

$ xml_pp < xl/worksheets/sheet1.xml
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet mc:Ignorable="x14ac"
           xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006
+/main"
           xmlns:mc="http://schemas.openxmlformats.org/markup-compatib
+ility/2006"
           xmlns:r="http://schemas.openxmlformats.org/officeDocument/2
+006/relationships"
           xmlns:x14ac="http://schemas.microsoft.com/office/spreadshee
+tml/2009/9/ac">
  <dimension ref="A1"/>
  <sheetViews>
    <sheetView tabSelected="1" workbookViewId="0"/>
    </sheetViews>
  <sheetFormatPr defaultRowHeight="15" x14ac:dyDescent="0.25"/>
  <sheetData>
    <row r="1" spans="1:1" x14ac:dyDescent="0.25">
      <c r="A1">
        <v>0.98880000000000001</v>
        </c>
      </row>
    </sheetData>
  <pageMargins bottom="0.75" footer="0.3" header="0.3" left="0.7" righ
+t="0.7" top="0.75"/>
  </worksheet>
$
[download]

It clearly shows that Excel stored 0.98880000000000001 in the spreadsheet. (For all that now want to blame the formatter, using cat showed the same content, but that would be way too wide a line to neatly show in this forum).

Gentlemen and Colleagues, there is absolutely n-o reason to use such utterly disrespectful language in reference to any other Monk ... who asked a perfectly valid question that I’m not sure really has been properly answered yet.   He has instead received brutal treatment deserving of an apology.

If someone appears to find that a Perl program gives one answer in one environment, but a different answer in another, and says that he can demonstrate this, then that’s a valid question deserving of a careful answer.   We do not practice castigation in this Monastery.

Don’t we? . . .

Thank you for you're concern. Let me do more research on this and post a reply if i get a solution.

Using sprintf or %.4f will not work here, as the value from ReadData (which is part of Spreadsheet::Read) subroutine is it self coming as rounded, so even i used .4f on .99 it would be the same. As of now, as a work around I have given a vb tool to convert the xlsx files to xls, since this issue is not coming in xls files. May be I should continue by comparing the parsers of XLS and XLSX and see the difference.

Thanks to kcott, Tux, afoken and sundialsvc4 for your help :)

I obviously was not clear enough.

Spreadsheet::Read is no more and no less than a wrapper over a bunch of spreadsheet parsers to feature a generic API to all forms of spreadsheets.

ReadData does not alter the data being returned by the underlying parser effectively in use. If it does, it might well be a bug.

The underlying parser in the case of the OP's problem is likely Spreadsheet::XLSX, which is a problem to start with. That module is (very) buggy and unmaintained. It parses the XML with regular expressions and is loaded with small but annoying mistakes. My advice would be to uninstall that module immediately and replace it with Spreadsheet::ParseXLSX. There is a major chance your problems will vanish.

Even with the use Spreadsheet::XLSX; statement in the code, which is utterly useless here, it does not guarantee that that module is used anywhere. If Spreadsheet::Read finds Spreadsheet::ParseXLSX installed, it will use that instead.

So let us assume that with Spreadsheet::ParseXLSX your value will always return 0.98880000000000001 (or whatever floating point value is actually stored in that cell). It is still not Spreadsheet::Read's problem in that it is different on different systems. Find out what parser is used and trace back if the difference still exists when you parse the spreadsheet with that module (peel the layers): it might be that even lower levels are to blame like XML::Twig.

I personally think that sundialsvc4 overreacts, though basically right, I don't see freshman-bashing here. Just valid tips and tricks that might need some more friendlyness in their wording, but are certainly not unfriendly IMHO.

(There are reasons for people to not use Spreadsheet::ParseXLSX, support for Spreadsheet::XLSX::Reader::LibXML is work in prgress)


Enjoy, Have FUN! H.Merijn

Looking at your posts you do this to others frequently. You're full of it. Nothing more than another charm offensive on your part.

Well said! One of the big assets of PerlMonks is the kindness of its members. Let's contribute to keep this good culture!