Browse All Articles > 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.
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:
Writing a ExportToExcel class
* * * IMPORTANT * * *
Include a VerifyRenderingInServerForm() function in EVERY page where the above export function is used.
m visit here or here
Calling the Function to export on button click
You can download the code from here
Regards, Meetu Choudhary
Microsoft MVP-ASP/ASP.NET
MsDnM || My Forums || My Blog
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();
}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* * * IMPORTANT * * *
Include a VerifyRenderingInServerFor
//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) {}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)
{
}You can download the code from here
Regards, Meetu Choudhary
Microsoft MVP-ASP/ASP.NET
MsDnM || My Forums || My Blog
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.
Comments (0)