Unit testing with ExcelDna.Integration.dll classes.

Aug 4, 2015 at 10:03 AM
Hi Govert,

I have a utilities project which has some core functionality that my ExcelDna addins use. For example I have a method that processes a range of values, some of which may have the value ExcelError.ExcelErrorNA. My unit test has some code similar to the following:
var values = new object[,] { { ExcelDna.Integration.ExcelError.ExcelErrorNA, 2.0, 3.0 }, { 4.0, 5.0, 6.0 } };
double result = MyClass.DoStuff(values);
This means my unit test needs to have a reference to ExcelDna.Integration.dll in order to work. However ExcelDna.Integration.dll has Copy Local set to False so the test doesn't work on my build server because it is missing. How should I set up my references/structure my projects in order for the unit test to run successfully.

Just to clarify:
MyAddin1 references MyUtils
MyAddin2 references MyUtils
Where MyUtils contains the MyClass.DoStuff() method.
Coordinator
Aug 4, 2015 at 12:14 PM
I understand your question - it's not a scenario I've considered before.

I think you'll have to find a way to either change the Copy Local setting yourself, after the NuGet package is installed, or add a custom build step that copies it to the directory you test from.

What would you suggest?

-Govert
Aug 4, 2015 at 12:58 PM
Yes I had been thinking of adding a post build step to copy it, however I hesitated after seeing the following post: https://groups.google.com/forum/#!topic/exceldna/TLah0Vfymxk You seemed to be telling the guy that it's very naughty to try and copy ExcelDna.Integration.dll anywhere!

Of course I can manually change the setting or add a post build step however I see two potential issues:
  1. The dll gets copied into a big bin folder that is used to deploy the add in and other solution apps. From the post I mentioned you would advise against this.
  2. The setting may get overridden when the NuGet package is updated.
Do you have any concerns about either of these points?
Coordinator
Aug 4, 2015 at 1:16 PM
The issues occur if you are distributing the ExcelDna.Integration.dll as part of your add-in.

For unit testing, I presume you are not loading your assembly as an add-in into Excel. I'd suggest for this scenario, you make a separate output directory (bin\DebugTest or something) where you won't have the .xll file or the packed files, but just your assembly and the ExcelDna.Integration.dll, where you can run these tests.

For the real add-in, or if you're testing in Excel, you don't need the ExcelDna.Integration.dll, since the copy is already embedded in the .xll.

The general guidance would be to separate your stuff into an assembly that does not depend on Excel at all (so it can even be run from a console application or something) and then have a thin add-in wrapper that turns it into an Excel add-in. The add-in facade will have the dependency on ExcelDna.Integration.dll, convert results and other errors into Excel-related types, and have the attributes that mark up your functions for Excel.

If you split it this way, you can comprehensively test your core assembly any way you want (it has no Excel dependency) and you'd test the whole add-in in Excel. Testing the thin add-in wrapper without Excel then has little purpose - nearly every problem you find would be in the interaction with Excel. So you might rather focus on integration testing of the add-in in the real Excel context.

-Govert
Aug 4, 2015 at 4:19 PM
>>The issues occur if you are distributing the ExcelDna.Integration.dll as part of your add-in.
This is my concern too.

For unit testing, I presume you are not loading your assembly as an add-in into Excel.
Yes that's right.

I'd suggest for this scenario, you make a separate output directory (bin\DebugTest or something)
Hmm, the problem with this is that our build server builds everything into one directory. It uses this directory for it's unit testing and as the location for all the bin files etc. that get deployed.

The general guidance would be to separate your stuff into an assembly that does not depend on Excel at all
Yep I had thought of this but it seemed rather difficult. Maybe I need to give it some more thought. The types of things I am doing are all the boilerplate code any add in would need, e.g.

Returning a 1-D array: (https://exceldna.codeplex.com/wikipage?title=Returning%201-D%20Arrays&referringTitle=Documentation)
Getting the current cell name: https://www.pyxll.com/docs/automation.html
bool isMissing = o is ExcelMissing;
bool isEmpty = o is ExcelEmpty || (o is string && (string)o == string.Empty);
bool isNa = o.Equals(ExcelError.ExcelErrorNA);
ExcelDnaUtil.IsInFunctionWizard()
LogDisplay.WriteLine(e.ToString())
Admittedly some of these are easy to inject in with and interface, other things are more challenging.

My concern is that I can see more and more "boilerplate" functionality creeping into this class and basically I end up have to inject loads and loads of interfaces for things like "GetCurrentCellName".
Aug 7, 2015 at 12:49 PM
Govert,

I've been having a think about your suggestions.

>>The general guidance would be to separate your stuff into an assembly that does not depend on Excel
>>... If you split it this way, you can comprehensively test your core assembly any way you want (it has no Excel dependency)

I think the key problem is that I actually want to write Excel code. E.g. I want to extend the ExcelDna functionality in such a way that any of our add-in can use this functionality. So by definition I need to have a dependency on ExcelDna.Integration

Let's say I wanted to write an extension method that returns "ExcelMissing", or what about methods that convert C# objects to Excel cells/ranges or vice versa. These, dy definition, need a reference to ExcelDna.Integration because they integrate my classes with ExcelDna. How do I unit test that without reference to ExcelDna.Integration tests. I think these methods ARE the "wrapper" that you are referring to. As you say I can always find a way extract this functionality into some sort of wrapper, but this wrapper will get bigger and bigger over time and unless it's trivial I need to find a way to test the wrapper.

Where is the correct place to write core ExcelDna functionality that is to be shared by a number of add ins?
Coordinator
Aug 7, 2015 at 5:46 PM
I don't have any clever suggestions for this case, apart from splitting off the unit testing outputs.
I understand the problem, though.

-Govert

Aug 12, 2015 at 10:13 AM
OK, thanks Govert, I think I'll just add a post build event for now.