[WinForm]项目开发中Excel使用小计_.NET_编程开发_程序员俱乐部

实际项目开发中，所涉及的EXCEL会比较复杂，而且列中会带有一些计算公式，这给读取带来困难，尝试过一些免费的第三方dll，譬如Myxls，NPOI，IExcelDataReader都会出现一些问题，最后采用OLEDB形式读取，再x64操作系统上有点问题，不过采用小技巧即可解决，参考链接：Connecting to Excel and Access Files using .Net on a 64-bit Server

封装代码：

class="csharpcode">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即可正常读取

代码效果