Export Grid to Excel

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.

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 datatale
                         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();
                         //binding databale to the grid 
                         grdtoexport.DataSource = dt;

Open in new window

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()
                       //fuction 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
                           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);
                       //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.AddAt(i, l);
                               else if (gv.Controls[i].GetType() == typeof(DropDownList))
                                   l.Text = (gv.Controls[i] as  DropDownList).SelectedItem.Text;
                                   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.AddAt(i, l);
                               if (gv.Controls[i].HasControls())
                       }  // class ExportToExcel
                      }   // namespace ExportToExcel

Open in new window

* * * IMPORTANT * * *
Include a VerifyRenderingInServerForm() function in EVERY page where the above export function is used.
//Here is a template of this function.  Copy/Paste and UNCOMMENT it with 
                      //class code that calls the ExportGridView() function
                      //public override void VerifyRenderingInServerForm(Control control) {}

Open in new window

for more details on VerifyRenderingInServerForm visit here or here

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); 
                      //Reminder: Include 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)

Open in new window

You can download the code from here

Regards, Meetu Choudhary

MsDnM || My Forums || My Blog

Comments (0)

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.