Export Gridview data to Excel in asp.net

Post date: Jun 04, 2013 9:17:59 AM

Many time we show report/data in Gridview control and at time we need to export that data into Excel. This post is going to show you how we can easily and very conveniently export gridview data into excel.

Step1:

Create your aspx page and place a gridview control. Bind the gridview control with data from database/xml or any other data source.

Now place a button in the same aspx page (after displaying gridview control) as "ExportToExcelButton". The code for this buttn click event will look like below:

protected void ExportToExcelButton_Click(object sender, EventArgs e)

{

string attachment = "attachment; filename=MyExcelData.xls";

Response.ClearContent();

Response.AddHeader("content-disposition", attachment);

Response.ContentType = "application/ms-excel";

StringWriter sw = new StringWriter();

HtmlTextWriter htw = new HtmlTextWriter(sw);

GridView1.RenderControl(htw);

Response.Write(sw.ToString());

Response.End();

}

Now you might think using this small piece of code you are done. No, if you run the code you will get HttpException as follows:

Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server.

The immediate thought that comes to your mind is to check aspx page and verify if you really have placed your gridview control outside the form tag. But the gridview control is already placed inside the form tag. In order to solve the above error, place the below code in your codebehind file

public override void VerifyRenderingInServerForm(Control control)

{

}

Now the code is complete. Build your project and run, you will be able to export the gridview data into excel

Important:

Your gridview control might have cascading controls (controls within gridview - like textbos, checkbox, dropdown list etc). What will happen when you export your gridview data to excel. You need to convert the control data into value before exporting to excel.

The below recursive method is used to identify the type of control and use the relevant value of the control to be exported to excel. Note: The below method uses Reflection to determine the type of control.

private void PrepareGridView_ForExport(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())

{

PrepareGridView_ForExport(gv.Controls[i]);

}

}

}

Your final code in codebehind page will look like this

protected void ExportToExcelButton_Click(object sender, EventArgs e)

{

PrepareGridView_ForExport(GridView1);

ExportGridViewToExcel();

}

private void ExportGridViewToExcel()

{

string attachment = "attachment; filename=MyExcelData.xls";

Response.ClearContent();

Response.AddHeader("content-disposition", attachment);

Response.ContentType = "application/ms-excel";

StringWriter sw = new StringWriter();

HtmlTextWriter htw = new HtmlTextWriter(sw);

GridView1.RenderControl(htw);

Response.Write(sw.ToString());

Response.End();

}

private void PrepareGridView_ForExport(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())

{

PrepareGridView_ForExport(gv.Controls[i]);

}

}

}

public override void VerifyRenderingInServerForm(Control control)

{

}