WinForm項目開發中Excel用法實例解析

2020-01-24 02:34:35

字體：大中小

來源：轉載

供稿：網友

在實際項目的開發過程中，所涉及的EXCEL往往會比較復雜，并且列中還會帶有一些計算公式，這就給讀取帶來了很大的困難，曾經嘗試過一些免費的第三方dll，譬如Myxls，NPOI，IExcelDataReader都會出現一些問題，最后采用OLEDB形式讀取，再x64操作系統上有點問題，不過采用小技巧即可解決，可以參考鏈接地址：http://ellisweb.net/2010/01/connecting-to-excel-and-access-files-using-net-on-a-64-bit-server/

封裝代碼如下：

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);  }}

若在x64操作系統，將第二個參數設置true，并且按照AccessDatabaseEngine_X64.exe即可正常讀取

代碼運行效果如下圖所示：