Free Support Forum - aspose.com

(Java) ArrayIndexOutOfBoundsException when saving large spreadsheet with pivot table

I’m currently evaluating Apose.Cells for a large web application. The critical feature for us is the ability to generate a Pivot Table. I’m able to build a basic pivot table (with a small range of fields), but it seems whenever I try to expand to a larger number of columns or rows I get an ArrayIndexOutOfBoundsException.

The goal is to have a pivot for a data sheet with around 50 columns and 20000 rows. Here is some code that generates the exception:

import com.aspose.cells.Cells;
import com.aspose.cells.CellsHelper;
import com.aspose.cells.PivotTable;
import com.aspose.cells.PivotTables;
import com.aspose.cells.Workbook;
import com.aspose.cells.Worksheet;

public class Test
{
public static void main(String[] args)
{
long startTime = System.currentTimeMillis();

// workbook properties
int columnCount=47;
int rowCount=17000;

// create workbook
String excelFilePath = “/path/to/file”;

Workbook workbook = new Workbook();

// write first sheet
Worksheet dataSheet = workbook.createSheet();
Cells dataCells = dataSheet.getCells();

// write headers
for( int colIndex=0; colIndex<columnCount; colIndex++ )
{
dataCells.getCell(0, colIndex).setValue(“Header”+colIndex);
}

// write values
for( int rowIndex=1; rowIndex<rowCount; rowIndex++)
{
for( int idx=0; idx<columnCount; idx++ )
{
dataCells.getCell(rowIndex, idx).setValue(“COLUMNVALUE”+rowIndex);
}
}

// write pivot table
Worksheet pivotSheet = workbook.createSheet();
PivotTables pivotTables = pivotSheet.getPivotTables();

String endColumnName = CellsHelper.convertColumnIndexToName(columnCount-1);
String sourceData = “Sheet2!A1:AU17000”;

System.out.println("endColumnName: " + endColumnName);
System.out.println("rowCount: " + rowCount);

int pivotTableIndex = pivotTables.add(sourceData, 0, 0, “pivotTest1”);
PivotTable pivotTable = pivotTables.get(pivotTableIndex);

// write workbook
try
{
workbook.save(excelFilePath);
}
catch(Exception e)
{
e.printStackTrace(System.out);

Throwable cause = e.getCause();
if( cause != null )
System.out.println("cause: " + cause.getClass());
}

// timer
long stopTime = System.currentTimeMillis();
long elapsedTime = stopTime - startTime;

System.out.println("generated in " + elapsedTime + “ms”);
}
}



The first thing I looked at was memory. I expanded the jvm memory up to Xmx/s1024M with no effect. I’m hoping this is just something silly that I’m missing in my code, and not a problem with the library itself (or maybe a limitation of the evaluation version). Thank you!

Hi,

Please try this fix.

Thank you Warren. That works great. We’re very impressed with the performance so far.