Saving large Excel document that has pivot tables throws ArrayIndexOutOfBoundsException in .NET

Aspose.Cells Product Family
1

i've created a large excel document from scratch in aspose. it contains several pivot tables and several sheets. when saving the document i get the following ArrayIndexOutOfBoundsException:

java.lang.ArrayIndexOutOfBoundsException: 16514
at com.aspose.cells.ba.a(Unknown Source)
at com.aspose.cells.ba.b(Unknown Source)
at com.aspose.cells.PivotTable.a(Unknown Source)
at com.aspose.cells.PivotTable.a(Unknown Source)
at com.aspose.cells.PivotTable.b(Unknown Source)
at com.aspose.cells.PivotTable.a(Unknown Source)
at com.aspose.cells.PivotTables.a(Unknown Source)
at com.aspose.cells.cK.b(Unknown Source)
at com.aspose.cells.cK.w(Unknown Source)
at com.aspose.cells.cK.a(Unknown Source)
at com.aspose.cells.Workbook.save(Unknown Source)
at com.aspose.cells.Workbook.save(Unknown Source)
at com.clicktravel.mi.utilities.WorkbookRunner.run(WorkbookRunner.java:71)

Any idea what is wrong ?

Simon

ps. FYI: your forums are really frustrating for mac users! trying topost a new topic in safari and you get "bad request". swithc to firefox and it complains about a javascript error when you submit the form. I've had to find myself an old windows box and ressurect it just to get in touch with you guys!

2

Hi,

Which version of Aspose.Cells for Java you are using? Could you try the attached version (2.0.0.27) if it works fine.

If you still find the issue, kindly create a console demo application to reproduce the issue, we will check it soon.

For your other issue regarding "posting new posts in forums on mac", I will inform to the concerned authorities and they will be approaching you soon.

Thank you.

3

we have tried 2.0.0.27 but it has the same bug.



i’ve attached a sample raw data file. if you run the following code against it you will see that it throws when trying to save the workbook.



however, if you cut the sample data down in size (reduce to say, 5,000 rows) it works fine.



please can you have a look into this ?



thanks, simon









public void runTest() throws Exception {

Workbook workbook = new Workbook();



try {

workbook.open("/SampleData.xls");

} catch (IOException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}



// Add an sheet.

Worksheet sheet = workbook.getWorksheets().addSheet(“Test”);

PivotTables pivotTables = sheet.getPivotTables();



// Create a pivot

int pti3 = pivotTables.add(workbook.getWorksheets().getRangeByName(“RawDataRange”).getRefersTo(), 5, 8,

“TestTest”);



PivotTable pt3 = sheet.getPivotTables().get(pti3);

pt3.setManualUpdate(false);

pt3.setPreserveFormatting(true);



int contactFieldIndex3 = pt3.addFieldToArea(PivotFieldType.ROW, 1);

PivotField contactField3 = pt3.getRowFields().get(contactFieldIndex3);

contactField3.setDisplayName(“Booker”);



int typeFieldIndex3 = pt3.addFieldToArea(PivotFieldType.COLUMN, 2);

PivotField typeField3 = pt3.getColumnFields().get(typeFieldIndex3);

typeField3.setDisplayName(“Type”);



int voucherIDFieldIndex3 = pt3.addFieldToArea(PivotFieldType.DATA, 0);

PivotField voucherIDField3 = pt3.getDataFields().get(voucherIDFieldIndex3);

voucherIDField3.setNumber(1);

voucherIDField3.setFunction(ConsolidationFunction.COUNT);

voucherIDField3.setDisplayName(“Number of Transactions”);



int totalFieldIndex3 = pt3.addFieldToArea(PivotFieldType.DATA, 3);

PivotField totalField3 = pt3.getDataFields().get(totalFieldIndex3);

totalField3.setNumber(2);

totalField3.setFunction(ConsolidationFunction.SUM);

totalField3.setDisplayName(“Gross Service Cost”);



int netFieldIndex3 = pt3.addFieldToArea(PivotFieldType.DATA, 4);

PivotField netField3 = pt3.getDataFields().get(netFieldIndex3);

netField3.setNumber(2);

netField3.setFunction(ConsolidationFunction.SUM);

netField3.setDisplayName(“Net Service Cost”);



int rateFieldIndex3 = pt3.addFieldToArea(PivotFieldType.DATA, 5);

PivotField rateField3 = pt3.getDataFields().get(rateFieldIndex3);

rateField3.setNumber(2);

rateField3.setFunction(ConsolidationFunction.AVERAGE);

rateField3.setDisplayName(“Average Gross Rate”);



contactField3.setAutoSort(true);

contactField3.setAutoSortField(1);

contactField3.setAscendSort(true);



pt3.setAutoFormat(true);

pt3.setAutoFormatType(PivotTableAutoFormatType.CLASSIC);

pt3.setRowGrand(true);

pt3.setColumnGrand(true);

pt3.setPageFieldOrder(OrderType.DOWN_THEN_OVER);



try {

String filename = “/TestResult.xls”;

workbook.save(filename);

} catch (IOException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

4

Hi Simon,

Thank you for sharing your template file and sample code. After an initial test, we have found the issue, we will fix it soon.

Thank You & Best Regards,

5

That fixed it thanks.



I must say it’s been a pleasure to work with Aspose - the product is great but your support is even better. You guys are so fast at reacting to issues like this - thanks!



Simon