Memory problems when saving a large formmatted excel file using aspose.cells

Aspose.Cells Product Family
1

Hi,
I am using aspose.Cells version 1.9.5.
While generating a large excel report (arround 10000 rows) I am getting OutOfMemory exception.
Looking at the profiler, I see that a large amount of memory is taken by aspose items:
Aspose.cells.color (12%)
aspose.cells.font(3%)
aspose.cells.Style (3%)
aspose.cells.cR (3%)...
A total of 25% of the entire application memory consumption.
For styling, I am using the method you recommended here in the forums for better memory handling. for example:

Style cellStyle = cell.getStyle();
cellStyle.setBorderLine(BorderType.TOP,style.getBorderLine(BorderType.TOP));

cellStyle.setBorderLine(BorderType.BOTTOM,style.getBorderLine(BorderType.BOTTOM));


cell.setStyle(cellStyle);

Please advise...
Thanks,
Ido Raz

2

Hi,

Thanks for your inquiry.

Well, for creating large formatted excel files, please make sure that you have sufficient memory assigned/allotted to JVM to process the file. Kindly make sure that you have provided and extended the JVM memory appropriately to process/save the file.

Following is the command line you may use on the Windows XP command prompt to extend the JVM memory accordingly:

e.g

java -Xms1024m -Xmx1024m MyTestProgram


Also, since you are using some older version of the product, we recommend you try our latest version v2.1.1: http://www.aspose.com/community/files/72/java-components/aspose.cells-for-java/entry208557.aspx


If you still could not evaluate, kindly provide us more details with sample code, template file(if you have) etc. We will check it soon.


Thank you.


3

Hello, And thank you for the quick response.


I upgraded to the latest version, and the problem persists.

Also, my server is running with 1 GB of memory.

I am less concerned about the OutOfMemory I receive- I can always increase the memory.

what worries me is that the aspose formatting objects takes 250 MB of memory.

I am concerned that there is some miss use.

for instance, I see more than 120 MB of com.aspose.cells.Color- even though I dont create even one instance of it.

Is the memory enhancement I referred to above relevant for Java also, or just .NET?

Thanks,

Ido Raz

4

We think the memory issue is mainly due to the large amount of Style objects used in your program. Currently we are working on improving the performance of using Styles for the cells. We will give a new release with such an enhancement for you in the next month.

Thank you.

5

For the issue of memory problems with Styles, as a workaround, we think you can set the same Style object to multiple cells if some cells can share the same style. In future versions, we will do this for users when applying a Style Object to a Cell (to gather Style objects and share them between multiple cells). Anyways, to assign the same Style object to multiple cells y self will always improve the performance (for memory and/or time cost).

Thank you.

6

Hi,

How do I do this? using applyStyle, or setStyle?
if I apply style with only some of the attributes set to true- will it create a new style?
Thanks,
Ido
7

Hi,

We will get back to you soon.

Thank you.

8

Hi,

Yes, Cell.applyStyle() will create a new style for the cell. To share one style object between multiple cells, you should use setStyle() with the same Style object for multiple cells. The code should be like the following:

Cell cell = cells.getCell("A1");

// get one Style object
Style style = cell.getStyle();

// set style attributes
style.set...;

...

cell.setStyle(style);
cells.getCell("B3").setStyle(style);
cells.getCell("C6").setStyle(style);

...

And if all cells in a range can share the same style, you can use another convenient method:

com.aspose.cells.Cells.setRangeStyle(int startRow, int endRow, int startColumn, int endColumn, Style style)

Thank you.

9

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

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

Thanks!

I will give it a try in the next few weeks.
Ido
11

Hi,

We have solved the performance issue for large formatted excel file in the new version v2.1.2.

Thanks for your time.

12

I am running the same demo program provided by Aspose team

aspose-cells-7.0.3-java\JDK 1.5\aspose-cells-7.0.3- java\samples\consoleDemo\Northwind\src\com\aspose\cells\demos\lightcells\Demo332587.java

Columns : -250

Row: 65 K

Still it is giving Exception in thread "main" java.lang.OutOfMemoryError: Java heap space

13

Hi,


I tested compiling and running the demo you mentioned from the release archive of v7.0.3.0, it works fine. Still you got to have sufficient amount of memory assigned to JMV even you are using LightCells APIs. I did not edit the demo just compiled the source “LightCellsDataProvider332587.java” and “Demo332587.java” source classes and then run the Demo using my command line on the Command prompt:
e.g
D:\Aspose.CellsJava\15-11-2011 Aspose.Cells for Java v7.0.3\JDK 1.5\samples\cons
oleDemo\Northwind\src\com\aspose\cells\demos\lightcells>java -Xms1024m -Xmx1024m
… Demo332587

Rows are printed on the command prompt’s screen and I got about 60MB file generated. It took me some minutes to even finally open the resultant XLSX file into MS Excel 2007 because of huge values list and formattings applied. So, MS Excel will also demand lot of memory and take some time to be opened into it.


Also, I am using v7.0.3.6 (latest fix).

Thank you

14

Hi,

Please try our latest version (v7.0.4) that we released now:

After further analyzing your memory issue, for the demo (Demo332587), there are many merged cells that are created and that will surely cost a certain amount of memory. In the new release, we have optimized memory usage for the empty merged cells. With the new version (Demo332587) will require less amount of memory. By our test, creating xlsx file with 65Kx256 cells only needs about 30-40M memory by the new version (v7.0.4). Even with the old versions, it needed about 100M memory for light cells to create such an xlsx file. Please make sure you have given enough memory for the JVM when execute this demo.

Thank you.

15

I want to save this file as XLS file. Today while opening a XLS file using LightCell, it will prompt 'The file you are tyring to open is in differant format than specified by the file extension. do you want to open it now ?'

How to avoid this ?

16

Hi,


For your information XLS (Excel 2003) file format can only have 65536 rows and 256 columns in a single worksheet, this is the limitation put forth by XLS format (of MS Excel and not by Aspose.Cells for Java).

For creating a big file (e.g having a worksheet with 65K * 240 cells filled with values) that would have a huge process which will demand some memory to be assigned to JVM still. I have created a sample demo project that creates such a huge Excel XLS file, it woks fine, you may get it here. Here is the command line that I simply used.

java -Xms550m -Xmx550m DemoTest


Moreover, I am afraid, currently, opening Huge files (XLS/XLSX) by LightCells APIs is not supported as only saving XLS/XLSX files is supported, we will support it later on

Thank you.

17

I want to save this file as XLS file. Today while opening a XLS file using LightCell, it will prompt 'The file you are tyring to open is in differant format than specified by the file extension. do you want to open it now ?'

How to avoid this ?

18
Hi,

Please check my previous reply in this thread.

For creating a big file (e.g having a worksheet with 65K * 240 cells filled with values) that would have a huge process which will demand some memory to be assigned to JVM still. I have created a sample demo project that creates such a huge Excel XLS file, it woks fine, you may get it here. Here is the command line that I simply used.

java -Xms550m -Xmx550m DemoTest


Moreover, I am afraid, currently, opening Huge files (XLS/XLSX) by LightCells APIs is not supported as only saving XLS/XLSX files is supported, we will support it later on

If you still have any confusion or issue, please give us your sample runnable project to create the desired excel file with LightCells API. We will check your issue soon.