Reading Excel and CSV file using LinqToExcel

Introduction

Linq to Excel is a open source .Net library that allows us to query Excel spreadsheets or CSV files using the LINQ way. The project was created by Paul Yoder. In this article, we will look into reading an Excel spreadsheets and a CSV file using Linq to Excel project.

Environment Setup and Using the code

Fire up Visual Studio 2015 and add a blank solution. Then add a class library to it.Name it as Model. Add a class by the name Employee.cs to it and add the below properties as under

namespace Model
{
    using System;

    public class Employee
    {
        public int EmployeeId { get; set; }
        public string EmployeeName { get; set; }
        public string Gender { get; set; }
        public string PhoneNumber { get; set; }
        public string Email { get; set; }
        public string Address { get; set; }

        public override string ToString()
        {
            string strEmployeeRecords = $"EmployeeId: {EmployeeId}";
            strEmployeeRecords += $", EmployeeName: {EmployeeName}";
            strEmployeeRecords += $", Gender: {Gender}";
            strEmployeeRecords += $", PhoneNumber: {PhoneNumber}";
            strEmployeeRecords += $", Email: {Email}";
            strEmployeeRecords += $", Address: {Address}";
            return String.Format(strEmployeeRecords);
        }
    }
}

Let's create another class library and name it as ExcelAndCSVOperationsLibrary.A Right click on references and select Manage Nuget Packages for Solution and search for LinqToExcel in the online packages in the Package Manager window and install that

Alternatively , we can install the same through Nuget package manager as under

PM >Install-Package LinqToExcel

This package has a dependency on log4net

Now let's create an Interface say IExcelCSVOperations.cs and add the below operations

namespace ExcelAndCSVOperations
{
    using System.Collections.Generic;

    interface IExcelCSVOperations
    {       
        IEnumerable<T> ReadFile<T>();
    }
}

Next add a class say FileUtility.cs and add the below code to it

namespace ExcelAndCSVOperations
{
    using LinqToExcel;
    using System.Collections.Generic;

    public class ExcelAndCSVFileUtility
    {      
        public static IEnumerable<T> ReadRecords<T>(string fileName,string sheetName)
        { 
            var items = new ExcelQueryFactory(fileName)
                                  .Worksheet<T>(sheetName);
        
            foreach (var item in items) yield return item;            
        }
    }
}

We are creating an instance of the ExcelQueryFactory class by passing the full path to the Excel spreadsheet or CSV file.Then we are invoking the Worksheet method that expects the sheetName. Though, it is however optional.This method enables Linq queries against an Excel worksheet.We need to pass the TSheetData type to the Worksheet method. The TSheetData class returns the excel's row data as the specific Class Type (in our case it is Employee).The yield moves the program pointer to and fro between caller (we will specify shortly) and the collection.

Next add two classes say ExcelOperation.cs and CSVOperations.cs and write the below piece of code

ExcelOperation.cs
-------------------

namespace ExcelAndCSVOperations
{
    using System.Collections.Generic;

    public class ExcelOperation : IExcelCSVOperations
    { 
        public IEnumerable<T> ReadFile<T>()
        {
            return ExcelAndCSVFileUtility
                .ReadRecords<T>
                (@"D:\EmployeesSpreadsheet.xls"
                , "EmployeeRecordSheet");
        }
    }
}


CSVOperations.cs
-----------------

namespace ExcelAndCSVOperations
{
    using System.Collections.Generic;
    public class CSVOperations : IExcelCSVOperations
    {
        public IEnumerable<T> ReadFile<T>()
        {
            return ExcelAndCSVFileUtility
                .ReadRecords<T>
                (@"D:\EmployeesCSV.csv"
                , "EmployeeRecordSheet");
        }
    }
}

These classes have implemented the IExcelCSVOperations interface methods and invoked the ReadRecords method of the ExcelAndCSVFileUtility class. These are the callers.

Finally, let's fire up a console application and add the below code piece for displaying the records.

using ExcelAndCSVOperations;
using Model;
using System;
using System.Linq;
using static System.Console;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            WriteLine("------------------------- READ EXCEL FILE---------------------------" + Environment.NewLine);
            new ExcelOperation()
            .ReadFile<Employee>()
            .ToList()
            .ForEach(e => WriteLine(e.ToString() + Environment.NewLine));

            WriteLine("------------------------- READ CSV FILE-----------------------------" + Environment.NewLine);

            new CSVOperations()
               .ReadFile<Employee>()
               .ToList()
               .ForEach(e => WriteLine(e.ToString() + Environment.NewLine));

            ReadKey();
        }        
    }
}

The final result

Conclusion

Hope this article will be helpful to those who are dealing with Excel Interoperability and want to perform it in a nice way and to fire LINQ on the Excel records. Thanks for reading.Zipped file attached.