Grid binding error while exporting to excel

Question

I have a GridView with 75000 records. The data will increase in few days. I have no issues while populating in UI as I am using paging. Now, while exporting to excel all the blogs suggest to remove pagination and then load the grid again to export. But during that process the databind fails with out of memory exception. Please help. I even tried to load to datatable and reload to a new gridview.

(Added my code below, currently this is looping only the final page in the grid multiple times)

try
        {
            GrdReport.AllowPaging = false;
            LoadReportData();
            int a = GrdReport.PageIndex;
            if (GrdReport.PageCount <= 650)
            {
                DataTable dt = new DataTable();
                for (int i = 0; i < GrdReport.PageCount; i++)
                {
                    GrdReport.PageIndex = i;
                    //GrdReport.SetPageIndex(a);

                    if (i == 0)
                    {
                        for (int k = 0; k < GrdReport.HeaderRow.Cells.Count; k++)
                        {

                            if (GrdReport.HeaderRow.Cells[k].HasControls())
                            {
                                                                    if (GrdReport.HeaderRow.Cells[k].Controls[0] is LinkButton)
                                {
                                    LinkButton headerControl = GrdReport.HeaderRow.Cells[k].Controls[0] as LinkButton;
                                    string headerName = headerControl.Text;
                                    dt.Columns.Add(headerName);
                                }

                            }
                        }
                    }

                    foreach (GridViewRow row in GrdReport.Rows)
                    {

                        dt.Rows.Add();
                        for (int j = 0; j < row.Cells.Count; j++)
                        {
                            dt.Rows[dt.Rows.Count - 1][j] = row.Cells[j].Text;
                        }
                    }
                }
                int x = dt.Rows.Count;
                int y = dt.Columns.Count;
                GrdReport.SetPageIndex(a);

                Session["New"] = dt;
                HttpResponse Response = HttpContext.Current.Response;
                Response.Redirect("ExportToExcelHandler.ashx?gv=" + Session["New"], false);

            }

            else
            {
                lblErr.Text = "Result exceeds 65000 records. Please modify search criteria to reduce records.";
                lblErr.Visible = true;
            }
        }

And this is the code in handler:

public class ExportToExcelHandler : System.Web.UI.Page, IHttpHandler, IRequiresSessionState
{

    public new void ProcessRequest(HttpContext context)
    {
        GridView grid = new GridView();
        this.EnableViewState = false;           
        grid.DataSource = (DataTable)HttpContext.Current.Session["New"];
        grid.DataBind();
        HttpResponse Response = HttpContext.Current.Response;
        Response.Clear();
        Response.AddHeader("content-disposition", "attachment;filename=Results.xls");
        Response.Charset = "";
        Response.ContentType = "application/vnd.ms-excel";
        StringWriter StringWriter = new StringWriter();          
        HtmlTextWriter HtmlTextWriter = new System.Web.UI.HtmlTextWriter(Response.Output);            
        grid.RenderControl(HtmlTextWriter);
        Response.End();
    }

    public new bool IsReusable
    {
        get
        {
            return false;
        }
    }
}

Answer 1

An HTML file with XLS extension is not a real MS Excel file. MS Excel only knows how to read them and to display data.

Saving large HTML files leads to high memory allocation and it is time consuming.

You should use an Excel library like EasyXLS that saves xls or xlsx Excel files and has a better memory management.

Check the following links for directions:
Export Gridview to Excel in C#
and
Export large Excel files in C#