public class ExcelOledbHelper
    {
        private readonly string connectionStr;
        /// <summary>
        /// 
        /// </summary>
        /// <param name="pathOfExcel">path of Excel, extension must be ".xlsx" or ".xls".</param>
        /// <param name="mode">0->export mode,write /1->import mode,read /2->linked mode,full</param>
        /// <param name="hasHeader">"true" means first row is head.</param>
        public ExcelOledbHelper(string pathOfExcel,int mode,bool hasHeader)
        {
            if (System.IO.File.Exists(pathOfExcel))
            {
                string hdr = hasHeader == true ? "Yes" : "No";
                this.connectionStr = new FileInfo(pathOfExcel).Extension switch
                {
                    ".xlsx" => $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=‘{pathOfExcel}‘;Extended Properties=‘Excel 12.0;HDR={hdr};IMEX={mode};‘",
                    ".xls"=> $"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=‘{pathOfExcel}‘;Extended Properties=‘Excel 8.0;HDR={hdr};IMEX={mode};‘",
                    _=> throw new ArgumentException("Excel文件扩展名错误，应是“.xlsx”或“.xls”。")
                };
            }
            else
                throw new ArgumentException($"Excel文件路径：{pathOfExcel} 不存在！");
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="commandText">SQL clause</param>
        /// <param name="tableName">data table‘s name</param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(string commandText,string tableName)
        {
            using OleDbConnection connection = new OleDbConnection(this.connectionStr);
            using OleDbCommand command = connection.CreateCommand();
            command.CommandText = commandText;
            using OleDbDataAdapter dataAdapter = new OleDbDataAdapter(command);
            DataTable dataTable = new DataTable(tableName);
            dataAdapter.Fill(dataTable);
            return dataTable;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="commandTextList"> list of SQL clauses</param>
        /// <param name="tableNameList"> names list of data tables,each name correspond to a sql clause executed result.</param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(List<string> commandTextList,List<string> tableNameList )
        {
            if (commandTextList.Count != tableNameList.Count)
                throw new ArgumentException("传入的SQL查询字符串列表与结果表名字符串列表数量不一致！");
            DataSet dataSet = new DataSet();
            for (int i = 0; i < commandTextList.Count; i++)
            {
                DataTable dataTable = ExecuteDataTable(commandTextList[i], tableNameList[i]);
                dataSet.Tables.Add(dataTable);
            }
            return dataSet;
        }
        /// <summary>
        /// get all contents of given tables.
        /// </summary>
        /// <param name="tableNameList">names list of data tables(sheets in Excel)</param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(List<string> tableNameList)
        {
            DataSet dataSet = new DataSet();
            foreach (var name in tableNameList)
            {
                DataTable dataTable = ExecuteDataTable($"SELECT * FROM [{name}$]", name);
                dataSet.Tables.Add(dataTable);
            }
            return dataSet;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="commandTexts"> sql clauses.</param>
        public void ExecuteNoneQuery(List<string> commandTexts)
        {
            using OleDbConnection connection = new OleDbConnection(this.connectionStr);
            connection.Open();
            using OleDbCommand command = connection.CreateCommand();
            using OleDbTransaction transaction = connection.BeginTransaction();
            command.Transaction = transaction;
            try
            {
                foreach (var item in commandTexts)
                {
                    command.CommandText = item;
                    command.ExecuteNonQuery();
                }
                transaction.Commit();
            }
            catch (OleDbException exp)
            {
                transaction.Rollback();
                throw new Exception("SQL语句执行错误。\r\n"+ exp.Message+"\r\n"+exp.StackTrace);
            }
            finally
            {
                transaction.Dispose();
                command.Dispose();
                connection.Close();
            }
        }
    }
}