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.
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.
Joined: Sep 21, 2011
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.
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.
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.