C與C++ 無參函數的差別解析。本站提示廣大學習愛好者:(C與C++ 無參函數的差別解析)文章只能為提供參考,不一定能成為您想要的結果。以下是C與C++ 無參函數的差別解析正文
在現實項目標開辟進程中,所觸及的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便可正常讀取
代碼運轉後果以下圖所示: