读取Excel里面的内容转为DataTable

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;

namespace ExcelRead
{
    class ExcelHelper
    {
        private static string excelConstr;
        private OleDbConnection conn = null;//操作数据库
        private OleDbDataAdapter ada = null;//填充dataset
        public ExcelHelper(string path)
        {
            excelConstr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties = Excel 12.0";
            if (conn == null || conn.State == ConnectionState.Closed)
            {
                conn = new OleDbConnection(excelConstr);
            }
        }

        public DataTable GetDataSource(string sheetName)
        {
            DataTable dt = new DataTable();
            string sql = string.Empty;
            sql = "select * from [" + sheetName + "]";
            dt = GetDT(sql);
            return dt;
        }

        /// <summary>
        /// 获取excel数据
        /// </summary>
        /// <param name="sql">用于查询的sql</param>
        /// <returns></returns>
        public DataTable GetDT(string sql)
        {
            DataSet ds = new DataSet();
            try
            {
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }
                ada = new OleDbDataAdapter(sql, conn);
                ada.Fill(ds);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return ds.Tables[0];
        }
    }
}

　　

//获取Excel表里Sheet1的数据
//调用               
ExcelHelper _excelhelper = new ExcelHelper("Excel文件路径");
//Excel的sheet名称，后面要跟$符号
  _excelhelper.GetDataSource("Sheet1$");

　　

 

第二种方式  
/// <summary>
        /// 根据excel的文件的路径提取其中表的数据，不需要传sheet名称只需 
        ///excel路径即可
        /// </summary>
        /// <param name="Path">Excel文件的路径</param>
        private void GetDataFromExcelWithAppointSheetName(string Path)
        {
            //连接串
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();

            //返回Excel的架构，包括各个sheet表的名称,类型，创建时间和修改时间等  
            DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });

            //包含excel中表名的字符串数组
            string[] strTableNames = new string[dtSheetName.Rows.Count];
            for (int k = 0; k < dtSheetName.Rows.Count; k++)
            {
                strTableNames[k] = dtSheetName.Rows[k]["TABLE_NAME"].ToString();
            }

            OleDbDataAdapter myCommand = null;
            DataTable dt = new DataTable();

            //从指定的表明查询数据,可先把所有表明列出来供用户选择
            string strExcel = "select * from [" + strTableNames[0] + "]";
            myCommand = new OleDbDataAdapter(strExcel, strConn);
            dt = new DataTable();
            myCommand.Fill(dt);
           
          
        }

　　推荐使用第二种