The code is quite simple actually, the only "tricky" part coming in the button_click event for btnExport.
In the .aspx page you simply create a GridView in the usual manner. You may create the GridView by hand or use
the wizard after dropping a GridView control on the page in design view. I've also used the new (2.0)
SqlDataSource control to gather the data for the GridView using a stored procedure. The data comes from the
Northwind database.
<asp:GridView ID="gvToExport"
runat="server"
AutoGenerateColumns="False"
CellPadding="4"
DataSourceID="SqlDataSource1"
ForeColor="#333333">
<FooterStyle BackColor="#990000" ForeColor="White" Font-Bold="True" />
<Columns>
<asp:BoundField DataField="ProductName" HeaderText="Product Name" SortExpression="ProductName" />
<asp:BoundField DataField="QuantityPerUnit" HeaderText="Quantity Per Unit" SortExpression="QuantityPerUnit" />
<asp:BoundField DataField="UnitPrice" HeaderText="Unit Price" SortExpression="UnitPrice" />
<asp:BoundField DataField="UnitsInStock" HeaderText="Units In Stock" SortExpression="UnitsInStock" />
</Columns>
<RowStyle BackColor="#FFFBD6" ForeColor="#333333" />
<SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
<PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />
<HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
</asp:GridView>
<br />
<asp:SqlDataSource ID="SqlDataSource1"
runat="server"
ConnectionString="<%$ ConnectionStrings:dotnetjohnConnectionString %>"
SelectCommand="spGetProductsGridView"
SelectCommandType="StoredProcedure">
</asp:SqlDataSource>
<asp:Button ID="btnExport" runat="server" OnClick="btnExport_Click" Text="Export to Excel" />
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.IO;
public partial class GridViewToExcel : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
VerifyRenderingInServerForm ( form1 );
gvToExport.GridLines = GridLines.Both;
}
protected void btnExport_Click ( object sender, EventArgs e )
{
Response.Clear ( );
Response.AddHeader ( "content-disposition", "attachment;filename=FileName.xls" );
Response.Charset = "";
Response.ContentType = "application/vnd.xls";
StringWriter StringWriter = new System.IO.StringWriter ( );
HtmlTextWriter HtmlTextWriter = new HtmlTextWriter ( StringWriter );
gvToExport.RenderControl ( HtmlTextWriter );
Response.Write ( StringWriter.ToString ( ) );
Response.End ( );
}
public override void VerifyRenderingInServerForm ( Control control )
{
/* Verifies that a Form control was rendered */
}
}
Because of the line "gvToExport.RenderControl (HtmlTextWriter)" the page gets confused unless there is a call to
the overridden VerifyRenderingInServerForm method.
The line in Page_Load setting GridLines to Both is there just to show that you can modify attributes of the
GridView at runtime. You may want to do this to handle display differences in Excel as opposed to the GridView
in the browser.