CODEWITHSUNDEEP
asp.net-mvcvb.netexcelout-of-memorymemorystream
Scorpio
Scorpio

Reputation: 1171

System.OutOfMemoryException while writing to excel

        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..

Upvotes: 2

Views: 4996

Answers (2)

George Polevoy
George Polevoy

Reputation: 7681

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.

Upvotes: 2

Roy Dictus
Roy Dictus

Reputation: 33139

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.

Upvotes: 0

Related Questions