Bug 54436 - Broken matadata for GETPIVOTDATA function
Summary: Broken matadata for GETPIVOTDATA function
Alias: None
Product: POI
Classification: Unclassified
Component: POI Overall (show other bugs)
Version: 3.9-FINAL
Hardware: PC All
: P2 major (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2013-01-16 14:31 UTC by Daniil Lopatin
Modified: 2013-03-02 12:06 UTC (History)
3 users (show)

Example of failing poi if GETPIVOTDATA function exists (9.19 KB, application/octet-stream)
2013-01-16 14:31 UTC, Daniil Lopatin
Getpivotdata function bug patch (398 bytes, patch)
2013-03-01 17:22 UTC, Daniil Lopatin
Details | Diff
Getpivotdata function bug patch txt format (611 bytes, patch)
2013-03-01 17:35 UTC, Daniil Lopatin
Details | Diff

Note You need to log in before you can comment on or make changes to this bug.
Description Daniil Lopatin 2013-01-16 14:31:01 UTC
Created attachment 29858 [details]
Example of failing poi if GETPIVOTDATA function exists


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.
Comment 1 alantushenko 2013-03-01 14:26:19 UTC
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....
Comment 2 Daniil Lopatin 2013-03-01 17:22:55 UTC
Created attachment 30007 [details]
Getpivotdata function bug patch

Attached patch
Comment 3 Daniil Lopatin 2013-03-01 17:28:47 UTC
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 @@
 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 ...
Comment 4 Daniil Lopatin 2013-03-01 17:35:31 UTC
Created attachment 30008 [details]
Getpivotdata function bug patch txt format

Previous patch.tar.gz can't be downloaded or viewed.
Comment 5 Yegor Kozlov 2013-03-02 12:06:16 UTC
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:


Without this fix it fails with ArrayIndexOutOfBoundsException and with the patch applied it passes OK.