Export to Excel from DataGridView in ASP.NET

Johnbhatt
Posted by Johnbhatt under ASP.NET category on | Points: 40 | Views : 5721
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

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


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;


BindGridView Function

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();
}


Export LinkButton Click Event:

//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();
}


Now with This Code, When you Click Export to Excel button, An Excel file will be created with All GridView data and prompt you for Saving to Opening.
Enjoyy.....

Comments or Responses

Posted by: T.Saravanan on: 6/23/2012 Level:Silver | Status: [Member] [MVP] | Points: 10
Hi,

Kindly use any other character instead of 'i' in for loop. Because our DNF square brace of 'i' is consider as italic format.
Posted by: Johnbhatt on: 6/24/2012 Level:Starter | Status: [Member] | Points: 10
Sorry, I will be more careful next time.
Posted by: T.Saravanan on: 6/25/2012 Level:Silver | Status: [Member] [MVP] | Points: 10
No Problem. Just go and edit your code.
Posted by: Johnbhatt on: 6/28/2012 Level:Starter | Status: [Member] | Points: 10
Saravanan Sir,
Necessary changed as made in Post.

Login to post response