I am generating a pivot table from a data set (attached) and am running into 2 problems. The first is that the pivotTable data does not seem to save when the workbook is saved. The file alwasy rebuilds the pivot data when opened.
The second problem is more fun. I am adding several columns to the PAGE area. Adding columns 1, 6, and 8 all worked fine. When I added row 7, I get an ArrayIndexOutOfBoundsException. The odd thing is that the exception happens during the call to workbook.save(). Even more odd is that the error does not occur if I limit the source data to 4496 rows, but as soon as I try to use 4497 the file save fails.
I've posted the sample code and attached source.xls file as well. public static final void test(String exportDir) {
String filename = exportDir + "COPY_FPExport.xls";
// Load the Aspose license
// Helper.loadLicense();
// Create the Excel workbook
Workbook wkb = new Workbook();
try {
wkb.open(exportDir + "SourceExport.xls");
} catch (IOException e) {
System.out.println("Error opening file");
}
// create a second sheet for the data
wkb.getWorksheets().addSheet("Pivot");
Worksheet pivotSheet = wkb.getSheet(1);
PivotTables pivotTables = pivotSheet.getPivotTables();
// String sourceData = "DataSheet!A3:T4496"; // Works
String sourceData = "DataSheet!A3:T4497"; // Fails
int pivotId = pivotTables.add(sourceData, 1, 0, "myPivot");
PivotTable pivotTable = pivotTables.get(pivotId);
// Add the standard fields
pivotTable.addFieldToArea(PivotFieldType.ROW, 4);
pivotTable.addFieldToArea(PivotFieldType.DATA, 11);
pivotTable.addFieldToArea(PivotFieldType.PAGE, 1);
// PROBLEM 1: setting field 7 causes a ArrayOutOfBoundsException on
// Workbook.save()
// but only if i use more than 4496 rows of source data
pivotTable.addFieldToArea(PivotFieldType.PAGE, 1);
pivotTable.addFieldToArea(PivotFieldType.PAGE, 6);
pivotTable.addFieldToArea(PivotFieldType.PAGE, 7); // causes an error
pivotTable.addFieldToArea(PivotFieldType.PAGE, 8);
// PROBLEM 2: Pivot table data is not being saved
pivotTable.setSaveData(true);
// write workbook
try {
wkb.save(filename);
} catch (Exception e) {
System.out.println("Exception class: " + e.getClass());
Throwable cause = e.getCause();
if (cause != null)
System.out.println("cause: " + cause.getClass());
}
}