CODEWITHSUNDEEP
c#asp.netout-of-memoryexport-to-excelhttpresponse
Anusha
Anusha

Reputation: 121

Export to excel throwing System.OutOfMemoryException

A webpage when trying to write datatable from memory stream to output stream is throwing System.OutOfMemoryException while trying to deliver excel file to user. I'm using Closed XML for saving file in Excel and the datatable is about 40K rows and 150 columns large containing mostly decimals, exported file is usually 10MB or greater. What are the suggested tricks to hack around large data sets while exporting to excel?

This is the code from closed XML that I am using http://closedxml.codeplex.com/wikipage?title=How%20do%20I%20deliver%20an%20Excel%20file%20in%20ASP.NET%3f&referringTitle=Documentation

            public HttpResponseMessage Get()
            {
            // Create the workbook
            var workbook = new XLWorkbook();
            Datatable dt = getDataTable();
            workbook.Worksheets.Add(dt);

            // Prepare the response
            HttpResponseMessage response = new HttpResponseMessage(HttpStatusCode.OK);
            var memoryStream = new MemoryStream(); // If I put this in a 'using' construct, I never get the response back in a browser.
            workbook.SaveAs(memoryStream);
            memoryStream.Seek(0, SeekOrigin.Begin); // Seem to have to manually rewind stream before applying it to the content.
            response.Content = new StreamContent(memoryStream);
            response.Content.Headers.ContentType = new MediaTypeHeaderValue("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment") { FileName = "HelloWorld.xlsx" };
            return response;
            }

Upvotes: 3

Views: 14670

Answers (2)

Anusha
Anusha

Reputation: 121

I stumbled on this link OpenXML libraries (alternatives to ClosedXML) And EPPlus https://epplus.codeplex.com/ works much better than ClosedXML when large amount of data is being exported to Excel. Atleast no more "OutOfMemory" exceptions since EPPlus seems to steer clear of Memory Streams, although I would still be interested in knowing how they accomplished this or even learning the difference between Closed XML and EPPlus.

Upvotes: 4

Check Mate
Check Mate

Reputation: 107

Hi You can avoid that memory overflow exception by following below the types.

  1. use reflation method it will reduce the memory and get the good performance.
  2. load the value only to the excel sheet by using below the code snippet.

Code snippet[C#]:

ExcelEngine excelEngine = new ExcelEngine();
            IApplication application = excelEngine.Excel;
            IWorkbook workbook = application.Workbooks.Create(1); //We are using single workbook
            IWorksheet sheet = workbook.Worksheets[0]; //In this case we are exporting to single ExcelSheet so we marked Worksheets as 0 
            for (int i = 0; i < grid.Model.RowCount; i++)
            {
                //Setting Excel cell height based on Grid Cell height
                sheet.SetRowHeightInPixels(i + 1, set heigth here);
                for (int j = 0; j < ColumnCount; j++)
                {
                    int width = Convert.ToInt32(ColumnWidths[j]); //Getting Grid Cell column width
                    sheet.SetColumnWidthInPixels(j + 1, width); //Setting Width for Excel cell
                    sheet.Range[i + 1, j + 1].Text = dt value here;
                }
            }

Upvotes: 3

Related Questions