Export to Excel from DataGridView in ASP.NET

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>

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:

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