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).
Note:
See TracTickets
for help on using tickets.
In 7709: