Exporting data grid in to Excel file

Step  1 : Create a web form containing a datagrid and a Button named Export
Step 2 :  On  Page_Load event write code to retrieve data from database and display in to grid
               In following example I have used storeprocedure  spGetExpiredUsers To retrieve data
Step 3 :  Write code in Button1_Click Event  to export data from grid to excel sheet



Refer the code below

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.IO;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;


public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        DataTable dt = new DataTable();
        SqlCommand strCommand = null;

SqlConnection conn = new SqlConnection("Data                             Source=@@@.@@@.@@.@@;Initial Catalog=DBName;User ID=Sunil;Password=Sunil123");

        strCommand = new SqlCommand("spGetExpiredUsers", conn);
        strCommand.CommandType = CommandType.StoredProcedure;
        strCommand.CommandTimeout = 0;
        conn.Open();


        SqlDataAdapter strDA = new SqlDataAdapter(strCommand);
        strDA.Fill(dt);
        dtgrid.DataSource = dt;
        dtgrid.DataBind();

        conn.Close();



    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        string fileName = "reportsSun.xls";
        string Extension = ".xls";
        if (Extension == ".xls")
        {
            PrepareControlForExport(dtgrid);
            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", fileName));
            HttpContext.Current.Response.Charset = "";
            HttpContext.Current.Response.Cache.SetCacheability(System.Web.HttpCacheability.Public);
            HttpContext.Current.Response.ContentType = "application/ms-excel";
            try
            {
                using (StringWriter sw = new StringWriter())
                {
                    using (HtmlTextWriter htw = new HtmlTextWriter(sw))
                    {
                        //  Create a form to contain the grid
                        System.Web.UI.WebControls.Table table = new System.Web.UI.WebControls.Table();
                        table.GridLines = dtgrid.GridLines;

                        //  add the header row to the table
                        if (dtgrid.HeaderRow != null)
                        {
                            PrepareControlForExport(dtgrid.HeaderRow);
                            table.Rows.Add(dtgrid.HeaderRow);
                        }

                        //  add each of the data rows to the table
                        foreach (GridViewRow row in dtgrid.Rows)
                        {
                            PrepareControlForExport(row);
                            table.Rows.Add(row);
                        }

                        //  add the footer row to the table
                        if (dtgrid.FooterRow != null)
                        {
                            PrepareControlForExport(dtgrid.FooterRow);
                            table.Rows.Add(dtgrid.FooterRow);
                        }

                        //  render the table into the htmlwriter
                        dtgrid.GridLines = GridLines.Both;
                        table.RenderControl(htw);

                        //  render the htmlwriter into the response
                        HttpContext.Current.Response.Write(sw.ToString());
                        HttpContext.Current.Response.End();
                    }
                }
            }
            catch (HttpException ex)
            {
                throw ex;
            }
        }

    }


    private static void PrepareControlForExport(Control control)
    {
        for (int i = 0; i < control.Controls.Count; i++)
        {
            Control current = control.Controls[i];
            if (current is LinkButton)
            {
                control.Controls.Remove(current);
                control.Controls.AddAt(i, new LiteralControl((current as LinkButton).Text));
            }
            else if (current is ImageButton)
            {
                control.Controls.Remove(current);
                //control.Controls.AddAt(i, new LiteralControl((current as ImageButton).AlternateText));
            }
            else if (current is HyperLink)
            {
                control.Controls.Remove(current);
                //control.Controls.AddAt(i, new LiteralControl((current as HyperLink).Text));
            }
            else if (current is DropDownList)
            {
                control.Controls.Remove(current);
                //control.Controls.AddAt(i, new LiteralControl((current as DropDownList).SelectedItem.Text));
            }
            else if (current is CheckBox)
            {
                control.Controls.Remove(current);
                //control.Controls.AddAt(i, new LiteralControl((current as CheckBox).Checked ? "True" : "False"));
            }
            else if (current is HiddenField)
            {
                control.Controls.Remove(current);
                //control.Controls.AddAt(i, new LiteralControl((current as CheckBox).Checked ? "True" : "False"));
            }

            if (current.HasControls())
            {
                PrepareControlForExport(current);
            }
        }
    }
}