WinForm项目开发中Excel用法实例解析

namespace DBUtilHelpV2{public class OLEDBExcelToolV2{static readonly string xls = ".xls";static readonly string xlsx = ".xlsx";string _ExcelExtension = string.Empty;//后缀string _ExcelPath = string.Empty;//路径string _ExcelConnectString = string.Empty;//链接字符串static bool _X64Version = false;//是否强制使用x64链接字符串，即xlsx形式public OLEDBExcelToolV2(string excelPath, bool x64Version){  if (string.IsNullOrEmpty(excelPath)) throw new ArgumentNullException("excelPath");  if (!File.Exists(excelPath)) throw new ArgumentException("excelPath");  string _excelExtension = Path.GetExtension(excelPath);  _ExcelExtension = _excelExtension.ToLower();  _ExcelPath = excelPath;  _X64Version = x64Version;  _ExcelConnectString = BuilderConnectionString();}/// <summary>/// 创建链接字符串/// </summary>/// <returns></returns>private string BuilderConnectionString(){  Dictionary<string, string> _connectionParameter = new Dictionary<string, string>();  if (!_ExcelExtension.Equals(xlsx) && !_ExcelExtension.Equals(xls))  { throw new ArgumentException("excelPath");  }  if (!_X64Version)  { if (_ExcelExtension.Equals(xlsx)) {   // XLSX - Excel 2007, 2010, 2012, 2013   _connectionParameter["Provider"] = "Microsoft.ACE.OLEDB.12.0;";   _connectionParameter["Extended Properties"] = "'Excel 12.0 XML;IMEX=1'"; } else if (_ExcelExtension.Equals(xls)) {   // XLS - Excel 2003 and Older   _connectionParameter["Provider"] = "Microsoft.Jet.OLEDB.4.0";   _connectionParameter["Extended Properties"] = "'Excel 8.0;IMEX=1'"; }  }  else  { _connectionParameter["Provider"] = "Microsoft.ACE.OLEDB.12.0;"; _connectionParameter["Extended Properties"] = "'Excel 12.0 XML;IMEX=1'";  }  _connectionParameter["Data Source"] = _ExcelPath;  StringBuilder _connectionString = new StringBuilder();  foreach (KeyValuePair<string, string> parameter in _connectionParameter)  { _connectionString.Append(parameter.Key); _connectionString.Append('='); _connectionString.Append(parameter.Value); _connectionString.Append(';');  }  return _connectionString.ToString();}/// <summary>/// Excel操作/// DELETE不支持/// </summary>/// <param name="sql"></param>/// <returns></returns>public int ExecuteNonQuery(string sql){  int _affectedRows = -1;  using (OleDbConnection sqlcon = new OleDbConnection(_ExcelConnectString))  { try {   sqlcon.Open();   using (OleDbCommand sqlcmd = new OleDbCommand(sql, sqlcon))   { _affectedRows = sqlcmd.ExecuteNonQuery();   } } catch (Exception) {   return -1; }  }  return _affectedRows;}/// <summary>/// Excel操作///获取EXCEL内sheet集合/// </summary>/// <param name="sql"></param>/// <returns></returns>public string[] GetExcelSheetNames(){  DataTable _schemaTable = null;  using (OleDbConnection sqlcon = new OleDbConnection(_ExcelConnectString))  { try {   sqlcon.Open();   _schemaTable = sqlcon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);   String[] _excelSheets = new String[_schemaTable.Rows.Count];   int i = 0;   foreach (DataRow row in _schemaTable.Rows)   { _excelSheets[i] = row["TABLE_NAME"].ToString().Trim(); i++;   }   return _excelSheets; } catch (Exception) {   return null; } finally {   if (_schemaTable != null)   { _schemaTable.Dispose();   } }  }}/// <summary>/// 读取sheet/// eg:select * from [Sheet1$]/// </summary>/// <param name="sql"></param>/// <returns></returns>public DataTable ExecuteDataTable(string sql){  using (OleDbConnection sqlcon = new OleDbConnection(_ExcelConnectString))  { try {   using (OleDbCommand sqlcmd = new OleDbCommand(sql, sqlcon))   { using (OleDbDataAdapter sqldap = new OleDbDataAdapter(sqlcmd)) {   DataTable _dtResult = new DataTable();   sqldap.Fill(_dtResult);   return _dtResult; }   } } catch (Exception) {   return null; }  }}/// <summary>/// 获取excel所有sheet数据/// </summary>/// <returns>DataSet</returns>public DataSet ExecuteDataSet(){  DataSet _excelDb = null;  using (OleDbConnection sqlcon = new OleDbConnection(_ExcelConnectString))  { try {   sqlcon.Open();   DataTable _schemaTable = sqlcon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);   if (_schemaTable != null)   { int i = 0; _excelDb = new DataSet(); foreach (DataRow row in _schemaTable.Rows) {   string _sheetName = row["TABLE_NAME"].ToString().Trim();   string _sql = string.Format("select * from [{0}]", _sheetName);   using (OleDbCommand sqlcmd = new OleDbCommand(_sql, sqlcon))   {  using (OleDbDataAdapter sqldap = new OleDbDataAdapter(sqlcmd))  {    DataTable _dtResult = new DataTable();    _dtResult.TableName = _sheetName;    sqldap.Fill(_dtResult);    _excelDb.Tables.Add(_dtResult);  }   }   i++; }   } } catch (Exception) {   return null; }  }  return _excelDb;}}}

/// <summary>/// 合并EXCEL数据/// </summary>/// <param name="_excelPath">excel路径</param>private void HandleMergeExcel(string _excelPath){  if (!string.IsNullOrEmpty(_excelPath))  { OLEDBExcelToolV2 _excelHelper = new OLEDBExcelToolV2(_excelPath, true); DataSet _excelSource = _excelHelper.ExecuteDataSet(); HandleExcelSource(_excelSource);  }}