测试表结构:
CREATE TABLE [news] (
[news_id] [int] IDENTITY (1, 1) NOT NULL ,
[news_name] [nvarchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
[news_pass] [nvarchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
[news_data] [nvarchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
[news_memo] [nvarchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
CONSTRAINT [PK_news] PRIMARY KEY CLUSTERED
(
[news_id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
test.aspx (创建一个REPEATER)
<asp:Button id="Button1" style="Z-INDEX: 101; LEFT: 40px; POSITION: absolute; TOP: 8px" runat="server"
Text="导入EXCEL"></asp:Button>
<br>
<TABLE id="Table1" cellSpacing="1" cellPadding="1" width="80%" border="1">
<TR>
<TD>
<asp:CheckBox id="news_Id" runat="server"></asp:CheckBox></TD>
<TD>
<asp:CheckBox id="news_Name" runat="server"></asp:CheckBox></TD>
<TD>
<asp:CheckBox id="news_Pass" runat="server"></asp:CheckBox></TD>
<TD>
<asp:CheckBox id="news_Data" runat="server"></asp:CheckBox></TD>
<TD>
<asp:CheckBox id="news_Memo" runat="server"></asp:CheckBox></TD>
<TD>选择列</TD>
</TR>
<TR>
<TD>用户ID</TD>
<TD>用户名</TD>
<TD>用户密码</TD>
<TD>用户数据</TD>
<TD>用户备注</TD>
<TD>选择行</TD>
</TR>
<asp:repeater ID="list" Runat="server">
<ItemTemplate>
<TR>
<TD>
<asp:Label ID=labelId Runat=server Text='<%# DataBinder.Eval(Container.DataItem,"news_id")%>'/></TD>
<TD>
<asp:Label ID="labelName" Runat=server Text='<%# DataBinder.Eval(Container.DataItem,"news_name")%>'/></TD>
<TD>
<asp:Label ID="LabelPass" Runat=server Text='<%# DataBinder.Eval(Container.DataItem,"news_pass")%>'/></TD>
<TD>
<asp:Label ID="LabelData" Runat=server Text='<%# DataBinder.Eval(Container.DataItem,"news_data")%>'/></TD>
<TD>
<asp:Label ID="LabelMemo" Runat=server Text='<%# DataBinder.Eval(Container.DataItem,"news_memo")%>'/></TD>
<TD>
<asp:CheckBox id="checkedId" runat="server"></asp:CheckBox></TD>
</TR>
</ItemTemplate>
</asp:repeater>
</TABLE>
test.aspx.cs
private void Page_Load(object sender, System.EventArgs e)
{
// 在此处放置用户代码以初始化页面
if(!Page.IsPostBack)
{
using (SqlConnection Conn = new SqlConnection(数据库连接字符串))
{
Conn.Open();
using (SqlCommand Cmd = new SqlCommand())
{
Cmd.Connection = Conn;
Cmd.CommandText = "select top 4 * from news";
Cmd.CommandType = CommandType.Text;
SqlDataReader dr = Cmd.ExecuteReader();
list.DataSource = dr;
list.DataBind();
Cmd.Dispose();
Conn.Dispose();
Conn.Close();
}
}
}
}
private void Button1_Click(object sender, System.EventArgs e)
{
DataTable dt = new DataTable();
//被选中的列数组成数组
StringBuilder sbCol = new StringBuilder(100);
//构造列
if(news_Id.Checked==true)
{
DataColumn dc = new DataColumn();
dc.ColumnName = "user_id";
dc.DataType = System.Type.GetType("System.String");
dt.Columns.Add(dc);
sbCol.Append("user_id|");
}
if(news_Name.Checked==true)
{
DataColumn dc = new DataColumn();
dc.ColumnName = "user_name";
dt.Columns.Add(dc);
sbCol.Append("user_name|");
}
if(news_Pass.Checked==true)
{
DataColumn dc = new DataColumn();
dc.ColumnName = "user_pass";
dt.Columns.Add(dc);
sbCol.Append("user_pass|");
}
if(news_Data.Checked==true)
{
DataColumn dc = new DataColumn();
dc.ColumnName = "user_data";
dt.Columns.Add(dc);
sbCol.Append("user_data|");
}
if(news_Memo.Checked==true)
{
DataColumn dc = new DataColumn();
dc.ColumnName = "user_memo";
dt.Columns.Add(dc);
sbCol.Append("user_memo|");
}
//填充行
int repCount = list.Items.Count;
for ( int i = 0; i<repCount; i++ )
{
if(((CheckBox)list.Items[i].FindControl("checkedId")).Checked==true)
{
DataRow dr = dt.NewRow();
//指填充被选定列的行
if(sbCol.ToString().IndexOf("user_id|")>=0)
{
dr["user_id"] = ((Label)list.Items[i].FindControl("labelId")).Text;
}
if(sbCol.ToString().IndexOf("user_name|")>=0)
{
dr["user_name"] = ((Label)list.Items[i].FindControl("labelName")).Text;
}
if(sbCol.ToString().IndexOf("user_pass|")>=0)
{
dr["user_pass"] = ((Label)list.Items[i].FindControl("labelPass")).Text;
}
if(sbCol.ToString().IndexOf("user_data|")>=0)
{
dr["user_data"] = ((Label)list.Items[i].FindControl("labelData")).Text;
}
if(sbCol.ToString().IndexOf("user_memo|")>=0)
{
dr["user_memo"] = ((Label)list.Items[i].FindControl("labelMemo")).Text;
}
dt.Rows.Add(dr);
}
}// end for
//导入EXCEL
StringWriter sw=new StringWriter();
string strWl = sbCol.ToString().Replace("|","/t");
sw.WriteLine(strWl);
foreach(DataRow drs in dt.Rows)
{
StringBuilder sb = new StringBuilder(100);
for( int intI =0 ; intI< dt.Columns.Count; intI++)
{
sb.Append(drs[intI]+"/t");
}
sw.WriteLine(sb.ToString());
}
sw.Close();
Response.AddHeader("Content-Disposition", "attachment; filename=associator.xls");
Response.ContentType = "application/ms-excel";
Response.ContentEncoding=System.Text.Encoding.GetEncoding("GB2312");
Response.Write(sw);
Response.End();
}