#region 从Excel读入数据到DataSet
public static DataSet ExcelToDataSet(string strFilePath,string type)
{
string strConn="";
if (type == "excel2003")
{
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFilePath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
}
else if (type == "excel2007")
{
strConn = "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" + strFilePath + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";
}
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
DataTable sheetNames = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });//获得Excel中的所有sheetname
OleDbDataAdapter odda;
DataSet ds = new DataSet();
foreach (DataRow dr in sheetNames.Rows)
{
DataSet dsOne = new DataSet();
odda = new OleDbDataAdapter("select * from [" + dr[2] + "]", strConn);//dr[2] is sheetname
odda.Fill(dsOne);
if (dsOne.Tables.Count > 0)
{
DataTable dt = dsOne.Tables[0].Copy();
dt.TableName = dr[2].ToString();
ds.Tables.Add(dt);
}
}
conn.Close();
return ds;
}
#endregion
c# Excel转入到DataSet
最新推荐文章于 2021-04-27 09:53:23 发布