Home ProgrammingASP.NET Export GridView to Excel in ASP.NET

Export GridView to Excel in ASP.NET

by John Bhatt
1377 views
Export GridView to Excel Design

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>
Export GridView to Excel

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

Related Articles

Comments are closed.