将gridview的内容导出到excel中去

asp.net 2.0中，gridview控件是个很不错的控件，如何将gridview中的数据导出到EXCEL中去呢？特别是当gridview中的数据是分页显示时，要将整个gridview的数据导出到EXCEL中去呢？下面是解决的办法：
下面是部分代码片段：
defualt.aspx中，添加一个gridview控件
  <form id="form1" runat="server">
    <div>
        &nbsp;
        <asp:GridView ID="GridView1" runat="server" AllowPaging="true" OnPageIndexChanging="paging">
        </asp:GridView>
   
    </div>
        <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="导出到excel" />
    </form>

default.aspx

protected   void  Page_Load( object  sender, EventArgs e)
     ... {
        if (!Page.IsPostBack)
        ...{
            BindData();
        }
    }
   
     private   void  BindData()
     ... {
        // make the query 
        string query = "SELECT * FROM customers";
        SqlConnection myConnection = new SqlConnection(ConnectionString);
        SqlDataAdapter ad = new SqlDataAdapter(query, myConnection);
        DataSet ds = new DataSet();
        ad.Fill(ds, "customers");
        GridView1.DataSource = ds;
        GridView1.DataBind();

   }


     public   override   void  VerifyRenderingInServerForm(Control control)
     ... {

        // Confirms that an HtmlForm control is rendered for

    }

     protected   void  Button1_Click( object  sender, EventArgs e)
     ... {
        Response.Clear();

        Response.AddHeader("content-disposition",
        "attachment;filename=FileName.xls");

        Response.Charset = "gb2312";
        Response.ContentType = "application/vnd.xls";

        System.IO.StringWriter stringWrite = new System.IO.StringWriter();

        System.Web.UI.HtmlTextWriter htmlWrite =
        new HtmlTextWriter(stringWrite);

       
        GridView1.AllowPaging = false;
        BindData();
        GridView1.RenderControl(htmlWrite);

        Response.Write(stringWrite.ToString());

        Response.End();

        GridView1.AllowPaging = true;
        BindData();

    }
     protected   void  paging( object  sender,GridViewPageEventArgs e)
     ... {
        GridView1.PageIndex = e.NewPageIndex;
        BindData();
    }

 ----------------------------------------------------------

In this code snippet, I will show how you can export data from a GridView control to a Excel spreadsheet.

The Code

Listing 1: Default.aspx

< form id = " form1 "  runat = " server " >
   < div >
        < asp:GridView ID = " GridView1 "  runat = " server " ></ asp:GridView >
   </ div >
   < br  />
        < asp:Button ID = " BtnExport "  runat = " server "  OnClick = " BtnExport_Click "   Text = " Export to Excel "   />
</ form >

 

Listing 2: Default.aspx.cs

protected   void  Page_Load( object  sender, EventArgs e)
... {
    if (!Page.IsPostBack)
        ...{
            BindData();
        }
}

private   void  BindData()
... {
    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();
}  
private   string  ConnectionString
... {
    get 
        ...{ 
        return @"Server=localhost;Database=NorthWind;Trusted_Connection=true"; 
    }
}
   
protected   void  BtnExport_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 then  // 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);  
    GridView1.RenderControl(htmlWrite);  
    Response.Write(stringWrite.ToString());  
    Response.End();
}  

In the above listing, the GridView control will be populated with the data from the Categories table of the Northwind database. You will have to give the appropriate Server name or IP instead of localhost as server name in the above connection string.

If you use this code and try to export the GridView control, you will see an error message saying that the GridView control must be placed inside the form tags with the runat = server attribute.

笔者注:如出现这个要将GridView放在runat=server 里时,而你确实已经做了.但还是有这个问题.你就可以加入以下代码.

This is pretty confusing, since your GridView is already inside the form tags and also contains the runat = server attribute. You can easily resolve this error by adding the following lines.

Listing 3: Overiding VerifyRenderingInServerForm Method就是这里了:

public   override   void  VerifyRenderingInServerForm(Control control)
... {  
/**//* Confirms that an HtmlForm control is rendered for the specified ASP.NET     server control at run time. */
}

Yup, that’s it. Now, when you click the button, the GridView control will be exported correctly. It will prompt you either to open the file as it is or to save it elsewhere.