System.OutOfMemoryException thrown by Cells.ImportDataTable()

Aspose.Cells Product Family
1

Hello,


We are using Aspose.Cells v8.3.2.4 (.NET) to create an Excel workbook from a SQL result set.

Our code is as follows (VB.NET):

If book Is Nothing Then
book = New Workbook()
book.Worksheets.Clear()
End If
Dim sheet As Worksheet = book.Worksheets.Add(sheetName)
Dim dataTable As DataTable = MDADataLayer.DatabaseHelper.GetDataTable(sql)
sheet.Cells.ImportDataTable(dataTable, True, “A1”)

We are encountering an issue in production that when the SQL result set is very large (580,0000 rows / 36 columns) then the ImportDataTable() line throws a System.OutOfMemoryException with the following stack trace:

StackTrace " at ‰š‹.›‹..›‹.(Int32 , ™›‹ ) at ‰š‹.Œš‹.‚(Int32 , Boolean , Boolean ) at ‰š‹.š‹.GetCell(Int32 , Boolean , Boolean ) at Aspose.Cells.Row.GetCell(Int32 , Boolean , Boolean ) 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) at Aspose.Cells.Cells.ImportDataTable(DataTable dataTable, Boolean isFieldNameShown, Int32 firstRow, Int32 firstColumn, Int32 rowNumber, Int32 columnNumber, Boolean insertRows) at Aspose.Cells.Cells.ImportDataTable(DataTable dataTable, Boolean isFieldNameShown, Int32 firstRow, Int32 firstColumn) at Aspose.Cells.Cells.ImportDataTable(DataTable dataTable, Boolean isFieldNameShown, String startCell) at MDAcommon.AsposeHelper.ExportToExcel(String sql, Workbook book, String sheetName, Boolean formatForPivotTable) in

However I monitored the machine’s memory usage and it didn’t go above 75% when it crashed which doesn’t make sense.

We ran the same export (same result set) using Microsoft Excel interop libraries and it did not encounter this issue.

Please will you kindly advise how we can fix this issue?

Thank you.

2

Hi,


Thanks for providing us some details.

Please try our latest version/fix: Aspose.Cells for .NET v8.6.0 (you may download it from Downloads module).

If you still find any issue with v8.6.0, kindly do create a sample console demo application (runnable), zip it and post us here to reproduce the issue, we will check it soon. Please use dynamic Dataset/ data table in your code with your sample data and do not use any database/ data source or other inter-dependencies, so we could evaluate your issue precisely to consequently figure it out soon.

Thank you.
3
Hi Amjad,

Thank you for your reply.

I could repeat the issue in v8.6.0.

I have created a sample application but I am having trouble attaching it (I upload it but then the upload window goes to a 404 page). Can I instead email it to you, if so, please send me your address?

In my testing it crashes with OutOfMemoryException on line 153 in AsposeHelper.vb:

Dim pivotIndex As Integer = pivotTables.Add(String.Format("={0}!A1:{1}", dataSheetTitle, CellsHelper.CellIndexToName(row, dataSheet.Cells.MaxColumn)), "A1", "PivotTable1")

At the time it crashed the application was consuming about 1.5GB & my system still has a lot of free memory available.

However, as I reported previously, when I run the same code in our actual application (a VB.NET Windows Forms 32 bit application) it falls over earlier in the process on the Workbook.Sheet.Cells.ImportDataTable() call with OutOfMemoryException. This occurs when the application is only consuming about 800mb of memory (& my system still has a lot of free memory available). Do you know why this would be?

I don't understand why the console application proceeds further in the code before crashing than when run in our actual application.

Your help is much appreciated.

Regards,

Richard
4
Hi,

richard-4:

I have created a sample application but I am having trouble attaching it (I upload it but then the upload window goes to a 404 page). Can I instead email it to you, if so, please send me your address?

In my testing it crashes with OutOfMemoryException on line 153 in AsposeHelper.vb:

Dim pivotIndex As Integer = pivotTables.Add(String.Format("={0}!A1:{1}", dataSheetTitle, CellsHelper.CellIndexToName(row, dataSheet.Cells.MaxColumn)), "A1", "PivotTable1")

At the time it crashed the application was consuming about 1.5GB & my system still has a lot of free memory available..............


Thanks for providing further details.

I think you may try using some third party websites like dropbox or others etc. to update your sample project and provide us the link to download it. We will check your issue soon.

Thank you.

5
Amjad Sahi:
Hi,

richard-4:

I have created a sample application but I am having trouble attaching it (I upload it but then the upload window goes to a 404 page). Can I instead email it to you, if so, please send me your address?

In my testing it crashes with OutOfMemoryException on line 153 in AsposeHelper.vb:

Dim pivotIndex As Integer = pivotTables.Add(String.Format("={0}!A1:{1}", dataSheetTitle, CellsHelper.CellIndexToName(row, dataSheet.Cells.MaxColumn)), "A1", "PivotTable1")

At the time it crashed the application was consuming about 1.5GB & my system still has a lot of free memory available..............


Thanks for providing further details.

I think you may try using some third party websites like dropbox or others etc. to update your sample project and provide us the link to download it. We will check your issue soon.

Thank you.


Thank you, please download the sample app here


6

Hi,


Thanks for sharing the sample project.

I have evaluate your scenario/ case using your sample project a bit. Well, since you are creating or importing a huge data set (your underlying XML file you import is more than 900MB in size) and you are creating PivotTable(s) based on your huge data list, so it would surely consume your resources (RAM, CPU etc.). Also if the file is generated, it would be huge one. Generally, using your sample project with your XML file produces the OutOfMemoryException as you mentioned.

I would recommend you two things here to try to cope with your issue.

1) Kindly try our Memory Preference option, see the document for your reference here:

You would need to change your code segment in your project:
i.e.,
If book Is Nothing Then
book = New Workbook()
book.Worksheets.Clear()

End If

to:
If book Is Nothing Then
book = New Workbook(FileFormatType.Xlsx)
book.Worksheets.Clear()
book.Settings.MemorySetting = MemorySetting.MemoryPreference
End If
…

2) Since you are saving to XLSX file format, so when you open the file into Ms Excel, it will auto-refresh and calculate pivot table data accordingly, so you may comment out the lines:
e.g

…
.RefreshDataFlag = True
// .RefreshData()
// .CalculateData()
// .RefreshDataFlag = False

etc.

Hopefully, this helps you a bit.

Thank you.

7

Hi Amjad,

Thank you for your response.

I made the changes in the sample application that you suggested & it produced the final XLSX with pivottable successfully.

  1. However, the second change, commenting out .CalculateData(), does not meet our requirements because we want the XLSX file saved with the pivot data already calculated (if for example you open the XLSX in Excel Viewer then it does not calculate the data itself).

I tested the sample app again with only making the 1st change (MemoryPreference) but it still hits OutOfMemory exception on .CalculateData(). At the time it crashed the process was only consuming 1200mb mb of memory & my machine still had over 1GB memory available.

Why does this happen?

  1. More importantly: I made the 1st change (MemoryPreference) in our production application code but it is still throwing the OutOfMemory exception on the cells.ImportDataTable() line – the same problem reported in my first post - which I don’t understand why.

It throws this exception when the process is only consuming ±800 mb of memory (& my machine still has over 1GB memory available).

How we can overcome this?

We tested creating this exact same report with the exact same large data set in a previous version of our application which uses Microsoft.Interop.Excel with Excel 64bit installed and it created the report successfully.

Our current application version uses Aspose.Cells instead of Microsoft.Interop.Excel and now our client is complaining because that they cannot produce the same report as before.

Your help in this matter is highly appreciated.

PS: We are using Aspose.Cells with a purchased Aspose.Total licence.

Thanks.

Richard

8
Hi,

Thanks for providing further details and your concerns.

As we already reproduced the original issue you mentioned earlier and you do not accept the 2nd workaround to cope with the issue you pointed out, so I have logged a ticket with an id "CELLSNET-44011" for your original issue. Our product team will soon look into it and figure it out (if possible) or may suggest other workaround if there is any.

Once we have any update on your issue, we will let you know here.

richard-4:

2. More importantly: I made the 1st change (MemoryPreference) in our production application code but it is still throwing the OutOfMemory exception on the cells.ImportDataTable() line – the same problem reported in my first post - which I don’t understand why.

It throws this exception when the process is only consuming +-800 mb of memory (& my machine still has over 1GB memory available).

How we can overcome this?

Please try our latest version Aspose.Cells for .NET v8.6.1 (you may download it from Downloads module) as we have published our next official version now. Please make sure that you do not use older version in your production server as it is quite possible your project on the server is still referencing to older Aspose.Cells version.


Thank you.