实际项目开发中，所涉及的Excel会比较复杂，而且列中会带有一些计算公式，这给读取带来困难，尝试过一些免费的第三方dll，譬如Myxls，NPOI，IExcelDataReader都会出现一些问题，最后采用OLEDB形式读取，再x64操作系统上有点问题，不过采用小技巧即可解决，参考链接：Connecting to Excel andaccessFiles using .Net on a 64-bit Server
封装代码：
namespaceDBUtilHelpV2
{publicclassOLEDBExcelToolV2
{staticreadonlystringxls =".xls";staticreadonlystringxlsx =".xlsx";string_ExcelExtension =string.Empty;//后缀string_ExcelPath =string.Empty;//路径string_ExcelConnectString =string.Empty;//链接字符串staticbool_X64Version =false;//是否强制使用x64链接字符串，即xlsx形式publicOLEDBExcelToolV2(stringexcelPath,boolx64Version)
{if(string.IsNullOrEmpty(excelPath))thrownewArgumentNullException("excelPath");if(!File.Exists(excelPath))thrownewArgumentException("excelPath");string_excelExtension = Path.GetExtension(excelPath);
_ExcelExtension = _excelExtension.ToLower();
_ExcelPath = excelPath;
_X64Version = x64Version;
_ExcelConnectString = BuilderConnectionString();
}/// <summary>/// 创建链接字符串/// </summary>/// <returns></returns>PRivatestringBuilderConnectionString()
{
Dictionary<string,string> _connectionParameter =newDictionary<string,string>();if(!_ExcelExtension.Equals(xlsx) && !_ExcelExtension.Equals(xls))
{thrownewArgumentException("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.0xml;IMEX=1'";
}elseif(_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 =newStringBuilder();foreach(KeyValuePair<string,string> parameterin_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>publicintExecuteNonQuery(stringsql)
{int_affectedRows = -1;using(OleDbConnection sqlcon =newOleDbConnection(_ExcelConnectString))
{try{
sqlcon.Open();using(OleDbCommand sqlcmd =newOleDbCommand(sql, sqlcon))
{
_affectedRows = sqlcmd.ExecuteNonQuery();
}
}catch(Exception)
{return-1;
}
}return_affectedRows;
}/// <summary>/// Excel操作///获取EXCEL内sheet集合/// </summary>/// <param name="sql"></param>/// <returns></returns>publicstring[] GetExcelSheetNames()
{
DataTable _schemaTable =null;using(OleDbConnection sqlcon =newOleDbConnection(_ExcelConnectString))
{try{
sqlcon.Open();
_schemaTable = sqlcon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,null);
String[] _excelSheets =newString[_schemaTable.Rows.Count];inti = 0;foreach(DataRow rowin_schemaTable.Rows)
{
_excelSheets[i] = row["TABLE_NAME"].ToString().Trim();
i++;
}return_excelSheets;
}catch(Exception)
{returnnull;
}finally{if(_schemaTable !=null)
{
_schemaTable.Dispose();
}
}
}
}/// <summary>/// 读取sheet/// eg:select * from [Sheet1$]/// </summary>/// <param name="sql"></param>/// <returns></returns>publicDataTable ExecuteDataTable(stringsql)
{using(OleDbConnection sqlcon =newOleDbConnection(_ExcelConnectString))
{try{using(OleDbCommand sqlcmd =newOleDbCommand(sql, sqlcon))
{using(OleDbDataAdapter sqldap =newOleDbDataAdapter(sqlcmd))
{
DataTable _dtResult =newDataTable();
sqldap.Fill(_dtResult);return_dtResult;
}
}
}catch(Exception)
{returnnull;
}
}
}/// <summary>/// 获取excel所有sheet数据/// </summary>/// <returns>DataSet</returns>publicDataSet ExecuteDataSet()
{
DataSet _excelDb =null;using(OleDbConnection sqlcon =newOleDbConnection(_ExcelConnectString))
{try{
sqlcon.Open();
DataTable _schemaTable = sqlcon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,null);if(_schemaTable !=null)
{inti = 0;
_excelDb =newDataSet();foreach(DataRow rowin_schemaTable.Rows)
{string_sheetName = row["TABLE_NAME"].ToString().Trim();string_sql =string.Format("select * from [{0}]", _sheetName);using(OleDbCommand sqlcmd =newOleDbCommand(_sql, sqlcon))
{using(OleDbDataAdapter sqldap =newOleDbDataAdapter(sqlcmd))
{
DataTable _dtResult =newDataTable();
_dtResult.TableName = _sheetName;
sqldap.Fill(_dtResult);
_excelDb.Tables.Add(_dtResult);
}
}
i++;
}
}
}catch(Exception)
{returnnull;
}
}return_excelDb;
}
}
}
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp{ background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }代码使用/// <summary>/// 合并EXCEL数据/// </summary>/// <param name="_excelPath">excel路径</param>privatevoidHandleMergeExcel(string_excelPath)
{if(!string.IsNullOrEmpty(_excelPath))
{
OLEDBExcelToolV2 _excelHelper =newOLEDBExcelToolV2(_excelPath,true);
DataSet _excelSource = _excelHelper.ExecuteDataSet();
HandleExcelSource(_excelSource);
}
}
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }若在x64操作系统，将第二个参数设置true，并且按照AccessDatabaseEngine_X64.exe即可正常读取
代码效果