Tuesday, 29 March 2016

Read Xls File Data To an Object

In this article you will learn to read Excel File Data to an Object.
I have a xls file named Employee.xls that contains three columns "Name","Address","Mobile".


Download File: Employee.xls

I have a Employee Object.

    public class Employee
    {
        public string Name { get; set; }
        public string Address { get; set; }
        public string Mobile { get; set; }
        public List<Employee> empList { get; set; }

    }

I need the Data from Excel file to my Employee Object.

First Add ExcelDataReader from Nuget in your Project Solution.

Install-Package ExcelDataReader

It'll add some dlls in your Project.

To Read data from xls, the Code is below:

public static void importDataFromXls()
        {
            Employee data = new Employee();
            data.empList = new List<Employee>();

            //Reading from a OpenXml Excel file (2007 format; *.xlsx)
            FileStream stream = new FileStream("C:\\Users\\surajm\\Documents\\Employee.xlsx", FileMode.Open);
            IExcelDataReader excelReader2007 = ExcelReaderFactory.CreateOpenXmlReader(stream);

            //DataSet - The result of each spreadsheet will be created in the result.Tables
            DataSet result = excelReader2007.AsDataSet();

            //Data Reader methods
            foreach (DataTable table in result.Tables)
            {
                for (int i = 1; i < table.Rows.Count; i++)
                {
                    //for (int j = 0; j < table.Columns.Count; j++)
                  
                        Employee emp = new Employee();
                        emp.Name = table.Rows[i].ItemArray[0].ToString();
                        emp.Address = table.Rows[i].ItemArray[1].ToString();
                        emp.Mobile = table.Rows[i].ItemArray[2].ToString();
                        data.empList.Add(emp);
                 
                }
            }

            //Free resources (IExcelDataReader is IDisposable)
            //excelReader2003.Close();
            excelReader2007.Close();
        }

Download the Project File ImportXlsDataToObject.cs

0 comments:

Post a Comment

Topics

ADO .Net (2) Ajax (1) Angular Js (17) Angular2 (24) ASP .Net (14) Azure (1) Breeze.js (1) C# (49) CloudComputing (1) CMS (1) CSS (2) Design_Pattern (3) DI (3) Dotnet (21) Entity Framework (3) ExpressJS (4) Html (3) IIS (1) Javascript (6) Jquery (9) Lamda (3) Linq (11) Mongodb (1) MVC (48) NodeJS (7) RDLC (1) Report (1) Sql Server (29) SSIS (3) SSRS (2) UI (1) WCF (12) Web Api (9) Web Service (1) XMl (1)