Exhausting Memory Consumption with Aspose (v1.8.7.1 )

Aspose.Cells Product Family
1

Dear Aspose Support,
I am trying to create an excel file with 60 columns and 5000 rows and it is forcing the aspnet_wp to recycle at 272 MB memory.

I understood the 272 MB (60% of my available memory) limits. So I increased the memoryLimits to 90% in the machine.config file. Aspnet_wp did not recycle this time. I am lucky!!! Anyway, I saved the excel file and it is only 2.6 MB.

Now the question–My application often requires to create an excel file with upto 65,000 rows in it.
Can you tell me where Aspose stand on this?
How much memeory the machine will require to support that call?
Who is at fault for the bad memoery management–Aspose or Microsoft? I don’t like the blame game without the proof.

I will take your answers. Thanks in advance.
Prodip

2

Hi

Do you use dataset for populating Excel trough aspose.Excel ?

3

Obviously, we use the DataSet to populate the Excel through Aspose.Excel but the DataSet is not saved in Chache or session.

4

Well, I think your problem is in using the dataset

Use DataReader instead.
DataSet have a high cost measured in Memory and CPU cycles.

Data Readers are forward-only, read-only streams of data.
If your purpose is to display data then a data reader is best.

DataSets are in memory pictures of data that enable relationships, editing, etc.
These are much larger objects.

If you need to keep the data around for updating, etc. the DataSet is good.
In the Internet world you will likely find that a DataReader is the best solution 90%
of the time.

5

Thanks for your help, nima. And I agree with you.

6

Thanks nima and Laurence for your comments. However, you have not answered my question. To get a dataset with 50K records only takes less than 30 seconds. The memory is consumed by Aspose while writing an excel file.

Laurence: Is anyone using Aspose in the same boat as I am? If so, what is your expert opinion?

7

I create an excel file with 60 columns and 5000 rows and the aspnet_wp consume about 70 MB memory. But if repeatly run the program, I found the memory consumption was aggregated. So please set Excel object to null after call the save method, that can reduce the reference count and make memory recycle more faster.

excel.Save(…);
excel = null;

I will also try to optimize my code to minimize memory consumption.

8

Laurence, Will this code be better solution ?

excel.Save(…);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel) ;
excel = null;

9

The problem is in the Aspose.Excel. The DataSet is working superfast when looping through the records. The memory is chewed by Cell Formatting. If I don’t do the formatting, the memory consumption is about 72MB. Laurence you are right about that.

Now the question is–what’s the best way to format Columns/Cells in Aspose?

Thanks,
Prodip

10

Hi nima,

Thanks for your advice. But I think only set excel to null is enough.

Hi Prodip,

Could you post your setting format code here? Thus can greatly help me to investigate this problem. Thanks for your help.

If you set same format to a large numbers of cells, we recommend you to try this way.

//C#
Styles styles = excel.Styles;
int styleIndex = styles.Add();
Style style = excel.Styles[styleIndex];

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

'VB
Dim styles as Styles = excel.Styles
Dim styleIndex as Integer = styles.Add()
Dim style as Style = excel.Styles(styleIndex)

Dim i As Integer
For i = 0 To 99
Dim j As Integer
For j = 0 To 99
cells(i, j).Style = style
Next
Next

11

Hi Laurence,
I was able to contain the memory consuption by using technique you have described on 05-05-2004 12:04 AM but Cell Formatting are NOT preserved. If I don’t use the style at all the memoery consumption is excellent. Unfortunately, I must use the style as part of the app requiremnts.

This is what my code (not the actual code) trying to perform:-

Styles styles = excel.Styles;
int styleIndex = styles.Add();


for(int i = 0; i < 5000; i ++)
{
Style style = excel.Styles[styleIndex];
style.Font.Name=“Tahoma”;
style.Font.Size=10;
style.HorizontalAlignment=TextAlignmentType.Right;
style.Number=0;
for(int j = 0; j < 100; j ++)
{
if(sSomeString == sValueToCheck)
{
style.Custom=“#0.00000_);Red”;
style.Number=40;
}
else
{
style.Number=0;
style.HorizontalAlignment=TextAlignmentType.Left;
}

cells[i, j].Style = style;
}
}


Cell formatting is preserved if I directly assign the value to the Cell:
i.e. Cell.Style.Number=40;
Cell.Style.Custom=“#0.00000_);Red”;

Am I doing something wrong? What is the solution to this problem?

Thanks,
Prodip

12

Hi Prodip,

You can try this:


Styles styles = excel.Styles;
int styleIndex1 = styles.Add();
int styleIndex2 = styles.Add();

Style style1 = excel.Styles[styleIndex1];
style1.Font.Name=“Tahoma”;
style1.Font.Size=10;
style1.HorizontalAlignment=TextAlignmentType.Right;
style1.Custom=“#0.00000_);Red”;
style1.Number=40;

Style style2 = excel.Styles[styleIndex2];
style2.Font.Name=“Tahoma”;
style2.Font.Size=10;
style.Number=0;
style.HorizontalAlignment=TextAlignmentType.Left;


for(int i = 0; i < 5000; i ++)
{
for(int j = 0; j < 100; j ++)
{
if(sSomeString == sValueToCheck)
{
cells[i, j].Style = style1;
}
else
{
cells[i, j].Style = style2;
}


}
}

13

Hi Laurence,
Thank you much. It worked. I was able to contain the memory consumption by–

Declaring differnt Styles before entering into the loops
Setting the appropriate Style for different pass(if, else, etc)
Setting the Excel and styles(local variables) objects to null at the end because it’s a good practice.

To test the scenario I opened 5 IE session to run the process and the total memory in Windows Task Mgr was less than 93MB.

Again, thanks for your help.

Prodip