Export GridView to Excel-转Matt Berseth
I thought this sounded like a pretty standard requirement so I figured there would be a nice snippet on msdn or in an msdn publication describing the official Microsoft solution. I couldn’t find that, or any other approach that I was happy with. I
ran across a few sites that looked promising, but didn’t quite meet my
requirements – I had to either override a page level method and do
nothing (here) or else turn off event validation (here and here). It
also generally appeared that the people using these solutions were also
running into a variety of issues with grids that allow sorting and
paging - all of the grids I am working with allow both.
The
approach I settled on is very similar to the above links, but without
the limitations of having to turn off event validation or override any
page methods. This gives me the freedom to put these methods were I want them (in my case within a static GridViewExportUtil class) without having to worry about the page that hosts the GridView doing anything special.
Download code
[Update: 7/7/2007]
Fred van den Berg converted the sample web site to VB.NET. You can download it here. You can also just view the VB version of the export utility class here. Thanks Fred!
[Update: 6/26/2007]
Shane, you can use the GridLines proeprty of the Table to show or hide the GridLines. I have updated the demo and sample to include copying over this property to the Table before exporting.
// Create a table to contain the grid
Table table = new Table();
// include the gridline settings
table.GridLines = gv.GridLines;
[Update: 6/22/2007]
I updated the demo to include an option for specifing the nuber of rows that should be exported. The available options are 'Current Page', 'All Pages', or just the 'Top 100 Rows'.
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
/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="args"></param>
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);
}
Also, I moved the GridView to a content page to test terry's comment about making sure this approach works from content pages. I haven't run into any issues yet.
** Disclaimer **
I should stress that the only approach that I am using in a production environment is to export the current page, I have not tested the other 2 export options except on my local PC.
