Export Gridview to Excel

The focus of the article is the Export to Excel functionality – the Gridview and it’s data binding are only for demonstrating the Export functionality.The code fragments for the Export to Excel functionality below are not linked to the backend structure and can be re-used across projects for the common functionality provided.

Default.aspx :

<div>
<asp:SqlDataSource
ID=”sqldsCustomers” runat=”server”
SelectCommand=”select * from UserDetails” SelectCommandType=”Text”
ConnectionString=”server=server;database=LegalWindow;Trusted_Connection=yes;” />

<asp:GridView
id=”gvCustomers” runat=”server”
AllowPaging=”true” AllowSorting=”true” PageSize=”10″ DataSourceID=”sqldsCustomers” GridLines=”Both” />
<asp:Button
ID=”btnExportGrid” runat=”server”
Text=”Export to Excel” OnClick=”BtnExportGrid_Click” />
</div>

Default.aspx.cs :

protected void BtnExportGrid_Click(object sender, EventArgs args)
{
//  pass the grid that for exporting …
GridViewExportUtil.Export(“Customers.xls”, this.gvCustomers);
}

GridViewExportUtil.cs

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 form to contain the grid
Table table = new Table();

//  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();
}
}
}

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())
{
GridViewExportUtil.PrepareControlForExport(current);
}
}
}

For the case where I actually wanted all rows exported, I turned off paging and rebound the grid before sending the control to the export utility.  For exporting just the first 100 rows, I set the PageSize property to 100 and then rebound.  You should probably use care when exporting the complete GridView just in case your grid has a few more rows that you are expecting.  Here is the code for the export button click handler

protected void BtnExportGrid_Click(object sender, EventArgs args)
{
if (this.rdoBtnListExportOptions.SelectedIndex == 1)
{
//  the user wants all rows exported, turn off paging
//  and rebing the grid before sending it to the export
//  utility
this.gvCustomers.AllowPaging = false;
this.gvCustomers.DataBind();
}
else if (this.rdoBtnListExportOptions.SelectedIndex == 2)
{
//  the user wants just the first 100,
//  adjust the PageSize and rebind
this.gvCustomers.PageSize = 100;
this.gvCustomers.DataBind();
}

//  pass the grid that for exporting …
GridViewExportUtil.Export(“Customers.xls”, this.gvCustomers);
}

For More Extensive Study of GridView Export to Excel………………read more

---

Possibly related posts: (automatically generated)

• Selecting Rows Randomly from a table
• Export a GridView to Excel – C#
• GridView Export Excel
• Export GridView to Excel – C#