C#常用工具類——Excel操作類

標籤：blog   http   os   io   檔案   資料   for   ar   

/// 常用工具類——Excel操作類    /// <para>　------------------------------------------------</para>    /// <para>　CreateConnection：根據Excel檔案路徑和EXCEL驅動版本產生OleConnection對象執行個體</para>    /// <para>　ExecuteDataSet：執行一條SQL語句，返回一個DataSet對象</para>    /// <para>　ExecuteDataTable：執行一條SQL語句，返回一個DataTable對象</para>    /// <para>　ExecuteDataAdapter：表示一組資料命令和一個資料庫連接，它們用於填充 DataSet 和更新資料來源。</para>    /// <para>　ExecuteNonQuery：執行資料庫語句返回受影響的行數，失敗或異常返回-1[通常為:INSERT、DELETE、UPDATE 和 SET 語句等命令]。</para>    /// <para>　ExecuteScalar：執行資料庫語句返回第一行第一列，失敗或異常返回null</para>    /// <para>　ExecuteDataReader：執行資料庫語句返回一個自進結果集流</para>    /// <para>　GetWorkBookName：擷取Excel中的所有活頁簿</para>using System;using System.Configuration;using System.Web;using System.Data;using System.Data.OleDb;using System.Data.SqlClient; namespace Utils{    /// <summary>    /// <para>　</para>    /// 常用工具類——Excel操作類    /// <para>　------------------------------------------------</para>    /// <para>　CreateConnection：根據Excel檔案路徑和EXCEL驅動版本產生OleConnection對象執行個體</para>    /// <para>　ExecuteDataSet：執行一條SQL語句，返回一個DataSet對象</para>    /// <para>　ExecuteDataTable：執行一條SQL語句，返回一個DataTable對象</para>    /// <para>　ExecuteDataAdapter：表示一組資料命令和一個資料庫連接，它們用於填充 DataSet 和更新資料來源。</para>    /// <para>　ExecuteNonQuery：執行資料庫語句返回受影響的行數，失敗或異常返回-1[通常為:INSERT、DELETE、UPDATE 和 SET 語句等命令]。</para>    /// <para>　ExecuteScalar：執行資料庫語句返回第一行第一列，失敗或異常返回null</para>    /// <para>　ExecuteDataReader：執行資料庫語句返回一個自進結果集流</para>    /// <para>　GetWorkBookName：擷取Excel中的所有活頁簿</para>    /// </summary>    public class ExcelHelper    {        private ExcelHelper() { }         #region EXCEL版本        /// <summary>        /// EXCEL版本        /// </summary>        public enum ExcelVerion        {            /// <summary>            /// Excel97-2003版本            /// </summary>            Excel2003,            /// <summary>            /// Excel2007版本            /// </summary>            Excel2007        }        #endregion         #region 根據EXCEL路徑產生OleDbConnectin對象        /// <summary>        /// 根據EXCEL路徑產生OleDbConnectin對象        /// </summary>        /// <param name="ExcelFilePath">EXCEL檔案相對於網站根目錄的路徑</param>        /// <param name="Verion">Excel資料驅動版本：97-2003或2007,分別需要安裝資料驅動軟體</param>        /// <returns>OleDbConnection對象</returns>        public static OleDbConnection CreateConnection(string ExcelFilePath,ExcelVerion Verion)        {            OleDbConnection Connection = null;            string strConnection = string.Empty;            try            {                switch (Verion)                {                    case ExcelVerion.Excel2003: //讀取Excel97-2003版本                        strConnection = "Provider=Microsoft.Jet.OLEDB.4.0; " +"Data Source=" + HttpContext.Current.Server.MapPath(ExcelFilePath) + ";Extended Properties=Excel 8.0";                        break;                    case ExcelVerion.Excel2007: //讀取Excel2007版本                         strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=‘Excel 12.0;HDR=YES‘;data source=" + ExcelFilePath;                        break;                }                              if(!string.IsNullOrEmpty(strConnection)) Connection = new OleDbConnection(strConnection);            }            catch (Exception)            {            }             return Connection;        }                #endregion         #region 建立一個OleDbCommand對象執行個體        /// <summary>        /// 建立一個OleDbCommand對象執行個體        /// </summary>        /// <param name="CommandText">SQL命令</param>        /// <param name="Connection">資料庫連接對象執行個體OleDbConnection</param>        /// <param name="OleDbParameters">選擇性參數</param>        /// <returns></returns>        private static OleDbCommand CreateCommand(string CommandText, OleDbConnection Connection, params System.Data.OleDb.OleDbParameter[] OleDbParameters)        {            if (Connection.State == ConnectionState.Closed)                Connection.Open();            OleDbCommand comm = new OleDbCommand(CommandText, Connection);            if (OleDbParameters != null)            {                foreach (OleDbParameter parm in OleDbParameters)                {                    comm.Parameters.Add(parm);                }            }            return comm;        }        #endregion         #region 執行一條SQL語句，返回一個DataSet對象        /// <summary>        /// 執行一條SQL語句，返回一個DataSet對象        /// </summary>        /// <param name="Connection">OleDbConnection對象</param>        /// <param name="CommandText">SQL語句</param>        /// <param name="OleDbParameters">OleDbParameter選擇性參數</param>        /// <returns>DataSet對象</returns>        public static DataSet ExecuteDataSet(OleDbConnection Connection, string CommandText, params OleDbParameter[] OleDbParameters)        {            DataSet ds = new DataSet();            try            {                OleDbCommand comm = CreateCommand(CommandText, Connection, OleDbParameters);                OleDbDataAdapter da = new OleDbDataAdapter(comm);                da.Fill(ds);            }            catch (Exception)            {            }            finally            {                if (Connection.State == ConnectionState.Open) Connection.Close();            }             return ds;        }        #endregion         #region 執行一條SQL語句,返回一個DataTable對象        /// <summary>        /// 執行一條SQL語句,返回一個DataTable對象        /// </summary>        /// <param name="Connection">OleDbConnection對象</param>        /// <param name="CommandText">SQL語句</param>        /// <param name="OleDbParameters">OleDbParameter選擇性參數</param>        /// <returns>DataSet對象</returns>        public static DataTable ExecuteDataTable(OleDbConnection Connection, string CommandText, params OleDbParameter[] OleDbParameters)        {            DataTable Dt = null;            try            {                OleDbCommand comm = CreateCommand(CommandText, Connection, OleDbParameters);                OleDbDataAdapter da = new OleDbDataAdapter(comm);                DataSet Ds = new DataSet();                da.Fill(Ds);                Dt = Ds.Tables[0];            }            catch (Exception)            {            }            finally            {                if (Connection.State == ConnectionState.Open) Connection.Close();            }            return Dt;        }         #endregion         #region 表示一組資料命令和一個資料庫連接，它們用於填充 DataSet 和更新資料來源。        /// <summary>        /// 表示一組資料命令和一個資料庫連接，它們用於填充 DataSet 和更新資料來源。        /// </summary>        /// <param name="Connection">OleDbConnection對象</param>        /// <param name="CommandText">SQL語句</param>        /// <param name="OleDbParameters">OleDbParameter選擇性參數</param>        /// <returns></returns>        public static OleDbDataAdapter ExecuteDataAdapter(OleDbConnection Connection, string CommandText, params System.Data.OleDb.OleDbParameter[] OleDbParameters)        {            OleDbDataAdapter Da = null;            try            {                OleDbCommand comm = CreateCommand(CommandText, Connection, OleDbParameters);                Da = new OleDbDataAdapter(comm);                OleDbCommandBuilder cb = new OleDbCommandBuilder(Da);            }            catch (Exception)            {            }            finally            {                if (Connection.State == ConnectionState.Open) Connection.Close();            }            return Da;        }        #endregion         #region 執行資料庫語句返回受影響的行數，失敗或異常返回-1[通常為:INSERT、DELETE、UPDATE 和 SET 語句等命令]。        /// <summary>        /// 執行資料庫語句返回受影響的行數，失敗或異常返回-1[通常為:INSERT、DELETE、UPDATE 和 SET 語句等命令]。        /// </summary>        /// <param name="Connection">OleDbConnection對象</param>        /// <param name="CommandText">SQL語句</param>        /// <param name="OleDbParameters">OleDbParameter選擇性參數</param>        /// <returns>受影響的行數</returns>        public static int ExecuteNonQuery(OleDbConnection Connection, string CommandText, params System.Data.OleDb.OleDbParameter[] OleDbParameters)        {            int i = -1;            try            {                if (Connection.State == ConnectionState.Closed) Connection.Open();                OleDbCommand comm = CreateCommand(CommandText, Connection, OleDbParameters);                i = comm.ExecuteNonQuery();            }            catch (Exception)            {            }            finally            {                if (Connection.State == ConnectionState.Open) Connection.Close();            }            return i;        }        #endregion         #region 執行資料庫語句返回第一行第一列，失敗或異常返回null        /// <summary>        /// 執行資料庫語句返回第一行第一列，失敗或異常返回null        /// </summary>        /// <param name="Connection">OleDbConnection對象</param>        /// <param name="CommandText">SQL語句</param>        /// <param name="OleDbParameters">OleDbParameter選擇性參數</param>        /// <returns>第一行第一列的值</returns>        public static object ExecuteScalar(OleDbConnection Connection, string CommandText, params System.Data.OleDb.OleDbParameter[] OleDbParameters)        {            object Result = null;            try            {                OleDbCommand comm = CreateCommand(CommandText, Connection, OleDbParameters);                Result = comm.ExecuteScalar();            }            catch (Exception)            {            }            finally            {                if (Connection.State == ConnectionState.Open) Connection.Close();            }            return Result;        }        #endregion         #region 執行資料庫語句返回一個自進結果集流        /// <summary>        /// 執行資料庫語句返回一個自進結果集流        /// </summary>        /// <param name="Connection">OleDbConnection對象</param>        /// <param name="CommandText">SQL語句</param>        /// <param name="OleDbParameters">OleDbParameter選擇性參數</param>        /// <returns>DataReader對象</returns>        public static OleDbDataReader ExecuteDataReader(OleDbConnection Connection, string CommandText, params System.Data.OleDb.OleDbParameter[] OleDbParameters)        {            OleDbDataReader Odr = null;            try            {                OleDbCommand comm = CreateCommand(CommandText, Connection, OleDbParameters);                Odr = comm.ExecuteReader();            }            catch (Exception)            {            }            finally            {                if (Connection.State == ConnectionState.Open) Connection.Close();            }            return Odr;        }        #endregion         #region 擷取Excel中的所有活頁簿        /// <summary>        /// 擷取Excel中的所有活頁簿        /// </summary>        /// <param name="Connection">OleDbConnection對象</param>        /// <returns></returns>        public static DataTable GetWorkBookName(OleDbConnection Connection)        {            DataTable Dt = null;            try            {                if (Connection.State == ConnectionState.Closed) Connection.Open();                Dt = Connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);            }            catch (Exception)            {            }            finally            {                if (Connection.State == ConnectionState.Open) Connection.Close();            }            return Dt;        }        #endregion    }}