|Summary:||[PATCH] Floating point rounding problems with ROUND function|
|Product:||POI||Reporter:||Jostein Tveit <josteitv>|
|Component:||XSSF||Assignee:||POI Developers List <dev>|
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.