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

Export GridView to Excel in ASP.NET

by John Bhatt
1307 views

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 Design

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

You may also like

1 comment

John Bhatt September 26, 2013 - 5:14 pm

Sorry for the another Snapshots.

Actually, I don’t have backup for older database as in Code.

Comments are closed.

Export GridView to Excel in ASP.NET

by John Bhatt time to read: 3 min
1