Tuesday, September 8, 2020

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