Read Excel

Define File name in the appsetting

<appSettings>
<add key="class" value="server=.;database=Hcl;uid=sa;pwd=sa123"/>
<add key="filename" value="order.xlsx"/>
<add key="filecity" value="City.xls"/>
</appSettings>

Call the method on button click

protected void btnsearch_Click(object sender, EventArgs e)
{
string location = Server.MapPath("~/Excel_file/") + ConfigurationManager.AppSettings["filename"].ToString().Trim();
GetExcelData(location);
}

Define Method

public DataTable GetExcelData(string location)
{
OledbConnectionString = string.Empty;
string ext = Path.GetExtension(ConfigurationManager.AppSettings["filename"].ToString().Trim());
if (ext == ".xls")
{
OledbConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + location + ";Extended Properties=Excel 8.0;";
}
else if (ext == ".xlsx")
{
OledbConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + location + ";Extended Properties=Excel 12.0;";
}
else
{ }
OleDbConnection objConn = null;
objConn = new OleDbConnection(OledbConnectionString);
if (objConn.State == ConnectionState.Closed)
{
objConn.Open();
}

string query = "Select * From [Order Sheet$] Where rtrim(ltrim([Order No#]))=" + txtorderid.Text.ToString().Trim() + " and rtrim(ltrim([Email Id]))='" + txtemail.Text.Replace("'", " ").Replace("-", " ").ToString().Trim() + "' and rtrim(ltrim([Delivery Name]))='" + txtCname.Text.Replace("'", " ").Replace("-", " ").ToString().Trim() + "'";
OleDbCommand objCmdSelect = new OleDbCommand(query, objConn);
OleDbDataAdapter objAdapter = new OleDbDataAdapter();
objAdapter.SelectCommand = objCmdSelect;
DataSet objDataset = new DataSet();
objAdapter.Fill(objDataset);
box.Visible = true;

try
{
if (objDataset.Tables[0] != null)
{
if (objDataset.Tables[0].Rows.Count == 0)
{
viewdetail.Visible = false;
GridView1.DataSource = objDataset.Tables[0];
GridView1.DataBind();
txtCname.Text = "";
txtemail.Text = "";
txtorderid.Text = "";
}
else
{
viewdetail.Visible = true;
GridView1.DataSource = objDataset.Tables[0];
GridView1.DataBind();
txtCname.Text = "";
txtemail.Text = "";
txtorderid.Text = "";
}

}
else
{
viewdetail.Visible = false;
}
}
catch (Exception ex)
{

}
finally
{
objCmdSelect.Dispose();
objConn.Close();
objConn.Dispose();

}
// objConn.Close();
return objDataset.Tables[0];

}