Reading Excel From C# using OLEDB Connection

 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);

            }

        }