Powershell: Word/Excel Helper Functions

Using powershell I wrap up MS Word and Excel COM objects within a custom psobject. This object contains a handful of methods for making docx and xslx creation and manipulation easier.

Introduction

I typically don’t generate many Excel or Word documents programmatically. I find the process involved to be cumbersome due to the many nuances and intricacies of the COM objects which make up the API to the Office products. I also prefer html as it is a bit more universal, can easily be saved to an IIS directory for custom portals, and can be converted to PDF easily enough. But everyone likes Word/Excel so much that I finally bit the bullet and looked into what it would take to deal with generating reports for these apps on my terms.

Details

Most of the examples I came across for Word (or Excel) document generation used $global: or $script: scoping to modify the documents in custom functions. This made the functions very script dependent and hard to reuse. To get around this limitation I encapsulate several essential functions as script methods on a custom object which represents the document you are generating.

Using script methods as properties on a psobject in order to to make a self-contained uber-object is not always the right way to go. In fact, I don’t know that this technique should ever be used in powershell as it starts to blend into the world of OOP programming (my roots from oh so long ago…) and is not very self documenting. But, right or wrong, it works for me so it may work for you as well.

A caveat to doing it this way is that you need to call the script methods in a non-standard manner (at least for powershell). Here is an example of some of the resulting code. Note how functions are called as if you are calling COM methods. This is a bit apropos as all we are doing is wrapping some code around COM object instances.

Here is another example using the same data from the prior example to create a multiple worksheet excel workbook.

I’ve setup the actual wrapper functions with a handful of the most important methods to suit my needs. Mostly they are just for inserting tables, moving around the document, and some other general tasks. I didn’t go overboard as really most of the features you would need to use can be called directly from the underlying objects. Anyway, the wrapper functions are off at the Technet Script Gallery as usual. There is plenty of room for improvement but maybe you will get some use from it in your projects in its current form.