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
– Panagiotis Kanavos, Commented Dec 7, 2016 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
– Sunita, Commented Dec 7, 2016 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
– Panagiotis Kanavos, Commented Dec 7, 2016 at 11:17
Also check this similar question. 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
– Panagiotis Kanavos, Commented Dec 7, 2016 at 11:18

Sunita · Accepted Answer · 2016-12-08 07:06:51Z

0

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

`

answered Dec 8, 2016 at 7:06

Sunita

441 silver badge5 bronze badges

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

Collectives™ on Stack Overflow

Export to excel with large memory stream using c#

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related