Beefy Boxes and Bandwidth Generously Provided by pair Networks
more useful options
 
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

Re: Reading wrong value from excel sheet
by kjhambrick (Novice) on Oct 11, 2014 at 12:02 UTC
    Sandeep --

    It has been a while since anyone posted to this thread but the following trick may help someone else.

    This feature of SpreadsheetXLSX has annoyed me for several years.

    When the Cell is Numeric, try using $cell->unformatted() +0 ;

    Worked for me ... it did remove the excess precision from the numbers.

    -- kjh

    Example:

    # place these somewhere in the BEGIN Section # my $DateFmt = "yyyy-mm-dd" ; my %HtmlShitAry ; $HtmlShitAry{ '&nbsp;' } = ' ' ; $HtmlShitAry{ '&apos;' } = "'" ; $HtmlShitAry{ '&quot;' } = '"' ; # Watch this one -- it bites !!! $HtmlShitAry{ '&amp;' } = '&' ; $HtmlShitAry{ '&lt;' } = '<' ; $HtmlShitAry{ '&gt;' } = '>' ; my $HtmlShitREx = join "|", keys %HtmlShitAry ; $HtmlShitREx = qr/$HtmlShitREx/ ; # # place those somewhere in the BEGIN Section : # # then, to 'fix' excess precision, add 0 to unformatted() ; # if ( defined $Cell->{Type} ) { if ( $Cell->{Type} eq 'Date' ) { $Val = ExcelFmt( $DateFmt, $Cell->{Val} ); } elsif ( $Cell->{Type} eq 'Numeric' ) { $Val = ( $Cell->unformatted() ) ? $Cell->unformatted() +0 # fix precision ! : 0 ; } else { $Val =~ s/($HtmlShitREx)/$HtmlShitAry{ $1 }/g ; } } else { $Val =~ s/($HtmlShitREx)/$HtmlShitAry{ $1 }/g ; }

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 surveying the Monastery: (5)
As of 2014-12-20 06:03 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (95 votes), past polls