<

Export Grid to Excel

Published on
9,816 Points
3,816 Views
Last Modified:
Approved
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.

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

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

 }  // 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
Microsoft MVP-ASP/ASP.NET

MsDnM || My Forums || My Blog
0
Comment
0 Comments

Featured Post

Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Join & Write a Comment

See the Basics of Office 365's Note Taking app, OneNote
Learn how to collaborate with office 365 Office Online

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month