110,533
社区成员
发帖
与我相关
我的任务
分享
/// <summary>
/// 获取excel数据,返回一个dateset
/// </summary>
/// <param name="filePath">excel的物理路径</param>
/// <param name="sheet">第几个sheet</param>
/// <returns></returns>
public static DataSet LoadDataFromExcel(string filePath, int sheet)
{
try
{
string strConn = "";
if (filePath.EndsWith("xls"))
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
else if (filePath.EndsWith("xlsx"))
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
else
return null;
OleDbConnection OleConn = new OleDbConnection(strConn);
OleConn.Open();
String sql = "SELECT * FROM [Sheet" + sheet + "$]";//可是更改Sheet名称,比如sheet2,等等
OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
DataSet OleDsExcle = new DataSet();
OleDaExcel.Fill(OleDsExcle, "Sheet" + sheet);
OleConn.Close();
return OleDsExcle;
}
catch (Exception err)
{
return null;
}
}
string ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + ";Extended Properties=\"Excel 8.0;HDR=no;IMEX=0\"";
string StrSQL = "SELECT * FROM [Sheet1$]";
System.Data.OleDb.OleDbDataAdapter DataAda = new System.Data.OleDb.OleDbDataAdapter(StrSQL, ConnectionString);
DataSet DSTemp = new DataSet();
DataAda.Fill(DSTemp);
DataAda.Dispose();