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

Continue Reading →

Get the Elements and Attribute Value from XML

This article will learn you to read required element and attribute Value from Xml file to a List Object.

See XML File, that I am using for this Example: file.xml

XML file contains list of Airline names with their stops, fares and other information. I've to Read all the Airline Name with Their Fares,Stops and other respective information in a List Object.

See the Code Example:

   class TopDeals

    {

        public string Name { get; set; }

        public decimal Price { get; set; }

        public string Stops { get; set; }

        public string Currency { get; set; }

        public List<TopDeals> lstDeals { get; set; }

    }

The above class contains the properties of TopDeals Type that contanis all the required properties.
The Code for reading the Elements Value is below.

class Program

    {

        static void Main(string[] args)

        {

            XElement root = XElement.Load(@"file.xml");

            var rootElem = root.Elements("Airlines").ToList();

            TopDeals obj = new TopDeals();

            obj.lstDeals = new List<TopDeals>();

            foreach (var item in rootElem)

            {

                TopDeals obj2 = new TopDeals();

                obj2.Name = item.Attribute("name").Value;

                obj2.Price = Convert.ToDecimal(item.Element("stops").Attribute("Fare").Value);

                obj2.Stops = item.Element("stops").Attribute("stop").Value.ToString();

                obj2.Currency = item.Element("stops").Attribute("Currency").Value.ToString();

                obj.lstDeals.Add(obj2);

            }

            var lstData = obj.lstDeals.OrderBy(a => a.Price);

        }

    }

In lstData object you will get the all Airlines Name with their other information.
Find the Complete Program : XmlElementReadProgram

Thanks-

Suraj Kumar

Continue Reading →