Hello, this investigation began when I found out that external names looking
like a column (say, [1]!EXT) are not parsed correctly (parser throws). But then
I realized that the problem is deeper.
Currently FormulaParser doesn't allow to parse a formula that contains a
missing name, although it's a perfectly valid scenario for Excel which allows
to enter a formula containing a missing name and returns #REF!. Parsing and
evaluating are connected but distinct things.
Here's a use case:
XSSFWorkbook workbook = new XSSFWorkbook();
Cell cell = workbook.createSheet().createRow(0).createCell(0);
cell.setCellFormula("MISSING_NAME"); // shall not throw
// shall evaluate without exceptions and return REF_INVALID
workbook.getCreationHelper().createFormulaEvaluator().evaluateAll();
assertEquals(FormulaError.REF.getCode(), cell.getErrorCellValue());
// then we create a name...
Name name = workbook.createName();
name.setNameName("MISSING_NAME");
name.setRefersToFormula("4");
// re-evaluate...
workbook.getCreationHelper().createFormulaEvaluator().evaluateAll();
// and now it works!
assertEquals(4, cell.getNumericCellValue(), 0);
Are there any strong objections to this proposed behavior?
I am already working on the implementation and hopefully will be able to
release it soon if the maintainers approve the new behavior.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
