COM & Interop Hell: To Excel or to EPPLUS?

business-intelligence-that-rescues-you-from-excell-hellScrolling through a lot of questions on StackOverflow, CodeProject and other sites I often come across questions like how do I create an ExcelSheet, or how to close Excel after saving a document… Most of these questions stem from the problems of working with the COM/Interop model of EXCEL. It’s dirty, it’s messy and resource intensive.

But there is an alternative: a project called EPPLUS, which has several benefits.

  • You don’t need a local Excel copy installed on the machine you’re running your code. Great for server work, or when you have users that only have Office365 or no locally installed Excel on their machine!
  • It uses a simplified API model without compromising powerful features as it can: from basic reading/writing to cells and ranges, coloring, adding worksheets, saving to adding complex object, images and a lot more… The official website sums up that it supports:
    • Cell Ranges, Cell styling (Border, Color, Fill, Font, Number, Alignments)
    • Charts, Pivot tables, tables
    • Pictures, Shapes
    • Comments
    • Protection, Encryption
    • Data validation
    • Conditional formatting
    • VBA
    • Formula calculation
    • Many more…
  • Did I already mention simplified API, what I really mean is you don’t have to bother interfacing with the EXCEL COM API, and manage the different lifecycles, or write your own custom wrapper… In EPPLUS all objects that need to be closed and use resources that need to be cleaned up implement IDisposable, and a ssuch you can write code inside a using block, e.g.:
  • Free, open source & it’s actively maintained, the last commit on time of writing was 2017 February 17th on their codeplex repository.

At least give it a try (nugetcodeplex) and look into it. It’ll save you headaches from COMplicated API calls to the Excel Interop ;-)…- pun inteded.

Data Driven Tests with

Writing S.O.L.I.D. code doesn’t only apply to your domain model, UI code etc.. – but in fact also your test code!

In xUnit writing tests usually includes writing a bunch of methods with the attribute [Fact]. Yet when one wants to test the same Fact (or test) on a set of data, it can get messy… And thus, during my travels through automated testing I used to create a test and within the test a foreach loop over a given set of data, but that was before I discovered [Theories] in xUnit!

Facts are tests which are always true. They test invariant conditions.
Theories are tests which are only true for a particular set of data.

I was confused by those definitions at first. But, to me, it boils down to this: one can group a certain set of data and run it through the same test.

So where’s the benefit then, as I used to to do it with an inline foreach loop… Ok, so, theories have the added benefit that you can upfront declare, reuse and extend that data – and when the tests run, the testrunner will explicitely show results for each data item that was run through the test. This makes it easy to track which “case” (data) made the test fail – in stead of relying on an inline foreach-loop inside your traditional [Fact] test. Which results in (re)usable tests and reusable parts of code in your tests and easier trackability as mentioned before.

Last but not least, you can load the data inline, from Excel, from a Member (e.g. a method or property)…

For a more in depth overview, check this blogpost by Pankaj K: