Thursday, August 6, 2009

Export Grid to Excel

Export 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.

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)

Following 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.
  3. And place a button which will export the grid to excel.
  4. 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();

}

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

{

public ExportToExcel()

{

//

// TODO: Add constructor logic here

//

}

public void ExportGridView(GridView GridView1, String strFileName)

{

PrepareGridViewForExport(GridView1);

//string attachment = "attachment; filename=Contacts.xls";

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 = "";

//System.Web.UI.Page.EnableViewState = false;

StringWriter sw = new StringWriter();

HtmlTextWriter htw = new HtmlTextWriter(sw);

GridView1.RenderControl(htw);

HttpContext.Current.Response.Write(sw.ToString());

HttpContext.Current.Response.End();

}

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

}

}

}

/*Use this commented function in all the pages where the above export function is used

//public override void VerifyRenderingInServerForm(Control control)

//{

//}*/

}

}

Calling the Function to export on button click

protected void btnexport_Click(object sender, EventArgs e)

{

ExportToExcel ex = new ExportToExcel();

ex.ExportGridView(grdtoexport, "Client.xls");

}



You can download the code from here


Regards, Meetu Choudhary
Microsoft MVP-ASP/ASP.NET

MsDnM My Forums My Blog

5 comments:

  1. hi this is amit frm jaipur,
    tdy i saw ur pic in news paper, i glad tht u help to all, can we work toghter, u can help by software programing, n me bye solve hardware problam, n solve it , n also correct all computer program, hardware n software also. can we work w\togter if yes th main me , amit_kumar4471@yahoo.com

    ReplyDelete
  2. Thanks a lot Amit. I am Glad To see Such a Nice Comment. Thanks a lot. Hope we will work together. if the case comes like that. Thanks a lot for the wishes.

    ReplyDelete
  3. Thats great a job as you are sharing yourself with community and helping people like us.. Would glad to share with you at work as web designer and anytime you need any help can catch me out there. go thru my profile n workspace at www.hadiwebstudio.com n look forward to help you too as a professional..

    ReplyDelete
  4. Its so nice to be here with someone who shares her work with community.. God bless U.. few of your articles does help me.. i would like to extend my hand in helping you too thru any way of graphic or web designing anytime you wish.. go thru my workspace www.hadiwebstudio.com

    ReplyDelete

Subscribe via email

Enter your email address:

Delivered by FeedBurner

MSDotnetMentor

MSDotnetMentor My Website http://msdotnetmentor.com