Export GridView to Excel in ASP.NET

       Following Code shows how you can bind data to GridView control in the Page_Load event. Please keep in mind that for brevity I am storing connection string in the code. You should always store your database connection strings in web.config. 


protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            BindData();
        }
    }

private void BindData()
{
   string constr = @"Server=SampleServer;Database=NORTHWND;uid=test; Password=test;";
   string query = "SELECT ProductID, ProductName, UnitPrice FROM Products";

   SqlDataAdapter da = new SqlDataAdapter(query, constr);
   DataTable table = new DataTable();

   da.Fill(table);

   GridView1.DataSource = table;
   GridView1.DataBind();
}

       As you can guess from the above screen shot that the button “Export to Excel” will implement the main export functionality.The code below is the button click event handler code to export GridView control contents to excel but you can use similar code to export to other formats by changing the ContentType property.

Response.Clear();

Response.AddHeader("content-disposition", "attachment; filename=Products.xls");
Response.Charset = "";

Response.ContentType = "application/vnd.xls";

StringWriter stringWriter = new StringWriter();
HtmlTextWriter htmlWriter = new HtmlTextWriter(stringWriter);

GridView1.RenderControl(htmlWriter);
Response.Write(stringWriter.ToString());

Response.End();
        
        Sometimes, when you render any asp.net control dynamically as I am doing with the GridView control in this tutorial, you can get HttpException with the following message:

Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server. 

The problem can be solved by overriding Page class VerifyRenderingInServerForm method which confirms that an HtmlForm control is rendered for the specified ASP.NET server control at run time. 

public override void VerifyRenderingInServerForm(Control control)
{ 
}

When you will click the Export to Excel button you will see the following dialog box asking you to open or save dynamically generated Excel file.

Following screen shot shows you how the data is saved in Excel file.

Happy Programming...................!!!!!!