Convert Excel to XMl using LINQ OR Read Data From Excel using c#

        

      Before that I request to you all please give your valuable feed back about the blog and this post. It will help me to create upcoming blog post better than now.

In this example I want to explain how to read data from Excel file and save it as XML. for that I will use LinqToExcel

C# Linq to Excel

            LinqToExcel invited by Paul Yoder is a .NET library which facilitates querying Excel spreadsheets and .CSV files using the familiar LINQ syntax. So if you have idea in Linq then you can easily use it. It is common for both xls, xlsx, csv file.

How to Install

            We can easily install this using Nuget Package by using following comets.

PM> Install-Package LinqToExcel

Or you can use “Manage Nuget Package “and search “LinqToExcel” and install ref following windows.

Now our application is ready to use the feature of Linq to Excel. Before go to c# code we need to prepare excel file using bellow employee details

EmployeeName	Address	City	State	Country
Name1	Address1	Chennai	Tamil Nadu	India
Name2	Address2	Bangalore	Karnataka	India
Name3	Address3	Delhi	Delhi	India
Name4	Address4	Thiruvananthapuram	Kerala	India
Name5	Address5	Hyderabad	Telangana	India

                          using this above  data and converted it into excel book like and name it as EmployeeWorkBook.xlsx.It look like bellow.

      

Now open the Visual studio and create a console application for read data from excel file, name it as ExceltoXml. Let us see how read data from employee details excel file using Linq to Excel and convert it into XML.

 Rewrite Program.cs class file like bellow.

using LinqToExcel; using System; using System.Collections.Generic; using System.Xml.Linq; using System.Linq; namespace ExcelToXml {     class Program     {        static void Main(string[] args)         {            try             {                var excel = new ExcelQueryFactory(@"E:\employeeworkbook.xlsx"); foreach (string sheetName in excel.GetWorksheetNames()) { //Get Excel column var columnList = excel.GetColumnNames(sheetName); XDocument srcTree = new XDocument(new XComment("This is a xml contain Employee Details"), new XElement("EmployeeRoot", excel.Worksheet(sheetName).AsEnumerable().Select(a=> new XElement("Employees",from cn in columnList select new XElement(cn,a[cn])))));  srcTree.Save(@"E:\employee.xml"); }             }             catch (Exception ex)             {                 throw ex;             }         }              } }

Save it and Run the application .for run the application pres F5 button from your key board.
It will create employee.xml file and save it in E Directory.

- <!-- This is a xml contain Employee Details   --> -<EmployeeRoot> - <Employees> - <Employee>   <EmployeeName>Name1</EmployeeName>   <Address>Address1</Address>   <City>Chennai</City>   <State>Tamil Nadu</State>   <Country>India</Country>   </Employee> - <Employee>   <EmployeeName>Name2</EmployeeName>   <Address>Address2</Address>   <City>Bangalore</City>   <State>Karnataka</State>   <Country>India</Country>   </Employee> - <Employee>   <EmployeeName>Name3</EmployeeName>   <Address>Address3</Address>   <City>Delhi</City>   <State>Delhi</State>   <Country>India</Country> </Employee> - <Employee>   <EmployeeName>Name4</EmployeeName>   <Address>Address4</Address>   <City>Thiruvananthapuram</City>   <State>Kerala</State>   <Country>India</Country>   </Employee> - <Employee>   <EmployeeName>Name5</EmployeeName>   <Address>Address5</Address>   <City>Hyderabad</City>   <State>Telangana</State>   <Country>India</Country>   </Employee>   </Employees>   </EmployeeRoot>