把DataGrid中的内容以Excel显示

客户端脚本

<TABLE id="Table1" style="Z-INDEX: 102; LEFT: 16px; WIDTH: 288px; POSITION: absolute; TOP: 16px; HEIGHT: 178px"
                cellSpacing="1" cellPadding="1" width="288" border="0">
                <TR>
                    <TD>
                        <asp:datagrid id="dgExcel" runat="server" Font-Names="宋体" Font-Size="9pt" Height="100%" Width="100%"
                            BorderStyle="None" BorderWidth="1px" BorderColor="#CC9966" BackColor="White" CellPadding="4">
                            <SelectedItemStyle Font-Bold="True" ForeColor="#663399" BackColor="#FFCC66"></SelectedItemStyle>
                            <AlternatingItemStyle BackColor="#FFCC99"></AlternatingItemStyle>
                            <ItemStyle BorderWidth="2px" ForeColor="#330099" BorderStyle="Solid" BorderColor="Black" BackColor="White"></ItemStyle>
                            <HeaderStyle Font-Bold="True" HorizontalAlign="Center" BorderWidth="2px" ForeColor="#FFFFCC"
                                BorderStyle="Solid" BorderColor="Black" BackColor="#990000"></HeaderStyle>
                        </asp:datagrid></TD>
                </TR>
                <TR>
                    <TD>
                        <asp:Button id="btnGetExcel" runat="server" Text="把DataGrid中的内容以Excel显示"></asp:Button></TD>
                </TR>
            </TABLE>

服务端代码

        protected System.Web.UI.WebControls.Button btnGetExcel;
        protected System.Web.UI.WebControls.DataGrid dgExcel;
    
        private void Page_Load(object sender, System.EventArgs e)
        {
            DataSet objDataset = new DataSet();
            SqlConnection objConn = new SqlConnection();
            objConn = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionSqlServer"].ToString());
            objConn.Open();
            SqlDataAdapter objAdapter = new SqlDataAdapter("Select top 5 * from customers where country='USA'",objConn);
            objAdapter.Fill(objDataset);    
            DataView oView = new DataView(objDataset.Tables[0]);
            dgExcel.DataSource = oView;
            dgExcel.DataBind();
            objConn.Close();
            objConn.Dispose();
            objConn = null;
            if(Request.QueryString["bExcel"] == "1")
            {
                Response.ContentType = "application/vnd.ms-excel";
                
                Response.Charset = "";

                //关闭 ViewState
                EnableViewState = false;
                System.IO.StringWriter tw = new System.IO.StringWriter();//将信息写入字符串
                System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);//在WEB窗体页上写出一系列连续的HTML特定字符和文本。
                //此类提供ASP.NET服务器控件在将HTML内容呈现给客户端时所使用的格式化功能
                //获取control的HTML
                dgExcel.RenderControl(hw);//将DATAGRID中的内容输出到HtmlTextWriter对象中
                // 把HTML写回浏览器
                Response.Write(tw.ToString());
                Response.End();
            }

        }

 

        private void btnGetExcel_Click(object sender, System.EventArgs e)
        {
            Response.Redirect("excel.aspx?bExcel=1");
        }