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.
– Marcus Vinicius
Your computer memory exceeded 95% when performing this routine.
– Daniel Godinho