using System.Data;
using System.Data.OleDb;
/// <summary>
/// 取得Excel中数据
/// </summary>
/// <param name="_path"></param>
/// <returns></returns>
public DataSet XlsToDs(string _path)
{
string my_StrConnection;
string my_StrSelect;
DataSet my_Ds = null;
OleDbConnection my_conn = null;
OleDbDataAdapter my_Adapter;
OleDbCommandBuilder my_Builder;
try
{
//string strSheetName = GetExcelWorkSheet(_path);
string strSheetName = "SHEET1";
//数据库连接
my_StrConnection= @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+_path+";Extended Properties=Excel 8.0;";
//my_StrSelect="SELECT * FROM [SHEET1$]";
my_StrSelect="SELECT * FROM [" + strSheetName + "$]";
my_conn = new OleDbConnection(my_StrConnection);
my_conn.Open();
my_Adapter = new OleDbDataAdapter(my_StrSelect,my_conn);
my_Builder=new OleDbCommandBuilder(my_Adapter);
my_Ds=new DataSet();
//填充数据集
my_Adapter.Fill(my_Ds,"ExcelData");
my_conn.Close();
return my_Ds;
}
catch
{
if( my_conn != null )
{
my_conn.Close();
}
return null;
}
}
以上方法对excel2007以前版本有效,但对excel2007就力不从心了,下面给出可以读取所有版本的代码:
读取xlsx 用的是Microsoft.Ace.OleDb.12.0;
具体操作方法如下:
public
static
DataTable GetExcelToDataTableBySheet(
string
FileFullPath,
string
SheetName)
{
string
strConn =
"Provider=Microsoft.Ace.OleDb.12.0;"
+
"data source="
+ FileFullPath +
";Extended Properties='Excel 12.0; HDR=NO; IMEX=1'"
;
OleDbConnection conn =
new
OleDbConnection(strConn);
conn.Open();
DataSet ds =
new
DataSet();
OleDbDataAdapter odda =
new
OleDbDataAdapter(
string
.Format(
"SELECT * FROM [{0}]"
, SheetName), conn);
odda.Fill(ds, SheetName);
conn.Close();
return
ds.Tables[0];
}
读取Excel文件时,可能一个文件中会有多个Sheet,因此获取Sheet的名称是非常有用的。
具体操作方法如下:
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();
}