Reading Excel From C# using OLEDB Connection

In this article i will explain how to read Excel sheet in C#.NET 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);

            }

        }

            
Note : If you facing any problem to connect OLEDB connection . Install "Microsoft Office Access database engine 2007" software . It free software .

Below is the total code ....... Take Website or web application , Copy the below code and paste it into your site and check .



using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Data;
using System.Data.OleDb;
using System.Configuration;
using System.Data.SqlClient;

namespace CandidateID1
{
    public partial class _Default : System.Web.UI.Page
    {
        string Save_Path;
        string Save_Path_Name;
        string FinalPath;
        int i;
        DataTable dtSaveCandateID = new DataTable();
        DataSet dsSaveCandateID = new DataSet();
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void btnDownload_Click(object sender, EventArgs e)
        {
            Response.Redirect("Template/CandidateID.xlsx", false);
        }


        protected void btnUpload_Click(object sender, EventArgs e)
        {
            string[] FileExt = fuUpload.PostedFile.FileName.Split('.');
            string[] FileSep = fuUpload.PostedFile.FileName.Split('\\');
            string FileEx = FileExt[FileExt.Length - 1];
            string FileSepExt = FileSep[FileSep.Length - 1];

            if (fuUpload.PostedFile.FileName == string.Empty)
            {
                return;
            }

            else if (FileEx.ToLower() == "xls" || FileEx.ToLower() == "xlsx") // When excel file is upload, Below code is applicable...
            {
                DataSet ds = new DataSet();
                DataTable dtXL = new DataTable();
                dtXL.Columns.Add("candidateid");
                string Images_Path = Server.MapPath("~/") + "\\Upload\\";
                DirectoryInfo di = CreateUserDirectory(Images_Path);
                Save_Path = Images_Path + "" + fuUpload.PostedFile.FileName;
                string[] count = Save_Path.Split('\\');
                int Count = count.Length - 1;
                Save_Path_Name = Save_Path.Split('\\')[Count];
                FinalPath = Images_Path + "" + Save_Path_Name;
                fuUpload.PostedFile.SaveAs(FinalPath);
             

                ds.Tables.Add(this.GetDataTable(Images_Path + "" + FinalPath));
                dtXL = ds.Tables[0];
                if (System.IO.Directory.Exists(Images_Path))
                {
                    di.Delete(true);
                    System.IO.File.Delete(FinalPath);
                }

                if (dtXL.Columns.Count == 1)
                {
                    string i = "candidateid";
                    string Title = dtXL.Rows[1][0].ToString().ToLower();
                    if (Title == "")
                    {
                        dtSaveCandateID = GetData(dtXL);
                    }
               

                    dsSaveCandateID.Tables.Add(dtSaveCandateID);
                    string InputXml = dsSaveCandateID.GetXml();
                    SqlConnection con = new SqlConnection("Data Source=localhost;Database=DatabaseName;User Id=sa;Password=Password");
                    SqlCommand cmd = new SqlCommand();

                    //cmd.CommandText = CommandType.StoredProcedure.ToString();

                    // Hello All You write code here for DB Connection....


                }
                else
                {
                    lblMessage.Text = "Excel headers are Changed";
                }
                return;
            }
        }

        public DirectoryInfo CreateUserDirectory(string directoryPath)
        {
            string userName = System.Guid.NewGuid().ToString("N");
            if (!string.IsNullOrEmpty(User.Identity.Name))
            {
                userName = User.Identity.Name;
            }
            string userDirectoryName = directoryPath + "\\" + userName;
            DirectoryInfo di;

            if (!Directory.Exists(userDirectoryName))
            {
                di = System.IO.Directory.CreateDirectory(userDirectoryName);
                return di;
            }
            else
            {
                di = new DirectoryInfo(userDirectoryName);
            }
            return di;
        }

        //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);
            }
        }
        //This method is used for convert string to int
        private int ConvertToInt(string str)
        {

            try
            {
                i = Convert.ToInt32(str);
                return i;
            }
            catch (Exception ex)
            {
                i = 0;
                return i;
            }
        }

        public DataTable GetData(DataTable dt)
        {
            DataTable dt1 = new DataTable("Datatable");
            DataColumn dc = new DataColumn();
            dc = new DataColumn();
            dc.ColumnName = "CandidateID";
            dc.DataType = Type.GetType("System.Int32");
            dt1.Columns.Add(dc);

            for (int a = 2; a < dt.Rows.Count; a++)
            {
                DataRow dr = dt1.NewRow();
                int k = ConvertToInt(dt.Rows[a][0].ToString());
                if (i!=0)
                {
                    dr["CandidateID"] = dt.Rows[a][0].ToString();
                    dt1.Rows.Add(dr);
                }
                else
                {
                }
            }

            DataTable distinctTable = dt1.DefaultView.ToTable(true);
            return distinctTable;

        }
    }
}