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             try
102             {
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             finally
112             {
113                 if (Connection.State == ConnectionState.Open) Connection.Close();
114             }
115 
116             return ds;
117         }
118         #endregion
119 
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             try
132             {
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             finally
143             {
144                 if (Connection.State == ConnectionState.Open) Connection.Close();
145             }
146             return Dt;
147         }
148 
149         #endregion
150 
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             try
163             {
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             finally
172             {
173                 if (Connection.State == ConnectionState.Open) Connection.Close();
174             }
175             return Da;
176         }
177         #endregion
178 
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             try
191             {
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             finally
200             {
201                 if (Connection.State == ConnectionState.Open) Connection.Close();
202             }
203             return i;
204         }
205         #endregion
206 
207         #region 执行数据库语句返回第一行第一列，失败或异常返回null
208         /// <summary>
209         /// 执行数据库语句返回第一行第一列，失败或异常返回null
210         /// </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             try
219             {
220                 OleDbCommand comm = CreateCommand(CommandText, Connection, OleDbParameters);
221                 Result = comm.ExecuteScalar();
222             }
223             catch (Exception)
224             {
225             }
226             finally
227             {
228                 if (Connection.State == ConnectionState.Open) Connection.Close();
229             }
230             return Result;
231         }
232         #endregion
233 
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             try
246             {
247                 OleDbCommand comm = CreateCommand(CommandText, Connection, OleDbParameters);
248                 Odr = comm.ExecuteReader();
249             }
250             catch (Exception)
251             {
252             }
253             finally
254             {
255                 if (Connection.State == ConnectionState.Open) Connection.Close();
256             }
257             return Odr;
258         }
259         #endregion
260 
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             try
271             {
272                 if (Connection.State == ConnectionState.Closed) Connection.Open();
273                 Dt = Connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
274             }
275             catch (Exception)
276             {
277             }
278             finally
279             {
280                 if (Connection.State == ConnectionState.Open) Connection.Close();
281             }
282             return Dt;
283         }
284         #endregion
285     }