c#用OleDb这种方式导入excel并转换为datatable

辅助方法：

   public  void AcquireTwoArray(DataSet ds)
        {

            foreach (DataTable dt in ds.Tables)//遍历所有的DataTable
            {
                foreach (DataRow dr in dt.Rows)//遍历所有的行
                {
                    foreach (DataColumn dc in dt.Columns)//遍历所有的列
                    {
                        Console.Write("{0},{1},{2}", dt.TableName, dc.ColumnName, dr[dc].ToString());//表名，列名，单元格数据


                    }
                }

            }

        }


        public  System.Data.DataSet ExcelSqlConnection(string filepath, string tableName)
        {

            string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
            OleDbConnection ExcelConn = new OleDbConnection(strCon);
            try
            {
                string strCom = string.Format("SELECT * FROM [Sheet1$]");
                ExcelConn.Open();
                OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, ExcelConn);
                DataSet ds = new DataSet();
                myCommand.Fill(ds, "[" + tableName + "$]");
                ExcelConn.Close();
                return ds;
            }
            catch
            {
                ExcelConn.Close();
                return null;
            }
        }

调用：
string path = AppDomain.CurrentDomain.BaseDirectory + “report.xlsx”;

        Excel1 ex=new Excel1 ();

        string path1 = AppDomain.CurrentDomain.BaseDirectory + "excel1.xls";
        DataSet dse = ex.ExcelSqlConnection(path, "report.xls");
   ex.AcquireTwoArray(dse);

注意：当excel版本不一样时，会弹出外部表不是预期格式错误，只需修改链接字符窜即可
string strCon = “Provider=Microsoft.Ace.OleDb.12.0;” + “data source=” + filepath + “;Extended Properties=’Excel 12.0; HDR=Yes; IMEX=1’”;