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 on GridView:

 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