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()); } }