[size=large]OLEDB 读取Excel2003、Excel2007内容为Table[/size]
private DataTable GetData(string Path, string exceltype)
{
try
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
if (exceltype == ".xlsx")
{
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Path + ";Extended Properties=" + "\"" + "Excel 12.0;HDR=Yes;IMEX=1" + "\"";
}
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
//得到所有sheet的名称
DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
string strExcel = "";
OleDbDataAdapter myCommand = null;
strExcel = "select * from [" + dtSheetName.Rows[0]["TABLE_NAME"].ToString() + "]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
DataTable dt = new DataTable();
myCommand.Fill(dt);
conn.Close();
return dt;
}
catch (Exception ex)
{
return new DataTable();
}
}
//注意:如果读取Excel2007的话请下载http://download.microsoft.com/download/7/0/3/703ffbcb-dc0c-4e19-b0da-1463960fdcdb/AccessDatabaseEngine.exe文件并安装