IBM to offer mobile security as a service IBM will start delivering mobile security-as-a-service (MaaS) through its own cloud infrastructure in India, a move that is expected to better address in-country data requirements... IBM Spectrum Computing: IBM has enlarged its portfolio of software-defined infrastructure solutions with cognitive features for workload management.  * LiFi internet technology has been introduced, the new type of wireless internet connection that gives 100 times faster than traditional WiFi -- Invented by Professor Harald Haas from the University of Edinburgh. * Sci. Rachid Yazami has developed a smart chip that charges smartphones in less than 10 minutes. BenQ has launched BlueCore projector - Consumer electronics major BenQ has launched its first BlueCore laser light source projector. For those unaware, devices with BlueCore laser technology have a high contrast output of 80000:1 with an extended lamp life and efficiency. ***
  Feb 25 2017 6:26AM     Michael
  0 Comments    4291 Views  
Here Michael provided a short description with example for reading excel file content and convert to List.
Excel File:

File Details:

Excel File Name: Employee.xls
Excel Sheet Name: Employee


Here, I have used OleDbConnection, OleDbCommand and OleDbDataAdapter to read .xls and .xlsx files.

Using OleDbConnection we can read the excel faster and efficiently.

Supportable extensions .xls and .xlsx


using System.Data.OleDb;
using System.IO;
using System.Data;

C# Code:

We have to create a class with the properties. Properties are columns or fields in the excel sheet.

Employee Class:

    public class Employee
        public string Name { getset; }

        public string Designation { getset; }

        public int?  Experience { getset; }

OleDb Connection Open:

        /// <summary>
        /// Open Connection
        /// </summary>
        /// <param name="path"></param>
        /// <returns></returns>
        private  OleDbConnection  OpenConnection(string  path)
             OleDbConnectio n  oledbConn  = null;
                 if (Path.GetExtension(path) == ".xls")
                    oledbConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + path + 

"; Extended Properties= \"Excel 8.0;HDR=Yes;IMEX=2\"");
                else if (Path.GetExtension(path)  == ".xlsx")
                    oledbConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + 

path + "; Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';");

            catch (Exception ex)
            return oledbConn;

Extract Excel File:

        /// <summary>
        /// Extract 'Service Information' sheet
        /// </summary>
        /// <param name="oledbConn"></param>
        private IList<Employee> ExtractEmployeeExcel(OleDbConnection oledbConn)
            OleDbCommand cmd = new OleDbCommand (); ;
            OleDbDataAdapter oleda = new OleDbDataAdapter();
            DataSet dsEmployeeInfo = new DataSet ();

            cmd.Connection  = oledbConn;
            cmd.CommandT ype = CommandType.Text;
            cmd.CommandT ext = "SELECT * FROM [Employee$]"; //Excel Sheet Name ( Employee )
            oleda = new OleDbDataAdapter(cmd);
            oleda.Fill (dsEmployeeInfo, "Employee");

            var dsEm ployeeInfoList = dsEmployeeInfo.Tables[0].AsEnumerable().Select (s => new Employee
                Name = Convert.ToString(s["Name"] ! = DBNull.Value ? s["Name"] : ""),
                Designation = Convert.ToString(s["Designation"] ! = DBNull.Value ? s["Designation"] : ""),
                Experience = Convert.ToInt32(s["Experience"])
            }).ToList ();

            return dsEmployeeInfoList;

To Read and Convert to List:

        /// <summary>
        /// Read excel sheet with the user path
        /// </summary>
        /// <param name="path"></param>
        public IList<Employee> ReadExcel(string path)
            IList<Employ ee> objEmployeeInfo = new List<Employee>();
                OleDbConnection oledbConn = OpenConnection(path);
                if (oledbConn.State == ConnectionState.Open)
                    objEmployeeInfo = ExtractEmployeeExcel (oledbConn);
            catch (Exception ex)
                // Error
            return objEmployeeInfo;

The above method will return the excel content in the list format.

Use of Code:

Here calling the ReadExcel() method to get the excel content.

            string path = @"D:\Employee.xls";
            IList<Employee> objExcelCon = ReadExcel(path);

Here I gave simple example about read excel file and convert to List. I hope it will helps. Thanks.


Recent Posts
Create Amazon ElasticCache Using Memcached in CSharp
Nov 21 2017 12:09PM Posted By Amose
Get Google Map Lat Lng (Geo Point) By Pincode in C#
Nov 8 2017 10:26AM Posted By Pranav
Google URL Shortener in C#
Oct 26 2017 10:43AM Posted By Sanjay
Bind Gridview from CSV file in Asp.Net C#
Jun 18 2017 11:38PM Posted By Michael
Call WebService method from jQuery in every 1 minute
Feb 27 2017 11:51AM Posted By John
Gridview custom CSS in ASP.Net
Feb 25 2017 10:59AM Posted By Micheal Ryan
Read excel file and bind to Gridview in C#
Feb 25 2017 10:20AM Posted By Micheal
Follow us on Facebook
Follow us on Google +
Recent post in your Email inbox.
Enter your email address: