Parsing Excel Files in .Net Core

Photo: Unsplash

.Net Core development is both fun and frustrating.

It’s like working in a multiverse where everything is the same only slightly different. It’s familiar enough and seems like the .Net we all love. However, it’s frustrating when you attempt to add code that is not supported or implemented. Framework features you have taken for granted for years no longer exist and libraries that you use over and over are no longer supported. The situation is evolving at an impressive pace though. See Mark Woodhall’s “dotnet core package a day” series as an example. New libraries are appearing all the time and existing ones are being polished very quickly.

As part of our current development work at Open Energy Market, I have been writing an Excel file parser. On .Net 4.6 there are a plethora of libraries for reading and parsing Excel data into models. My recent favourite was LinqToExcel. These libraries all supported both the old and new file formats used by Excel. This is an interesting point. The new xslx format is over 10 years old so “new” might be a wrong term.

On .Net Core the options are much more restricted and I found the best option was an unofficial port of EPPlus. This is super simple to install via NuGet:

PM> Install-Package EPPlus.Core 

Reading and parsing data from a xslx file is very straightforward. (Note there is a lack of error handling and file creation for clarity).

Writing data to a file is also very easy.

Both of these snippets are reading and writing standard tabular Excel data. Unfortunately, some of the files I was parsing are laid out in a “unique” manner. Some of our suppliers like to provide invoice data using Excel instead of the more suitable pdf or word formats. So I needed a way to parse blocks of defined cells (named ranges). This was also easy using the Cells method of the Worksheet object. For example, the following is reading the B6 to E12 range.

EPPlus is a great library. It makes working with xslx files trivial and is by far and away the only serious option on .Net Core.