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

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