用REPEATER实现选定任意行任意列导入EXCEL

测试表结构:

---

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();

   }