Export Gridview Data to Excel in ASP.NET


In this post, I will explain how to export Gridview data to Excel document in ASP.NET

Coding Part:

1. Let’s add a Gridview control to aspx page and fill it with our sample Employees table fromNorthwind Database. Here is a code snippet.


<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="EmployeeID" DataSourceID="SqlDataSource1" AllowPaging="True" AllowSorting="True">
 <Columns>
 <asp:BoundField DataField="EmployeeID" HeaderText="EmployeeID" ReadOnly="True" InsertVisible="False" SortExpression="EmployeeID"></asp:BoundField>
 <asp:BoundField DataField="FirstName" HeaderText="FirstName" SortExpression="FirstName"></asp:BoundField>
 <asp:BoundField DataField="LastName" HeaderText="LastName" SortExpression="LastName"></asp:BoundField>
 <asp:BoundField DataField="Address" HeaderText="Address" SortExpression="Address"></asp:BoundField>
 <asp:BoundField DataField="City" HeaderText="City" SortExpression="City"></asp:BoundField>
 <asp:BoundField DataField="PostalCode" HeaderText="PostalCode" SortExpression="PostalCode"></asp:BoundField>
 <asp:BoundField DataField="Country" HeaderText="Country" SortExpression="Country"></asp:BoundField>
 </Columns>
 </asp:GridView>
 <asp:SqlDataSource runat="server" ID="SqlDataSource1" ConnectionString='<%$ ConnectionStrings:NorthwindConnectionString %>' SelectCommand="SELECT [EmployeeID], [FirstName], [LastName], [Address], [City], [PostalCode], [Country] FROM [Employees]"></asp:SqlDataSource>

2. Set the connection string in the web.config file as shown below.


<connectionStrings>
 <add name="NorthwindConnectionString" connectionString="Data Source=localhost;Initial Catalog=Northwind;Persist Security Info=True;User ID=sa;Password=123456"
 providerName="System.Data.SqlClient" />
 </connectionStrings>

3. Now let’s write a code to export this gridview data to Excel. Since we need to use System IO operations, we need to include following namespace.


using System.IO;

4. Let’s add a asp button to aspx page, call it as “Export to Excel” and on the “onClick” event of the button write following code.


Response.ClearContent();
 Response.Buffer = true;
 Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "EmployeesData.xls"));
 Response.ContentType = "application/ms-excel";

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

GridView1.AllowPaging = false;
 GridView1.DataBind();

//This will change the header background color
 GridView1.HeaderRow.Style.Add("background-color", "#FFFFFF");

//This will apply style to gridview header cells
 for (int index = 0; index < GridView1.HeaderRow.Cells.Count; index++)
 {
 GridView1.HeaderRow.Cells[index].Style.Add("background-color", "#d17250");
 }

int index2 = 1;
 //This will apply style to alternate rows
 foreach (GridViewRow gridViewRow in GridView1.Rows)
 {
 gridViewRow.BackColor = Color.White;
 if (index2 <= GridView1.Rows.Count)
 {
 if (index2 % 2 != 0)
 {
 for (int index3 = 0; index3 < gridViewRow.Cells.Count; index3++)
 {
 gridViewRow.Cells[index3].Style.Add("background-color", "#eed0bb");
 }
 }
 }
 index2++;
 }

GridView1.RenderControl(htmlTextWriter);

Response.Write(stringWriter.ToString());
 Response.End();

To avoid the exception like “Control of type must be placed inside a form tag with runat=server.“, we need to add following method in the code behind.


public override void VerifyRenderingInServerForm(Control control)
{
/* Verifies that the control is rendered */
}

5. Once done, rebuild the solution and run it. You will see following output on the page.

6. Now let’s click on “Export to Excel” button. Download the file and see the output in Excel sheet.

7. Since we have formatted excel rows and cells, we can see the such colorful output.

Source Code Download

Github [Repository link]

Box.com [Direct Link to Rar file]

Reference

Advertisements

8 thoughts on “Export Gridview Data to Excel in ASP.NET

  1. Pingback: Export Gridview to PDF in ASP.NET using iTextSharp Library | A Programming Blog

  2. Pingback: Export Gridview Data to Word Document in ASP.NET | A Programming Blog

  3. Pingback: Export Gridview Data to CSV File in ASP.NET | A Programming Blog

  4. Pingback: Import/Insert Excel file into SQL Server using ASP.NET | Satish Ratnaparkhi's blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s