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(""); } }
I have re-tested in 3.11
cell.getNumericCellValue() returns the double precision approximations stored in the Excel file. If you want the values as displayed in the Excel app, use https://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/DataFormatter.html#formatCellValue-org.apache.poi.ss.usermodel.Cell-