Currently when evaluate the addition of two double variables, + is use directly. But as we know there are precision loss in java float types, like: System.out.println(0.05+0.01); result: 0.060000000000000005 Actually it's not POI's fault but as POI's API gives the result of the addition, it should be better if POI can return a immutable, arbitrary-precision signed decimal numbers (like BigDecimal). In fact, it's much more easier to process 0.06 than 0.060000000000000005.
POI's internal evaluation works entirely in terms of IEEE doubles which is mostly consistent with Excel. My bet is that internally Excel experiences the same floating point anomalies, but hides them relatively well by using nice number-to-text conversion. I don't think using BigDecimal is a solution. It just transforms the problem into a question of setting the correct 'scale' (which I'm assuming would be POI's responsibility). There is an outstanding request to get POI to more closely emulate Excel number-to-text conversion. Do you think this would help solve your problem?
Thanks Josh. It's really helpful but I don't agree with your suppose ahout the number-to-text conversion in excel. In fact I investigated some about this conversion these days and find it's hard to get the solution. Just imagin how people judge when looking at the numbler like 0.060000000000000005. I bet people maybe guess it's 0.06 but only decide when they've seen 0.05+0.01 So if we only get the result 0.060000000000000005 and we don't know the scale, how to converse? Anyway, this condition only appears when there are Formula cells. What if POI can give support to get the Formula RESULT directly from excel? Or if it's not reachable, maybe we can only consider the expression (0.05+0.01) when do the conversion you said.
Just an aside - to be clear about the rounding problem at hand. The following java code: System.out.println(0.06); System.out.println(0.05 + 0.01); produces the following output: 0.06 0.060000000000000005 In this example of the apparent anomaly, the value 0.06 *seems* to be exactly representable with an IEEE double, but the evaluation of 0.05+0.01 produces a result which is not quite equal to 0.06. These anomalies are not obvious in Excel.
(In reply to comment #2) > ... What if POI > can give support to get the Formula RESULT directly from excel? ... As it turns out, Excel *does* cache the results of calculations for each formula cell (In POI the equivalent data is mapped to FormulaRecord.field_4_value). You can access numeric formula results with HSSFCell.getNumericCellValue() For example: InputStream is = new FileInputStream("sample.xls"); HSSFWorkbook wb = new HSSFWorkbook(is); HSSFCell cell = wb.getSheetAt(0).getRow(0).getCell(0); System.out.println("Value is " + cell.getNumericCellValue()); I made a workbook 'sample.xls' in Excel with the formula "=0.5+0.1" in Sheet1!A1. With that spreadsheet, the above code produced the following output: Value is 0.060000000000000005 It appears that internally Excel has the same rounding issue, so getting "the Formula RESULT directly from excel" is not going to help us here. -- -- -- -- I still believe that the solution to this problem involves intelligent rounding during number to text conversion. For example: double messyResult = 0.05+0.01; String resultText = ExcelNumberToTextConverter.render(messyResult); // * double roundedResult = Double.parseDouble(resultText); System.out.println(roundedResult); *ExcelNumberToTextConverter would be a new POI class. If you disagree, can you explain why a solution like this won't do what you need, and/or suggest a specific alternative?
Hi, I do think that the ExcelNumberToTextConverter may be a good idea, but I want to insist that for the convertion we need not only the result (number like 0.060000000000000005) but also the expression (like 0.5 + 0.1). Otherwise, I think it's hard to find out the approach of 'intelligent rounding'. Just as what I mentioned, people can maybe guess out the result but can only confirm when they've seen the original expression. So does the computer. However, if the expression is really needed. The ExcelNumberToTextConverter is no longer a single converter as it's named.
Fixed in svn r722284 A new class NumberToTextConverter was added. It is now used for all number to text conversion used during formula rendering and formula evaluation. You can also use it on plain double values in the following way: String text = NumberToTextConverter.toText(0.05+0.01); assertEquals("0.06", text); ---- You mentioned the idea of storing both the formula text and result value together in a single object. I imagine it would be quite difficult to provide a complete solution along these lines. Formulas can reference other formulas, and the formula text of the top cell may not have the necessary information. Furthermore, it is not clear how knowing the formula text would be useful in general. If you still think such a class (holding formula & result) would be useful in POI, please submit a working patch to explain more clearly.