excel

NET 读取Excel 2007的xlsx文件和读取老的.xls文件是一样的，都是用Oledb读取,仅仅连接字符串不同而已。 

02 读取xlsx 用的是Microsoft.Ace.OleDb.12.0; 

03 具体操作方法如下： 

04 public static DataTable GetExcelToDataTableBySheet(string FileFullPath, string SheetName) 

05 { 

06 //string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + FileFullPath + ";Extended Properties='Excel 8.0; HDR=NO; IMEX=1'"; //此连接只能操作Excel2007之前(.xls)文件 

07 string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + FileFullPath + ";Extended Properties='Excel 12.0; HDR=NO; IMEX=1'"; //此连接可以操作.xls与.xlsx文件 

08 OleDbConnection conn = new OleDbConnection(strConn); 

09 conn.Open(); 

10 DataSet ds = new DataSet(); 

11 OleDbDataAdapter odda = new OleDbDataAdapter(string.Format("SELECT * FROM [{0}]", SheetName), conn); //("select * from [Sheet1$]", conn); 

12 odda.Fill(ds, SheetName); 

13 conn.Close(); 

14 return ds.Tables[0]; 

15 

16 } 

17 

18 

19 读取Excel文件时，可能一个文件中会有多个Sheet，因此获取Sheet的名称是非常有用的。 

20 

21 具体操作方法如下： 

22 

23 //根据Excel物理路径获取Excel文件中所有表名 

24 

25 public static String[] GetExcelSheetNames(string excelFile) 

26 { 

27 OleDbConnection objConn = null; 

28 System.Data.DataTable dt = null; 

29 

30 try

31 { 

32 //string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + excelFile + ";Extended Properties='Excel 8.0; HDR=NO; IMEX=1'"; //此连接只能操作Excel2007之前(.xls)文件 

33 

34 string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + excelFile + ";Extended Properties='Excel 12.0; HDR=NO; IMEX=1'"; //此连接可以操作.xls与.xlsx文件 

35 objConn = new OleDbConnection(strConn); 

36 objConn.Open(); 

37 dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); 

38 if (dt == null) 

39 { 

40 return null; 

41 } 

42 String[] excelSheets = new String[dt.Rows.Count]; 

43 int i = 0; 

44 foreach (DataRow row in dt.Rows) 

45 { 

46 excelSheets[i] = row["TABLE_NAME"].ToString(); 

47 i++; 

48 } 

49 

50 return excelSheets; 

51 } 

52 catch

53 { 

54 return null; 

55 } 

56 finally

57 { 

58 if (objConn != null) 

59 { 

60 objConn.Close(); 

61 objConn.Dispose(); 

62 } 

63 if (dt != null) 

64 { 

65 dt.Dispose(); 

66 } 

67 } 

68 }