這里貼的代碼是讀取本地文件系統中的Excel
//讀取EXCEL的方法 (用范圍區域讀取數據) PRivate static DataTable GetExcelTableByOleDB(string excelFilePath) { //Excel連接 OleDbConnection conn = null; DataTable dataTable = null; try { //數據表 DataSet ds = new DataSet(); //獲取文件擴展名 string extension = System.IO.Path.GetExtension(excelFilePath); string fileName = System.IO.Path.GetfileName(excelFilePath); switch (extension) { //HDR=YES,略過第一行數據;IMEX=1,只讀方式打開 case ".xls": conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFilePath + ";" + "Extended Properties=/"Excel 8.0;HDR=YES;IMEX=1;/""); break; case ".xlsx": conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFilePath + ";" + "Extended Properties=/"Excel 12.0;HDR=YES;IMEX=1;/""); break; default: conn = null; break; } if (conn == null) { return null; } conn.Open(); //獲取Excel中所有Sheet表的信息 System.Data.DataTable schemaTable = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null); //獲取Excel的第一個Sheet表名 string tableName = schemaTable.Rows[0][2].ToString().Trim(); string strSql = "select * from [" + tableName + "]"; //獲取Excel指定Sheet表中的信息 OleDbCommand objCmd = new OleDbCommand(strSql, conn); OleDbDataAdapter myData = new OleDbDataAdapter(strSql, conn); myData.Fill(ds, tableName);//填充數據 //dataTable即為excel文件中指定表中存儲的信息 dataTable = ds.Tables[tableName]; } catch(Exception e){ ExceptionHelper.throwException(e, "解析excel文件出錯"); } finally { if (conn != null) { conn.Close(); } } return dataTable; }新聞熱點
疑難解答
