C#导入Excel文件，并用repreater显示

最新推荐文章于 2022-04-21 19:10:00 发布

事理

最新推荐文章于 2022-04-21 19:10:00 发布

阅读量1.2k

点赞数 1

分类专栏： csharp 文章标签： excel c# dataset string table properties

本文链接：https://blog.csdn.net/slyzly/article/details/5841094

版权

csharp 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

后台

//导入要通知的员工
    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;
    }