*
The moose likes Java in General and the fly likes messy decimal when reading from excel Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Java » Java in General
Bookmark "messy decimal when reading from excel" Watch "messy decimal when reading from excel" New topic
Author

messy decimal when reading from excel

Hendra Kurniawan
Ranch Hand

Joined: Jan 31, 2011
Posts: 239
The number in the excel was 1.6741, but when read by apache POI's getNumericCellValue() it returns 1.6741000000000001. Several cases are like that, but curiously not all. Some are correctly read like 0.632183 (six decimal places which is longer than the problematic 4 decimal places). How to solve this? thanks.
Tim Moores
Rancher

Joined: Sep 21, 2011
Posts: 2408
Read #20 in the http://www.coderanch.com/how-to/java/JavaBeginnersFaq. You probably want to round the results to the number of digits you need. POI may also have methods that do that for you.
Hendra Kurniawan
Ranch Hand

Joined: Jan 31, 2011
Posts: 239
the number was returned garbled by apache POI. Furthermore, I need the numbers to remain unaltered from the excel. If anybody knows how POI solves this problem, then I'm happy to hear it.
Tim Moores
Rancher

Joined: Sep 21, 2011
Posts: 2408
Hendra Kurniawan wrote:the number was returned garbled by apache POI

This comment suggests that you have not read, or not fully understood, the article I linked to. Not all numbers can be displayed and/or stored precisely. It's up to the GUI (Excel in this case) to allow for that, or to handle it if it happens. It would appear that Excel and your code don't do it in the same way.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

I'd just add that Excel handles these cases not only when displaying the numbers, but also when performing calculations: put a formula "=1/10" into ten cells (A1:A10) and add a sum of these cells into A11. Not only it will come up as 1.00000000, but if you compare that to a constant value of 1, it will match (eg. a formula "=A11=1" will display TRUE).

This might seemingly support the perception that Excel has it right somehow and Apache POI messes it up, but - as Tim has explained - that is not the case. The POI is giving you the value as precise as it is, it is up to you to handle the decimal inaccuracies correctly. It will probably be very difficult to exactly match Excel's handling.
Vikas Kumar Gupta
Greenhorn

Joined: Aug 03, 2011
Posts: 4
Hi Hendra,

Once I face similar problem, and this can be resolved.

How excel stores values:
Excel stores all values as double. Formatting in excel is attached to the cell and not the values. It means if you put 123.33333333333333 and formatted it to show 3 digits after decimal, then in that case excel will still contain the value as 123.33333333333333 but shows up value 123.3333.

What POI does:
POI reads the actual double value that is stored by the excel.

Solutions:
You can use DataFormatter for such scenario. DataFormatter will read the format of the cell to read. Then it read the double value of cell, and then apply the format on the value and will return the formatted value.

Be careful:
DataFormatter is capable of reading format of a cell that is NOT custom formatted. It means whatever you see (not contained) in a cell after formatting will be read, if the cell is NOT custom formatted.
In case the cell is custom formatted, what value you see in the formula bar when you click on the cell will be read by POI. (Because sometimes value in formula bar, value that you see in cell and value you entered in cell, may differ)

Another approach:
You can also use custom format with DataFormatter but that would not be recommended, as they are hardcoded. But will work in every scenario.

Please corect if I am wrong.

Regards,
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 38057
    
  22
Vikas Kumar Gupta wrote: . . . formatted it to show 3 digits after decimal, then in that case excel will still contain the value as 123.33333333333333 but shows up value 123.3333. . . ,
123.333, surely? But if the value were 123.4567890, it would display 123.457 at 3 decimal places.
Vikas Kumar Gupta
Greenhorn

Joined: Aug 03, 2011
Posts: 4
Hi Campbell,

Thanks for correcting.

That was a printing mistake
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: messy decimal when reading from excel
 
Similar Threads
How to Trim Numbers if No Decimal Values
Losing decimal places when using JXL
double with only two decimals?
Export to excel file
BigDecimal is driving me crazy