将excel文件中的数据读取到DataTable中
//读取excel文件数据
public static DataTable ExcelToDataTable(string excelFilePath, string sheetName)
{
//采用OleDB方式读取EXCEL文件
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelFilePath + ";" + "Extended Properties='Excel 8.0;HDR=NO;IMEX=1';";
string strExcel = "select * from [sheet1$]";
DataSet ds = new DataSet();
//创建连接数据源
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
//适配到数据源
OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, strConn);
adapter.Fill(ds, sheetName);
conn.Close();
return ds.Tables[sheetName];
}
方法调用:ExcelToDataTable("D:\XX\aaa.xls","Sheet1")