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.
2 Comments
Tieson Trowbridge said
Very nice. I do almost the same thing, except I wrap the stream and the reader in using statements (makes extra sure that they are disposed).
Rick Bradford said
How would you call individual sheets of a single spreadsheet?