WinForm项目开发中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即可正常读取

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