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:
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:
We think the memory issue is mainly due to the large amount of Style objects
used in the 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 next month.<o:p></o:p>
For the issue of memory problems with Styles, as a workaround, we think you can set same Style object to multiple cells if some cells can
share the same style. In the 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 same Style object to multiple
cells by you will always improve the performance (for memory and / or time cost).<o:p></o:p>
Yes,
Cell.applyStyle() will create a new style for the cell. To share one style
object between multiple cells, you should use setStyle() with same Style
object for multiple cells. The code should be like 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)
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
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.
After further analyzing your memory issue, for the demo Demo332587, there are many merged
cells that are created and that will surely cost 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.
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 ?'
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
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 ?'
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.