Vinit Patel

Export to Excel in ASP.Net 2.0 – Gridview to Excel, DataTable to Excel

Posted on: September 29, 2009


Exporting contents to excel or preparing a excel report of the data displayed on a webpage is one of the most common tasks in real world web application. Most frequently, we will export the contents of GridView control to excel. In this article, i will implement some of the common ways of exporting a table of data displayed on a web page to a excel file.

Export to Excel

Ø Rendering the Gridview Control to Excel

Ø Rendering the underlying DataTable to Excel

//

Rendering the Gridview Control to Excel

This is one of the most commonly done approach where we set MIME type and use Gridview’s RenderControl() method, similar to we do for a Datagrid control.

string attachment = “attachment; filename=Employee.xls”;

Response.ClearContent();

Response.AddHeader(“content-disposition”, attachment);

Response.ContentType = “application/ms-excel”;

StringWriter stw = new StringWriter();

HtmlTextWriter htextw = new HtmlTextWriter(stw);

gvEmployee.RenderControl(htextw);

Response.Write(stw.ToString());

Response.End();

When we execute the above code, it will give the following error.

Control ‘gvEmployee’ of type ‘GridView’ must be placed inside a form tag with runat=server

We can resolve the error by 2 ways,

1.      Adding the Gridview to a HtmlForm object programmatically

2.      Overriding VerifyRenderingInServerForm Event in the page.

Adding the Gridview to a HtmlForm object programmatically

So, instead of rendering the gridview add the gridview to an HtmlForm object and render the form.

HtmlForm form = new HtmlForm();string attachment = “attachment; filename=Employee.xls”;

Response.ClearContent();

Response.AddHeader(“content-disposition”, attachment);

Response.ContentType = “application/ms-excel”;

StringWriter stw = new StringWriter();

HtmlTextWriter htextw = new HtmlTextWriter(stw);

form.Controls.Add(gvEmployee);

this.Controls.Add(form);

form.RenderControl(htextw);

Response.Write(stw.ToString());

Response.End();

This will solve the error.

Overriding VerifyRenderingInServerForm Event in the page

Adding this event in the codebehind confirms that an HtmlForm control is rendered for the specified ASP.NET server control at run time.

protected void Button1_Click(object sender, EventArgs e)

{

string attachment = “attachment; filename=Employee.xls”;

Response.ClearContent();

Response.AddHeader(“content-disposition”, attachment);

Response.ContentType = “application/ms-excel”;

StringWriter stw = new StringWriter();

HtmlTextWriter htextw = new HtmlTextWriter(stw);

gvEmployee.RenderControl(htextw);

Response.Write(stw.ToString());

Response.End();

}

public override void VerifyRenderingInServerForm(Control control)

{

}

Rendering the underlying DataTable to Excel

This is one of the easiest ways of exporting the data to excel.  The content of the DataTable should be written to the response by setting ContentType attribute to “application/vnd.ms-excel” and by setting its header.

protected void Button3_Click(object sender, EventArgs e)

{

DataTable dt = GetData();

string attachment = “attachment; filename=Employee.xls”;

Response.ClearContent();

Response.AddHeader(“content-disposition”, attachment);

Response.ContentType = “application/vnd.ms-excel”;

string tab = “”;

foreach (DataColumn dc in dt.Columns)

{

Response.Write(tab + dc.ColumnName);

tab = “\t”;

}

Response.Write(“\n”);

int i;

foreach (DataRow dr in dt.Rows)

{

tab = “”;

for (i = 0; i < dt.Columns.Count; i++)

{

Response.Write(tab + dr[i].ToString());

tab = “\t”;

}

Response.Write(“\n”);

}

Response.End();

}

Execute the page and see export to excel in action.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Calendar

September 2009
M T W T F S S
« Jul   Oct »
 123456
78910111213
14151617181920
21222324252627
282930  

Stats Of Blog

  • 6,824 hits

Cluster Map

%d bloggers like this: