SystemOutOfMemory when using ImportDataTable - large amount of data

Aspose.Cells Product Family
1

Hello, we’re trying to export large amount of data to excel by using Aspose.Cells 7.6.0.0 and we get this exception when the application gets to the ImportDataTable line code:


System.OutOfMemoryException: Exception of type ‘System.OutOfMemoryException’ was thrown.
at System.String.ConcatArray(String[] values, Int32 totalLength)
at System.String.Concat(String[] values)
at System.Text.RegularExpressions.Regex…ctor(String pattern, RegexOptions options, TimeSpan matchTimeout, Boolean useCache)
at System.Text.RegularExpressions.Regex.IsMatch(String input, String pattern, RegexOptions options)
at Aspose.Cells.Style.set_Custom(String value)
at Aspose.Cells.Cells.ImportDataTable(DataTable dataTable, Boolean isFieldNameShown, Int32 firstRow, Int32 firstColumn, Int32 rowNumber, Int32 columnNumber, Boolean insertRows, String dateFormatString, Boolean convertStringToNumber)
at Aspose.Cells.Cells.ImportDataTable(DataTable dataTable, Boolean isFieldNameShown, Int32 firstRow, Int32 firstColumn, Int32 rowNumber, Int32 columnNumber, Boolean insertRows, String dateFormatString)

****************************************
This is the code we use:

public virtual void Export(List someObjects, string columns, Stream stream)
{
_logger.InfoFormat(“START – Function: {0}”, MethodBase.GetCurrentMethod());
try
{
Guard.That(() => columns).IsNotNullOrEmpty();
Guard.That(() => someObjects).IsNotNull();

var inColumns = this.GetColumns(columns);

var excel = new Workbook();
var sheet = excel.Worksheets[0];
var cells = sheet.Cells;
var ds = this.CreateDataSet(someObjects, inColumns);
var dt = ds.Tables[0];
//it crushes when we get here
cells.ImportDataTable(dt, true, 0, 0, ds.Tables[0].Rows.Count, ds.Tables[0].Columns.Count, false, “MM/dd/yyyy”);
SetCurrencyStyle(sheet);
sheet.AutoFitColumns();
excel.Save(stream, SaveFormat.Xlsx);
}
catch (Exception ex)
{
_logger.Error(string.Format(“EXCEPTION – Function: {0} – Exception thrown while trying to export – Exception Message: {1}”, MethodBase.GetCurrentMethod(), ex.Message), ex);
throw;
}
finally
{
_logger.InfoFormat(“FINISH – Function: {0}”, MethodBase.GetCurrentMethod());
}
}

we have 12 GB of RAM in our server. Is there anything we’re missing? I’ve tried setting the rowNum to 200000 and it worked, with 400000 it crushed. In our current scenario, we want to export 600000 records.

Please let us know your thoughts.

Thanks in advance
Jesus
2

Hi Jesus,

Thanks for your posting and using Aspose.Cells.

Please download and use the latest version: Aspose.Cells for .NET 8.0.2 as we have optimized it for memory use in workbooks when large dataset is used.

It should fix your issue. Please use the MemorySetting.MemoryPreference option as mentioned in this documentation article.


If your issue still occurs, then please provide us your sample console application and data to replicate this issue with the latest version. We will look into it and update you asap.

3

Hi Shakeel, thanks for you reply. I wanted to let you know that I tried the latest and still doesn’t work. It took more time to try to execute the ImportDataTable method but at the end it didn’t work, same problem related to SystemOutOfMemory exception. My code is what I already posted. You can paste that in a application console, you only have to make sure you handle a datatable with more than 600K records with 16 columns.


Please let me know how it went.

Regards
Jesus
4

Hi Jesus,

Thanks for your posting and using Aspose.Cells.

Could you please try creating workbook in xlsx format like the following and see if it makes any difference.

Please change this line

var excel = new Workbook();

into this

var excel = new Workbook(FileFormatType.Xlsx);

By default, workbook is created in XLS format, which supports only 65K record. As you need 600K records, so you should create the workbook in XLSX format as shown above.

Let us know your feedback.

5

Thanks for your reply but still, same problem.

6

Hi Jesus,

Thanks for your posting and using Aspose.Cells.

Kindly use cells.MemorySetting = MemorySetting.MemoryPreference;

We have tested this issue with the following code and it successfully imported 600K rows with 16 columns datatable into the worksheet. I have also attached the output xlsx file which is about 60MB for your reference.

Please see the red highlighted code. The code first creates a datatable with 600K rows data and then import it inside the worksheet.

It took less than 2.5 minutes to create the output xlsx file. We tested it on Windows 7 - 64 bit with 8 GB RAM.

C#


int COLUMNS_COUNT = 16;

int ROWS_COUNT = 600000;


Stopwatch sw = new Stopwatch();

sw.Start();


//Create a datatable

DataTable dt = new DataTable();


for (int i = 0; i < COLUMNS_COUNT; i++)

{

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

}


for (int i = 0; i < ROWS_COUNT; i++)

{

DataRow dr = dt.NewRow();


for (int j = 0; j < COLUMNS_COUNT; j++)

{

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

}


dt.Rows.Add(dr);


}


var excel = new Workbook(FileFormatType.Xlsx);

var sheet = excel.Worksheets[0];

var cells = sheet.Cells;


cells.MemorySetting = MemorySetting.MemoryPreference;


//it works fine now

cells.ImportDataTable(dt, true, 0, 0, dt.Rows.Count, dt.Columns.Count, false, “MM/dd/yyyy”);


sheet.AutoFitColumns();

excel.Save(“output.xlsx”, SaveFormat.Xlsx);


sw.Stop();


Debug.WriteLine(“Elapseeeeeeeeeeeeeeeeeeeeed Timeeeeeeeeeeeeeeeee -” + sw.ElapsedMilliseconds);



7

thanks for the reply. It worked, we were able to export 600K records, but when we started to test even more data (between 700K and 800K), we couldn’t, the outOFMemory issue came up again. Would you be so kind to try the code with those amounts and even try to do it up to the top of records accepted by excel 2010 (1048576 rows).


Thanks
Best regards

8

Hi Jesus,

Thanks for your posting and using Aspose.Cells.

Please make sure you are using the latest version: Aspose.Cells for .NET 8.0.2 it works fine as we have tested it with above given code.

We were able to generate 1048576 rows with 16 columns data successfully with the latest version without encountering any SystemOutOfMemory exception.

I have attached the output xlsx file for your reference.