Win a copy of Functional Reactive Programming this week in the Other Languages forum!

messy decimal when reading from excel

Hendra Kurniawan
Ranch Hand
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
Bartender
Posts: 2954
46
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
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
Bartender
Posts: 2954
46
• 2
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
Posts: 3752
62
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
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
Posts: 50258
79
• 1
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
Posts: 4
Hi Campbell,

Thanks for correcting.

That was a printing mistake