Export Grid Data To Excel


Hello all this article will show you how to export GridView Data to excel sheet.this is very is to understand and consumes very less time.No matter id you have check box like controls inside grid view. hope this article will help you.

here in this method you just need to pass File Name with extension that is .xls and id of the grid view you will find that all the data was appearing in grid is now converted to excel sheet.


public static void Export(string fileName, GridView gv)
{
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", fileName));
HttpContext.Current.Response.ContentType = "application/ms-excel";

using (StringWriter sw = new StringWriter())
{
using (HtmlTextWriter htw = new HtmlTextWriter(sw))
{
// Create a table to contain the grid
Table table = new Table();

// include the gridline settings
table.GridLines = gv.GridLines;

// add the header row to the table
if (gv.HeaderRow != null)
{
GridViewExportUtil.PrepareControlForExport(gv.HeaderRow);
table.Rows.Add(gv.HeaderRow);
}

// add each of the data rows to the table
foreach (GridViewRow row in gv.Rows)
{
GridViewExportUtil.PrepareControlForExport(row);
table.Rows.Add(row);
}

// add the footer row to the table
if (gv.FooterRow != null)
{
GridViewExportUtil.PrepareControlForExport(gv.FooterRow);
table.Rows.Add(gv.FooterRow);
}

// render the table into the htmlwriter
table.RenderControl(htw);

// render the htmlwriter into the response
HttpContext.Current.Response.Write(sw.ToString());
HttpContext.Current.Response.End();
}
}
}


Here i am giving the method PrepareControlForExport using this if you r grid view has any controls it will not matter all the data appearing would be converted to excel.

private static void PrepareControlForExport(Control control)
{
for (int i = 0; i < control.Controls.Count; i++)
{
Control current = control.Controls[i];
if (current is LinkButton)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as LinkButton).Text));
}
else if (current is ImageButton)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as ImageButton).AlternateText));
}
else if (current is HyperLink)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as HyperLink).Text));
}
else if (current is DropDownList)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as DropDownList).SelectedItem.Text));
}
else if (current is CheckBox)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as CheckBox).Checked ? "True" : "False"));
}

if (current.HasControls())
{
MainDataCode.PrepareControlForExport(current);
}
}
}


Comments

Guest Author: Rohit Kanugo12 Jan 2012

This will export data only once.If want to export again then controls of that pop-up are not working.

Guest Author: kameshwer26 Aug 2012

1 ex..... are very nice & cofortable

Guest Author: Bill Atlanta07 Dec 2012

Where do I get GridViewExportUtil and MainDataCode??