News
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 2016 6:26AM     Michael
  0 Comments    934 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

Namespace:

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;
             try
             {
                 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;';");

                oledbConn.Open();
            }
            catch (Exception ex)
            {
                //Error
            }
            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>();
            try
             {               
                OleDbConnection oledbConn = OpenConnection(path);
                if (oledbConn.State == ConnectionState.Open)
                {
                    objEmployeeInfo = ExtractEmployeeExcel (oledbConn);
                    oledbConn.Close();
                }
            }
            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.


BackToTop
Comments



 
Search
1&1 Hosting
Recent Posts
Bind Gridview from CSV file in Asp.Net C#
Jun 18 2016 11:38PM Posted By Michael
Call WebService method from jQuery in every 1 minute
Feb 27 2016 11:51AM Posted By John
Gridview custom CSS in ASP.Net
Feb 25 2016 10:59AM Posted By Micheal Ryan
Read excel file and bind to Gridview in C#
Feb 25 2016 10:20AM Posted By Micheal
Read excel file and convert to List in C#
Feb 25 2016 6:26AM Posted By Michael
Encryption and Decryption for Password in C#
Feb 17 2016 3:52AM Posted By Michael
How to create Chart in ASP.Net C#
Feb 16 2016 8:03AM Posted By Michael Ryan
Tags
Follow us on Facebook
Follow us on Google +
Recent post in your Email inbox.
Enter your email address: