C#常用工具类——Excel操作类
来源：互联网 发布：union在sql中的意思 编辑：程序博客网 时间：2024/06/14 17:24
原文出处：
http://www.cnblogs.com/wangchuang/p/5546345.html
/// 常用工具类——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 }}阅读全文
0 0
- C#常用工具类——Excel操作类
- Java常用工具类封装——封装对excel的操作
- J2EE常用工具类—Jdbc操作
- Excel操作类C#
- C# Excel操作类
- C#操作EXCEL类
- C#操作EXCEL类
- c# 操作excel类
- C# Excel操作类
- C#操作EXCEL类
- c#操作Excel类
- C#常用工具类
- Java常用工具类封装——String操作工具类
- Java常用工具类封装——String操作工具类
- Excel操作类C#版
- Excel操作类C#版
- C# 操作Excel的类
- Excel操作类C#版
- Tensorflow知识集合
- UML常用图的几种关系的总结
- [乐意黎原创]CentOS里VSFTPD指定ftp 根目录的配置
- 关于Eclipse新建Android环境下的百度地图工程学习经验总结
- java实现 HTTP/HTTPS请求绕过证书检测代码实现
- C#常用工具类——Excel操作类
- 6.适配器模式
- Centos启动nginx
- Kotlin基础教程-枚举类
- 脉冲云之持续集成（下篇）
- SQL Exception
- 从framework获取context
- 在旋转之后的已排序数组中查找一个数字
- 多线程练习程序 之 模拟火车票售票系统01版
