Workbook constructor generate System.OutOfMemoryException on opening large excel file

mostafizaz · December 5, 2012, 5:25am

I have an excel file with 100000 records where each record contains142 columns. I need to append another 10000000 records in the existing excel file.

Here is the code block -
var loadOptions = new LoadOptions(LoadFormat.Xlsx);
var workbook = new Workbook(filePath, loadOptions);

var saveOptions = new Aspose.Cells.XlsSaveOptions(SaveFormat.Xlsx);
saveOptions.CachedFileFolder = @“C:\Temp\ReportTemp”;

int dataRowIndex = headerRows + 4 + alreadyExportedRows;
var worksheet = workbook.Worksheets.GetSheetByCodeName(“Data”);
WriteReportData(worksheet, realExport, dataRowIndex, 0, formattingSettings, 10);
workbook.Save(filePath, saveOptions);

When I’m trying to open the existing excel file with Workbook(filePath, loadOptions) code, it throws System.OutOfMemoryException.

So, what is the maximum number of record that aspose.cells library can manipulate?
Is there any workaround to this problem?

amjad.sahi · December 5, 2012, 6:04am

Hi,

Aspose.Cells can handle (read/write) large Excel files, there is no limitation regarding the records filled in a file, the limitation depends upon the file format and put forth by MS Excel. For example for XLS, you can only have 65, 536 records in a single sheet, for XLSX file format, you may have millions of records in a single sheet (you may check the limits of MS Excel, Aspose.Cells works the same as MS Excel).

Since your file contains 100000 records in a single worksheet, so opening file’s process would surely require more memory and it does take some time to be completed. So, make sure that you got sufficient amount of memory for the task. Also, the generated Excel file (e.g XLSX) would be huge in size as you are inserting more and more records. Aspose.Cells for .NET is capable of importing exporting huge list of data and can perform the task if you got sufficient resources. Also, if you have 64 bit machine with sufficient amount of RAM, you can do it (reading writing huge files). For lessor hardware and resources, I think it would be better if you could split your data into multiple worksheets, it would surely enhance the performance to certain extent.

If you still could not evaluate, attach your template file here, we will check your issue if we can enhance the performance more. Also we recommend you to kindly use our latest version/fix e.g v7.3.4.2.

Thank you.

mostafiz.rahman · December 6, 2012, 3:51am

I'm exporting data to .xlsx format and my machine configuration is

Processor: Core i7

RAM: 8GB

System Type: 64-bit Operating System

Also I have download v7.3.4.2. Of Aspose.Cells library and still get the same System.OutOfMemoryException exception.

I have monitored my system resources while exporting data to excel and it never exceeds 60% memory uses.

Your idea of exporting data to multiple sheets instead of single sheet sounds good but if I cannot open an existing .xlsx file with aspose.cells library then how can I add new sheet?

amjad.sahi · December 6, 2012, 4:54am

Hi,

Well, I am afraid, there is no better solution available to load huge Excel files, although we have good way to save large Excel files (with huge list of data, formattings etc.) via LightCells APIs: (see the Java topic, but similar APIs are available in .NET version too: http://www.aspose.com/docs/display/cellsjava/Using+LightCells+API).

By the way, if your existing Excel file has multiple huge worksheets in it, you may load only one worksheet in it at a time, it will consume lessor amount of Memory for sure, see the document for reference:

http://www.aspose.com/docs/display/cellsnet/Load+only+Specific+Sheets+in+a+Workbook

If your Excel file has only one sheet, please attach a sample file here, we need to investigate it further.

Thank you.

mostafizaz · December 12, 2012, 11:15pm

Yes, My excel file contains only one data sheet. It’s an urgent issue and we need to resolve it ASAP.

The sample file is attached with this post.

Please let me know if you need to know any further information.

~mostafiz

amjad.sahi · December 13, 2012, 2:00am

Hi,

Thanks for sharing the file.

I have tested by just opening / loading your file using the latest fix/version: Aspose.Cells for .NET v7.3.4.3

It works fine, it takes only some seconds to load the file. Could you try this fix and let us know your feedback.

I also re-save the file, now it takes 2,3 minutes on my normal configured pc.

Here is my sample code:

Sample code:

string filePath = @“e:\test2\Claim 11-30-2012 12-36-19.xlsx”;

Workbook oWB = new Workbook(filePath); //It takes a few seconds to load the file

oWB.Save(“e:\test2\out_Claim 11-30-2012 12-36-19.xlsx”); /It take 2/3 or more minutes to re-save the file.

If you still find any issue, give us your sample runnable console application (you may zip it prior attaching here) and we will check your issue soon.

Thank you.

mostafizaz · December 13, 2012, 5:03am

I have created a sample console application to demonstrate the real problem. The sample application works as below:

Every time I run the sample application it opens an existing excel file and copy/paste top 400,00 rows of data in the existing file and replace that file with the new one. If I need to experiment with huge data then i need to run the console application as many time as I needed.

I have experimented with this tool and the finding is -

a. I have successfully run the sample application 2 times. So after this my excel had 500,00 + (400,00 * 2) = 130,000 records.

b. Then I attempted to run the sample application for the third time and got System.OutOfMemoryException exception.

I am getting System.OutOfMemoryException exception will exporting more than 30,000 records so how can I export millions of records with aspose.cells?

NOTE: The attached .rar file contains the sample application with source code so that you can to further investigation on the issue. If you need to run the sample application then do it as below:

a. UNRAR the attached file.

b. Copy the sample excel file to the C:\Temp\ReportTemp\ location (file path should be : C:\Temp\ReportTemp\Claim 11-30-2012 12-36-19.xlsx)

c. Double click on the …\AsposeExplorer\bin\Debug\AsposeExplorer.exe file.

If you need any further information please let me know.

~mostafiz

amjad.sahi · December 13, 2012, 12:40pm

Hi,

Thanks for the sample project.

I have tested/checked your project a bit. Since you are inserting more and records into a big file’ s single worksheet (that has already huge list of data and formattings etc. ) which surely demands more RAM and consequently it might result in Out of memory error after a few (2,3) iterations. If you could save your final file for your desired iterations, the output file would be huge which might take some time even opening it into MS Excel. We still recommend you to kindly split your data into multiple worksheets or even in multiple wordbooks for better efficiency and performance.

Anyways, I have logged a ticket with an id: :“CELLSNET-41260” for your issue. We will investigate and look into it if we can enhance the performance more that could take lessor amount of RAM a bit. Once we have any update on it, we will let you know here.

Thank you.

mostafiz · December 17, 2012, 4:38am

I have tried to split data in multiple sheet in the following ways -

Add a blank sheet in the excel file (Sheet name - Blank sheet)
Open the excel file with
var loadOptions = new LoadOptions(LoadFormat.Excel97To2003);
dataOption.SheetNames = new string[] { “Blank sheet” };
Add a new sheet named Data Sheet 1 and export data in that sheet and save it to file.
Next time open the excel file with same option and add another sheet - Data Sheet 2 to the excel file and save that file. In this case, Aspose.Cells lose data from Data Sheet 1.

Code block to open excel file -

var dataOption = new LoadDataOption();

var loadOptions = new LoadOptions(LoadFormat.Excel97To2003);

dataOption.SheetNames = new string[] { “Blank sheet” };

loadOptions.LoadDataOptions = dataOption;

loadOptions.LoadDataOnly = true;

workbookG = new Workbook(filePath, loadOptions);

Code block to export data in separate sheet

var worksheet = workbookG.Worksheets.Add(string.Format(“Data Sheet_{0}”, sheetIndex));

WriteReportData(worksheet, realExport, dataRowIndex, 0, formattingSettings, 10);

workbookG.Save(filePath, FileFormatType.Excel2003)

So, how can I export data in multiple sheet without opening the whole file (because aspose.cells library cannot open large file)?

~mostafiz

shakeel.faiz · December 17, 2012, 11:51am

Hi,

Thanks for your input and feedback.

Please spare us some time to investigate your issue. We have logged your comments/requirements inside our database against this issue id: CELLSNET-41260.

Once, we will have some update for you, we will let you know asap.

amjad.sahi · May 16, 2014, 4:59am

Hi,

To further update you that since the release of Aspose.Cells for .NET v 8.0.0 we provided new options e.g MemorySetting.MemoryPreference for memory performance consideration. We recommend you to kindly try our latest version/fix: Aspose.Cells for .NET 8.0.2 with the new option to check if it works fine regarding Excel file reading or conversions/ rendering. Please see the topic for your complete reference on how to optimize memory usage while loading/ rendering bigger data sets in the workbook etc.:

http://www.aspose.com/docs/display/cellsnet/Optimizing+Memory+Usage+while+Working+with+Big+Files+having+Large+Datasets

We think the memory issue should be solved using the latest version and following the suggestion to set memory preference option.

Thank you.