C#主動斷定Excel版本應用分歧的銜接字符串。本站提示廣大學習愛好者:(C#主動斷定Excel版本應用分歧的銜接字符串)文章只能為提供參考,不一定能成為您想要的結果。以下是C#主動斷定Excel版本應用分歧的銜接字符串正文
用OLEDB經由過程設置銜接字符串可以像讀取sqlserver一樣將excel中的數據讀掏出來,然則excel2003和excel2007/2010的銜接字符串是分歧的。
/// <summary> /// 把數據從Excel裝載到DataTable /// </summary> /// <param name="pathName">領路徑的Excel文件名</param> /// <param name="sheetName">任務表名</param> /// <param name="tbContainer">將數據存入的DataTable</param> /// <returns></returns> public DataTable ExcelToDataTable(string pathName, string sheetName) { DataTable tbContainer = new DataTable(); string strConn = string.Empty; if (string.IsNullOrEmpty(sheetName)) { sheetName = "Sheet1"; } FileInfo file = new FileInfo(pathName); if (!file.Exists) { throw new Exception("文件不存在"); } string extension = file.Extension; switch (extension) { case ".xls": strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'"; break; case ".xlsx": strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pathName + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'"; break; default: strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'"; break; } //鏈接Excel OleDbConnection cnnxls = new OleDbConnection(strConn); //讀取Excel外面有 表Sheet1 OleDbDataAdapter oda = new OleDbDataAdapter(string.Format("select * from [{0}$]", sheetName), cnnxls); DataSet ds = new DataSet(); //將Excel外面有表內容裝載到內存表中! oda.Fill(tbContainer); return tbContainer; }
這裡須要留意的處所是,當文件的後綴名為.xlsx(excel2007/2010)時的銜接字符串是"Provider=Microsoft.ACE.OLEDB.12.0;....",留意中央白色部門不是"Jet"。