Bug 57550

Summary: evaluating formula produces different result to excel due to using double instead of BigDecimal
Product: POI Reporter: Iain <iainxt>
Component: XSSFAssignee: POI Developers List <dev>
Status: NEW ---    
Severity: normal    
Priority: P2    
Version: 3.11-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: All   
Attachments: Used by code to demostrate the issue

Description Iain 2015-02-09 09:51:50 UTC
Created attachment 32442 [details]
Used by code to demostrate the issue

When I evaulate a the formula in Excel I get 92.4, but if I invoke the following and read the same cell I get 92.39

workbook.getCreationHelper().createFormulaEvaluator().evaluateAll();

I suspect the issue is internally poi is storing part of the result as a double, and just before I FLOOR the value it has is 92.399999999999

The formula
=FLOOR(2100*0.044,0.01)

Excel result: 92.4
POI result: 94.39

The code
  @Test
  public void roundingBug() throws IOException {
    File file = new File("E:\\Temp\\POI.Formula.RoundingBug.xlsx");

    XSSFWorkbook workbook = new XSSFWorkbook((new FileInputStream(file)));
    workbook.getCreationHelper().createFormulaEvaluator().evaluateAll();
    XSSFSheet sheet = workbook.getSheetAt(0);
    for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
      Row row = sheet.getRow(i);
      if (row == null) {
        continue;
      }
      System.out.print("Row " + i + ": ");
      for (int c = 0; c < row.getPhysicalNumberOfCells(); c++) {
        Cell cell = row.getCell(c);
        if (cell == null) {
          System.out.print("null ");
        } else {
          int type = cell.getCellType();
          if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
            type = cell.getCachedFormulaResultType();
          }
          if (type == Cell.CELL_TYPE_NUMERIC) {
            System.out.print(cell.getNumericCellValue() + " ");
          } else if (type == Cell.CELL_TYPE_STRING) {
            System.out.print(cell.getStringCellValue() + " ");
          }
        }
      }
      System.out.println("");
    }
  }
Comment 1 Iain 2015-02-09 09:52:26 UTC
I have re-tested in 3.11