I'm reading workbooks that may have been downloaded from Google sheets, so there are errors where a formula doesn't get converted. for each cell in the range I'm looking at, I want to display "ERROR" for cells with invalid formulas. I'm using lambda, so the code was essentially: inRow.forEach(inCell -> { if (CellType.FORMULA == inCell.getCellType()) { try { evaluator.evaluateInCell(inCell); } catch (Exception ex) { inCell.setCellType(CellType.STRING); inCell.setCellValue("ERROR"); } } }); I was getting FormulaParseException thrown WITHOUT being caught! The problem was that the value looked at by the evaluator was itself an error message that a FormulaParseException had been thrown. With more evaluation, I determined that the cells for which the exception was thrown were empty, and that a single FormulaParseException was thrown to my code as part of the row evaluation, probably dealing with the forEach invocation. My code is now: try { inRow.forEach(inCell -> { if (CellType.FORMULA == inCell.getCellType()) { String raw = ((XSSFCell) inCell).getRawValue(); if (raw.isEmpty()) { inCell.setCellType(CellType.STRING); inCell.setCellValue(""); } else { try { evaluator.evaluateInCell(inCell); } catch (Exception ex) { inCell.setCellType(CellType.STRING); inCell.setCellValue("ERROR"); } } }); } catch (FormulaParseException) { // whatever.... } I think the empty cells should be silently ignored.
Please attach a sample file demonstrating the problem. We will include it in our collection of test cases. Yegor