1     /// 常用工具类——Excel操作类  2     /// <para>　------------------------------------------------</para>  3     /// <para>　CreateConnection：根据Excel文件路径和EXCEL驱动版本生成OleConnection对象实例</para>  4     /// <para>　ExecuteDataSet：执行一条SQL语句，返回一个DataSet对象</para>  5     /// <para>　ExecuteDataTable：执行一条SQL语句，返回一个DataTable对象</para>  6     /// <para>　ExecuteDataAdapter：表示一组数据命令和一个数据库连接，它们用于填充 DataSet 和更新数据源。</para>  7     /// <para>　ExecuteNonQuery：执行数据库语句返回受影响的行数，失败或异常返回-1[通常为:INSERT、DELETE、UPDATE 和 SET 语句等命令]。</para>  8     /// <para>　ExecuteScalar：执行数据库语句返回第一行第一列，失败或异常返回null</para>  9     /// <para>　ExecuteDataReader：执行数据库语句返回一个自进结果集流</para> 10     /// <para>　GetWorkBookName：获取Excel中的所有工作簿</para> 11     /// </summary> 12     public class ExcelHelper 13     { 14         private ExcelHelper() { } 15  16         #region EXCEL版本 17         /// <summary> 18         /// EXCEL版本 19         /// </summary> 20         public enum ExcelVerion 21         { 22             /// <summary> 23             /// Excel97-2003版本 24             /// </summary> 25             Excel2003, 26             /// <summary> 27             /// Excel2007版本 28             /// </summary> 29             Excel2007 30         } 31         #endregion 32  33         #region 根据EXCEL路径生成OleDbConnectin对象 34         /// <summary> 35         /// 根据EXCEL路径生成OleDbConnectin对象 36         /// </summary> 37         /// <param name="ExcelFilePath">EXCEL文件相对于站点根目录的路径</param> 38         /// <param name="Verion">Excel数据驱动版本：97-2003或2007,分别需要安装数据驱动软件</param> 39         /// <returns>OleDbConnection对象</returns> 40         public static OleDbConnection CreateConnection(string ExcelFilePath, ExcelVerion Verion) 41         { 42             OleDbConnection Connection = null; 43             string strConnection = string.Empty; 44             try 45             { 46                 switch (Verion) 47                 { 48                     case ExcelVerion.Excel2003: //读取Excel97-2003版本 49                         strConnection = "Provider=Microsoft.Jet.OLEDB.4.0; " + 50 "Data Source=" + ExcelFilePath + ";Extended Properties=Excel 8.0"; 51                         break; 52                     case ExcelVerion.Excel2007: //读取Excel2007版本 53                         strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=‘Excel 12.0;HDR=YES‘;data source=" + ExcelFilePath; 54                         break; 55                 } 56                 if (!string.IsNullOrEmpty(strConnection)) Connection = new OleDbConnection(strConnection); 57             } 58             catch (Exception) 59             { 60             } 61  62             return Connection; 63         } 64         #endregion 65  66         #region 创建一个OleDbCommand对象实例 67         /// <summary> 68         /// 创建一个OleDbCommand对象实例 69         /// </summary> 70         /// <param name="CommandText">SQL命令</param> 71         /// <param name="Connection">数据库连接对象实例OleDbConnection</param> 72         /// <param name="OleDbParameters">可选参数</param> 73         /// <returns></returns> 74         private static OleDbCommand CreateCommand(string CommandText, OleDbConnection Connection, params System.Data.OleDb.OleDbParameter[] OleDbParameters) 75         { 76             if (Connection.State == ConnectionState.Closed) 77                 Connection.Open(); 78             OleDbCommand comm = new OleDbCommand(CommandText, Connection); 79             if (OleDbParameters != null) 80             { 81                 foreach (OleDbParameter parm in OleDbParameters) 82                 { 83                     comm.Parameters.Add(parm); 84                 } 85             } 86             return comm; 87         } 88         #endregion 89  90         #region 执行一条SQL语句，返回一个DataSet对象 91         /// <summary> 92         /// 执行一条SQL语句，返回一个DataSet对象 93         /// </summary> 94         /// <param name="Connection">OleDbConnection对象</param> 95         /// <param name="CommandText">SQL语句</param> 96         /// <param name="OleDbParameters">OleDbParameter可选参数</param> 97         /// <returns>DataSet对象</returns> 98         public static DataSet ExecuteDataSet(OleDbConnection Connection, string CommandText, params OleDbParameter[] OleDbParameters) 99         {100             DataSet ds = new DataSet();101             try102             {103                 OleDbCommand comm = CreateCommand(CommandText, Connection, OleDbParameters);104                 OleDbDataAdapter da = new OleDbDataAdapter(comm);105                 da.Fill(ds);106             }107             catch (Exception ex)108             {109                 throw ex;110             }111             finally112             {113                 if (Connection.State == ConnectionState.Open) Connection.Close();114             }115 116             return ds;117         }118         #endregion119 120         #region 执行一条SQL语句,返回一个DataTable对象121         /// <summary>122         /// 执行一条SQL语句,返回一个DataTable对象123         /// </summary>124         /// <param name="Connection">OleDbConnection对象</param>125         /// <param name="CommandText">SQL语句</param>126         /// <param name="OleDbParameters">OleDbParameter可选参数</param>127         /// <returns>DataSet对象</returns>128         public static DataTable ExecuteDataTable(OleDbConnection Connection, string CommandText, params OleDbParameter[] OleDbParameters)129         {130             DataTable Dt = null;131             try132             {133                 OleDbCommand comm = CreateCommand(CommandText, Connection, OleDbParameters);134                 OleDbDataAdapter da = new OleDbDataAdapter(comm);135                 DataSet Ds = new DataSet();136                 da.Fill(Ds);137                 Dt = Ds.Tables[0];138             }139             catch (Exception)140             {141             }142             finally143             {144                 if (Connection.State == ConnectionState.Open) Connection.Close();145             }146             return Dt;147         }148 149         #endregion150 151         #region 表示一组数据命令和一个数据库连接，它们用于填充 DataSet 和更新数据源。152         /// <summary>153         /// 表示一组数据命令和一个数据库连接，它们用于填充 DataSet 和更新数据源。154         /// </summary>155         /// <param name="Connection">OleDbConnection对象</param>156         /// <param name="CommandText">SQL语句</param>157         /// <param name="OleDbParameters">OleDbParameter可选参数</param>158         /// <returns></returns>159         public static OleDbDataAdapter ExecuteDataAdapter(OleDbConnection Connection, string CommandText, params System.Data.OleDb.OleDbParameter[] OleDbParameters)160         {161             OleDbDataAdapter Da = null;162             try163             {164                 OleDbCommand comm = CreateCommand(CommandText, Connection, OleDbParameters);165                 Da = new OleDbDataAdapter(comm);166                 OleDbCommandBuilder cb = new OleDbCommandBuilder(Da);167             }168             catch (Exception)169             {170             }171             finally172             {173                 if (Connection.State == ConnectionState.Open) Connection.Close();174             }175             return Da;176         }177         #endregion178 179         #region 执行数据库语句返回受影响的行数，失败或异常返回-1[通常为:INSERT、DELETE、UPDATE 和 SET 语句等命令]。180         /// <summary>181         /// 执行数据库语句返回受影响的行数，失败或异常返回-1[通常为:INSERT、DELETE、UPDATE 和 SET 语句等命令]。182         /// </summary>183         /// <param name="Connection">OleDbConnection对象</param>184         /// <param name="CommandText">SQL语句</param>185         /// <param name="OleDbParameters">OleDbParameter可选参数</param>186         /// <returns>受影响的行数</returns>187         public static int ExecuteNonQuery(OleDbConnection Connection, string CommandText, params System.Data.OleDb.OleDbParameter[] OleDbParameters)188         {189             int i = -1;190             try191             {192                 if (Connection.State == ConnectionState.Closed) Connection.Open();193                 OleDbCommand comm = CreateCommand(CommandText, Connection, OleDbParameters);194                 i = comm.ExecuteNonQuery();195             }196             catch (Exception)197             {198             }199             finally200             {201                 if (Connection.State == ConnectionState.Open) Connection.Close();202             }203             return i;204         }205         #endregion206 207         #region 执行数据库语句返回第一行第一列，失败或异常返回null208         /// <summary>209         /// 执行数据库语句返回第一行第一列，失败或异常返回null210         /// </summary>211         /// <param name="Connection">OleDbConnection对象</param>212         /// <param name="CommandText">SQL语句</param>213         /// <param name="OleDbParameters">OleDbParameter可选参数</param>214         /// <returns>第一行第一列的值</returns>215         public static object ExecuteScalar(OleDbConnection Connection, string CommandText, params System.Data.OleDb.OleDbParameter[] OleDbParameters)216         {217             object Result = null;218             try219             {220                 OleDbCommand comm = CreateCommand(CommandText, Connection, OleDbParameters);221                 Result = comm.ExecuteScalar();222             }223             catch (Exception)224             {225             }226             finally227             {228                 if (Connection.State == ConnectionState.Open) Connection.Close();229             }230             return Result;231         }232         #endregion233 234         #region 执行数据库语句返回一个自进结果集流235         /// <summary>236         /// 执行数据库语句返回一个自进结果集流237         /// </summary>238         /// <param name="Connection">OleDbConnection对象</param>239         /// <param name="CommandText">SQL语句</param>240         /// <param name="OleDbParameters">OleDbParameter可选参数</param>241         /// <returns>DataReader对象</returns>242         public static OleDbDataReader ExecuteDataReader(OleDbConnection Connection, string CommandText, params System.Data.OleDb.OleDbParameter[] OleDbParameters)243         {244             OleDbDataReader Odr = null;245             try246             {247                 OleDbCommand comm = CreateCommand(CommandText, Connection, OleDbParameters);248                 Odr = comm.ExecuteReader();249             }250             catch (Exception)251             {252             }253             finally254             {255                 if (Connection.State == ConnectionState.Open) Connection.Close();256             }257             return Odr;258         }259         #endregion260 261         #region 获取Excel中的所有工作簿262         /// <summary>263         /// 获取Excel中的所有工作簿264         /// </summary>265         /// <param name="Connection">OleDbConnection对象</param>266         /// <returns></returns>267         public static DataTable GetWorkBookName(OleDbConnection Connection)268         {269             DataTable Dt = null;270             try271             {272                 if (Connection.State == ConnectionState.Closed) Connection.Open();273                 Dt = Connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);274             }275             catch (Exception)276             {277             }278             finally279             {280                 if (Connection.State == ConnectionState.Open) Connection.Close();281             }282             return Dt;283         }284         #endregion285     }