Unit testing with Excel workbook

Question

I'm starting to get into unit testing more. I have a project where Excel is the "engine" and it can't be taken out (at this time). I need to provide the workbook with various inputs, run a macro in the workbook, and then collect the results. It's a huge nasty workbook so taking anything out of it isn't an option right now.

So given that the main dependency is Excel.Interop objects, how do I setup unit tests where almost all the code is hitting Excel?

Answer 1

In theory, "unit tests" should test as little as possible, with as many dependencies mocked as possible. With Excel that may be very tricky if not impossible.

Ideally, I would think the tests would be:

• Unit test your app logic, abstracting out Excel by providing a "mock" (easier said than done) that looks for specific inputs and provides hard-coded outputs. The basic idea is "Are my inputs valid" and "did I return all of the required outputs"
• Unit test your Excel macro within Excel by providing hard-coded inputs and verifying the outputs.

That takes Interop out of the picture and lets you unit test those pieces separately.

Putting it all together is more of an integration test (which are typically not automated) that a unit test.