Pages

Sunday, January 29, 2012

Export gridview to excel in Asp.net

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 :
<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)
        {
        }
    }
}

Related Other posts