Created attachment 38347 [details] Value Function Of Blank Code: FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); evaluator.evaluate(row.getCell(0)).getCellType(); -------------------- The CellValue object of A1. should be CellType.ERROR instead of CellType.NUMERIC. The issue when this is that the generated Excel will not mark cell A1 in Red, resulting in possible escape.
I added a test case - r1903170 It looks like a bug alright. One workaround is to use: Cell a1 = row.getCell(0); assertEquals(CellType.FORMULA, a1.getCellType()); assertEquals(CellType.ERROR, a1.getCachedFormulaResultType()); These 2 assertions return the correct results. The issue is with evaluator.evaluate(...).
I think r1903171 fixes this
(In reply to PJ Fanning from comment #2) > I think r1903171 fixes this Hi, Which version will this be on?
This will be in next release (5.2.3) which can be tracked at https://poi.apache.org/changes.html
Hi, I believe this change broke other related behaviour - referencing blank cell. Having empty cell A1 I whould expect formula "=VALUE(A1)" to evaluate to zero, instead it now returns "#VALUE!". Interestingly enough, chaining reference (empty A1, B1 = "=A1", C1 = "=VALUE(B1)") works well. I don't know if this behaviour (value on reference to empty cell evaluating to zero) is desired - I could confirm that LibreOffice and Excel 365 does that. I've created pull request https://github.com/apache/poi/pull/397 to illustrate the issue (and potential fix).
I added r1905308