Re: XSSFEvaluationWorkbook.getSheet can throw ArrayIndexOutOfBoundsException

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
> > > 
> > >