Reading an Excel .xlsx file from C#

*Updated 06/28/2017* – Fixed the code sample. It was simply wrong before.

I have often written code to parse CSV files. Excel supports editing CSV files so this seemed to be a good method of managing moderate-sized data sets. I have not bothered to parse the Excel file directly because that seemed to be a rather large task with minimal additional value over my existing CSV parser. A quick search on google reveals there are several libraries which can be used. I choose EPPLus because it seemed to be the best.

Rapid Prototyping

Very quickly, I realized that parsing an excel file with a tool like EPPLus is dead simple. Here is the code to loop through all the cells in the first worksheet.

var package = new ExcelPackage(new FileInfo("..\\..\\..\\sample.xlsx"));

ExcelWorksheet workSheet = package.Workbook.Worksheets[1];

for (int i = workSheet.Dimension.Start.Row;
         i <= workSheet.Dimension.End.Row;
         i++)
{
    for (int j = workSheet.Dimension.Start.Column;
             j <= workSheet.Dimension.End.Column;
             j++)
    {
         object cellValue = workSheet.Cells[i, j].Value;
    }
}

Next Steps

Well that wasn’t nearly as interesting as I had hoped. I certainly won’t shy away from parsing excel files in the future. Rather than just end here, I took some time to build a tool which reads in an Excel file and outputs an XML file. This included setting up a project to be highly modular with loosely coupled components, which could be easily swapped out. The resulting components are:

• ExcelReader – Logic for reading from an excel file
• TableReader – Logic for reading a generic set of tabular data. This could be later used for data coming from a CSV or a SQL table.
• Tables – Table information specific to this solution.
• XmlWriter – Logic for writing tables to an Xml File.

The dependencies look like the following:

ExcelReader is nicely isolated and can be easily swapped out for another data source.

Final Thoughts

EPPLus is a great library. It makes parsing Excel files simple.