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:

 &LTasp: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."&GT
                    &LTColumns&GT
                        &LTasp:TemplateField HeaderText="Party Name"&GT
                            &LTItemTemplate&GT
                                &LTasp:Label ID="lblPartyName" runat="server" Text='&LT%#Eval("PartyName")%&GT'&GT&LT/asp:Label&GT
                            &LT/ItemTemplate&GT
                        &LT/asp:TemplateField&GT
                    &LT/Columns&GT
                    &LTColumns&GT
                        &LTasp:TemplateField HeaderText="Mode"&GT
                            &LTItemTemplate&GT
                                &LTasp:Label ID="lblFreight" runat="server" Text='&LT%#Eval("Division")%&GT'&GT&LT/asp:Label&GT
                            &LT/ItemTemplate&GT
                        &LT/asp:TemplateField&GT
                    &LT/Columns&GT
                    &LTColumns&GT
                        &LTasp:TemplateField HeaderText="Month"&GT
                            &LTItemTemplate&GT
                                &LTasp:Label ID="lblMonthName" runat="server" Text='&LT%#Eval("ProMonth")%&GT'&GT&LT/asp:Label&GT
                            &LT/ItemTemplate&GT
                        &LT/asp:TemplateField&GT
                    &LT/Columns&GT
                    &LTColumns&GT
                        &LTasp:TemplateField HeaderText="Total Box"&GT
                            &LTItemTemplate&GT
                                &LTasp:Label ID="lblTotalBox" runat="server" Text='&LT%#Eval("ProPkg")%&GT'&GT&LT/asp:Label&GT
                            &LT/ItemTemplate&GT
                        &LT/asp:TemplateField&GT
                    &LT/Columns&GT
                    &LTColumns&GT
                        &LTasp:TemplateField HeaderText="Actual Weight"&GT
                            &LTItemTemplate&GT
                                &LTasp:Label ID="lblPartyName" runat="server" Text='&LT%#Eval("ProActWt")%&GT'&GT&LT/asp:Label&GT
                            &LT/ItemTemplate&GT
                        &LT/asp:TemplateField&GT
                    &LT/Columns&GT
                    &LTColumns&GT
                        &LTasp:TemplateField HeaderText="Charged Wt"&GT
                            &LTItemTemplate&GT
                                &LTasp:Label ID="lblChWt" runat="server" Text='&LT%#Eval("ProChWt")%&GT'&GT&LT/asp:Label&GT
                            &LT/ItemTemplate&GT
                        &LT/asp:TemplateField&GT
                    &LT/Columns&GT
                    &LTColumns&GT
                        &LTasp:TemplateField HeaderText="Freight"&GT
                            &LTItemTemplate&GT
                                &LTasp:Label ID="lblFreight" runat="server" Text='&LT%#Eval("ProFreight")%&GT'&GT&LT/asp:Label&GT
                            &LT/ItemTemplate&GT
                        &LT/asp:TemplateField&GT
                    &LT/Columns&GT
                &LT/asp:GridView&GT

Adding Export Link

 &LTasp:LinkButton ID="lnkExport" runat="server" Text="Export to Excel" onclick="lnkExport_Click"&GT&LT/asp:LinkButton&GT

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....