Export to excel with large memory stream using c#

Question

I am using this code

 using (XLWorkbook wb = new XLWorkbook())
            {
                wb.Worksheets.Add(ds);
                wb.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                wb.Style.Font.Bold = true;
                HttpContext.Current.Response.Clear();
                HttpContext.Current.Response.Buffer = true;
                HttpContext.Current.Response.Charset = "";
                HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename= EmployeeAndOrderReport.xlsx");
                using (MemoryStream MyMemoryStream = new MemoryStream())
                {
                    wb.SaveAs(MyMemoryStream);
                    MyMemoryStream.WriteTo(HttpContext.Current.Response.OutputStream);
                    HttpContext.Current.Response.Flush();
                    HttpContext.Current.Response.End();
                }
            }

Its working fine But when i am exporting large record set its showing out of memory exception..why its showing like this??

Instead of writing to a memory stream, write directly to the output stream `wb.SaveAs(HttpContext.Current.Response.OutputStream)`. You may also want to Buffer to false `HttpContext.Current.Response.Buffer = false;` if the file is large enough. Otherwise the data will be buffered on the server before they are sent to the client. — Panagiotis Kanavos, Dec 07 '16 at 10:25
Yes actually i am trying with 40-50 k records to export as u said i try but still it showing same exception.i am really fed up of all this... — Sunita, Dec 07 '16 at 11:10
Post the full exception. It could easily be something else. Are you sure it isn't ClosedXML itself that's failing? — Panagiotis Kanavos, Dec 07 '16 at 11:17
Also check [this similar question](http://stackoverflow.com/questions/32690851/export-big-amount-of-data-from-xlsx-outofmemoryexception/32787219#32787219). ClosedXML may have settings that use SAX which you aren't using. Note that this related question deals with 115K and 500K rows. — Panagiotis Kanavos, Dec 07 '16 at 11:18
Thanks for you contribution but i found the solution, its working fine with EPPLUS nuget package. I am directly passing IEnumerable to export.so its easier to do with EPPLUS — Sunita, Dec 07 '16 at 12:23
I use EPPlus too to export larger files, so this is a limitation of ClosedXML — Panagiotis Kanavos, Dec 07 '16 at 12:24

score 0 · Answer 1 · answered Dec 08 '16 at 07:06

I am posting my solutions..which is best i think to export large files as excels..Just install EPPLUS nuget package.

`ExcelPackage excel = new ExcelPackage();
 var userSheet = excel.Workbook.Worksheets.Add("DB_USER");
 userSheet.Cells["A1"].LoadFromCollection(salaryReportlistObj, true);
 using (var memoryStream = new MemoryStream())
 {
                HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                HttpContext.Current.Response.AddHeader("content-disposition", "attachment;  filename=Dane.xlsx");
                excel.SaveAs(memoryStream);
                memoryStream.WriteTo(HttpContext.Current.Response.OutputStream);
                HttpContext.Current.Response.Flush();
                HttpContext.Current.Response.End();
 }

`

Export to excel with large memory stream using c#

1 Answers1

Linked