Export data from Gridview to Excel in asp.net

Question

I am Having Error while Exporting data from Gridview to Excel in asp.net

Firebug shows that error is

"Error: Sys.WebForms.PageRequestManagerParserErrorException: The message received from the server could not be parsed. Common causes for this error are when the response is modified by calls to Response.Write(), response filters, HttpModules, or server trace is enabled. Details: Error parsing near '

and my code is

EditUser _editUser = new EditUser();
        string s = Request.QueryString["UserName"];
        DataTable _dsLeaveDetails1 = new DataTable();
        _dsLeaveDetails1 = _editUser.GetUserWiseECO(s.ToLower(), "Earned Comp Off");
        if (_dsLeaveDetails1.Rows.Count > 0)
        {
            Response.ClearContent();
            Response.Buffer = true;
            Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "MonthlyLeaveReport.xls"));
            Response.ContentType = "application/ms-excel";
            StringWriter sw = new StringWriter();
            HtmlTextWriter htw = new HtmlTextWriter(sw);

            sample.AllowPaging = false;

            sample.DataSource = _dsLeaveDetails1;
            sample.DataBind();

            //Change the Header Row back to white color
            sample.HeaderRow.Style.Add("background-color", "#FFFFFF");
            //Applying stlye to gridview header cells
            for (int i = 0; i < sample.HeaderRow.Cells.Count; i++)
            {
                sample.HeaderRow.Cells[i].Style.Add("background-color", "#507CD1");
            }
            int j = 1;
            //This loop is used to apply stlye to cells based on particular row
            foreach (GridViewRow gvrow in sample.Rows)
            {
                gvrow.BackColor = Color.White;
                if (j <= sample.Rows.Count)
                {
                    if (j % 2 != 0)
                    {
                        for (int k = 0; k < gvrow.Cells.Count; k++)
                        {
                            gvrow.Cells[k].Style.Add("background-color", "#EFF3FB");
                        }
                    }
                }
                j++;
            }
            sample.RenderControl(htw);
            Response.Write(sw.ToString());
            Response.End();
        }
        else
        {
            ScriptManager.RegisterStartupScript(this, GetType(), "displayalertmessage", "alert('No records Found!!!');", true);
        }

Answer 1

This is the complete code I'm using.

First override this method:

public override void VerifyRenderingInServerForm(Control control)
{
}

And use this method to export to Excel:

private void ExportToExcel()
{
    Response.Clear();
    Response.Buffer = true;
    Response.AddHeader("content-disposition", "attachment;filename=name.xls");
    Response.Charset = "utf-8";
    Response.ContentType = "application/vnd.ms-excel";
    using (StringWriter sw = new StringWriter())
    {
        HtmlTextWriter hw = new HtmlTextWriter(sw);

        //To Export all pages
        gridview.AllowPaging = false;

        gridview.HeaderRow.BackColor = Color.White;
        foreach (TableCell cell in gridview.HeaderRow.Cells)
        {
            cell.BackColor = Color.FromName("#4091A4");
            cell.ForeColor = Color.White;

            foreach (Control control in cell.Controls)
            {
                if ((control.GetType().Name == "DataControlLinkButton") ||
                     (control.GetType().Name == "DataControlLinkButton"))
                {
                    cell.Controls.Add(new Literal { Text = (control as LinkButton).Text });
                    cell.Controls.Remove(control);
                }
            }
        }
        foreach (GridViewRow row in gridview.Rows)
        {
            row.BackColor = Color.White;
            foreach (TableCell cell in row.Cells)
            {
                List<Control> controls = new List<Control>();

                foreach (Control control in cell.Controls)
                {
                    controls.Add(control);
                }

                cell.CssClass = "textmode";
                foreach (Control control in controls)
                {
                    switch (control.GetType().Name)
                    {
                        case "HyperLink":
                            cell.Controls.Add(new Literal { Text = (control as HyperLink).Text });
                            break;
                        case "TextBox":
                            cell.Controls.Add(new Literal { Text = (control as TextBox).Text });
                            break;
                        case "DataControlLinkButton":
                        case "LinkButton":
                            cell.Controls.Add(new Literal { Text = (control as LinkButton).Text });
                            break;
                        case "CheckBox":
                            cell.Controls.Add(new Literal { Text = (control as CheckBox).Text });
                            break;
                        case "RadioButton":
                            cell.Controls.Add(new Literal { Text = (control as RadioButton).Text });
                            break;
                    }
                    cell.Controls.Remove(control);
                }

                if (row.RowIndex % 2 == 0)
                {
                    cell.BackColor = gridview.AlternatingRowStyle.BackColor;
                }
                else
                {
                    cell.BackColor = gridview.RowStyle.BackColor;
                }
                cell.HorizontalAlign = HorizontalAlign.Center;
            }
        }

        gridview.RenderControl(hw);

        //style to format numbers to string
        string style = @"<style> .textmode { } </style>";
        Response.Write(style);
        Response.Output.Write(sw.ToString());
        Response.Flush();
        Response.End();
    }
}