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);

        }