Greetings Dominik. thanks for the feedback. I actually assumed already a similar thing and it is worth to note that my Workbook is completely code generated (with many sheets and references in between the sheets).
However, based on my suspicion I actually created all Sheets and Cells and Formulas first before I create the Evaluator at the very end and run the evaluation. Also, it seems to happen only sporadically: Sometimes it runs through without an exception, sometimes exceptions are thrown. So my next assumption was about a timing/parallelism issue. "Re-Create the Evaluator" at then end alone does not solve this problem, I would investigate clearAllCachedResultValues() and also work on a simplified test. Best regards Andreas On Sat, 2021-10-30 at 14:55 +0200, Dominik Stadler wrote: > Hi, > > I can reproduce it if you keep the instance of the FormulaEvaluator > while > adding more sheets to the workbook between calls to evaluateXXX() as > then > internal caches get confused. > > Workaround is to either create all sheets up-front or re-create the > Evaluator or use clearAllCachedResultValues() to clear caches > whenever > sheets are added. > > Thanks... Dominik. > > On Sat, Oct 30, 2021 at 2:37 PM Dominik Stadler > <[email protected]> > wrote: > > > Hi, > > > > Sounds like a strange case as there is a check right before that > > line > > which should trigger a more specific error message, but somehow it > > is not > > triggered. > > > > Can you create an issue and if possible share a sample workbook > > which > > shows this problem? > > > > Thanks... Dominik. > > > > On Sat, Oct 30, 2021 at 12:20 PM Andreas Reichel < > > [email protected]> wrote: > > > > > Greetings POI team. > > > > > > Using Apache POI 5.0 I would like evaluate all Cells in order to > > > auto- > > > size all columns in all sheets: > > > FormulaEvaluator evaluator = > > > workbook.getCreationHelper().createFormulaEvaluator(); > > > for (int i =0; i<sheetParameters.length; i++) { > > > Sheet sheet = workbook.getSheetAt(i); > > > for (Row r : sheet) { > > > for (Cell c : r) { > > > if (c.getCellType() == CellType.FORMULA) { > > > try { > > > // this can throw an > > > ArrayIndexOutOfBoundsException > > > // at > > > org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook.getSheet(XSS > > > FEvaluationWorkbook.java:77) > > > evaluator.evaluateFormulaCell(c); > > > } catch (Exception ignore) { > > > > > > } > > > } > > > } > > > } > > > > > > for (int k=0; k<=CAPTIONS.length; k++) > > > sheet.autoSizeColumn(k); > > > } > > > > > > To my surprise this can throw an Exception: > > > > > > java.lang.ArrayIndexOutOfBoundsException: Index 8 out of bounds > > > for > > > length 8 > > > at > > > org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook.getSheet(XSS > > > FEvaluationWorkbook.java:77) > > > at > > > org.apache.poi.ss.formula.WorkbookEvaluator.getSheet(WorkbookEval > > > uator.java:117) > > > at > > > org.apache.poi.ss.formula.SheetRefEvaluator.getSheet(SheetRefEval > > > uator.java:53) > > > at > > > org.apache.poi.ss.formula.SheetRefEvaluator.getEvalForCell(SheetR > > > efEvaluator.java:48) > > > at > > > org.apache.poi.ss.formula.SheetRangeEvaluator.getEvalForCell(Shee > > > tRangeEvaluator.java:74) > > > at > > > org.apache.poi.ss.formula.LazyRefEval.getInnerValueEval(LazyRefEv > > > al.java:39) > > > at > > > org.apache.poi.ss.formula.eval.OperandResolver.chooseSingleElemen > > > tFromRef(OperandResolver.java:217) > > > at > > > org.apache.poi.ss.formula.eval.OperandResolver.getSingleValue(Ope > > > randResolver.java:67) > > > at > > > org.apache.poi.ss.formula.eval.UnaryMinusEval.evaluate(UnaryMinus > > > Eval.java:38) > > > at > > > org.apache.poi.ss.formula.functions.Fixed1ArgFunction.evaluate(Fi > > > xed1ArgFunction.java:33) > > > at > > > org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(Oper > > > ationEvaluatorFactory.java:140) > > > at > > > org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(Workb > > > ookEvaluator.java:541) > > > at > > > org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookE > > > valuator.java:275) > > > at > > > org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEval > > > uator.java:216) > > > at > > > org.apache.poi.xssf.usermodel.BaseXSSFFormulaEvaluator.evaluateFo > > > rmulaCellValue(BaseXSSFFormulaEvaluator.java:56) > > > at > > > org.apache.poi.ss.formula.BaseFormulaEvaluator.evaluateFormulaCel > > > l(BaseFormulaEvaluator.java:184) > > > at > > > com.manticore.etl.custom.UBASecurityDetails.buildReport(UBASecuri > > > tyDetails.java:600) > > > at > > > com.manticore.etl.custom.UBASecurityDetails.main(UBASecurityDetai > > > ls.java:334) > > > > > > Should I open an Ticket on that or am I doing something wrong? > > > Thanks and warm regards > > > Andreas > > > > > >
