Excel Javascript - Adding Unit Tests

9 months ago

The second part of my Excel Javascript series covering my trials and tribulations adding Jest Unit Tests to my project.

This is part two in my Excel Javascript series.

Part one is here.


So my last blog post rambled on about the history of Excel customization from VBA to VSTO to Javascript and how they are different with some suggestions as to why you might want to use the newer Javascript approach.

Today, I'm going to discuss the trials and tribulations I went through getting my first Vue-based Javascript setup prepared and ready.

Rather than reworking the whole process from start to finish, Microsoft has a really good guide for the initial setup located here:

https://docs.microsoft.com/en-us/office/dev/add-in...


One further step .. I decided to use JetBrains WebStorm as my IDE, so that's what these screenshots are from.

This will get you to a functioning example where you can push a button and turn cells green. Hooray! Sort of like Hello World, you have a basic add-in that interacts with Excel through a web page.. But, I really wasn't quite ready to start building things ..

Notably in the instructions, it tells you NOT to use the default unit test setup. Well, I wanted unit tests so, I turned to the jest library.

Step one for this process is to actually install the Jest package with npm with


npm install --save-dev jest

The next step is some manual updating of the highest level package.json file.

The "dependencies" property needs a "jest": "^23.6.0" entry while the "scripts" property needs "test": "jest". At this point, my package.json looked like this:


The next step is to add a new folder called "tests" to the parent directory. This is the folder that jest will look for your tests in. Within that folder I've created a dummy test file with one simple test that simply checks to see if 1 equals 1, so I can know that my test command works.


At this point, I've set up my initial unit testing framework successfully and can confirm my results by going to my terminal and executing "npm run test".


There's nothing crazy difficult here, especially if you've worked with Javascript packages a bunch in the past; however, this was my first experience with it and I found that the Jest "Getting Started" guide did not successfully get me running, as it doesn't include the direction to add jest to the dependencies section of the package.json file.

At this point, you can start adding Javascript code and Vue code and building tests for them. As there are components that will run inside the Excel application, there are some tricks to constructing a sound testing framework that I'm working on and which I hope to discuss in a future blog entry. For now, though, this basic setup let me start building tests to make sure that at least the Javascript side was working for me.


The next challenge that I undertook was incorporating Vuetify and vue-router into my add-in so that my developer could build me some pretty features. Whew .. we'll come back to that one some other time.