C#读Excel表内容到DataTable 源码 Excel导入成DataTble 支持xls、xlsx文件

 Excel导入成DataTble 支持xls、xlsx文件

/// <summary>
        /// Excel导入成DataTble 支持xls、xlsx文件 ,过滤表头
        /// </summary>
        /// <param name="file">导入路径(包含文件名与扩展名)</param>
        /// <returns></returns>
        public static DataTable ExcelToTable(MyDel Log,string file)
        {
            if (!File.Exists(file)){    Log("错误 Excel文件不存在");  return null; }
            DataTable dt = new DataTable();
            IWorkbook workbook;
            string fileExt = Path.GetExtension(file).ToLower();
            using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
            {
                if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(fs); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(fs); } else { workbook = null; }
                if (workbook == null) {  Log("workbook为空"); return null; }
                ISheet sheet = workbook.GetSheetAt(0);//第0个表

                //表头  
                IRow header = sheet.GetRow(sheet.FirstRowNum);
                List<int> columns = new List<int>();
                for (int i = 0; i < header.LastCellNum; i++)
                {
                    object obj = GetValueType(header.GetCell(i));
                    if (obj == null || obj.ToString() == string.Empty)
                    {
                        dt.Columns.Add(new DataColumn("Columns" + i.ToString())); //默认
                    }
                    else
                        dt.Columns.Add(new DataColumn(obj.ToString()));
                    columns.Add(i);
                }
                //数据  
                for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
                {
                    DataRow dr = dt.NewRow();
                    bool hasValue = false;
                    foreach (int j in columns)
                    {
                        dr[j] = GetValueType(sheet.GetRow(i).GetCell(j));
                        if (dr[j] != null && dr[j].ToString() != string.Empty)
                        {
                            hasValue = true;
                        }
                    }
                    if (hasValue)
                    {
                        dt.Rows.Add(dr);
                    }
                }
            }
            return dt;
        }

以下：只支持.xls，不支持.xlsx 

//调用     
DataTable dt=GetDataFromExcelByConn("外箱标签对应物料代码-20211230.xls",true);
//分解数据  
for(int col=0;col<dt.Columns.Count;col++)
{
    for (int row = 0; row < dt.Rows.Count; row++)
    {
        Log(dt.Rows[row][col].ToString());
    }
}


//读excel第一个表数据到DataTable
DataTable GetDataFromExcelByConn(string filePath,bool hasTitle = false)
{
    //OpenFileDialog openFile = new OpenFileDialog();
    //openFile.Filter = "Excel(*.xlsx)|*.xlsx|Excel(*.xls)|*.xls";
    //openFile.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
    //openFile.Multiselect = false;
    //if (openFile.ShowDialog() == DialogResult.Cancel) return null;
    //var filePath = openFile.FileName;
    string fileType = System.IO.Path.GetExtension(filePath);
    if (string.IsNullOrEmpty(fileType)) return null;

    using (DataSet ds = new DataSet())
    {
        string strCon = string.Format("Provider=Microsoft.Jet.OLEDB.{0}.0;" +
                        "Extended Properties=\"Excel {1}.0;HDR={2};IMEX=1;\";" +
                        "data source={3};",
                        (fileType == ".xls" ? 4 : 12), (fileType == ".xls" ? 8 : 12), (hasTitle ? "Yes" : "NO"), filePath);



        using (OleDbConnection myConn = new OleDbConnection(strCon))
        {
            myConn.Open();
            DataTable schemaTable = myConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
            string tableName = schemaTable.Rows[0][2].ToString().Trim(); //自动获取第一个表名
            string strCom = " SELECT * FROM ["+ tableName + "$]";
            using (OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn))
            {     
                myCommand.Fill(ds);
            }

            if (ds == null || ds.Tables.Count <= 0)
            {
                return null;
            }       
            return ds.Tables[0];
        }    
    }
}