asp.net导入Excel文件，并用repreater控件显示

后台

    //导入要通知的员工

    protected void btnLoad_Click(object sender, EventArgs e)
    {
        if (flup.HasFile)
        {
            DataTable dt = TransferData(flup.PostedFile.FileName, "sheet1");
            if (dt != null)
            {
                if (dt.Columns.Contains("姓名") && dt.Columns.Contains("手机号码") && dt.Columns.Contains("机构简称"))
                {
                    rpt_EmployeeList.DataSource = dt;
                    rpt_EmployeeList.DataBind();
                }
            }
        }
        else
        {
            PageHelp.Alert(this, "", "请选择要导入的文件！");
        }
    }

    /// <summary>
    /// 读取Excel数据到DataSet
    /// </summary>
    /// <param name="excelFile">EXCEL的绝对路径</param>
    /// <param name="sheetName">EXCEL名称————表名</param>
    /// <returns></returns>
    public DataTable TransferData(string excelFile, string sheetName)
    {
        DataSet ds = new DataSet();
        DataTable dt = new DataTable();
        try
        {
            ExcelOperate excelOperate = new ExcelOperate();
            Excel.Application xApp = new Excel.ApplicationClass();

            Excel.Workbook xBook = xApp.Workbooks._Open(excelFile,
            Missing.Value, Missing.Value, Missing.Value, Missing.Value
            , Missing.Value, Missing.Value, Missing.Value, Missing.Value
            , Missing.Value, Missing.Value, Missing.Value, Missing.Value);

            Excel.Worksheet xSheet = (Excel.Worksheet)xBook.Sheets[1];
            string table = xSheet.Name;

            excelOperate.Dispose(xSheet, xBook, xApp);
            //获取全部数据           
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFile + ";Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            string strExcel = "";
            OleDbDataAdapter myCommand = null;
            strExcel = string.Format("select * from [{0}$]", table);
            myCommand = new OleDbDataAdapter(strExcel, strConn);
            myCommand.Fill(ds, sheetName);
            conn.Close();
            dt = ds.Tables[0];
            return dt;
        }
        catch (Exception ex)
        {
            PageHelp.Alert(ex.ToString());
            return null;
        }
    }

    /// <summary>
    /// 判断Excel列值是否有空值
    /// </summary>
    /// <param name="dt"></param>
    /// <returns></returns>
    public DataTable ValidateDataTable(DataTable dt)
    {
        foreach (DataRow row in dt.Rows)
        {
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                if (string.IsNullOrEmpty(row[i].ToString()))
                {
                    row[i] = DBNull.Value;
                }
            }
            if (string.IsNullOrEmpty(row["姓名"].ToString()) && string.IsNullOrEmpty(row["身份证"].ToString()))
            {
                row.Delete();
            }
        }
        return dt;
    }

前台

 <table cellpadding="0" cellspacing="0" class="table">
       <tbody style="margin-left: 40px">
             <tr>
                   <th style="width: 5%">
                       姓名
                    </th>
                    <th style="width: 7%">
                       手机号码
                     </th>
                     <th style="width: 7%">
                        机构简称
                      </th>
               </tr>
               <asp:Repeater ID="rpt_EmployeeList" runat="server">
                      <ItemTemplate>
                            <tr>
                                 <td>
                                     <%#Eval("姓名")%>
                                 </td>
                                 <td>
                                      <%#Eval("手机号码")%>
                                 </td>
                                 <td>
                                      <%#Eval("机构简称")%>
                                 </td>
                                </tr>
                        </ItemTemplate>
              </asp:Repeater>

        </tbody>

 </table>

导入的excel文件格式

姓名        手机号码        机构简称 
张三       45345             xxx

李四       3244234        xxxx

转载于:https://www.cnblogs.com/slyzly/articles/1940612.html