santosh-asp tutorial

Gridview export to Excel

In Asp.Net, we can export the data in the Gridview control to an Excel file. Here we are going to bind the data in GridView at runtime and we create a button to allow user to export the dtata to an Excel file. In the previous lesson we saw how to bind a Gridview at runtime.

Default.aspx code:-

  

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="Default" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <center> 
    <div>
        <h1>Export to Excel:-</h1>
    <asp:GridView ID="Gridview1" runat="server" BackColor="#DEBA84" BorderColor="#DEBA84" BorderStyle="None" BorderWidth="1px" CellPadding="3" CellSpacing="2">
        <FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />
        <HeaderStyle BackColor="#A55129" Font-Bold="True" ForeColor="White" />
        <PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />
        <RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" />
        <SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="White" />
        <SortedAscendingCellStyle BackColor="#FFF1D4" />
        <SortedAscendingHeaderStyle BackColor="#B95C30" />
        <SortedDescendingCellStyle BackColor="#F1E5CE" />
        <SortedDescendingHeaderStyle BackColor="#93451F" />
        </asp:GridView>
        <br />
    <asp:Button ID="btnConvertToExcel" runat="server" Text="Export to Excel" Font-Bold="true" OnClick="btnConvertToExcel_Click" Width="186px" />
    </div>
            </center>
    </form>
</body>
</html>

Default.aspx.cs code:-

  

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;

public partial class Default : System.Web.UI.Page
{
    string connectionString = "Data Source=PS-PC\\SANTOSH;Initial Catalog=dbSantoshTest;Integrated Security=True";
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            loadRecord();
        }

    }
    protected void loadRecord()
    {
        SqlConnection con = new SqlConnection(connectionString);
        con.Open();
        SqlCommand cmd = new SqlCommand("Select * from tblRecord", con);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds);
        int count = ds.Tables[0].Rows.Count;
        con.Close();
        if (ds.Tables[0].Rows.Count > 0)
        {
            Gridview1.DataSource = ds;
            Gridview1.DataBind();
        }
        else
        {
            ds.Tables[0].Rows.Add(ds.Tables[0].NewRow());
            Gridview1.DataSource = ds;
            Gridview1.DataBind();
            int columncount = Gridview1.Rows[0].Cells.Count;
           
        }
    }
    protected void btnConvertToExcel_Click(object sender, EventArgs e)
    {
        
        Response.ClearContent();
        Response.AddHeader("content-disposition", "attachment; filename=RecordExcel.xls");
        Response.ContentType = "application/excel";
        System.IO.StringWriter sw = new System.IO.StringWriter();
        HtmlTextWriter htw = new HtmlTextWriter(sw);
        Gridview1.RenderControl(htw);
        Response.Write(sw.ToString());
        Response.End();
    }

    public override void VerifyRenderingInServerForm(Control control)
    {
        /*Tell the compiler that the control is rendered
         * explicitly by overriding the VerifyRenderingInServerForm event.*/
    }
}

Output:-