java.lang.OutOfMemoryError trying to create a large sheet

Aspose.Cells Product Family
1

And are there ways to reduce the memory needed?

Our application is using a table control to display a large array of data items. Each column could contain dates, integers, reals, or strings. In one of our tests the table contained just over 61000 rows.

We take this data and put it into a 2 dimensional object array which in turn we move into a sheet. I can create the sheet without the data inserted into the cells using cell.setValue. Is there a more efficient way to create a sheet this large? (I found some suggestions in the forum related to adding cells one row at a time and we are doing that.)

I can only seem to process about 25500 rows with 5 columsn of data before the OutOfMemory occurs.

FYI, I just downloaded Cells Java 1.9.5 and tried that and it didn't help.

Or is there some way to know that we will hit an OutOfMemory error creating a large spreadsheet before it happens instead of getting the java.lang.OutOfMemoryError which we can't catch?

Thanks, Steve

2

Hi Steve,

For importing data from object array, I think you can use Cells.importArray(...) or Cells.importObjectArray(...) instead of using cell.setValue(...) one by one. And we will look into the performance of importArray(...) and importObjectArray(...) and try to enhance it if it is possible.

Creating a huge Excel file with huge data set does cause more memory cost. I think you have to specify more available memory for your java application, you can use the JVM option -Xmx, such as following:

java -Xmx512m -classpath... YourMainApp

I am afraid there is no good way to predict exactly that the application will exceed memory limit. If you have to do so, I think you can check it approximately like following:

private static Runtime rt = Runtime.getRuntime();
private static final long MAX_MEMORY = rt.maxMemory();
private static final long APP_MEM_INCREASE = 10240; //depends on specific application

private void test()
{
Workbook wb = ...;
...
for(...)
{
cell.setValue(...);
if(rt.freeMemory() < APP_MEM_INCREASE || MAX_MEMORY-rt.totalMemory() < APP_MEM_INCREASE)
{ //will cause OutOfMemoryError in all probability
...;
return;
}
}
}

If you just want to catch the OutOfMemoryError to avoid application crash, I think you can use code like following:

try
{
cells.importObjectArray(...);
}
catch(OutOfMemoryError err)
{
...; //handle the OutOfMemoryError
return;
}

3

Thanks Johnson,

I was able to trap the OutOfMemory error and give the user information on what happened.

I tried the the importObjectArray and it still failed with an out of memory error. As long as I can trap the OutOfMemory condition then I can give a better message than a black box. :)

Steve