Export gridgiew with caption to excel file in asp.net

Description:-

In this article I will explain how to export ASP.Net GridView control with Caption or Title to Excel file. GridView by default has a caption property but the problem is that it does not have much option of styling and alignment. Below is the code to populate the ASP.Net GridView control. I am populating a Data Table with some sample data and then binding it to the GridView in the Page Load event of the page.

Default.aspx:-

<div>
<asp:Panel runat="server" ID="Panel1">
    <table width="510px">
        <tr>
            <td style="background-color: green; border: 1px solid black" align="center" colspan="3">
                <asp:Label ID="lblCaption" runat="server" Text="Patel Hardware & Software" Style="font-weight: bold;
                    color: White;" Font-Size="Larger"></asp:Label>
            </td>
        </tr>
        <tr>
            <td style="background-color: green; border: 1px solid black" align="center" colspan="3">
                <asp:Label ID="Label1" runat="server" Text="Behind XYZ Shopping Complex,Beside ABC Rest,PBC"
                    Style="font-weight: bold; color: White;"></asp:Label>
            </td>
        </tr>
        <tr>
            <td style="background-color: green; border: 1px solid black" align="center" colspan="3">
                <asp:Label ID="Label2" runat="server" Text="Junction, AH, GJ 000000" Style="font-weight: bold;
                    color: White;"></asp:Label>
            </td>
        </tr>
    </table>
    <br />
    <asp:GridView ID="GridView1" HeaderStyle-BackColor="#3AC0F2" HeaderStyle-ForeColor="White"
        RowStyle-BackColor="#A1DCF2" AlternatingRowStyle-BackColor="White" AlternatingRowStyle-ForeColor="#000"
        runat="server" AutoGenerateColumns="false" Height="95px" Width="510px">
        <Columns>
            <asp:BoundField DataField="Item" HeaderText="Item" ItemStyle-Width="150px" />
            <asp:BoundField DataField="Price" HeaderText="Price" ItemStyle-Width="150px" />
            <asp:BoundField DataField="StoreName" HeaderText="Store Name" ItemStyle-Width="200px" />
        </Columns>
    </asp:GridView>
</asp:Panel>
<br />
<asp:Button ID="btnExport" runat="server" Text="Export" OnClick="Export" />
</div>

Default.aspx.cs:-

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        DataTable dt = new DataTable();
        dt.Columns.AddRange(new DataColumn[3] { new DataColumn("Item"), new DataColumn("Price"), new DataColumn("StoreName") });
        dt.Rows.Add("Laptopn", 30000,"Param Computers");
        dt.Rows.Add("Mouse", 150,"COM-Tech");
        dt.Rows.Add("Key Board", 250,"Computer Store");
        dt.Rows.Add("Lan Wire", 80, "COM-Tech");
        dt.Rows.Add("Adapter", 700,"Shivam Computers");
        dt.Rows.Add("Battery", 1300, "COM-Tech");
        GridView1.DataSource = dt;
        GridView1.DataBind();
    }
}

protected void Export(object sender, EventArgs e)
{
    Response.Clear();
    Response.Buffer = true;
    Response.AddHeader("content-disposition", "attachment;filename=Export.xls");
    Response.Charset = "";
    Response.ContentType = "application/vnd.ms-excel";
    using (StringWriter sw = new StringWriter())
    {
        HtmlTextWriter hw = new HtmlTextWriter(sw);
        Panel1.RenderControl(hw);
        Response.Output.Write(sw.ToString());
        Response.Flush();
        Response.End();
    }
}

public override void VerifyRenderingInServerForm(Control control)
{
    /* Verifies that the control is rendered */
}

Author : Umesh Patel

Related Posts