Opened 5 years ago

Closed 5 years ago

#2177 closed enhancement (fixed)

Better handling of Excel files with formula errors

Reported by: Nicklas Nordborg Owned by: everyone
Priority: major Milestone: BASE 3.15.1
Component: core Version:
Keywords: Cc:

Description

Formula evaluation is enabled by default when parsing Excel files. But the Apache POI library doesn't support all functions that Excel has. Also, if LibreOffice is the source of the document, there are even more functions that are not supported. For example, I had a file with =CONCAT(...) and got:

Error message 	Error evaluating cell Sheet1!D3
Stacktrace 	

org.apache.poi.ss.formula.eval.NotImplementedException
...at org.apache.poi.ss.formula.WorkbookEvaluator.addExceptionInfo(WorkbookEvaluator.java:344)
...at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:285)
...at org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:216)
...at org.apache.poi.xssf.usermodel.BaseXSSFFormulaEvaluator.evaluateFormulaCellValue(BaseXSSFFormulaEvaluator.java:56)
...at org.apache.poi.ss.formula.BaseFormulaEvaluator.evaluateFormulaCell(BaseFormulaEvaluator.java:185)
...at org.apache.poi.ss.usermodel.DataFormatter.formatCellValue(DataFormatter.java:990)
...at org.apache.poi.ss.usermodel.DataFormatter.formatCellValue(DataFormatter.java:948)
...at net.sf.basedb.util.excel.XlsxToCsvUtil$SheetInfo.getCellValueAsString(XlsxToCsvUtil.java:520)
...at net.sf.basedb.util.excel.XlsxToCsvUtil$SheetInfo.parseToBytes(XlsxToCsvUtil.java:614)
...at net.sf.basedb.util.excel.XlsxToCsvUtil$SheetInfo.parseToCsv(XlsxToCsvUtil.java:587)
...at net.sf.basedb.util.parser.FlatFileParser.setInputStream(FlatFileParser.java:745)
...at org.apache.jsp.common.plugin.parse_005ffile_jsp._jspService(parse_005ffile_jsp.java:357)
...

In this case the workaround was to use =CONCATENATE(...) instead.

On the other hand, it would be nice if a formula error doesn't cause the entire parsing to fail, but only displays an error message in the cell. For example, #NAME? (as LibreOffice does).

Change History (1)

comment:1 by Nicklas Nordborg, 5 years ago

Resolution: fixed
Status: newclosed

In 7709:

Fixes #2177: Better handling of Excel files with formula errors

This should fix the problem with errors. If the error is due to a missing function we also try to output some information about this. It can probably be improved to cover more cases, but cells with an error should no longer crash the entire import.

Note: See TracTickets for help on using tickets.