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.