private DataTable GetDataTable(string sSheetName, string excelFile)
{
OleDbConnection objConn = null;
System.Data.DataTable dt = null;
try
{
// Connection String. Change the excel file to the file you
// will search.
string file = ConfigurationManager.AppSettings["Upload"].ToString() + excelFile;
String connString = "Provider=Microsoft.ACE.OLEDB.12.0;" +
"Data Source=" + file + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
// Create connection object by using the preceding connection string.
objConn = new OleDbConnection(connString);
// Open connection with the database.
objConn.Open();
string strConString = "SELECT * FROM [" + sSheetName + "]";
OleDbCommand objCmdSelect = new OleDbCommand(strConString, objConn);
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
// Pass the Select command to the adapter.
objAdapter1.SelectCommand = objCmdSelect;
// Create new DataSet to hold information from the worksheet.
DataSet ds = new DataSet();
objAdapter1.Fill(ds);
if (ds != null && ds.Tables.Count > 0)
{
dt = ds.Tables[0];
if (dt == null)
{
return null;
}
else return dt;
}
return null;
}
catch
{
return null;
}
finally
{
// Clean up.
if (objConn != null)
{
objConn.Close();
objConn.Dispose();
}
if (dt != null)
{
dt.Dispose();
}
}
}