后台动态输出的table中如何导出的Excel
前台代码：要导出的是id="dbt"表，因为要表里要执行<% ShowData(); %> 所以不能在表中加 runat="server"，要不就报错，只能再加个表id="dbt"。
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="DataAdapter.aspx.cs" Inherits="DataAdapter" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>利用Adapter对象填充DataTable的例子</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<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>
<%
//在页面中调用后台代码，这样也能保证生成的代码不会位于<html></html>标记之外
ShowData();
%>
</table>
</td>
</tr>
</table>
<asp:ImageButton ID="word" runat="server" AlternateText="导出到Word" DescriptionUrl="导出到Word"
Height="25px" Width="25px" ImageUrl="~/images/Word.png" OnClick="word_Click" />
</div>
</form>
</body>
</html>
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.Text;
using System.IO;
public partial class DataAdapter : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
//供前台页面调用的方法，这个方法必须是protected或者public
protected void 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"];
*/
for (int i = 0; i < data.Rows.Count; i++)
{
Response.Write("<tr><td>" + data.Rows[i]["UserId"].ToString() + "</td>");
Response.Write("<td>" + data.Rows[i]["UserName"].ToString() + "</td>");
Response.Write("<td>" + data.Rows[i]["RealName"].ToString() + "</td>");
Response.Write("<td>" + data.Rows[i]["Age"].ToString() + "</td>");
//下面是按照列顺序直接读取值，并且根据值来判断最终显示结果
Response.Write("<td>" + (bool.Parse(data.Rows[i]["Sex"].ToString()) == true ? "男" : "女") + "</td>");
//根据列顺序读，列的值需要做相应转换
Response.Write("<td>" + data.Rows[i]["Mobile"].ToString() + "</td>");
//根据列名来读取，列的值需要做相应转换
Response.Write("<td>" + data.Rows[i]["Phone"].ToString() + "</td>");
Response.Write("<td>" + data.Rows[i]["Email"].ToString() + "</td></tr>\n");
}
}
#region 输出Word报表
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;
StringWriter tw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(tw);
dbt.RenderControl(hw);
Response.Write(tw.ToString());
Response.End();
}
#endregion
public override void VerifyRenderingInServerForm(Control control)
{
//base.VerifyRenderingInServerForm(control);
}
protected void word_Click(object sender, ImageClickEventArgs e)
{
Export("application/ms-word", "xnjs.doc");
}
protected void excel_Click(object sender, ImageClickEventArgs e)
{
Export("application/ms-excel", "xnjs.xls");
}
protected void ppt_Click(object sender, ImageClickEventArgs e)
{
Export("application/ms-powerpnt", "xnjs.ppt");
}
}
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();
}
