Created attachment 38332 [details] Sample file causing a StackoverflowError Formatting A1013 on the first worksheet of the attached excel file in this way FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); DataFormatter formatter = new DataFormatter(); var value = formatter.formatCellValue(cell, evaluator); causes an StackoverflowError. A1013 contains the weird formular '=SUMME(A1012+1)' / '=SUM(A1012+1)'. Nevertheless it is evaluated in Excel without any problems. java.lang.StackOverflowError at java.base/java.util.regex.Pattern$GroupHead.match(Pattern.java:4789) at java.base/java.util.regex.Pattern$Branch.match(Pattern.java:4734) at java.base/java.util.regex.Pattern$BranchConn.match(Pattern.java:4698) at java.base/java.util.regex.Pattern$GroupTail.match(Pattern.java:4820) at java.base/java.util.regex.Pattern$BmpCharPropertyGreedy.match(Pattern.java:4329) at java.base/java.util.regex.Pattern$Ques.match(Pattern.java:4230) at java.base/java.util.regex.Pattern$GroupHead.match(Pattern.java:4789) at java.base/java.util.regex.Pattern$Branch.match(Pattern.java:4734) at java.base/java.util.regex.Matcher.match(Matcher.java:1755) at java.base/java.util.regex.Matcher.matches(Matcher.java:712) at org.apache.poi.ss.formula.FormulaParser.parseSimpleRangePart(FormulaParser.java:1041) at org.apache.poi.ss.formula.FormulaParser.parseRangeable(FormulaParser.java:476) at org.apache.poi.ss.formula.FormulaParser.parseRangeExpression(FormulaParser.java:325) at org.apache.poi.ss.formula.FormulaParser.parseSimpleFactor(FormulaParser.java:1553) at org.apache.poi.ss.formula.FormulaParser.percentFactor(FormulaParser.java:1511) at org.apache.poi.ss.formula.FormulaParser.powerFactor(FormulaParser.java:1498) at org.apache.poi.ss.formula.FormulaParser.Term(FormulaParser.java:1872) at org.apache.poi.ss.formula.FormulaParser.additiveExpression(FormulaParser.java:1999) at org.apache.poi.ss.formula.FormulaParser.concatExpression(FormulaParser.java:1983) at org.apache.poi.ss.formula.FormulaParser.comparisonExpression(FormulaParser.java:1940) at org.apache.poi.ss.formula.FormulaParser.intersectionExpression(FormulaParser.java:1913) at org.apache.poi.ss.formula.FormulaParser.Arguments(FormulaParser.java:1484) at org.apache.poi.ss.formula.FormulaParser.function(FormulaParser.java:1357) at org.apache.poi.ss.formula.FormulaParser.parseNonRange(FormulaParser.java:902) at org.apache.poi.ss.formula.FormulaParser.parseRangeable(FormulaParser.java:581) at org.apache.poi.ss.formula.FormulaParser.parseRangeExpression(FormulaParser.java:325) at org.apache.poi.ss.formula.FormulaParser.parseSimpleFactor(FormulaParser.java:1553) at org.apache.poi.ss.formula.FormulaParser.percentFactor(FormulaParser.java:1511) at org.apache.poi.ss.formula.FormulaParser.powerFactor(FormulaParser.java:1498) at org.apache.poi.ss.formula.FormulaParser.Term(FormulaParser.java:1872) at org.apache.poi.ss.formula.FormulaParser.additiveExpression(FormulaParser.java:1999) at org.apache.poi.ss.formula.FormulaParser.concatExpression(FormulaParser.java:1983) at org.apache.poi.ss.formula.FormulaParser.comparisonExpression(FormulaParser.java:1940) at org.apache.poi.ss.formula.FormulaParser.intersectionExpression(FormulaParser.java:1913) at org.apache.poi.ss.formula.FormulaParser.unionExpression(FormulaParser.java:1894) at org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:2041) at org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:173) at org.apache.poi.xssf.usermodel.XSSFCell.convertSharedFormula(XSSFCell.java:434) at org.apache.poi.xssf.usermodel.XSSFCell.getCellFormula(XSSFCell.java:403) at org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook.getFormulaTokens(XSSFEvaluationWorkbook.java:85) at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:260) at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateReference(WorkbookEvaluator.java:791) at org.apache.poi.ss.formula.SheetRefEvaluator.getEvalForCell(SheetRefEvaluator.java:48) at org.apache.poi.ss.formula.SheetRangeEvaluator.getEvalForCell(SheetRangeEvaluator.java:74) at org.apache.poi.ss.formula.LazyRefEval.getInnerValueEval(LazyRefEval.java:39) at org.apache.poi.ss.formula.eval.OperandResolver.chooseSingleElementFromRef(OperandResolver.java:216) at org.apache.poi.ss.formula.eval.OperandResolver.getSingleValue(OperandResolver.java:64) at org.apache.poi.ss.formula.eval.TwoOperandNumericOperation.singleOperandEvaluate(TwoOperandNumericOperation.java:31) at org.apache.poi.ss.formula.eval.TwoOperandNumericOperation.evaluate(TwoOperandNumericOperation.java:60) at org.apache.poi.ss.formula.functions.Fixed2ArgFunction.evaluate(Fixed2ArgFunction.java:31) at org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:133) at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:537) at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:264) at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateReference(WorkbookEvaluator.java:791) at org.apache.poi.ss.formula.SheetRefEvaluator.getEvalForCell(SheetRefEvaluator.java:48) at org.apache.poi.ss.formula.SheetRangeEvaluator.getEvalForCell(SheetRangeEvaluator.java:74) at org.apache.poi.ss.formula.LazyRefEval.getInnerValueEval(LazyRefEval.java:39) at org.apache.poi.ss.formula.eval.OperandResolver.chooseSingleElementFromRef(OperandResolver.java:216) at org.apache.poi.ss.formula.eval.OperandResolver.getSingleValue(OperandResolver.java:64) at org.apache.poi.ss.formula.eval.TwoOperandNumericOperation.singleOperandEvaluate(TwoOperandNumericOperation.java:31) at org.apache.poi.ss.formula.eval.TwoOperandNumericOperation.evaluate(TwoOperandNumericOperation.java:60)
One workaround is use 'formulaEvaluator.evaluateAll()' - this will work out the cell values 1 by 1. And each cell value is cached - so when you calculate the last one, you don't need to calculate all the preceding ones. Also, you don't really need to use the formula evaluator when you read a workbook. The last values calculated by Excel are stored in the xlsx file. You only need to use the formulaEvaluator if you have to re-evaluate after changing some values in POI code.
A test case that produces a similar issue (not exactly the same) - r1902390
(In reply to PJ Fanning from comment #1) > One workaround is use 'formulaEvaluator.evaluateAll()' - this will work out > the cell values 1 by 1. And each cell value is cached - so when you > calculate the last one, you don't need to calculate all the preceding ones. > > Also, you don't really need to use the formula evaluator when you read a > workbook. The last values calculated by Excel are stored in the xlsx file. > You only need to use the formulaEvaluator if you have to re-evaluate after > changing some values in POI code. @PJ Fanning Thank you for your support and the workaround. The workaround avoids the StackoverflowError and the file is evaluated without further problems.
The workaround only works if the formulas depend on cells above the cell in question. If the cells depend on cells below them then the Stackoverflow can still happen. I have a general idea to create a new AsyncFormulaEvaluator that uses Java Futures to avoid calculating all the formulas in the same thread.
Added a POC for one way to fix this - https://github.com/apache/poi/pull/363 - feel free to comment.