OutOfMemory exception when trying to import datatable >100-000 records

Aspose.Cells Product Family
1

Hello,


We are getting out of memory exception when we try to import a data table with more than 100,000 rows with 20 columns.

It works fine with 100, 000 rows but if we give 110,000 it fails. We are evaluating aspose for our requirement. We have data upto 1 million records with 20 columns. Just want to know whats the limit aspose can support. We are using pivot excel export and also normal excel export.

We are using it in our webapplication(asp.net 4.0 , IIS7.5).
Developer machine configuration (12GB RAM, Win-7 OS, Intel Core -i5)

Below listed code is what we are using to load test aspose.cells.
Please let us know where we are going wrong and also the limit aspose.cells has for exporting.

protected void GenerateExcel(object sender, EventArgs e)
{
const int rowsCount = 100000;
const int columnsCount = 20;

var sw = new Stopwatch();

sw.Start();

var dt = new DataTable();


for (var i = 0; i < columnsCount; i++)
{
dt.Columns.Add().DataType = typeof(string);
}


for (var i = 0; i < rowsCount; i++)
{
var dr = dt.NewRow();
for (var j = 0; j < columnsCount; j++)
{
dr[j] = “Test-” + (j + 1) + “-” + (i + 1);
}
dt.Rows.Add(dr);
}


var excelWorkbook = new Workbook(FileFormatType.Xlsx);
excelWorkbook.Worksheets.Clear();
var dataWorksheet = excelWorkbook.Worksheets.Add(“Data”);

dataWorksheet.Cells.MemorySetting = MemorySetting.MemoryPreference;
dataWorksheet.Cells.ImportDataTable(dt, true, “A3”);
dataWorksheet.AutoFitColumns();

excelWorkbook.Save(HttpContext.Current.Response,
string.Format(CultureInfo.InvariantCulture, “{0}.xlsx”,
“SampleLoadTest”), ContentDisposition.Attachment,
new OoxmlSaveOptions());
sw.Stop();
var time = sw.ElapsedMilliseconds;

elapsedTime.InnerText = time.ToString();
}

Regards,
Anil

2

Hi Anil,

Thanks for your posting and using Aspose.Cells.

There is no limit imposed by Aspose.Cells. It is actually the memory limitation of IIS 7.5, probably IIS servers sets some memory limits for its worker process. You should increase its memory.

I have generated the output xlsx in standalone console application and it threw no exception. It took 298 seconds to generate a file of 1048,000 rows with 20 columns. I have attached the output file for your reference.

C#


const int rowsCount = 1048000;

const int columnsCount = 20;


var sw = new Stopwatch();


sw.Start();


var dt = new DataTable();



for (var i = 0; i < columnsCount; i++)

{

dt.Columns.Add().DataType = typeof(string);

}



for (var i = 0; i < rowsCount; i++)

{

var dr = dt.NewRow();

for (var j = 0; j < columnsCount; j++)

{

dr[j] = “Test-” + (j + 1) + “-” + (i + 1);

}

dt.Rows.Add(dr);

}



var excelWorkbook = new Workbook(FileFormatType.Xlsx);

excelWorkbook.Worksheets.Clear();

var dataWorksheet = excelWorkbook.Worksheets.Add(“Data”);


dataWorksheet.Cells.MemorySetting = MemorySetting.MemoryPreference;

dataWorksheet.Cells.ImportDataTable(dt, true, “A3”);

dataWorksheet.AutoFitColumns();


excelWorkbook.Save(“output.xlsx”);


sw.Stop();

var time = sw.ElapsedMilliseconds;


Debug.WriteLine("Elapsed Time…: " + time);

3

Hello,


Thanks for the information.

Will investigate on what we can do to increase the memory for web application.

Regards,
Anil