C# 如何获取指定路径下的Excel文件并转换成DataTable

Question

如题所述

Answer 1

拣出来给你了，自己稍微修改下。

/// <summary>
/// 根据传入的Excel表输出Datatable
/// </summary>
/// <param name="strExcelFileName">Excel表路径及文件名称</param>
/// <param name="strTableName">数据表名</param>
/// <returns>DataTable:mytable</returns>
public System.Data.DataTable LeadIn(string strExcelFileName, string strSheetName)
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strExcelFileName + ";" + "Extended Properties='Excel 8.0;HDR=NO;IMEX=1';";
string strExcelSql = string.Format("select * from [{0}$]", strSheetName);
System.Data.DataSet ds = new DataSet();
OleDbConnection conn = new OleDbConnection(strConn);
try
{
OleDbDataAdapter adapter = new OleDbDataAdapter(strExcelSql, strConn);
adapter.Fill(ds, "mytable");
return ds.Tables["mytable"];
}
catch (System.Data.OleDb.OleDbException e)
{
System.Console.WriteLine("OleDbException:{0}", e);
}
finally
{
conn.Close();
}
ds.Tables.Add("mytable");
return ds.Tables[0];
}

Answer 2

publicDataSet ImportExcel(string strFileName) //strFileName指定的路径+文件名.xls
{
if (strFileName != "")
{
string conn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFileName + ";Extended Properties=Excel 8.0";
string sql = "select * from [Sheet1$]";
OleDbDataAdapter da = newOleDbDataAdapter(sql, conn);
DataSet ds = newDataSet();
try
{
da.Fill(ds, "datatable");
}
catch
{

}
return ds;
}
else
{
return null;
}
}

这个方法我经常用，转成dataset和datatable没什么太大区别吧，反正ds.Tables[0]就是dataTable了本回答被提问者采纳

Answer 3

oledb的方式，就像查询数据库一样，把excel表格里的东西都查询出来到DATASET中，之后赋值给datatable