Bug 57550 - evaluating formula produces different result to excel due to using double instead of BigDecimal
Summary: evaluating formula produces different result to excel due to using double ins...
Status: NEW
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.11-FINAL
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2015-02-09 09:51 UTC by Iain
Modified: 2015-02-09 09:52 UTC (History)
0 users



Attachments
Used by code to demostrate the issue (8.13 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2015-02-09 09:51 UTC, Iain
Details

Note You need to log in before you can comment on or make changes to this bug.
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