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());
}
}
This did fix the PAGE field problem in my small test data set, but it still failed in the full-scale file. It fails in the same spot - during the Workbook.save() call. I've attached a zip with the source xls file, the java code to test the pivot functions, and the errors from Excel (for the second problem).
The second problem I have regards teh Data fields. When I add a certain columns to the DATA area, it causes errors when opening the file in Excel. Excel says it encountered errors but fixed them. I also get this error when converting multiple Data fields to Column format using the following code:
And lastly, if the data is not saved in the Pivot, what is the setSaveData() function used for? The API says it "Sets whether data for the PivotTable report is saved with the workbook." Is this planned for a future release?
That fix worked - mostly. The template formatting is all good, but there is still one issue with the pivot tables. The PAGE fields now work great - no problems there.
The DATA fields are still giving me issues. If I only add the 3 good Data columns and I use the getDataField() function (problem 3) it works great. If I add the fourth bad DATA column (problem 1) along with #3 it still works great.
The problem is if I try to use the fourth bad column WITHOUT using getData Field(). If I only work with problem #1, I still get the same error. You can use the same files from above. Uncomment the fourth Data field, but leave problem 3 commented out.
Note: in this fix, Workbook.getNumerOfSheets() and Worksheets.getNumerOfSheets() has bean renamed as Workbook.getNumberOfSheets() and Worksheets.getNumberOfSheets() . And if you want to get number of sheets, it's better to use Worksheets.size() method instead.
The fix worked for teh DATA fields, but now the PAGE fields are breakign with a similar (but larger) data set. I am getting the same ArrayIndexOutOfBoundsException as in the original data. Let me know if you need me to post a larger data set.
That worked - the dynamic pivots are all working fine now.
I had a question about a previous post in this thread.
"And lastly, if the data is not saved in the Pivot, what is the setSaveData() function used for? The API says it 'Sets whether data for the PivotTable report is saved with the workbook.' Is this planned for a future release?"
You said that this was used for MS Excel, but could you clarify that for me? Does this function tell the pivot to save it's data? Is there a way to keep the data in the pivot when the workbook is saved?
This function is used to force MS Excel to save the pivot data to the pivot table range when saving the created file with MS Excel.If you want to keep the data and not refresh the pivot table when opening the file next time , you should open the file with MS Excel , check save data with table layout and remove Refresh on open in pivot tableoptions settings, then save the file.
Now ,Aspose.Cells for Java does not support to save data to the pivot table range and it forces Ms Excel to refresh the pivot table when opening the file.So this function is only used for MS Excel now.
We will look into the feature how to save data to the pivot table range.