Export Grid to Excel


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.



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


}







"http://www.msdotnetmentor.com/wp-content/uploads/2009/08/ExportToExcel.rar"



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

Reference: http://aspnetbymeetu.blogspot.com/2009/08/export-to-excel.html


Related Articles

Export Data to Excel in CSV format using .NET

Export Data to excel in CSV format. .NET code to export CSV data. Free .NET code to export data to excel. .NET way of exporting data to excel. Explanation of .NET code to export data to excel in CSV.

More articles: Export to Excel

Comments

Author: soniumesh11 Aug 2009 Member Level: Gold   Points : 1

Hi meetu

Your code is good but we can do grid to excel much easier way.

umesh

Author: Mrs. Meetu Choudhary Nanda12 Aug 2009 Member Level: Gold   Points : 1

Other Wyas are upto you I liked This way... So shared... and by the way there are so many ways to do a single task...

Author: Ramesh N D20 Aug 2009 Member Level: Gold   Points : 0

this is good for web application...what about in Windows Applications....



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: