Hi,
Try this code.
private DataTable ExcelToDataTable()
{
string conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Temp\\Sample.xlsx;Extended Properties='Excel 12.0 Xml;HDR=YES'";
OleDbConnection connExcel = new OleDbConnection(conStr);
connExcel.Open();
try
{
OleDbCommand cmdExcel = new OleDbCommand();
OleDbDataAdapter oda = new OleDbDataAdapter();
DataTable dt = new DataTable();
cmdExcel.Connection = connExcel;
DataTable dtexcelSchema;
dtexcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string sheetName = dtexcelSchema.Rows[0]["TABLE_NAME"].ToString();
connExcel.Close();
connExcel.Open();
cmdExcel.CommandText = "SELECT * FROM [" + sheetName + "]";
oda.SelectCommand = cmdExcel;
oda.Fill(dt);
connExcel.Close();
return dt;
}
catch (Exception ex)
{
connExcel.Close();
throw ex;
}
}