Business loves Excel, Engineers loves XML

In the middle of my project, the client sends me this excel file that I should validate user input against. I asked around among my colleagues to get their opinion of what I should do with this treacherous thing. Their answers were any of

• Import it into an SQL table and query against the database
• Give the client a web page where they can upload it, and transform the file to XML

Programmers doesn't like Excel as a data format because it is an unreliable grid where anything could be a text, numeric or date value. There is nothing short of convention of how columns are placed and ordered on the spreadsheet; a complete nightmare. I decided to take on the challenge. We all know that this customer will come back with an updated spreadsheet in the future, and then I would like to just replace the old one. I know that you can use OLEDB connections to query Excel documents with SQL-like queries, but I wanted something cleaner. What I found was LINQ to Excel - Google Code, which is really an outstanding idea. Look at the following example.

First I create my model object that represents a row in the spreadsheet.

public class ZipCodeWorksheet
{
    public const string Name = "Report";

public string Country { get; set; }
public string ZipCode { get; set; }
public string City { get; set; }

}

Then I write the LINQ query.

var repo = new ExcelRepository(this.filePath, ExcelVersion.PreExcel2007);


repo.AddMapping(x => x.Country, "COUNTRY");
repo.AddMapping(x => x.ZipCode, "ZIPCODE");
repo.AddMapping(x => x.City, "CITY");

var q = from worksheet in repo.Worksheet(ZipCodeWorksheet.Name)
        where worksheet.ZipCode == indata
        select worksheet;

Easy, maintainable and your clients will love you for it.