Question

后台动态输出的table中怎么导出的Excel

Answer 1

  static readonly string tabletemplate = @"<table id=""dbt"" runat=""server"">
    <tr>
    <td>
    <table border=""1"" cellpadding=""0"" cellspacing=""0""> 
    <tr><td>编号</td><td>帐号</td><td>真实姓名</td><td>年龄</td><td>性别</td><td>手机</td><td>电话</td><td>电子邮件</td></tr> 
{0}
    </table> ";
    protected String ShowData()
    {
        //实例化Connection对象 
        SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=AspNetStudy;Persist Security Info=True;User ID=sa;Password=sa");
        //实例化Command对象 
        SqlCommand command = new SqlCommand("select * from UserInfo where sex=0", connection);
        SqlDataAdapter adapter = new SqlDataAdapter(command);
        /* 
         下面的被注释掉的代码与上面的代码是等效的 
        SqlDataAdapter adapter = new SqlDataAdapter("select * from UserInfo where sex=0", connection); 
         */
        DataTable data = new DataTable();
        adapter.Fill(data);
        /* 下面的被注释掉语句与上面填充DataTable的效果是一样的，我更倾向于没有注释掉的部分 
        DataSet ds = new DataSet();//实例化DataSet 
        adapter.Fill(ds, "UserInfo");//填充ds中的"UserInfo"表 
        DataTable data = ds.Tables["UserInfo"]; 
        */
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < data.Rows.Count; i++)
        {
            sb.Append("<tr><td>" + data.Rows[i]["UserId"].ToString() + "</td>");
            sb.Append("<td>" + data.Rows[i]["UserName"].ToString() + "</td>");
            sb.Append("<td>" + data.Rows[i]["RealName"].ToString() + "</td>");
            sb.Append("<td>" + data.Rows[i]["Age"].ToString() + "</td>");
            //下面是按照列顺序直接读取值，并且根据值来判断最终显示结果 
            sb.Append("<td>" + (bool.Parse(data.Rows[i]["Sex"].ToString()) == true ? "男" : "女") + "</td>");
            //根据列顺序读，列的值需要做相应转换 
            sb.Append("<td>" + data.Rows[i]["Mobile"].ToString() + "</td>");
            //根据列名来读取，列的值需要做相应转换 
            sb.Append("<td>" + data.Rows[i]["Phone"].ToString() + "</td>");
            sb.Append("<td>" + data.Rows[i]["Email"].ToString() + "</td></tr>\n");
        }
        String s = tabletemplate.Replace("{0}", sb.ToString());
        return s;
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        Export("application/ms-excel", "xnjs.xls");
    }
    private void Export(string Filetype, string FileName)
    {
        Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlDecode(FileName, Encoding.UTF8).ToString());
        Response.ContentType = Filetype;
        this.EnableViewState = false;
        Response.Write(ShowData());
        Response.End();
    }