Bug 56315

Summary: [PATCH] Floating point rounding problems with ROUND function
Product: POI Reporter: Jostein Tveit <josteitv>
Component: XSSFAssignee: POI Developers List <dev>
Status: RESOLVED FIXED    
Severity: normal    
Priority: P2    
Version: 3.10-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: All   
Attachments: Simple Java program and Excel sheet to reproduce the bug
[PATCH] Patch against trunk to solve this bug
The patch including unit test against excel spreadsheet
The patch as text file

Description Jostein Tveit 2014-03-25 22:21:18 UTC
Created attachment 31438 [details]
Simple Java program and Excel sheet to reproduce the bug

When running the attached code I get the following output:
A1 numeric: 0.049999999999999975
A3 numeric: 0.0
A3 text: 0
A3 formatted: 0
A4 concatenated: test 0

If I open the spreadsheet in Excel it displays:
A1: 0.05
A3: 0.1
A4: test 0.1


The ROUND function in cell A3 returns 0.0 in POI, while it returns 0.1 in Excel.
I assume this is because of the problems with storing exact floating point numbers, but I really think POI and excel should return the same result.

When the value is used in a CONCATENATE function in cell A4, the result from POI also differs from Excel.
Comment 1 Jostein Tveit 2014-03-28 12:22:53 UTC
Created attachment 31453 [details]
[PATCH] Patch against trunk to solve this bug

I fixed this bug by using

new java.math.BigDecimal(NumberToTextConverter.toText(n))

instead of

java.math.BigDecimal.valueOf(n)

All other unit tests goes green, so I assume this solution is ok.
Comment 2 Jostein Tveit 2014-03-28 12:30:25 UTC
Created attachment 31454 [details]
The patch including unit test against excel spreadsheet

I'm adding the patch and the tar.gz separately
Comment 3 Jostein Tveit 2014-03-28 12:31:14 UTC
Created attachment 31455 [details]
The patch as text file

I'm adding the patch and the tar.gz separately
Comment 4 Dominik Stadler 2014-04-18 18:22:26 UTC
Thanks for the patch, this is applied with r1588538 with some additional testing in TestMathX.
Comment 5 Dominik Stadler 2015-03-14 15:00:13 UTC
Why was this bug reopened? Please provide some useful explanation if you believe a bug is not fixed. 

Before reopening bugs, please make sure to verify the problem with latest trunk builds (either from source or nightly snapshot builds) as fixes usually only become available as part of releases after quite some time.