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

在实际项目的开发过程中，所涉及的excel往往会比较复杂，并且列中还会带有一些计算公式，这就给读取带来了很大的困难，曾经尝试过一些免费的第三方dll，譬如myxls，npoi，iexceldatareader都会出现一些问题，最后采用oledb形式读取，再x64操作系统上有点问题，不过采用小技巧即可解决，可以参考链接地址：

封装代码如下：

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即可正常读取

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