Aspose.Cells Memory Usage

Aspose.Cells Product Family
1

Laurence,

I’m wondering why Aspose.Cells is eating so much memory when filling an Excel template (using 4.0.2.0). I’m only calling InsertRows() once which made the program 100% faster, however the memory usage is peaking at 150mb for 10,000 rows of data. Here’s the code that fills in the template:

’ Fill the template with data.
For Each pb In malPatternBlocks
xmlDataRows = xmlData.SelectNodes("//" & pb.DataLayerID)
For I = 0 To xmlDataRows.Count - 1 Step 1
For Each pbc In pb.PatternBlockCells
exlCell = mexlTemplate.Worksheets(pb.Worksheet).Cells(pb.sGetExcelCell(pbc))
If pbc.Value <> “” Then
Call subWriteCellValue(exlCell, sGetCellValue(pbc.Value, xmlDataRows(I)), pbc.AdjustRowHeight)
End If
exlCell = Nothing
Next
Call pb.subOffsetBlockRange(nOffset)
Next

’ Reset the offset for the next sibling pattern block
nOffset = 0

Next

And here’s the code for subWriteCellValue():

’ If auto-adjust row height has been enabled, we need to set the text-wrapped style for this cell.
If isTextWrapped Then exlCell.Style.IsTextWrapped = True

’ Determine if the value being inserted is a date.
Dim bIsDate As Boolean = False
If sValue.Length >= 10 Then
If sValue.Chars(4) = “-” Then
If IsDate(sValue) Then bIsDate = True
End If
End If

’ Write the value to the spreadsheet.
If bIsDate Then
exlCell.PutValue(CType(sValue, Date), True)
Else
exlCell.PutValue(sValue, True)
End If

exlCell = Nothing

Is there anything I’m missing to reduce the memory overhead? If I fill 10,000 rows it will hover around 50mb RAM usage until the very end, and then it will spike up to 150mb.

Thanks,
Natan

2

Hi Natan,

Please try this attached version.

3

Laurence,

No change after applying that hotfix. The memory usage is still spiking towards the very end.

Natan

4

Hi Natan,

I think it takes same memory wheter you call InsertRows method once or InsertRow multiple times.

This problem may be caused by that you access each cell's formatting from cell.Style property. A style object is created when you call cell.Style get property.

Please try to comment out all code to access cell.Style first to verify if it decrease memory usage. If yes, you can set cell.Style to null after you retrieve formattings from it.

5

Laurence,

I’ve commented out the one reference to Cell.Style, but that line was never being executed in my sample tests to begin with. Also, the sample code I posted doesn’t make any calls to InsertRows() and the memory still spikes to 120mb. If I put InsertRows() back in, the memory will spike to 150mb.

Thanks,
Natan

6

Before you call Workbook.Save method, how much memory is used?

7

And could you please post your test output file here?

8

Laurence,

I figured that the Save() method might be responsible for the memory spike, but there was no change in memory usage after commenting that line out. The memory spike is happening before it saves.

Natan

9

I’ll post the test output when I’m back in the office.

10

I use the following test code:

Workbook workbook = new Workbook();
Cells cells = workbook.Worksheets[0].Cells;
for(int i = 0; i < 10000; i ++)
{
for(int j = 0; j < 50; j ++)
cells[i,j].PutValue(i * 100 + j);
}

System.Threading.Thread.Sleep(5000);

It uses about 50MB memory size. So you can see that each Cell object consumes about 100 bytes.

With following code:

Workbook workbook = new Workbook();
//workbook.Open("d:\\test\\book3.xls");
Cells cells = workbook.Worksheets[0].Cells;
for(int i = 0; i < 10000; i ++)
{
for(int j = 0; j < 50; j ++)
{
cells[i,j].PutValue(i * 100 + j);
Style style = cells[i, j].Style;
}
}

System.Threading.Thread.Sleep(5000);

It uses about 118MB memory size. So you can see that each Style object also consumes about 100 bytes.

I think some optimization can be done, however I don't think it can improve much for memory usage.

Following code takes about 18MB.

ArrayList ar = new ArrayList();

for(int i = 0; i < 10000; i ++)
{
for(int j = 0; j < 50; j ++)
{
Class3 c3 = new Class3(); // Class3 is a dummy class without anything.
ar.Add(c3);
}
}

System.Threading.Thread.Sleep(5000);

So actually each Cell object or Style object takes about 60 bytes. For such complex objects, I think 60 bytes is not a very large sum.

11

Laurence,

Thanks for your help. I’ve confirmed that most of our memory problems were related to Style objects getting created. I’ve fixed this. We’re using 4.0.0.3, but we’ll upgrade the DLL if the hotfix you gave me earlier in this thread optimizes something. Should we continue using 4.0.0.3 or upgrade to 4.0.2.1?

Thanks,
Natan

12

Hi Natan,

It's better to upgrade to v4.0.2 which can be downloaded at www.aspose.com/downloads.

I changed something in InsertRows method and built v4.0.2.1 for a test. Now I roll it back. So v4.0.2 is better.