System.Outmemory error while exporting excel c#

Asked

Viewed 37 times

1

I need to export a report to excel in c#, but when I use this code, the exception is returned 'System.OutOfMemory' on the line: wb.Worksheets.Add(dt, "Relatorio"); on account of excel size (580mil lines).

using (XLWorkbook wb = new XLWorkbook())
{
    string db = Request.QueryString["DB"].ToString().Replace("-", "_");
    string de = Request.QueryString["DB"].ToString().Replace("-", "_");


    wb.Worksheets.Add(dt, "Relatorio");
    wb.Worksheet(1).Range("A1:O1").Style.Fill.BackgroundColor = XLColor.Gainsboro;
    wb.Worksheet(1).Range("A1:O1").Style.Font.FontColor = XLColor.DimGray;

    Response.Clear();
    Response.Buffer = true;
    Response.Charset = "";
    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    Response.AddHeader("content-disposition", "attachment;filename=InventoryReport___" + db + "___" +
                       de + "___" + DateTime.Now.Day + "_" + DateTime.Now.Month + "_" +
                       DateTime.Now.Year + "_" + DateTime.Now.Hour + "_" + DateTime.Now.Minute + "_" +
                       DateTime.Now.Second + ".xlsx");

    using (MemoryStream MyMemoryStream = new MemoryStream())
    {

        wb.SaveAs(MyMemoryStream);
        MyMemoryStream.WriteTo(Response.OutputStream);
        Response.Flush();
        Response.End();
    }
}
  • How big is your Datatable? I created a test here, with a datatable of 580,000 rows and 8 columns, one of which is always a string with 3KB of text. It works normally, generating an Excel file of 40MB. See the code in this gist: https://gist.github.com/marcusvx/22531eeed1d21226f6241f37687b2a89 . If you can show more datatable code you can also help.

  • Your computer memory exceeded 95% when performing this routine.

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.