Few days ago there was a question asked on www.asp.net forums that how can I export the whole GridView to Excel if the paging is enabled. I prepared this small example which shows how to achieve the task. Please note that this example is implemented in Visual Studio.NET 2005 Professional.
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.Data.SqlClient; using System.IO;
public partial class EXportTest : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { if (!Page.IsPostBack) { BindData; }
} private string ConnectionString { get { return @"Server=localhost;Database=Northwind; Trusted_Connection=true"; }
} private void BindData { // make the query string query = "SELECT * FROM Categories"; SqlConnection myConnection = new SqlConnection(ConnectionString); SqlDataAdapter ad = new SqlDataAdapter(query, myConnection); DataSet ds = new DataSet; ad.Fill(ds, "Categories"); GridView1.DataSource = ds; GridView1.DataBind; /* It's a good idea if you can Cache the DataSet */ } public override void VerifyRenderingInServerForm(Control control) { // Confirms that an HtmlForm control is rendered for the specified ASP.NET server control at run time.
} protected void Button1_Click(object sender, EventArgs e) { Response.Clear;
Response.AddHeader("content-disposition", "attachment;filename=FileName.xls");
Response.Charset = ""; // If you want the option to open the Excel file without saving than
// comment out the line below
// Response.Cache.SetCacheability(HttpCacheability.NoCache); Response.ContentType = "application/vnd.xls";
System.IO.StringWriter stringWrite = new System.IO.StringWriter;
System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite); // turn off paging GridView1.AllowPaging = false; BindData;
GridView1.RenderControl(htmlWrite);
Response.Write(stringWrite.ToString);
Response.End; // turn the paging on again GridView1.AllowPaging = true; BindData; } protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e) { GridView1.PageIndex = e.NewPageIndex; BindData; } }