How to read an Excel file and convert it to a List of C# objects

Introduction

This C# recipe shows how you can use EPPlus library to open an Excel file and read the contents of the first worksheet to a List of C# objects

Ingredients

The recipe uses the EPPlus library version 4.5.3.3. This is the last version of EPPlus under the LGPL License (aka free for commercial uses). You can install it using Nuget Package manager:

Install-Package EPPlus -Version 4.5.3.3

C# Code Snippet

var path  = @"C:\Temp\ExcelRecipes\";
var fileName = "recipe1_nasdaq_companies.xlsx";
var fileInfo = new FileInfo(path + fileName);

var finalResult = new List<NasdaqCompany>();
using (var excelFile = new ExcelPackage(fileInfo))
{
    var worksheet = excelFile.Workbook.Worksheets.First();
    {
        int rowCount = worksheet.Dimension.End.Row; //get row count
        for (int row = 2; row <= rowCount; row++)
        {
            var company = new NasdaqCompany();
            company.Symbol = worksheet.Cells[row, 1].Text;
            company.Name = worksheet.Cells[row, 2].Text;
            company.Country = worksheet.Cells[row, 3].Text;
            if(!String.IsNullOrEmpty(worksheet.Cells[row, 4].Text))
                company.IpoYear = Convert.ToInt32(worksheet.Cells[row, 4].Text);
            company.Sector = worksheet.Cells[row, 5].Text;
            company.Industry = worksheet.Cells[row, 6].Text;
            finalResult.Add(company);
        }
    }

}

NasdaqCompany.cs

public class NasdaqCompany
{
    public string Symbol { get; set; }
    public string Name { get; set; }
    public string Country { get; set; }
    public int? IpoYear { get; set; }
    public string Sector { get; set; }
    public string Industry { get; set; }
}

Steps

Open the excel file using the path and file name declared.

var path  = @"C:\Temp\ExcelRecipes\";
var fileName = "recipe1_nasdaq_companies.xlsx";
var fileInfo = new FileInfo(path + fileName);

...

using (var excelFile = new ExcelPackage(fileInfo))
{
    ...
}

if you are using C# 8, the using line can be made a bit simpler with a single using statement:

...

using var excelFile = new ExcelPackage(fileInfo);

Select the relevant worksheet where the data is stored

Selecting the first worksheet

 var worksheet = excelFile.Workbook.Worksheets.First();

Note, you could also select a worksheet based on the name of the worksheet

var worksheet = excelFile.Workbook.Worksheets['Sheet1'];

Get the number of total rows for the worksheet

 int rowCount = worksheet.Dimension.End.Row; //get row count

Start at row 2 (as the first row is the header), read the contents of each cell for the row and assign it to the appropriate property of the C# class (in this case, NasdaqCompany)

for (int row = 2; row <= rowCount; row++)
{
    var company = new NasdaqCompany();
    company.Symbol = worksheet.Cells[row, 1].Text;
    company.Name = worksheet.Cells[row, 2].Text;
    company.Country = worksheet.Cells[row, 3].Text;

    // IPO is an integer. So we first look whether it has any content,
    // if yes we convert the content to an integer
    if(!String.IsNullOrEmpty(worksheet.Cells[row, 4].Text))
        company.IpoYear = Convert.ToInt32(worksheet.Cells[row, 4].Text);
    company.Sector = worksheet.Cells[row, 5].Text;
    company.Industry = worksheet.Cells[row, 6].Text;

    // finally, we add the populated company to  the list of finalResult
    finalResult.Add(company);
}