Bug 54436

Summary: Broken matadata for GETPIVOTDATA function
Product: POI Reporter: Daniil Lopatin <solid.danil>
Component: POI OverallAssignee: POI Developers List <dev>
Status: RESOLVED FIXED    
Severity: major CC: greeson1, lantushenkoao, solid.danil
Priority: P2    
Version: 3.9-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: All   
Attachments: Example of failing poi if GETPIVOTDATA function exists
Getpivotdata function bug patch
Getpivotdata function bug patch txt format

Description Daniil Lopatin 2013-01-16 14:31:01 UTC
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.
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 @@
 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 ...
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:

 workbook.getCreationHelper().createFormulaEvaluator().evaluateAll();

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

Regards,
Yegor