Bug 62846 - FormulaParseException thrown for row containing empty formula cells
Summary: FormulaParseException thrown for row containing empty formula cells
Status: NEEDINFO
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 4.0.0-FINAL
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords: ErrorMessage
Depends on:
Blocks:
 
Reported: 2018-10-22 22:03 UTC by Valerie Griffin
Modified: 2018-10-23 07:12 UTC (History)
1 user (show)



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Valerie Griffin 2018-10-22 22:03:08 UTC
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.
Comment 1 Yegor Kozlov 2018-10-23 07:12:23 UTC
Please attach a sample file demonstrating the problem. We will include it in our collection of test cases.

Yegor