I get the data from a sheet to datatable with the following code. Please check if it'll help you or not.
public static System.Data.DataTable FetchSheet(string ExcelFilePath, string SheetName)
{
try
{
string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + ExcelFilePath + ";" + "Extended Properties='Excel 8.0;HDR=YES;'";
OleDbConnection objConn = new OleDbConnection(sConnectionString);
objConn.Open();
OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [" + SheetName + "$]", objConn);
OleDbDataAdapter objAdapter = new OleDbDataAdapter();
objAdapter.SelectCommand = objCmdSelect;
System.Data.DataTable dtReturned = new System.Data.DataTable();
objAdapter.Fill(dtReturned);
objConn.Close();
return dtReturned;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return null;
}
}