We face these same difficulties trying to unit test our code that interacts with Excel. It stems from the fact that there is no global Office or Excel object available when we're running our test in jest (outside of Office).
Currently, we have a Mock object that creates a Mock context for our Excel.run() calls. From there, we need to create a mock for everything that we call into, which can be quite extensive. At this point, we're pretty much just mocking the functions that 'create' other objects, so that they create other Mock objects so that we can get unit test coverage through our code. So, we put in a lot of work just making sure that our code will execute fully in the unit test.
But, we're not really doing any useful tests in many of these scenarios. We would like to be able to do something like:
- take a raw value
- assign it to a range
- set the format on it
- test that the range is displaying the formatted value that we expect.
But, unless we go into our mock object and implement all of the formatting code, we can't test it. We can only test that we have the raw value and format that we expect. It's not really feasible to implement everything that excel does in a mock, so finding out what is the most useful to test is a major challenge.