.Net读取xlsx文件Excel2007
.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 |
} |
