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

摘要：在实际项目的开发过程中，所涉及的EXCEL往往会比较复杂，并且列中还会带有一些计算公式，这就给读取带来了很大的困难，曾经尝试过一些免费的第三...

在实际项目的开发过程中，所涉及的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即可正常读取

代码运行效果如下图所示：

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