{"Exception of type 'System.OutOfMemoryException' was thrown."}

Aspose.Cells Product Family
1

Hi I am getting an out of memory exception when exporting to an excel workbook which contains 14 sheets each with about 35067 rows built by apose. The work sheets have been built up cell by cell. Although I have tried importing to the worksheet straight form a data table. This sometimes works first time but not subsequent times. I have tried performing a GC.Collect before and although the memory appears to be low before the export I still receive the exception. I was hoping to use aspose for larger datasets than this one. In terms of metrics what kind of volumes can aspose handle for an excel export? As I may need to re think my export strategy. Do you have an examples of the most memory efficient way of exporting large volumes of data to excel using aspose?



Aspose Version 5.1.3.1 .Net





Stack Trace



at Aspose.Cells.Workbook.Save(String fileName, SaveOptions saveOptions)

at Aspose.Cells.Workbook.Save(String fileName, SaveFormat saveFormat)

at Eon.TP.Consolidator.ReportWriter.ModelOutputsReport.GenericModelOutputWriter.Save(FileInfo fileInfo) in C:\svn\TransferPricing\Trunk\Working\src\Eon.TP.Consolidator.Core\ReportWriter\ModelOutputsReport\GenericModelOutputWriter.cs:line 45

at Eon.TP.Consolidator.UI.Modules.ScenarioModule.ViewModels.ListScenariosViewModel.<>c__DisplayClass3.b__1() in C:\svn\TransferPricing\Trunk\Working\src\Eon.TP.Consolidator.UI\Modules\ScenarioModule\ViewModels\ListScenariosViewModel.cs:line 150

at Eon.TP.Consolidator.UI.Common.BusyViewModel.<>c__DisplayClass1.b__0(Object sender, DoWorkEventArgs e) in C:\svn\TransferPricing\Trunk\Working\src\Eon.TP.Consolidator.UI\Common\BusyViewModel.cs:line 51

at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e)

at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)

2

Hi,

Please download the latest version, we have lately fixed memory issues on it: Aspose.Cells
for .NET v6.0.0.4

Also, you may partially load your large workbooks.

a) - If you just want to load data of one worksheet, please try the following code:

C#


LoadOptions loadOptions = new LoadOptions(LoadFormat.Xlsx);
LoadDataOption dataOption = new LoadDataOption();
dataOption.SheetIndexes = new int[] { 0 };
dataOption.ImportFormula = true;
loadOptions.LoadDataOnly = true;

//Specify the LoadDataOption
loadOptions.LoadDataOptions = dataOption;

//Create a Workbook object and opening the file from its path
Workbook wb = new Workbook(Constants.sourcePath + “Source.xlsx”, loadOptions);


Worksheet sheet1 = wb.Worksheets[0];
Worksheet sheet2 = wb.Worksheets[1];
Worksheet sheet3 = wb.Worksheets[2];


Cell cellA1OfSheet1 = sheet1.Cells[“A1”];
Cell cellA1OfSheet2 = sheet2.Cells[“A1”];
Cell cellA1OfSheet3 = sheet3.Cells[“A1”];

Assert.AreEqual(cellA1OfSheet2.Type, CellValueType.IsNull);
Assert.AreEqual(cellA1OfSheet3.Type, CellValueType.IsNull);


b) - If you just want to get the all sheets’ name, please use the following code:

C#
dataOption.SheetIndexes = new int[] { };


3

Hi Shakeel
Thanks for the fast reply. Sorry I don’t think I was very clear. I build the workbook in memory and the out of memory exception occurs when I try and save the workbook. When I call the following line of code

workBook.Save(fileInfo.FullName, SaveFormat.Excel97To2003);

Is there a way to partial save a workbook say worksheet at a time?

Thanks
Dean

4

Hi,

Yes, if you want to partially save the workbook then you need to partially load the workbook.

Did you try the code example I gave you above? Please use “Source.xlsx” to get better understanding.

5

Hi,


If the out of memory error is thrown while saving workbook (e.g Workbook.Save), you may try to set SaveOptions.CachedFileFolder while saving the workbook/file. When this option is not set, we have to save something to memory first, then save to the file. Setting this option will directly save to the file.

Sample code:
SaveOptions SaveOptions = new Aspose.Cells.XlsSaveOptions(SaveFormat.Excel97To2003);
SaveOptions.CachedFileFolder = stringFilePath;
workook.Save(stringFileName, SaveOptions);

6

Hi Shakeel

The problem is I have no worksheet to load. I have not yet created it yet. The workbook is created entirely in memory. The save then throws the out of memory execption.

However I tried your second solution setting the cache file and this seems to have done the trick. I was able to save 10 times in a row successfully. So this should be sufficient for now.

Thanks for your really fast help with this again.

Dean

7

Hi Dean,

Its great. Amjad suggested a very good solution. I have saved it and your comment for future reference. It will be helpful for others too.