1 private DataTable LoadDataTableFromExcel(string filePath)
2 {
3 try
4 {
5 string strConn;
6 if ("xlsx".Equals(Path.GetExtension(filePath).Substring(1), StringComparison.OrdinalIgnoreCase))
7 strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0;HDR=No;IMEX=1;'";
8 else
9 strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=No;IMEX=1;'";
10
11 //链接Excel
12 OleDbConnection cnnxls = new OleDbConnection(strConn);
13 cnnxls.Open();
14 DataTable schemaTable = cnnxls.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
15 string tableName = schemaTable.Rows[0][2].ToString().Trim();
16
17 //读取Excel里面有 表tableName
18 OleDbDataAdapter oda = new OleDbDataAdapter("select * from [" + tableName + "]", cnnxls);
19 DataSet ds = new DataSet();
20 //将Excel里面有表内容装载到内存表中！
21 oda.Fill(ds);
22 DataTable dt = ds.Tables[0];
23 return dt;
24 }
25 catch (Exception)
26 {
27 throw;
28 }
29 }
