[Bug 57550] New: evaluating formula produces different result to excel due to using double instead of BigDecimal

[bu...@apache.org]

https://issues.apache.org/bugzilla/show_bug.cgi?id=57550

            Bug ID: 57550
           Summary: evaluating formula produces different result to excel
                    due to using double instead of BigDecimal
           Product: POI
           Version: 3.10-FINAL
          Hardware: PC
            Status: NEW
          Severity: normal
          Priority: P2
         Component: XSSF
          Assignee: dev@poi.apache.org
          Reporter: iainxt@hotmail.com

Created attachment 32442
  --> https://issues.apache.org/bugzilla/attachment.cgi?id=32442&action=edit
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("");
    }
  }

-- 
You are receiving this mail because:
You are the assignee for the bug.

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org

[Bug 57550] evaluating formula produces different result to excel due to using double instead of BigDecimal

[bu...@apache.org]

https://issues.apache.org/bugzilla/show_bug.cgi?id=57550

Iain <ia...@hotmail.com> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
            Version|3.10-FINAL                  |3.11-FINAL
                 OS|                            |All

--- Comment #1 from Iain <ia...@hotmail.com> ---
I have re-tested in 3.11

-- 
You are receiving this mail because:
You are the assignee for the bug.

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org