Export gridview to excel in Asp.net :
Introduction : In this article i will show you how to export GridView to excel file. Many times we require to export the GridView to excel sheet file.I have here written following code in asp.net c# to export the gridview control to excel sheet file.
Html code for gridview export to excel sheet :
 |
 |
Html code for gridview export to excel sheet :
<asp:GridView ID="GridViewExample" runat="server" AutoGenerateColumns="False" BackColor="White"
BorderColor="#3366CC" BorderStyle="None" BorderWidth="1px" CellPadding="4">
<Columns>
<asp:TemplateField HeaderText="City Name" SortExpression="Name" FooterStyle-Width="200px"
ItemStyle-Width="200px">
<ItemTemplate>
<asp:Label ID="lblCityName" Text='<%#Bind("Name")%>' runat="server"></asp:Label>
</ItemTemplate>
<FooterStyle Width="200px"></FooterStyle>
<ItemStyle Width="200px"></ItemStyle>
</asp:TemplateField>
<asp:TemplateField HeaderText="STD Code" SortExpression="STDCode" FooterStyle-Width="100px"
ItemStyle-Width="100px">
<ItemTemplate>
<asp:Label ID="lblSTDCOde" Text='<%#Bind("STDCode")%>' runat="server"></asp:Label>
</ItemTemplate>
<FooterStyle Width="100px"></FooterStyle>
<ItemStyle Width="100px"></ItemStyle>
</asp:TemplateField>
</Columns>
<FooterStyle BackColor="#99CCCC" ForeColor="#003399" />
<HeaderStyle BackColor="#003399" Font-Bold="True" ForeColor="#CCCCFF" />
<PagerStyle BackColor="#99CCCC" ForeColor="#003399" HorizontalAlign="Left" />
<RowStyle BackColor="White" ForeColor="#003399" />
<SelectedRowStyle BackColor="#009999" Font-Bold="True" ForeColor="#CCFF99" />
<SortedAscendingCellStyle BackColor="#EDF6F6" />
<SortedAscendingHeaderStyle BackColor="#0D4AC4" />
<SortedDescendingCellStyle BackColor="#D6DFDF" />
<SortedDescendingHeaderStyle BackColor="#002876" />
</asp:GridView>
<asp:Button ID="btnExport" BackColor="#003399" Text="Export To Excel" runat="server"
OnClick="btnExport_Click" />
C# Code to export the gridview to excel sheet :
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.IO;
namespace HamidSite
{
public partial class GridviewExportToExcel : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindGridView();
}
}
private void BindGridView()
{
try
{
DataTable Dt = GetDataTable("Select Name ,stdcode from citytable");
GridViewExample.DataSource = Dt;
GridViewExample.DataBind();
}
catch (Exception)
{
throw;
}
}
private DataTable GetDataTable(string Query)
{
DataSet Ds = new DataSet();
try
{
string strCon = @"Data Source=ServerName;Initial Catalog=Test;Integrated Security=True;"; //Conntection String
SqlConnection Con = new SqlConnection(strCon);
SqlDataAdapter Da = new SqlDataAdapter(Query, Con);
Da.Fill(Ds);
}
catch (Exception) { }
return Ds.Tables[0];
}
protected void btnExport_Click(object sender, EventArgs e)
{
Response.Clear();
Response.AddHeader("content-disposition", "attachment;filename=GridViewExample.xls");
Response.Charset = "";
Response.ContentType = "application/vnd.xls";
StringWriter StringWriter = new System.IO.StringWriter();
HtmlTextWriter HtmlTextWriter = new HtmlTextWriter(StringWriter);
GridViewExample.RenderControl(HtmlTextWriter);
Response.Write(StringWriter.ToString());
Response.End();
}
public override void VerifyRenderingInServerForm(Control control)
{
}
}
}