Created attachment 29858 [details] Example of failing poi if GETPIVOTDATA function exists Overview: If excel file (in my case xlsx) contains GETPIVOTDATA function, then the simple row shifting in workbook will throw an exception: Exception in thread "main" java.lang.ArrayIndexOutOfBoundsException: -1 at org.apache.poi.ss.formula.ptg.AbstractFunctionPtg.getParameterClass(AbstractFunctionPtg.java:160) at org.apache.poi.ss.formula.OperandClassTransformer.transformFunctionNode(OperandClassTransformer.java:277) at org.apache.poi.ss.formula.OperandClassTransformer.transformNode(OperandClassTransformer.java:134) at org.apache.poi.ss.formula.OperandClassTransformer.transformFormula(OperandClassTransformer.java:87) at org.apache.poi.ss.formula.FormulaParser.getRPNPtg(FormulaParser.java:1580) at org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:177) at org.apache.poi.xssf.usermodel.helpers.XSSFRowShifter.shiftFormula(XSSFRowShifter.java:186) at org.apache.poi.xssf.usermodel.helpers.XSSFRowShifter.updateRowFormulas(XSSFRowShifter.java:156) at org.apache.poi.xssf.usermodel.helpers.XSSFRowShifter.updateSheetFormulas(XSSFRowShifter.java:143) at org.apache.poi.xssf.usermodel.helpers.XSSFRowShifter.updateFormulas(XSSFRowShifter.java:136) at org.apache.poi.xssf.usermodel.XSSFSheet.shiftRows(XSSFSheet.java:2364) This happens due to broken metadata in the "functionMetadata.txt" where columns "Return Class" and "Parameter classes" for GETPIVOTDATA functions are omitted: 358 GETPIVOTDATA 2 30 Cause the source "excelfileformat.odt" file doesn't contain this information. Modifying the metadata for this formula fixes the problem. Working solution is: 358 GETPIVOTDATA 2 30 V V R ... Example files are attached.
Experienced the same problem. Proposed solution works for me. It would be great to have it as generic change instead of patching poi jar each time....
Created attachment 30007 [details] Getpivotdata function bug patch Attached patch
Comment on attachment 30007 [details] Getpivotdata function bug patch Index: src/resources/main/org/apache/poi/ss/formula/function/functionMetadata.txt =================================================================== --- src/resources/main/org/apache/poi/ss/formula/function/functionMetadata.txt (revision 1451559) +++ src/resources/main/org/apache/poi/ss/formula/function/functionMetadata.txt (working copy) @@ -274,7 +274,7 @@ 353 NUMBERSTRING 2 2 V V V 354 ROMAN 1 2 V V V # New Built-In Sheet Functions in BIFF8 -358 GETPIVOTDATA 2 30 +358 GETPIVOTDATA 2 30 V V R ... 359 HYPERLINK 1 2 V V V 360 PHONETIC 1 1 V R 361 AVERAGEA 1 30 V R ...
Created attachment 30008 [details] Getpivotdata function bug patch txt format Previous patch.tar.gz can't be downloaded or viewed.
Patch applied in r1451875 The metadata is fixed but you still be getting exceptions because GETPIVOTDATA is not implemented in current version of POI. If you need to evaluate workbooks with this function you need to implement it and register via WorkbookEvaluator#registerFunction, see http://poi.apache.org/spreadsheet/eval-devguide.html I could not reproduce the problem with the test from the attached archive. The code throws a exception but a different one: java.lang.IllegalArgumentException: firstMovedIndex, lastMovedIndex out of order at org.apache.poi.ss.formula.FormulaShifter.<init>(FormulaShifter.java:56) at org.apache.poi.ss.formula.FormulaShifter.createForRowShift(FormulaShifter.java:81) at org.apache.poi.xssf.usermodel.XSSFSheet.shiftRows(XSSFSheet.java:2361) I confirmed it with POI-3.9 and trunk. The simplest test for this fix is to evaluate all formulas in the workbook: workbook.getCreationHelper().createFormulaEvaluator().evaluateAll(); Without this fix it fails with ArrayIndexOutOfBoundsException and with the patch applied it passes OK. Regards, Yegor