[WinForm]项目开发中Excel使用小计

实际项目开发中，所涉及的Excel会比较复杂，而且列中会带有一些计算公式，这给读取带来困难，尝试过一些免费的第三方dll，譬如Myxls，NPOI，IExcelDataReader都会出现一些问题，最后采用OLEDB形式读取，再x64操作系统上有点问题，不过采用小技巧即可解决，参考链接：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即可正常读取

代码效果