Export to excel throwing System.OutOfMemoryException

Question

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;
            }

Community · Accepted Answer · 2017-05-23 10:32:40Z

4

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.

edited May 23, 2017 at 10:32

CommunityBot

11 silver badge

answered Sep 30, 2013 at 16:51

Anusha

1211 gold badge1 silver badge11 bronze badges

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

Comments

Check Mate · Accepted Answer · 2013-09-28 02:49:09Z

3

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

use reflation method it will reduce the memory and get the good performance.
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;
                }
            }

answered Sep 28, 2013 at 2:49

Check Mate

1172 silver badges10 bronze badges

1 Comment

Anusha Over a year ago

Could you post some helpful links explaining Reflation Method?

Collectives™ on Stack Overflow

Export to excel throwing System.OutOfMemoryException

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related