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

 <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.....

John Bhatt
Glad to Know, Free to Share.....
http://www.johnbhatt.com