There are some problems in your code:
the first one is that you build the connection string with the file name and not with the path of the Excel file;
then you use a provider (
Microsoft.ACE.OLEDB.4.0
) that doesn't exist.
The IMEX value is wrong, but I think that it isn't the main problem.
In the following I have tried to revise it:
private string GetConnection(string filePath)
{
string connectionString="";
string ext = System.IO.Path.GetExtension(path);
if (ext.ToString() == ".xls")
{
connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
}
else if (ext.ToString() == ".xlsx")
{
connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0 Xml;HDR=Yes;IMEX=1\"";
}
else
{
Response.Write("<script type=text/javascript language="javascript">alert('Please select proper file')</script>");
}
return connectionString;
}
private DataTable GetData(string filePath)
{
string conn = GetConnection(filePath);
DataTable dt = new DataTable();
string query = "SELECT * from [Sheet1$]";
OleDbConnection olCn = new OleDbConnection();
olCn.ConnectionString = conn;
olCn.Open();
OleDbCommand olCmd = new OleDbCommand(query, olCn);
OleDbDataAdapter olDa = new OleDbDataAdapter(olCmd );
DataSet ds = new DataSet();
olDa.Fill(ds);
dt = ds.Tables[0];
return dt;
}
I hope this helps you.