How to import excel data to a list of objects in C# using EPPlus Nuget Package

I would try to explain about my code which I wrote for importing an excel file to a list of objects in C#. These objects helped me to check for static types and write the code in more convenient way. This helped me with the support of intellisense for accessing those properties.

Here is the explanation of my code . I created a simple console application to import persons detail from an excel file to my application. I defined a generic method GetList<T> to import the excel file. The method works for type T (in my case T was Person) and creates a list of objects dynamically from the excel data. The excel column headers texts are property names of the type. Following is the definition of GetList<T> method.

private List<T> GetList<T>(ExcelWorksheet sheet)
        {
            List<T> list = new List<T>();
            //first row is for knowing the properties of object
            var columnInfo = Enumerable.Range(1, sheet.Dimension.Columns).ToList().Select(n =>
            
                new {Index=n,ColumnName=sheet.Cells[1,n].Value .ToString()}
            );

            for(int row=2; row<sheet.Dimension.Rows;row++)
            {
                T obj = (T)Activator.CreateInstance(typeof(T));//generic object
                foreach(var prop in typeof(T).GetProperties())
                {
                    int col = columnInfo.SingleOrDefault(c => c.ColumnName == prop.Name).Index;
                    var val = sheet.Cells[row, col].Value;
                    var propType = prop.PropertyType;
                    prop.SetValue(obj, Convert.ChangeType(val, propType));
                }
                list.Add(obj);
            }

            return list;
        }

The method takes the ExcelWorksheet object which is actually present in the workbook of the ExcelPackage object. Following is the code which calls this method.

static void Main(string[] args)
        {
            string file = @"D:\ForVideos\Demo\cs\data2.xlsx";
            
            using (ExcelPackage package = new ExcelPackage(new FileInfo(file)))
            {
                ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
                var sheet = package.Workbook.Worksheets["data"];
                var persons = new Program().GetList<Person>(sheet);
            }
        }

The main method creates an ExcelPackage object by passing FileInfo of the existing excel file.

The package has workbook details. The workbook objects has worksheets defined. The ‘data’ worksheet is retried from the worksheets array and passed to the GetList method.

The Person class was defined as

public class Person
{
    public string Name { get; set; }
    public string LastName { get; set; }
    public string Address1 { get; set; }
    public string Street { get; set; }
    public string State { get; set; }
    public string Zip { get; set; }
}

columnInfo object holds a list (columnInfo) of an anonymous type with column name and index properties..

A for loop is used to iterate over the all rows present in the excel and create an instance of generic type T. The properties of the type help to get the property of the generic type and set the value from the cell.

The excel cell is accessed using sheet. Cells which is a two dimensional array takes row and column value. Finally the instance was added in the list. The method returns the list to main method.

There are two points to be noted here that we must specify the license context to use the ExcelPackage.

Another point is to use Convert. ChangeType to check for the data type with the property type. Here is the code which was used for this.

var propType = prop.PropertyType;
prop.SetValue(obj, Convert.ChangeType(val, propType));

Following namespace was added in the namespace list after installing EPPlus Nuget Package.

using OfficeOpenXml;

In my application, the data in the excel was as below.

When the application was run, all this data was present in the form of list of person type. This helped me to display the data on the console screen from the list.

Thanks for reading this post and hope this helps you to understand the code for importing an excel file using EPPlus nuget package.

More C# examples can be found at https://www.writeafunction.com

My medium stories : gautammokal.medium.com

Happy Coding!!

Gautam Mokal