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.

Advertisements
This entry was posted in C# and tagged , , , , . Bookmark the permalink.

11 Responses to Reading an Excel .xlsx file from C#

  1. Laney says:

    There is another C# Library for reading Excel files known as Aspose.Cells for .NET. Try it, you can find many code samples on their documentation page.

  2. S says:

    I had to use package.Workbook.Worksheets[1]; rather than package.Workbook.Worksheets[0];

  3. luisk says:

    Thank you, it works. The epplus documentation is not detailed. So you have to look in the intellisense what you have to do to get the length and iterate the cells.

  4. KenK says:

    seems like this is out-dated now. I’ve seen it on dozens of blogs and forums and I am certain I’ve used this exact format in the past but now it always throws an error.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s