System.OutOfMemoryException thrown for large Excel files

Aspose.Cells Product Family
1

We are evaluating Aspose.Excel to generate large files (say 100,000 rows). I get a dataset from the database and then iterate through the rows and columns and write out data using Cell.PutValue. I also modify Style of some cells to change the background color within the same loop. The problem is that this process consumes so much memory that the server runs out of it. The first 10,000 records are fine, but then things tend to deteriorate fast. I tried this logic in both windows and web scenarios with the same result. Originally, I was running a version 2.5 of Aspose.Excel, but then upgraded to the latest, 2.7.5. This upgrade helped a little in terms of memory utilization, but the problem is still there. The actual Excel file that we are writing out would take about 25-30MB on disk. Memory utilized by this process, however, would be in the neighborhood of 700MB and more. Do you have a comment or suggestion on how to remedy the situation?

Thanks.

2

Could you tell me how many cells’ background color are changed at run time?

If you use the following code to set a cell style:

cell.Style.ForegroundColor = Color.Blue;

it did consume memory much if you set many many cells’ style because each cell will create a new Style object.

You can try this to minimize memory consuming:

1. If you want to set a whole row or a whole column backgound color, you can try this:

Row row = cells.Rows[1];
row.Style.ForegroundColor = Color.Blue;

Column column = cells.Columns[2];

column.Style.ForegroundColor = Color.Blue;

2. If there are many cells with same style setting, you can try this:

Style style = excel.Styles[excel.Styles.Add()];
style.ForegroundColor = Color.Blue;

for(int i = 0; i < 10; i ++)
{
for(int j = 0; j < 10; j ++)
cells[i,j].Style = style;
}

3
I am lead on the same project as the original poster, so I will attempt to answer your questions.
Our usage of style was the following until this past Friday:
[1] Alter the 35 header cells with the following code...
int styleIndex = excel.Styles.Add();
Style style = excel.Styles[styleIndex];
style.ForegroundColor = System.Drawing.Color.Navy;
style.Font.Color = System.Drawing.Color.White;
style.Font.IsBold = true;
style.Borders.SetStyle(CellBorderType.Thin);
style.Borders.DiagonalStyle = CellBorderType.None;
[We then use Worksheet.Cells[0, c].Style = style for each of the header cells.]
[2] Place a border on all individual data cells with the following code...
cell.Style.Borders.SetStyle(CellBorderType.Thin);
cell.Style.Borders.DiagonalStyle = CellBorderType.None;
[3] If a particular data row meets certain criteria, we gray out one or two cells as follows...
sht.Cells[r, 0].Style.ForegroundColor = System.Drawing.Color.Gray;
Last Friday, we removed the second border code, which did improve performance; but, the performance we are seeing is still poorly lacking. Also, it still prevents us from creating some of the larger spreadsheets we need to.
It sounds like we might want to change item [3] to create a style (Styles.Add) and apply that style when necessary. It may also be possible to regain the borders (item [2]) by using a third style.
We will attempt these changes, but if you see anything else in the above code snippets, or would like us to email you the actual full code, please let us know asap.
Thanks much,
Che' Smith
Project Leader - Software Engineering
UCC Direct Services, a Wolters Kluwer Company
4

Dear Che' Smith,

25-30MB excel file is really too large, especially with many styles. When you use MS Excel to open such a file, you can also find it consuming about 600MB memory.
And could you tell me how many cells in [2] and [3]?

In [2], you can also try this:

int styleIndex = excel.Styles.Add();
Style style = excel.Styles[styleIndex];
style.Borders.SetStyle(CellBorderType.Thin);
style.Borders.DiagonalStyle = CellBorderType.None;
cell.Style = style;

And if possible, please send me your full code, then I can find how to optimize it both in Aspose.Excel and your program.

Thank you very much.

5

Laurence,

The phrase “too large” is entirely subjective. We deal in 25-30+ MB Excel files on a daily basis, both receiving from our customers and sending to them. I just loaded a 34 MB file into Excel (2003), and found that I could not get memory consumption to go over 130 MB - I went to each tab, paged through all rows, etc. We have dealt in a good number of larger files, and have never had a problem. I understand the situation is different when trying to generate these files through a component such as Aspose.Excel, since Excel does various paging techniques; but, perhaps you can give advice on how to accomplish such paging through Aspose.Excel.

The number of cells in [2] and [3] will vary. [2] is 35 cells per row (to match the number of headers I mentioned). [3] will be anywhere from zero to two per row. We will encounter situations where only a few thousand rows are necessary, and others where we’ll have 10’s of thousands (split across multiple worksheets).

Please post the email address we should send our code to and we will send it as soon as possible. In the interim, we will try using a created style object rather than altering the styles on each individual cell.

Thanks much,

Che’ Smith
Project Leader - Software Engineering
UCC Direct Services, a Wolters Kluwer Company
6

Please send you code to excel@aspose.com. And if possible, please zip your generated file and also send it to me. I will check them right now.