将Excel转换成Datetable

       前一段时间做项目突然要用到这个，但是以前也没怎么研究过，于是就上了度娘，结果弄了好长时间也没弄出来，大家说的都一样，后来我又和我的一个同事研究了半天也不行。最后终于被另一个同事一语点醒梦中人啊。好了废话不多说。看代码。

     public class ExcelEntity : IDisposable
        {
            //Excel文件联接
            private OleDbConnection conn = new OleDbConnection();
            /// <summary>
            /// 获取Excel文件联接实体
            /// </summary>
            /// <param name="path"></param>
            /// <param name="xlsFileName"></param>
            /// <returns></returns>
            public OleDbConnection GetExcelConnection(string path, string xlsFileName)
            {
                string connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\"{0}\\{1}\";Extended Properties=\"Excel 12.0 Xml;HDR=YES\";", path, xlsFileName);

               一定注意加下划线的地方一定别把．ACE掉了，我刚开始的时候就在这儿浪费了很长时间，那是一个微软的提供程序，自己可以到网上搜一搜
                conn = new OleDbConnection(connectionString);
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }
                return conn;
            }

            public void Dispose()
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }

 

    然后调用该方法

   /// <summary>
        /// 获取Excel文件中某个数据表的数据
        /// </summary>
        /// <returns></returns>
        public static DataTable GetSheetData(string path, string xlsName, string sheetName)
        {
            DataTable table = new DataTable();
            using (ExcelEntity context = new ExcelEntity())
            {
                var conn = context.GetExcelConnection(path, xlsName);
                var xlsComm = conn.CreateCommand();
                xlsComm.CommandText = string.Format("Select * From [{0}$]", sheetName);
                var xlsReader = xlsComm.ExecuteReader();
                table.Load(xlsReader);
            }

            return table;
        }

 

        OK 搞定