Hi,
We have a business use case where in we are trying to merge individual workbooks [which actually contain one single sheet] into one big work book and send out the resultant workbook to the client.
The current flow of the program is:
1. Create the resultant output workbook object
2. For each workbook Item which contains a single sheet in the DB
2a. load it into a temporary workbook
2b. Copy the sheet from temporary workbook to resultant output workbook
3. Save the resultant workbook
While performing this exercise, what we have noticed is that the garbage collection does not happen for intermittent workbooks and hence when we are trying to merge around 400+ sheets, we are getting "Out of memory exception".
The sample code base (with 100 sheet merges) and the Memory Profiling Graph obtained is as attached:
Workbook reportBook = new Workbook();
reportBook.Worksheets.Clear();
Aspose.Cells.License license = new Aspose.Cells.License();
license.SetLicense(System.IO.File.OpenRead("Aspose.Cells.lic"));
byte[] data = new byte[0]; PLEASE PERFORM A READ OF FILE TO LOAD THE BYTE ARRAY
for (int i = 0; i < 100; i++)
{
Console.WriteLine("Processing Sheet : {0}... GC Memory : {1}", i, (GC.GetTotalMemory(false) / 1024));
using (MemoryStream stream = new MemoryStream(data))
{
Aspose.Cells.Workbook wBook = new Aspose.Cells.Workbook(stream, new Aspose.Cells.LoadOptions(LoadFormat.Xlsx));
reportBook.Worksheets.Add("Sheet" + i.ToString());
reportBook.Worksheets["Sheet" + i.ToString()].Copy(wBook.Worksheets[0]);
wBook = null;
}
}
Considerations:
* Each sheet is having around 500 Rows and 40 to 50 Columns
* The entire consolidated work book of 400+ sheets has to be delivered to the client at once [I mean, we have already proposed split and use, but it has not worked]
I would really appreciate if you would work on this on priority and let us know if we do have any solutions, or is there any other better way that we can reduce the memory foot print but still achieve the desired functionality.
Thanks & Regards,
Anil