.NET 读取Excel 2007的xlsx文件和读取老的.xls文件是一样的,都是用Oledb读取,仅仅连接字符串不同而已。
读取xlsx 用的是Microsoft.Ace.OleDb.12.0;
具体操作方法如下:
public
static
DataTable GetExcelToDataTableBySheet(
string
FileFullPath,
string
SheetName)
{
//string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + FileFullPath + ";Extended Properties='Excel 8.0; HDR=NO; IMEX=1'"; //此连接只能操作Excel2007之前(.xls)文件
string
strConn =
"Provider=Microsoft.Ace.OleDb.12.0;"
+
"data source="
+ FileFullPath +
";Extended Properties='Excel 12.0; HDR=NO; IMEX=1'"
;
//此连接可以操作.xls与.xlsx文件
OleDbConnection conn =
new
OleDbConnection(strConn);
conn.Open();
DataSet ds =
new
DataSet();
OleDbDataAdapter odda =
new
OleDbDataAdapter(
string
.Format(
"SELECT * FROM [{0}]"
, SheetName), conn);
//("select * from [Sheet1$]", conn);
odda.Fill(ds, SheetName);
conn.Close();
return
ds.Tables[0];
}
读取Excel文件时,可能一个文件中会有多个Sheet,因此获取Sheet的名称是非常有用的。
具体操作方法如下:
//根据Excel物理路径获取Excel文件中所有表名
public
static
String[] GetExcelSheetNames(
string
excelFile)
{
OleDbConnection objConn =
null
;
System.Data.DataTable dt =
null
;
try
{
//string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + excelFile + ";Extended Properties='Excel 8.0; HDR=NO; IMEX=1'"; //此连接只能操作Excel2007之前(.xls)文件
string
strConn =
"Provider=Microsoft.Ace.OleDb.12.0;"
+
"data source="
+ excelFile +
";Extended Properties='Excel 12.0; HDR=NO; IMEX=1'"
;
//此连接可以操作.xls与.xlsx文件
objConn =
new
OleDbConnection(strConn);
objConn.Open();
dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
null
);
if
(dt ==
null
)
{
return
null
;
}
String[] excelSheets =
new
String[dt.Rows.Count];
int
i = 0;
foreach
(DataRow row
in
dt.Rows)
{
excelSheets[i] = row[
"TABLE_NAME"
].ToString();
i++;
}
return
excelSheets;
}
catch
{
return
null
;
}
finally
{
if
(objConn !=
null
)
{
objConn.Close();
objConn.Dispose();
}
if
(dt !=
null
)
{
dt.Dispose();
}
}
}
|
命名空间:using System.IO;
返回路径:StreamReader sr = new StreamReader(@"路径", System.Text.Encoding.GetEncoding("GB18030")); //[设置中文编码]
读取文件:textBox1.Text = sr.ReadToEnd();
关闭流:sr.Close();