Follow by Email

Monday, April 15, 2013

how to export gridview data to Excel in Asp.net


1. First i will bind data to grid and then will export the grid data to excel on Export To Excel Button Click.

This below code i have written on the page_load


  protected void Page_Load(object sender, EventArgs e)
    {


         string conStr = ConfigurationManager.ConnectionStrings["NORTHWNDConnectionString"].ConnectionString.ToString();
        SqlConnection s1 = new SqlConnection(conStr);
        s1.Open();

        string queryString = "SELECT * FROM employee";
        SqlDataAdapter adapter = new SqlDataAdapter(queryString, s1);

        DataSet employee = new DataSet();
        adapter.Fill(employee, "employee");

        s1.Close();

        GridView1.DataSource = employee.Tables[0];
        GridView1.DataBind();

}
Employee Data in Gridview

2. Now on export to excel button click i will call the method to export data into excel.

add Namespace : using System.IO;


      Protected void ExportToExcel_Click(object sender, EventArgs e)
    {
        Response.Clear();
        Response.Buffer = true;
        Response.ContentType = "application/vnd.ms-excel";
        Response.Charset = "";
        Response.AppendHeader("content-disposition", "attachment; filename=DemoExcel.xls");
        System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
     
        GridView1.RenderControl(oHtmlTextWriter);
        Response.Write("<TABLE><TR><TD></TD><TD></TD><TD></TD><TD></TD><TD colspan=3 align=\"center\"><B>" + "Employee List" + "</B></TD></TR>".ToString());
        Response.Write("<TR><TD></TD><TD colspan=3><br><br></TD></TR></TABLE>".ToString());
        Response.Write(oStringWriter.ToString());
        Response.End();

    }

I got this error below when i called the method to export the data to excel on button click:

















To resolve this above error i used this following code and added it inside the class:

public override void VerifyRenderingInServerForm(Control control)
    {
        return;
    }





3. Now Run Your project and click on export to excel button to get the data in excel :