New Workbook(stream) throws System.OutOfMemoryException

I am trying to open xlsx stream after uploading the file via ASP.NET mvc 3.
file size is ~12mb and has 249859 rows.
I get the following outofmemory exception. Can you advice. Thanks
Updating to v7.1.1.0, does not help.

System.OutOfMemoryException occurred
Message=Exception of type 'System.OutOfMemoryException' was thrown.
Source=mscorlib
StackTrace:
at System.IO.MemoryStream.set_Capacity(Int32 value)
at System.IO.MemoryStream.EnsureCapacity(Int32 value)
at System.IO.MemoryStream.Write(Byte[] buffer, Int32 offset, Int32 count)
at ..(Stream )
at ..(String , Stream , String )
at ..(Stream )
at ..( )
at ?.?.( , String )
at ?.?.( , String )
at ?.?.( , String )
at ?.?.()
at ?.?.()
at ?..(Workbook )
at Aspose.Cells.Workbook.(Stream , LoadOptions , Boolean )
at Aspose.Cells.Workbook..ctor(Stream stream)
InnerException:

Hi,

You can try to load your workbook partially.

Please download latest version:
Aspose.Cells
for .NET v7.1.0.6


Please try the following code with the sample xlsx file given in my link.

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”];


Debug.Assert(cellA1OfSheet2.Type == CellValueType.IsNull, “This message should not appear”);

Debug.Assert(cellA1OfSheet3.Type == CellValueType.IsNull, “This message should not appear”);

VB.NET
Dim filePath As String = "F:\Shak-Data-RW\Downloads\source.xlsx"

Dim loadOptions As LoadOptions = New LoadOptions(LoadFormat.Xlsx)
Dim dataOption As LoadDataOption = New LoadDataOption()
dataOption.SheetIndexes = New Integer() {0}
dataOption.ImportFormula = True
loadOptions.LoadDataOnly = True

'Specify the LoadDataOption
loadOptions.LoadDataOptions = dataOption

'Create a Workbook object and opening the file from its path
Dim wb As Workbook = New Workbook(filePath, loadOptions)

Dim sheet1 As Worksheet = wb.Worksheets(0)
Dim sheet2 As Worksheet = wb.Worksheets(1)
Dim sheet3 As Worksheet = wb.Worksheets(2)

Dim cellA1OfSheet1 As Cell = sheet1.Cells("A1")
Dim cellA1OfSheet2 As Cell = sheet2.Cells("A1")
Dim cellA1OfSheet3 As Cell = sheet3.Cells("A1")

Debug.Assert(cellA1OfSheet2.Type = CellValueType.IsNull, "This message should not appear")
Debug.Assert(cellA1OfSheet3.Type = CellValueType.IsNull, "This message should not appear")

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

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

Thanks for the reply, But the above code does not help. I am already using Aspose v7.1.1.0 and my workbook contains only one worksheet. after little trial and error when i reduce the file rows to 200k it works. That too first time only, if i try again it throws out of memory,( unless i restart my IIS).

Can you also tell me how to remove or dispose workbook from memory once i am done processing? will simple workbook = null suffice?
Hi,

Well, the component (Aspose.Cells for .NET) does not necessarily require really to free up the resources for the processes as the component is already optimized to do so automatically. When objects are no more useful in the processes, GC would collect and release the memory occupied by the objects. For your information, Aspose.Cells for .NET is a pure .NET component (created in managed .NET code) and it relies on .NET garbage collector (GC) to allocate and free the memory for the different processes, although you may try to also use GC.Collect() or set Workbook to null in some cases for your need. Moreover, if you are using Stream objects, you need to call Close() and Flush() methods to manually release the resources for the objects when they are not used.

Now come to your issue, one thing should be taken into your account. Aspose.Cells would require 10 times or more memory of the file's size to be opened/saved by the component, so make sure that you have sufficient amount of memory (free/allocated) for your bigger process. If you still could not evaluate, kindly attach your big sample file here, we will evaluate your issue soon.

Thank you.

Attaching the file. Below code last line throws Out Of Memory Exception.

var file = Request.Files[“excelFile”];
License license = new License();
license.SetLicense(“Aspose[1].Cells.lic”);

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

LoadOptions loadOptions = new LoadOptions(LoadFormat.Xlsx);
loadOptions.LoadDataOptions = dataOption;

Workbook workbook = new Workbook(file.InputStream);

My dev environment Processor:Intel i7 2.80Ghz, Ram:8GB, Win 7, IISExpress, .Net 4, Asp.Net mvc 3. 

Hi,

Thanks for your feedback and providing us your source file.

We have logged this issue in our database. Development team will look into it and once we will have some update/fix relating to this issue, we will let you know asap.

This issue has been logged as CELLSNET-40381.

Hi,

I tested your code with your template file on desktop and Web applications, it works fine here with Aspose.Cells for .NET 7.1.1

Sample code:

string file = “e:\test2\Sample+file±+02_10_12_2.xlsx”;

LoadDataOption dataOption = new LoadDataOption();

dataOption.SheetIndexes = new int[] { 0 };

dataOption.ImportFormula = true;

LoadOptions loadOptions = new LoadOptions(LoadFormat.Xlsx);

loadOptions.LoadDataOnly = true;

loadOptions.LoadDataOptions = dataOption;

Workbook workbook = new Workbook(file);

Kindly create a sample project with v7.1.1, zip it and attach it here to reproduce the issue on our end. we will check it soon.

(My System: OS Win7, Processor: Intel Core Duo 2.0 GHz, RAM: 2GB, VS.NET 2008 etc. )

Thank you.