OutOfMemoryError when writing large amount of data to excel

Hi,

I want to generate a xlsx file with 600,000 rows or above, 20 columns in one worksheet. But after the program ran about 20 minutes, the OutOfMemoryError was throwed. The program is attached.

I am using Aspose.Cells 7.0.1 for Java. My CPU is 2.20GHz, and JVM argument setting is "-Xmx1280M".

How much memory does it need? Are there any mistakes in my program? Or is there any optimization to make my program generate the Excel file successfully under the limitation of memory of 1280 MB? Or is there a disk swap mechanism (dump memory to disk and then load it back separately) can be used?

Looking forward for your answer, thanks.

Hi,

Thanks for your reporting.

Please perform the test on latest version:
Aspose.Cells for Java v7.0.1.3
and provide us your feedback.

Hi,

I have performed the test on Aspose.Cells for Java v7.0.1.3. The result is the same.

Looking forward for your answer, thanks.

Hi,

Thanks for testing it with latest version. Please provide us also your runtime environment (e.g Processor Speed, Operating System, Memory etc).

Please provide us your results in a tabular form.

We have also logged this issue in our database. We will investigate it and fix it asap.

This issue has been logged as CELLSJAVA-30994.

Hi,

For your application, we recommend you to use LightCells APIs to save the xlsx file because your cells data set is very large.

Attached is a demo application to create the same xlsx file with your original application by LightCells APIs. It needs less than 1G memory to create the desired xlsx file.

Hi,

My runtime environment is attached.

The desired xlsx file was generated successfully by LightCells APIs on my machine. Thank you very much.

But when I want to generate more than 1 sheet by LightCells APIs, a NullPointerException is thrown.

The codes are attached. Is there something wrong with my codes?

Looking forward for your answer, thanks.

Hi,

Thanks for your feedback and files. We have added your comment in our database. Once we will get any update, we will let you know.

Ok, thank you.

Hi,

Please download: Aspose.Cells for Java v7.0.1.6

We have fixed the issue of NullPointerException for LightCells. Please try the new fix. And another notable thing in your program is that the “sheetCount” variable is useless in fact.

LightCellsDataProvider.startSheet(int sheetIndex) will be invoked for every existing worksheet only. In your program you created one new Workbook and then save it, in such situation you only have one worksheet in the workbook and LightCellsDataProvider.startSheet(int sheetIndex) will be called only once with sheet index 0 even if you set the “sheetCount” as more than 1. To create sheet data for multiple worksheets, you should create those worksheets before saving the workbook, code like following:
Workbook wb = new Workbook();
wb.getWorksheets().add(…);
wb.getWorksheets().add(…);

wb.save(…);

Hi,

I have modified my program as what you told me above to make the "sheetCount" variable useful, and tested it by using Aspose.Cells for Java v7.0.1.6. The result xlsx file is ok.

Thank you very much.

The issues you have found earlier (filed as 30994) have been fixed in this update.


This message was posted using Notification2Forum from Downloads module by aspose.notifier.