when exporting more then 500000 records in excel using C# .net it gives System.OutOfMemoryException was unhandled by user code

Question

Dear Concern, I stuck in the issue of exporting excel it gives error System.OutOfMemoryException in excel 2007 we cam export 1000000 rows but while exporting more then 250000 rows its gives the error the logic to upload the excel as i am using is Response.Clear(); Response.Charset = "";

    Response.Buffer = true;
    Response.ContentType = "application/vnd.ms-excel";
    string FileName = "PoliciesDetailsForBranch";
    Response.AppendHeader("content-disposition", "attachment; filename=" + FileName + ".xls");
    System.IO.StringWriter sw = new System.IO.StringWriter();
    System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(sw);
    GridView gv = new GridView();
     this.EnableViewState = false;
    gv.DataSource = (DataTable)dt;
    gv.DataBind();
    this.ClearControls(gv);
    gv.RenderControl(hw);
    Response.Write(sw.ToString());
    Response.End();

Can any one give me the solution to cope up with this issue.

Answer 1

Instead of using a StringBuilder and causing the whole thing to be converted to a string in memory, send it directly to the Response.Output.

Response.Buffer = true;//left unmodified, but why were you buffering?
Response.ContentType = "application/vnd.ms-excel";
string FileName = "PoliciesDetailsForBranch";
Response.AppendHeader("content-disposition", "attachment; filename=" + FileName + ".xls");
GridView gv = new GridView();
this.EnableViewState = false;
gv.DataSource = (DataTable)dt;
gv.DataBind();
this.ClearControls(gv);
System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(Response.Output);
gv.RenderControl(hw);
Response.End();