Export Grid to Excel

Introduction

In this article we are going to read and understand how in a web application we can export a grid data in the excel file. As many times in real time programming we generate reports in the grid format to display to the user.

For example

1. The list of commodities purchased this month
2. Reports of SMS Sent.
3. Reports of invites. etc.

and user might want to save this list for the future use. In Excel format then we need to export this grid to the excel.

Using the code

Prerequisites:

1. To view an Excel workbook file's contents, you must have installed Microsoft Excel (alone or with MS-Office) on your system.
2. Microsoft Visual Studio (VS) must be installed on the (I haven't tested it on the Prior versions)

Let's start with creating an application in VS2008 (You can even go for VS2005 or VS2010).

The steps to we are going to follow.

1. Create a new project in VS2008 as name it as ExporttoExcel in the C# category.
2. Place a gridview on the default.aspx page and rename it to grdtoexport, and a button which will export the grid to excel.

Now lets create a datatable which will bind the grid.

The Code will look like:

protected void Page_Load(object sender, EventArgs e)
{
    //creating a table for the grid use namespace System.Data;
    DataTable dt = new DataTable ();
    //adding columns to the datatable
    try
    {
        dt.Columns.Add( Srno );
        dt.Columns.Add( Name );
    }
    catch { }
    //adding values to the datatable
    for (int i = 1; i <= 10; i++)
    {
        DataRow dr = dt.NewRow();
        dr[0] = i;
        dr[1] = Meetu Choudhary + i.ToString();
        dt.Rows.Add(dr);
    }
    //binding databale to the grid
    grdtoexport.DataSource = dt;
    grdtoexport.DataBind();
}

Writing a ExportToExcel class

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Configuration;
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.Text;
using System.IO;
namespace ExportToExcel
{
    /// <summary>
    /// Summary description for ExportToExcel
    /// </summary>
    public class ExportToExcel
    {
        //Constructor of the class
        public ExportToExcel()
        {
        }
        //function call to export the grid to the excel file
        //GridView1 is the grid to be passed while calling to export
        //strFileName will be the filename passed at the time of calling by which the file will be saved at client system
        public void ExportGridView(GridView GridView1, String strFileName)
        {
            //calling the function to prepare the gridview to export
            PrepareGridViewForExport(GridView1);
            HttpContext.Current.Response.ClearContent();
            HttpContext.Current.Response.Buffer = true;
            HttpContext.Current.Response.AddHeader( content-disposition , attachment;filename= + strFileName);
            HttpContext.Current.Response.ContentType = application/ms-excel ;
            HttpContext.Current.Response.Charset = ;
            StringWriter sw = new StringWriter();
            HtmlTextWriter htw = new HtmlTextWriter(sw);
            GridView1.RenderControl(htw);
            HttpContext.Current.Response.Write(sw.ToString());
            HttpContext.Current.Response.End();
        }
        //this function will prepare the grid controls to be exported
        private void PrepareGridViewForExport(Control gv)
        {
            LinkButton lb = new LinkButton();
            Literal l = new Literal();
            string name = String.Empty;
            for (int i = 0; i < gv.Controls.Count; i++)
            {
                if (gv.Controls[i].GetType() == typeof(LinkButton))
                {
                    l.Text = (gv.Controls[i] as LinkButton).Text;
                    gv.Controls.Remove(gv.Controls[i]);
                    gv.Controls.AddAt(i, l);
                }
                else if (gv.Controls[i].GetType() == typeof(DropDownList))
                {
                    l.Text = (gv.Controls[i] as DropDownList).SelectedItem.Text;
                    gv.Controls.Remove(gv.Controls[i]);
                    gv.Controls.AddAt(i, l);
                }
                else if (gv.Controls[i].GetType() == typeof(CheckBox))
                {
                    l.Text = (gv.Controls[i] as CheckBox).Checked ? True : False;
                    gv.Controls.Remove(gv.Controls[i]);
                    gv.Controls.AddAt(i, l);
                }
                if (gv.Controls[i].HasControls())
                {
                    PrepareGridViewForExport(gv.Controls[i]);
                }
            }
        }
        /* * * IMPORTANT * * *
        Use this VerifyRenderingInServerForm() function in EVERY page where the above export function is used.
        * * * IMPORTANT * * * */
        //public override void VerifyRenderingInServerForm(Control control)
        //{
        //}
    }
}

Calling the Function to export on button click

protected void btnexport_Click(object sender, EventArgs e)
{
   //creating the object of the class
   ExportToExcel ex = new ExportToExcel();
   //exporting the grid to the excel
   ex.ExportGridView(grdtoexport, Client.xls);
}
/Writing the VerifyRenderingInServerForm to avoid unwanted error
/while exporting the grid. To overcome the gridview exception "grid must be in form
with runat=server"
/when rendering it to a string (not on page) for writing it else where you overide the
/VerifyRenderingInServerForm method in the page your doing it on. i.e.
public override void VerifyRenderingInServerForm(Control control)
{
}

For more details on VerifyRenderingInServerForm visit here or here.