First, You need to add below name spaces to read Excel file in OLEDB
using System.Data.OleDb;
using System.Configuration;
using System.Data.SqlClient;
And we need establish a connection to the Database and OLEDB. Add below connection code in web.config file
<connectionStrings>
<add name="AD_Phase2" connectionString="Data Source=localhost;Database=DatabaseName;User Id=sa;Password=Password" providerName="System.Data.SqlClient"/>
<add name="ExcelConnection" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=NO'\"/>
<add name="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=NO'"/>
<add name="Excel07ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=NO'"/>
</connectionStrings>
Below is the code for read the Excel data and fill into datatable
//Read the Excel file and fill in datatable .
public DataTable GetDataTable(string strSheetName)
{
try
{
string strComand;
if (strSheetName.IndexOf("|") > 0)
{
/* if Range is provided.*/
string SheetName = strSheetName.Substring(0, strSheetName.IndexOf("|"));
string Range = strSheetName.Substring(strSheetName.IndexOf("|") + 1);
strComand = "select * from [" + SheetName + "$" + Range + "]";
}
else
{
//= Images_Path + "" + SaveStudentDetails.FileName;
strComand = "select * from [Sheet1$]";
}
//HttpPostedFile StudentPhotoFile = SaveStudentDetails.PostedFile;
string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FinalPath + ";Extended Properties='Excel 8.0;HDR=NO;'";
OleDbConnection connection = new OleDbConnection(excelConnectionString);
OleDbDataAdapter daAdapter = new OleDbDataAdapter(strComand, connection);
DataTable dt = new DataTable("Datatable");
DataColumn dc = new DataColumn();
DataRow dr = dt.NewRow();
dt.Rows.Add(dr);
daAdapter.Fill(dt);
return dt;
}
catch (Exception ex)
{
throw new Exception("Select location" + ex.Message);
}
}