Beefy Boxes and Bandwidth Generously Provided by pair Networks
good chemistry is complicated,
and a little bit messy -LW
 
PerlMonks  

Reading wrong value from excel sheet

by sandeep_car (Novice)
on May 20, 2013 at 17:27 UTC ( #1034375=perlquestion: print w/ replies, xml ) Need Help??
sandeep_car has asked for the wisdom of the Perl Monks concerning the following question:

Hi,

We upload file in .xlsx or xls format. In this file there is one coloumn for which value is in decimal. For these value for some it shows differnet result while reading from excel file and for some it shows correct result.

For example:

at place of 1.14 it comes 1.139999999999999

at place of 1.13 it comes 1.129999999999999

where as for some it give correct result.

Code used for reading .xlsx file is:

$fileName = $_[0]; $fFnc = $_[1]; $logMsg = new LogMessage(); $logMsg->writeLog( $fFnc, "Reading $fileName \n" ); $FileData = ""; %FileHash = (); %source_headers = (); # For XLSX formats if( $fileName =~ /\.xlsx$/i ) { my $myConv = Text::Iconv->new ("utf-8" +, "windows-1251"); my %xlFileData = (); my $myBook = Spreadsheet::XLSX->new( " +$fileName", $myConv ); if( $myBook ) { foreach my $sheet (@{$myBook->{Work +sheet}}) { $sheet->{MaxRow} ||= $sheet +->{MinRow}; foreach my $row ($sheet -> +{MinRow} .. $sheet -> {MaxRow}) { $sheet -> {MaxCol} +||= $sheet -> {MinCol}; foreach my $col ($s +heet -> {MinCol} .. $sheet -> {MaxCol}) { my $cell = $ +sheet -> {Cells} [$row] [$col]; // At this place it gives incorrect result }

Code used for reading .xls file is:

if($fileName =~ /\.xls$/i ) { my $parser = Spreadsheet::ParseExcel-> +new(); my $workbook = $parser->parse( "$fileN +ame" ); if( defined $workbook ) { for my $worksheet ( $workbook->works +heets() ) { my ( $row_min, + $row_max ) = $worksheet->row_range(); my ( $col_min, + $col_max ) = $worksheet->col_range(); for my $row ( +$row_min .. $row_max ) { for my + $col ( $col_min .. $col_max ) { + my $cell = $worksheet->get_cell( $row, $col ); // At this place it gives incorrect result while reading. }

Please help me on this.

Thanks,

Sandeep

Comment on Reading wrong value from excel sheet
Select or Download Code
Re: Reading wrong value from excel sheet
by space_monk (Chaplain) on May 20, 2013 at 17:34 UTC

    Looks like a rounding error - can't you format the results to show values in the needed precision?

    If you spot any bugs in my solutions, it's because I've deliberately left them in as an exercise for the reader! :-)

      Hi space_monk,

      Thnks for your repply. This happens only with some record like in 1,000 records only with 5-6 record. So can't format it or do round off again as this will affect other value.I dont want other value to be rounded off. There are values which have values upto 3 places or 4 places.

      thanks,

      Sandeep

        Your calculations will still round up if you accept 5 or 6 decimal places, From your examples It is only the least significant bit at 14-15dp or so that is causing the problem

        1.139999999999999 will round up to 1.14 at anything up to 14dp (if I've counted correctly)

        If you spot any bugs in my solutions, it's because I've deliberately left them in as an exercise for the reader! :-)
Re: Reading wrong value from excel sheet
by davies (Vicar) on May 20, 2013 at 17:34 UTC
Re: Reading wrong value from excel sheet
by runrig (Abbot) on May 20, 2013 at 17:35 UTC
    You don't say what you use to get the value from the cell. Is it $cell->value() or $cell->unformatted()? Try both ('value()' is the formatted value and might be rounded). If neither help, you'll have to round it yourself. Note that the value you see in Excel is probably rounded also.

      Hi runrig,

      I am printing $cell from below

       my $cell = $sheet -> {Cells} [$row] [$col];

      this itself give me incorrect result.

      I have used

      $cell_value=sprintf("%s", $cell -> {Val});

      to get the values

      Thanks,
Re: Reading wrong value from excel sheet
by MidLifeXis (Prior) on May 20, 2013 at 17:43 UTC

      Hmmm... didn’t I once hear someone saying that “1/10” could not be exactly represented in base-two, just as “1/3” (0.333....) cannot be exactly represented in base-ten?

        I am not sure I understand your point. I don't think that I said that 1/10 could be represented in base-two.

        --MidLifeXis

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1034375]
Front-paged by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others chilling in the Monastery: (5)
As of 2014-09-02 03:13 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite cookbook is:










    Results (18 votes), past polls