Workbook constructor generate System.OutOfMemoryException on opening large excel file

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?






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.

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?










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.

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

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.

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


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.

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

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.

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.:

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

Thank you.