Bug 53076

Summary: cell.getNumericCellValue() returns incorrect results sometimes
Product: POI Reporter: rohit.vats
Component: HSSFAssignee: POI Developers List <dev>
Status: RESOLVED INVALID    
Severity: major CC: rohit.vats
Priority: P2    
Version: 3.6-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: All   
Attachments: File demonstrating the bug

Description rohit.vats 2012-04-13 14:06:27 UTC
Created attachment 28604 [details]
File demonstrating the bug

In an excel sheet processed through poi, getNumericCellValue() returned an incorrect result for a decimal value. The file is attached. The issue happens with the "Quantity" field of the first second row (-23,530.9150).

Value in .xls file: -23,530.9150
Formatting: Category - Number, Decimal Places - 4, Use 1000 separator - True
Value returned by getNumericCellValue: -23530.915000000045
Comment 1 Nick Burch 2012-04-13 14:12:18 UTC
getNumericCellValue() returns the number stored in the file, which is a floating point. If you want a string that looks the same as excel shows, use something like DataFormatter to format your number
Comment 2 rohit.vats 2012-04-13 15:27:00 UTC
Ok, the actual value in the field seems to be -23530.915, which is why I wondered why getNumericCellValue() returned -23530.915000000045.

Any thoughts?
Comment 3 rohit.vats 2012-04-13 15:28:43 UTC
(In reply to comment #1)
> getNumericCellValue() returns the number stored in the file, which is a
> floating point. If you want a string that looks the same as excel shows, use
> something like DataFormatter to format your number

Ok, the actual value in the field seems to be -23530.915, which is why I
wondered why getNumericCellValue() returned -23530.915000000045.

Any thoughts?
Comment 4 Nick Burch 2012-04-13 15:53:46 UTC
http://en.wikipedia.org/wiki/Floating_point
Comment 5 Richard Ngo 2012-04-15 02:42:15 UTC
have you tried using http://poi.apache.org/apidocs/org/apache/poi/ss/util/NumberToTextConverter.html?