Unit testing is a fairly standard practice, and TDD (Test Driven Development) is becoming more popular. Tests that access files or run a lot of code are termed integration tests. But what if you want to run acceptance tests? Or at the very least, integration tests where the QA team can modify the input data?
I’ve seen frameworks, such as Fitnesse, that provide that kind of flexibility, but it turns out that you can use standard Microsoft tools. You can use Excel as your UI, and enter in rows of data. MSTest will process each row as a separate test. The test doesn’t have to be a simple unit test; it can run a more complex integration test. I think the key is that you’re testing business logic without a UI, so your tests need to be separate from the real UI components.
Using Excel for data input into unit tests can also be helpful. Say that you have a known list of inputs for a function to process – test them all!
While you can find information online on how to read Excel spreadsheets from unit tests, putting it together is a bit more tricky. I had to resolve these issues:
- I needed a relative path for the Excel file, as different developers may have a different source path, but the “DataSource” attribute for the test fixture doesn’t use relative paths.
- I wanted to make it easier for non-developers to run tests if desired, while reducing the need to create another server. For something like Fitnesse, you have a wiki web interface, and a table form to enter values, and a way to submit the run. The same functionality could be easily performed from Excel.
If there’s a negative to using Excel for input data, it’s that it’s a separate file from the test fixture code. I don’t see how this is much of an issue, though.
While creating tests using Excel for the input data is well-documented online, these were the problems I wanted to solve in order to get a better solution. (I’m surprised that these aren’t more common.)
I solved the relative path problem by using the
|DataDirectory| keyword in the DataSource attribute. When running a test, this points to the bin\Debug folder. Now you can use relative directory mapping from there to the location of your Excel file.
While there are many examples online of using the DataSource attribute to run tests from Excel, not all of them use DataDirectory.
Excel as a Test Runner
While you can naturally run the tests from within Visual Studio, you can use VBA in Excel to program a button to launch MSTest to run the tests. This sounds simple, but it’s fairly tricky to get the right combination of obscure DOS commands to run with correct paths. Also, VBA doesn’t know about DataDirectory, so we need another way to obtain a starting point for the relative directory. I found a VBA command to obtain the location of the spreadsheet, added bin\Debug, and now I could send that path to MSTest. Once working, QA can experiment with different inputs and click one button to launch, all from the spreadsheet and without launching Visual Studio. They just need Visual Studio installed.
I built the call from VBA with the below components:
cmd.exe /s /c
mstest /testcontainer:" & Application.ActiveWorkbook.Path & "\bin\Debug\My.Tests.dll"
The Shell command is needed to launch an external process. That process is cmd.exe, because we want to run like a batch file. The Call command wraps the remaining calls. The path to Visual Studio tools needs to be set before mstest can be found. The relative path is found starting with the workbook path, and finally, a pause is used to allow viewing the Command Prompt window before it goes away. To allow multiple commands to run at once, separate with &.