Export to Excel from DataGridView in ASP.NET

Hi,
We are going to learn how to Export GridView data to Microsoft Excel at Runtime.

We are using: ASP.NET Framework 4.0, Visual Studio 2010, SQL Server 2008, C#, XHTML 4.01

 Code for ASPX Page (Front-End) Adding GridView and Columns:

 <asp:GridView ID="gvPros" runat="server" AutoGenerateColumns="false" Width="664px" Font-Names="Calibri"
                    HeaderStyle-Font-Bold="true" AlternatingRowStyle-BackColor="LightGoldenrodYellow" EmptyDataText="No Records Found. Try again by Changing Filter Options.">
                    <Columns>
                        <asp:TemplateField HeaderText="Party Name">
                            <ItemTemplate>
                                <asp:Label ID="lblPartyName" runat="server" Text='<%#Eval("PartyName")%>'></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                    </Columns>
                    <Columns>
                        <asp:TemplateField HeaderText="Mode">
                            <ItemTemplate>
                                <asp:Label ID="lblFreight" runat="server" Text='<%#Eval("Division")%>'></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                    </Columns>
                    <Columns>
                        <asp:TemplateField HeaderText="Month">
                            <ItemTemplate>
                                <asp:Label ID="lblMonthName" runat="server" Text='<%#Eval("ProMonth")%>'></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                    </Columns>
                    <Columns>
                        <asp:TemplateField HeaderText="Total Box">
                            <ItemTemplate>
                                <asp:Label ID="lblTotalBox" runat="server" Text='<%#Eval("ProPkg")%>'></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                    </Columns>
                    <Columns>
                        <asp:TemplateField HeaderText="Actual Weight">
                            <ItemTemplate>
                                <asp:Label ID="lblPartyName" runat="server" Text='<%#Eval("ProActWt")%>'></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                    </Columns>
                    <Columns>
                        <asp:TemplateField HeaderText="Charged Wt">
                            <ItemTemplate>
                                <asp:Label ID="lblChWt" runat="server" Text='<%#Eval("ProChWt")%>'></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                    </Columns>
                    <Columns>
                        <asp:TemplateField HeaderText="Freight">
                            <ItemTemplate>
                                <asp:Label ID="lblFreight" runat="server" Text='<%#Eval("ProFreight")%>'></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                    </Columns>
                </asp:GridView>

Adding Export Link

 <asp:LinkButton ID="lnkExport" runat="server" Text="Export to Excel" onclick="lnkExport_Click"></asp:LinkButton>

Now Code for ASPX.CS Page (BackEnd):

Namespaces :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Drawing;
using System.IO;
using System.Text;
using System.Web.Security;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls.WebParts;

Data Binding and Export LinkButton Click Event:

void GVProsFill()
    {
        string Query = "Select * from Parties,  Prosperity where Parties.PartyID=Prosperity.ProParty";
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConToSEPL"].ConnectionString);
        SqlDataAdapter adp = new SqlDataAdapter(Query, con);
        DataSet ds = new DataSet();
        adp.Fill(ds);
        gvPros.DataSource = ds.Tables[0];
        gvPros.DataBind();
    }
 
//You have to add an another Event for Export to work properly:
public override void VerifyRenderingInServerForm(Control control) 
     { 
         // Can Leave This Blank. 
     }

protected void lnkExport_Click(object sender, EventArgs e)
    {
        Response.ClearContent();
        Response.Buffer = true;
        Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "Prosperity.xls"));
        Response.ContentType = "application/ms-excel";
        StringWriter sw = new StringWriter();
        HtmlTextWriter htw = new HtmlTextWriter(sw);
        gvPros.AllowPaging = false;
        GVProsFill();
        gvPros.HeaderRow.Style.Add("background-color", "#FFFFFF");
        for (int a = 0; a < gvPros.HeaderRow.Cells.Count; a++)
        {
            gvPros.HeaderRow.Cells[a].Style.Add("background-color", "#507CD1");
        }
        int j = 1;
        foreach (GridViewRow gvrow in gvPros.Rows)
        {
            gvrow.BackColor = Color.White;
            if (j <= gvPros.Rows.Count)
                {
     if (j % 2 != 0)
      {
       for (int k = 0; k < gvrow.Cells.Count; k++)
         {
        gvrow.Cells[k].Style.Add("background-color", "#EFF3FB");
         }
      }
    }
   j++;
        }
        gvPros.RenderControl(htw);
        Response.Write(sw.ToString());
        Response.End();
    }

I will be waiting to Listen from you....
John Bhatt