.Net Memory issues

Aspose.Cells Product Family
1

We are having issues with excessive memory consumption when generating large reports using the Cells product. On occasion we get out of memory exceptions.


I have profiled the allocation using CLRProfiler and in our particular case we found most of the memory was being consumed in the Cell.Formula set property and also in the Workbook.Save() property.

I ran some tests comparing the Cells product to the Spreadsheet Gear product and to the OpenXML SDK using streaming. In each case I wrote out 40 columns and 5000 rows and set the formula of the cell to “=Rand()”. This was intentionally to stress the formula property of the Aspose.Cell object.

The results show Aspose uses 7-12 times the memory of Spreadsheet Gear and far more than that compared to a streaming API.

Do you have suggestions for how to use Aspose in a more memory efficient manner? Is the LightCells API available for the .Net version of the product?

Here are the results:

40/5000
Aspose
229Mb Version 7.0

SpreadSheet Gear
33Mb

OpenXML
7.7mb

time=1.7108471 seconds, Name=ASPOSE.XLSX, Rows=5000, Cols=40 Version 7.0
time=1.0834532 seconds, Name=openxml.XLSX, Rows=5000, Cols=40
time=1.8937046 seconds, Name=SpreadsheetGear.XLSX, Rows=5000, Cols=40

2

Hi,


Could you try our LightCells APIs feature that is also supported in .NET version. I have created a sample console program (full code is pasted below) that generates 1000 * 30 matrix in a worksheet, it also adds formulas to the cells accordingly, you may change/update the code accordingly as well. Also, we recommend you to kindly try our latest version/fix v7.2.0.4: Aspose.Cells for .NET v7.2.0.4

Sample Program

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Aspose.Cells;

namespace LightOoxmlTest
{
class Program
{
static void Main(string[] args)
{
var workbook = new Workbook();
var ooxmlSaveOptions = new OoxmlSaveOptions();
ooxmlSaveOptions.LightCellsDataProvider = new TestDataProvider(workbook);
workbook.Save(“e:\test2\LightCellsBook_test.xlsx”, ooxmlSaveOptions);

}
}

class TestDataProvider : LightCellsDataProvider
{
private int _row = -1;
private int _column = -1;

private Workbook _workbook;
public TestDataProvider(Workbook workbook)
{
_workbook = workbook;
}

#region LightCellsDataProvider Members

public bool IsGatherString()
{
return false;
}

//Just generating a dummy 1000x30 matrix.
public int NextCell()
{
++_column;
if (_column < 30)
return _column;
else
{
_column = -1;
return -1;
}
}
public int NextRow()
{
++_row;
if (_row < 1000)
{
_column = -1;
return _row;
}
else
return -1;
}

public void StartCell(Cell cell)
{
cell.PutValue(_row + _column);
if (_row == 1)
{
}
else
{
cell.Formula = “=Rand() + A2”;
}
}

public void StartRow(Row row)
{
}

public bool StartSheet(int sheetIndex)
{
if (sheetIndex == 0)
{
return true;
}
else
return false;
}

#endregion
}
}


Could you try it and let us know if it makes difference regarding memory consumption and performance.

Thank you.