When loading a very big Excel file (using the MemoryPreference setting, it is > 100MB) I get the following message:
System.OutOfMemoryException: Exception of type ‘System.OutOfMemoryException’ was thrown.
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 , Stream )
…
Aspose is critical for us but it fails to load large files. Using the Microsoft Memory Stream instead of System.IO.MemoryStream drastically reduces the potential of running into OutOfMemory exception.
Can you please use it or something like it to allow us to large big files with Aspose?
Well, Aspose.Cells would generally need 10 times or more memory of the size of the file (although it is not final) when loading/saving the Excel file, so you got to make sure that sufficient memory is available for the big process. Could you provide us your template Excel file (>100MB), you may upload to some third party drive services and share the download link here, we will check it soon.
Sorry, I cannot provide the file since it has customer data. I can see if I can create a file manually to re-create the problem.
Please make sure you read the blog article that I referenced. Your response did not indicate that you actually understood the issue.
Let me try to make it clear:
You may require 1 GB of memory for 100 MB Excel file (10x as you pointed out). The system does have 5 GB of free RAM available. BUT IT STILL FAIILS with an out of memory exception. The reason is not that there is not enough memory available (we have 4 times more than needed), it is that we do not have 1 GB of CONTIGUOUS memory available, since memory is fragmented and it may have only at most 600 MB of contiguous memory available. This is exactly what I observed on my machine.
System.IO.MemoryStream requires CONTIGUOUS memory, the library, which I reference above from Microsoft does not.
I hope this clarifies this issue and would appreciate addressing this issue since we cannot use the Aspose.Cells product until this is fixed.
Well, we need to investigate your issue in details. We need to evaluate if we could implement microsoft memorystream or not. Alternatively, we will also check if we could enhance existing APIs to handle huge sized files to cope with the issue. I have logged a ticket with an id “CELLSNET-45461” for your issue. Our product team will look into it soon.
Once we have an update on it, or we have some other way around, we will share it with you here.
Which file format are you reading and saving? It’s better if you could provide us a simple project to investigate your issue precisely. We will look into whether we can reduce memory usage.
Then call the method below with Test(@“c:\temp\large.xlsx”);
It should succeed. Then comment out the Recycable memory implementation, use the MemoryStream implementation instead and see it fail and the same with Aspose.Cells, which uses ). If you have it succeeding, just reduce the available memory somewhat.
public static void Test(string path)
{
//this fails. MemoryStream is used by Aspose.Cells
//var ms = new MemoryStream();
//this succeeds, PLEASE use this inside Aspose.Cells
var mm = new RecyclableMemoryStreamManager();
RecyclableMemoryStream ms = new RecyclableMemoryStream(mm);
using (var stream = LongFile.OpenRead(path))
{
stream.CopyTo(ms);
}
//this fails:
//var asposeOptions = new Aspose.Cells.LoadOptions { MemorySetting = MemorySetting.MemoryPreference };
//var d = new Workbook(path, asposeOptions);
//Console.WriteLine(d.AbsolutePath);
}
Please change the internal implementation to use Recyclable Memory Stream.
Additional note:
Recycable Memory Stream is subclass of Stream so it should be pretty easy to replace MemoryStream with it.
I had the same kind of problem in our product and this resolved it.
Thanks for providing us template Excel file in the zipped archive and sample code.
I have logged it against your existing issue “CELLSNET-45461” into our database for product team’ investigations. Our concerned developer from product team will look into it soon.
Once we have any new information, we will share it with you here.
Please try using LightCells API to read and write data for large files.
Aspose.Cells provides the LightCells APIs which is mainly designed to write or read cell’s data one by one without building a complete data model block into memory. See the document for your reference:
See the updated example (I am using the LightCellsDataHandlerVisitCells from the example in the link you provided):
public static void Test(string path)
{
//this fails. MemoryStream is used by Aspose.Cells
//var ms = new MemoryStream();
//this succeeds, PLEASE use this inside Aspose.Cells
//var mm = new RecyclableMemoryStreamManager();
//RecyclableMemoryStream ms = new RecyclableMemoryStream(mm);
//
//using (var stream = LongFile.OpenRead(path))
//{
// stream.CopyTo(ms);
//}
//this fails:
//var asposeOptions = new Aspose.Cells.LoadOptions { MemorySetting = MemorySetting.MemoryPreference };
//var d = new Workbook(path, asposeOptions);
//Console.WriteLine(d.AbsolutePath);
//this fails:
var asposeOptions = new Aspose.Cells.LoadOptions { MemorySetting = MemorySetting.MemoryPreference };
LightCellsDataHandlerVisitCells v = new LightCellsDataHandlerVisitCells();
asposeOptions.LightCellsDataHandler = v;
var d = new Workbook(path, asposeOptions);
Console.WriteLine(d.AbsolutePath);
}
System.OutOfMemoryException: Exception of type ‘System.OutOfMemoryException’ was thrown.
at System.IO.MemoryStream…ctor(Int32 capacity)
at .(Stream )
at Aspose.Cells.Workbook.(Stream )
at .(Object )
at .(MethodBase , Boolean )
I really need a fix where you stop using System.IO.MemoryStream.
We are sorry that LightCells APIs does not work for your scenario/case. I have logged your comments/concerns against your issue “CELLSNET-45461” into our database. Our concerned developer from product team will look into it further and we will get back to you soon.
This is to inform you that we have fixed your issue (logged earlier as “CELLSNET-45461”) now. We will soon provide you the fix/version with sample code after performing QA and incorporating other enhancements and fixes.
Once the fix is available for public use, we will share it here.
Please try the fix with the following sample code.
C#
CellsHelper.CustomImplementationFactory = new MM();
LightCellsDataHandlerVisitCells v = new LightCellsDataHandlerVisitCells();
wb = new Workbook(pathCase, new LoadOptions() { MemorySetting = MemorySetting.MemoryPreference, LightCellsDataHandler = v });
Console.WriteLine("Total sheets: " + wb.Worksheets.Count + ", cells: " + v.CellCount + ", strings: " + v.StringCount + ", formulas: " + v.FormulaCount);
class MM : CustomImplementationFactory
{
RecyclableMemoryStreamManager manager = new RecyclableMemoryStreamManager();
public override MemoryStream CreateMemoryStream()
{
return manager.GetStream("MM");
}
public override MemoryStream CreateMemoryStream(int capacity)
{
return manager.GetStream("MM", capacity);
}
}
With new APIs provided by the fix, user is now able to provide custom implementation such as using Recycable memory implementation instead of the default MemoryStream.
There is some problem with your Excel file and it looks corrupt. Microsoft Excel also throws out of memory message when we open it. Please see the following screenshot for a reference.
Do I have to implement the LightCellsDataHandler in order for this fix to work? …or is it sufficient to set the CustomImplementationFactory only (see below)?
CellsHelper.CustomImplementationFactory = MemoryStreamMemoryManager.Instance;
var asposeOptions = new Aspose.Cells.LoadOptions
{
MemorySetting = MemorySetting.MemoryPreference,
};
Does the LightCells API allow access to non-cell data (e.g. this.Doc.DataConnections or this.Doc.Worksheets.ExternalLinks)?
You only need to set the CustomImplementationFactory for your initial issue.
Of course, using LightCells apis correctly will decrease the memory cost efficiently too. With the LightCells, user should not keep most of cells in memory after process them one by one (ProcessCell()), so it is not fit for the scenarios that require cells data later, such as formula calculation. But other data models such as ExternalLinks can be accessed after the workbook be loaded with LightCells.
