Export ASP.NET GridView to Excel

Many developers use GridView control to display web reports in their ASP.NET websites. GridView control render data in a tabular form just like HTML tables but it lack the functionality of exporting its contents to any other format such as Excel of PDF file. It is very common requirements of many clients to export web report data to Excel file so that they can perform some analysis using Excel built in features. In this Tutorial I will show you how to export GridView control as Excel File.

For the purpose of this tutorial, I setup a sample ASP.NET page showing products from Northwind database using GridView control. I hope the data binding code will not be new for you if you have already read some of the articles related to GridView control on my website. Following is the preview of the page I am going to use to export to excel.

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.