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 <dominik.stad...@gmx.at>
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 <
> andr...@manticore-projects.com> 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(XSSFEvaluationWorkbook.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(XSSFEvaluationWorkbook.java:77)
>> at
>> org.apache.poi.ss.formula.WorkbookEvaluator.getSheet(WorkbookEvaluator.java:117)
>> at
>> org.apache.poi.ss.formula.SheetRefEvaluator.getSheet(SheetRefEvaluator.java:53)
>> 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:217)
>> at
>> org.apache.poi.ss.formula.eval.OperandResolver.getSingleValue(OperandResolver.java:67)
>> at
>> org.apache.poi.ss.formula.eval.UnaryMinusEval.evaluate(UnaryMinusEval.java:38)
>> at
>> org.apache.poi.ss.formula.functions.Fixed1ArgFunction.evaluate(Fixed1ArgFunction.java:33)
>> at
>> org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:140)
>> at
>> org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:541)
>> at
>> org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:275)
>> 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:184)
>> at
>> com.manticore.etl.custom.UBASecurityDetails.buildReport(UBASecurityDetails.java:600)
>> at
>> com.manticore.etl.custom.UBASecurityDetails.main(UBASecurityDetails.java:334)
>>
>> Should I open an Ticket on that or am I doing something wrong?
>> Thanks and warm regards
>> Andreas
>>
>>
