Skip to main content

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);
            }
        }
    }
}



Popular posts from this blog

How to export data grid in to Excel file using c#

How To increase Wi-Fi speed on Android phone

How To Increase Wi-Fi speed on Android phone You can restart Wi-Fi modem and again connect to Wi-Fi network to get maximized internet speed.Be aware of people around you who can utilize Wi-Fi connection to browse on their own systems. Always apply password protection avoid unauthorized usage of data.Update your Android phone’s browser and OS to the latest definitions.If you are using 2.4 Ghz cordless Android phone, make sure there should no confliction between 2.4 Ghz Wireless router, as it may cause slow internet speed on your phone.You can check manufacturer’s website for newly updated firmware downloads to upgrade router.Check for background running applications that are using internet connection and close all of them which you do not need while browsing.See if you are using connection with DNS settings. If no, you can change DNS settings on your Android device.Also keep clearing browsing history, cache, cookies and other browser junk to experience fast browsing.Switch to other brow…