Contents tagged with Excel

  • Read Excel Spreadsheets Easily – Excel Data Reader

    Tags: Excel

    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()
        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.