System.OutOfMemoryException while writing to excel

Question

        Public Function GenerateReportAsExcel()
        Dim workbook = WorkbookFactory.Create(template)
        template.Close()
        Dim worksheet = workbook.GetSheetAt(0)

    //           Writing record to worksheet


        Dim workbookStream = New MemoryStream()
        workbookStream.Flush()
        workbookStream.Position = 0
        workbook.Write(workbookStream)  //throws error if the rocord is more then 500000...runs fine for 400000 

        Return New MemoryStream(workbookStream.ToArray())
    End Function

WorkbookFactory is using NPOI.SS.UserModel ....

Is there a way to increase the memory stream capacity? I am getting System.OutOfMemoryException while writing 500000 record to the excel but upto 400000 record works fine. I found couple of similar issue but not getting any solid solution to this problem... Someone one suggested to use

workbookStream.Flush() workbookStream.Position = 0 but not of any help....

Thanks for the concern..

I see some copying happening (looks like you end up with 3 copies in memory-- the one from the factory, the copy you put in the 1st memory stream, then again a copy when you toarray it. What is happening in the commented out middle? Something is creating garbage faster than the GC can collect it, or creating garbage that can't be collected, e.g. not calling dispose, or you are working with an inmemory data structure that just doesn't fit in the space IIS allows it (which is small, like 800MB per request) — MatthewMartin
– MatthewMartin, Commented Oct 7, 2014 at 13:52
How big is the woorkBookstream when at 400000 records (aka the Length property)? — user957902
– user957902, Commented Oct 7, 2014 at 13:56
@MatthewMartin Dim masterRow = worksheet.CreateRow(RowMasterId) masterRow.CreateCell(ColumnOneCriteria, CellType.STRING).SetCellValue(masterName)....This is how I am adding columns to the worksheet ..I am adding atleast 80 columns like this... — Scorpio
– Scorpio, Commented Oct 7, 2014 at 13:57
I'm going to guess that the excel doc is represented by an entirely in memory data structure. This happens with DataSets, XML, and any other big data structure held entirely in memory. Same as if you created a bunch of 1GB strings, eventually you just don't have enough space to hold it in memory. Then you have to switch to streaming APIs, that make sure you never have the entire object graph in memory. — MatthewMartin
– MatthewMartin, Commented Oct 7, 2014 at 14:04

George Polevoy · Accepted Answer · 2014-10-07 14:12:02Z

2

What environment you are running in? If it's 32 bit you get OutOfMemoryException at aprox. 500meg memory stream.

    static void Main(string[] args)
    {
        var buffer = new byte[1024 * 1024];
        Console.WriteLine(IntPtr.Size);
        using (var memoryStream = new MemoryStream())
        {
            for (var i = 0; i < 100000000; i++)
            {
                try
                {
                    memoryStream.Write(buffer, 0, 1024);
                }
                catch (OutOfMemoryException e)
                {
                    Console.WriteLine("Out of memory at {0} meg", i);
                    break;
                }
            }
        }
        Console.ReadKey();
    }

If you run on a 64bit os, make sure you build with 'Prefer 32 bit' switch off. Turn off the switch in project properties:

enter image description here

I would recommend using a FileStream instead of MemoryStream here.

edited Oct 7, 2014 at 14:12

answered Oct 7, 2014 at 14:06

George Polevoy

7,7733 gold badges41 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Roy Dictus · Accepted Answer · 2014-10-07 13:57:17Z

The following code adds nothing, so you can let it go:

    workbookStream.Flush()       ' Does nothing
    workbookStream.Position = 0  ' Does nothing

But the rest is a matter of memory. You need more working memory (RAM) in order to do what you are trying to do. So if you add RAM memory to the machine you should be good to go... Unless you have a 32-bit-machine and run into the 3GB practical RAM limit. In that case you would need to upgrade to a 64-bit-machine where this memory limit is not an issue.

But if you are generating Excel files, you may want to look at ClosedXML instead of using the Excel object model. This is a library that doesn't require Excel on your machine. Have a look at http://www.campusmvp.net/blog/generating-excel-files-like-a-pro-with-closedxml.

Collectives™ on Stack Overflow

System.OutOfMemoryException while writing to excel

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related