private DataTable CopyExceltoDatatable(string path, string filename)
{
string sheetname="";
DataTable dtexcel = new DataTable();
string excelConnString = "Provider=Microsoft.ACE.OLEDB.12.0;OLE DB Services=-4;Data Source=" + path +"\\"+filename + ";Excel 12.0;HDR=Yes;IMEX=1";
using (OleDbConnection excelConnection = new OleDbConnection(excelConnString))
{
excelConnection.Open();
DataTable Sheets = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
DataRow schemaRow = Sheets.Rows[0];
sheetname = schemaRow["TABLE_NAME"].ToString();
if (!sheetname.EndsWith("_"))
{
string query = "SELECT * FROM [" + sheetname + "]";
OleDbDataAdapter daexcel = new OleDbDataAdapter(query, excelConnection);
daexcel.Fill(dtexcel);
}
return dtexcel;
}
}