---OleDbDataAdapter___FillSchema----
---OleDbDataAdapter___FillSchema----
/// <summary>
/// 取得到该Excle的数据连接
/// </summary>
/// <param name="strFilePath"></param>
/// <returns></returns>
string GetExcelConnection(string strFilePath)
{
if (!File.Exists(strFilePath))
{
throw new Exception("File not exsit!");
}
return
@"Provider=Microsoft.Jet.OLEDB.4.0;" +
@"Data Source=" + strFilePath + ";" +
@"Extended Properties=" + Convert.ToChar(34).ToString() +
@"Excel 8.0;" + "Imex=2;HDR=Yes;" + Convert.ToChar(34).ToString();
}
/// <summary>
/// 根据文件路径和sheet名字,读取excel内容,返回DataTable
/// </summary>
/// <param name="FilePath">文件全路径-"E:\\Work\\Investor.xls"</param>
/// <param name="WorkSheetName">Excel文件的sheet名字</param>
/// <returns></returns>
public DataTable GetExcelData(string FilePath, string WorkSheetName)
{
DataTable dtExcel = new DataTable();
OleDbConnection con = new OleDbConnection(GetExcelConnection(FilePath));
OleDbDataAdapter adapter = new OleDbDataAdapter("Select * from [" + WorkSheetName + "$]", con);
con.Open();
//FillSchema 方法使用 SelectCommand 从数据源中检索架构。与 SelectCommand 关联的连接对象必须有效,但不需要将其打开。如果调用 FillSchema 之前连接已关闭,则将其打开以检索数据,然后再将其关闭。如果调用 FillSchema 之前连接已打开,它将保持打开状态。
adapter.FillSchema(dtExcel, SchemaType.Mapped);
adapter.Fill(dtExcel);
con.Close();
dtExcel.TableName = WorkSheetName;
return dtExcel;
}

浙公网安备 33010602011771号