Best practices for generating large excel file

Aspose.Cells Product Family
1

Say, I am trying to create a large excel workbook with 20000 rows. I find that the amount of memory it uses on the server is extremely high, most times causing an outofmemory exception.

This is basically how i generate my files. Is there a better way of doing it which won't cause such a delay/memory usage. Thanks.

Aspose.Cells.License license = new Aspose.Cells.License();

license.SetLicense("../Aspose.Total.lic");

Aspose.Cells.Workbook excel = new Aspose.Cells.Workbook();

Aspose.Cells.Worksheet sheet = excel.Worksheets[0];

SqlDataReader sqlDataReader = SqlHelper.ExecuteReader(DB.ConnectionString, CommandType.Text, sql);

sheet.Cells.ImportFromDataReader(sqlDataReader, true, 1, 1, false);

if (includeAutoFilter)

{

sheet.AutoFilter.Range = "A2:Z2";

}

sheet.AutoFitColumn(0);

sheet.AutoFitColumn(1);

sheet.AutoFitColumn(2);

sheet.AutoFitColumn(3);

sheet.AutoFitColumn(4);

sheet.AutoFitColumn(5);

sheet.AutoFitColumn(6);

sheet.AutoFitColumn(7);

sheet.AutoFitColumn(8);

sheet.AutoFitColumn(9);

//}

sheet.FreezePanes("A3", 3, 1);

sheet.Cells["A1"].PutValue(title);

sheet.Cells["A1"].Style.Font.IsBold = true;

excel.Save(title + ".xls", Aspose.Cells.FileFormatType.Excel97, Aspose.Cells.SaveType.OpenInExcel, httpResp);

2

Hi,

I think you may try:

1) Well, the most time consuming and expensive method you are using (several times) is Worksheet.AutoFitColumn(), so try to be careful about it.

2) Always use Cell.GetStyle and Cell.SetStyle() methods instead of using Cells.Style attribute, so, if you use Cell.Style property several times, it might consume resources. See the document for your reference:
http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/dealing-with-font-settings.html

We recommend you try our latest version/fix v5.1.3.1 (attached), we have enhanced the performance to some extent in the recent versions too.

Also, for saving the workbooks, please see the document, how you may save the excel files.
http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/saving-files.html


Thank you.

3

I have tried all of the suggestions (including getting onto the latest version).

Basically, I ahve a file that could have up to the 65k rows allowed by excel with about 5 columns.

I noticed the file that it created was 32MB. However, what concerned me the most was that the server memory spiked 800mb while it was creating the file (I tried both to the response object and file creation -- no difference).

Why does it use up so much memory?

4

Hi Mike,

There are many reasons for taking a large amount of memory to be consumed. A cell in a sheet contains different types of data in it like it has its value, it has some formattings, it has some formulas etc. If the value is string then it takes different amount of memory, if it has an integer then it takes different amount of memory. In this way the size of memory consumed will increase.

Thanks,