ExcelHelper类--当使用excel当数据源时使用

ExcelHelper类  可以动态读取表名 

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

namespace DBUtility
{
    public class ExcelHelper
    {
        static string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;IMEX=1'";

        /// <summary>
        /// 获取Excel各个工作表名称
        /// </summary>
        /// <param name="excelPath"></param>
        /// <returns></returns>
        public IList<string> GetExcelTableNames(string excelPath)
        {
            string connStr = String.Format(connectionString, excelPath);
            IList<string> tblNames = null;
            DataTable tblSchema;

            OleDbConnection connection = new OleDbConnection(connStr);
            connection.Open();
            tblSchema = connection.GetSchema("Tables");//获取数据源的表定义元数据 
            //tblSchema = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });//获取数据源的表定义元数据  
            connection.Close();
            connection.Dispose();

            foreach (DataRow dr in tblSchema.Rows)
                tblNames.Add((string)dr["TABLE_NAME"]);//读取表名

            return tblNames;
        }

        /// <summary>
        /// 读取Excel各个表的数据
        /// </summary>
        /// <param name="excelPath"></param>
        /// <returns></returns>
        public static DataSet ExecuteDataSet(string excelPath)
        {
            string connStr = String.Format(connectionString, excelPath);
            string sql_F = "select * from [{0}]";

            OleDbConnection conn = null;
            OleDbDataAdapter da = null;
            DataTable tblSchema = null;
            IList<string> tblNames = null;

            // 初始化连接，并打开
            conn = new OleDbConnection(connStr);
            conn.Open();

            // 获取数据源的表定义元数据                       
            //tblSchema = conn.GetSchema("Tables");
            tblSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

            tblNames = new List<string>();
            foreach (DataRow row in tblSchema.Rows)
            {
                tblNames.Add((string)row["TABLE_NAME"]); // 读取表名
            }

            // 初始化适配器
            da = new OleDbDataAdapter();
            // 准备数据，导入DataSet
            DataSet ds = new DataSet();

            foreach (string tblName in tblNames)
            {
                da.SelectCommand = new OleDbCommand(String.Format(sql_F, tblName), conn);
                try
                {
                    da.Fill(ds, tblName);
                }
                catch
                {
                    // 关闭连接
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                    }
                    throw;
                }
            }

            // 关闭连接
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }

            return ds;
        }

        /// <summary>
        /// 读取Excel第一个表的数据
        /// </summary>
        /// <param name="cmdText">查询字串</param>
        /// <param name="cmdType">CommandType</param>
        /// <param name="cmdParams">参数数组</param>
        /// <returns></returns>
        public static OleDbDataReader ExecuteReader(string excelPath)
        {
            string connStr = String.Format(connectionString, excelPath);

            OleDbConnection connection = new OleDbConnection(connStr);
            try
            {
                connection.Open();
                string tblName = connection.GetSchema("Tables").Rows[0]["TABLE_NAME"].ToString();//得到第一个工作表的表名
                string cmdText = String.Format("select * from [{0}]", tblName);

                OleDbCommand cmd = new OleDbCommand();
                cmd.Connection = connection;
                cmd.CommandText = cmdText;
                OleDbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return reader;
            }
            catch
            {
                // 关闭连接
                if (connection.State == ConnectionState.Open)
                {
                    connection.Close();
                    connection.Dispose();
                }
                throw;
            }
        }

        /// <summary>
        /// 初始化OleDbCommand
        /// </summary>
        /// <param name="cmd">OleDbCommand对象</param>
        /// <param name="conn">OleDbConnection对象</param>
        /// <param name="trans">事务对象</param>
        /// <param name="cmdType">CommandType</param>
        /// <param name="cmdText">CommandText</param>
        /// <param name="cmdParms">参数数组</param>
        private static void PrepareCommand(OleDbCommand cmd, OleDbConnection conn, OleDbTransaction trans, CommandType cmdType, string cmdText, OleDbParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open) conn.Open();

            cmd.Connection = conn;
            cmd.CommandText = cmdText;

            if (trans != null) cmd.Transaction = trans;

            cmd.CommandType = cmdType;

            if (cmdParms != null)
            {
                foreach (OleDbParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }
    }
}