Read Excel Spreadsheets Easily – Excel Data Reader

So often I am asked to move data from Excel into an application I am building. I wanted a Lightweight and fast solution. I stumbled across this library Excel Data Reader on CodePlex and thought I would share a simple example of how its used.

In this example I needed to take a simple excel spreadsheet with a few thousand rows of employee names and addresses. The first row of the spreadsheet has a header so I want to skip it. I will return an enumerated collection of employees back to the calling method.

public IEnumerable<Employee> ReadFile(string filePath)
{
    var employees = new List<Employee>();
    var stream = File.Open(filePath, FileMode.Open, FileAccess.Read);
    // Want to be able to handle .xls or .xlsx file formats
    var excelReader = filePath.Contains(".xlsx")
                          ? ExcelReaderFactory.CreateOpenXmlReader(stream)
                          : ExcelReaderFactory.CreateBinaryReader(stream);

    excelReader.IsFirstRowAsColumnNames = true;

    excelReader.Read(); //skip first row
    while (excelReader.Read())
    {
        employees .Add(new Employee
                          {
                              Name = excelReader.GetString(0).CleanString(),
                              Address = excelReader.GetString(1).CleanString(),
                              City = excelReader.GetString(2).CleanString(),
                              State = excelReader.GetString(3).CleanString(),
                              PostalCode = excelReader.GetString(4).CleanString()
                          });
    }

    excelReader.Close();

    return employees;
}

The CleanString() method call is an extension to the string property. I do some simple cleanup on the string to remove extra spaces and any other cleanup I might need to do do the data coming in.