One popular way to gather and analyze both system configuration and system performance data is by using Microsoft Excel. Regular tables can be used to order and filter data and pivot tables and graphs can be used to analyze further any kind of collected data and find trends. This month we’ll see how to leverage PowerShell to gather system information data and consolidate it in Excel.
Excel automation basicsThe ability to build automation tasks by integrating different products has been available since a long time. If you’ve been around long enough, you may recall in the AmigaOS world the ARexx scripting language that could be used to automate tasks with products that exposed specific messaging ports. In Microsoft world, task automation has been traditionally done using DDE and OLE Automation which is based on COM.
More interestingly, you can start adding your own data to the sheet as in the following example:
In the following picture you can see the result:
Once you’ve added data, you can start manipulating it. For example, let’s say you want to add some borders around the cells containing the data you just added. You can start by creating a Range object representing a range of cells for convenience as follows:
Then you change the size so that values fit in cells width and add a dotted border line for each cell in the range by simply referring to the Borders collection of the range as in the code that follows:
Finally, you add a thicker border to the whole range of cells by invoking the BorderAround method of the range as follows:
You can see the final result in the following picture:
Adding borders, colors and all other visual tweaks to make the data more readable is quite boring and distracting especially when you’re trying to get some reporting done. Luckily, Excel packages some nice style that you can apply to ranges that represent tables of data. You start by clearing the border lines added previously and then continue by adding to the worksheet a ListObject object derived from the previously defined range as follows:
At this point you should already see the range formatted as a table with headers and one row using the default style. You can change the table style through the TableStyle property directly from the ListObject object or by its reference through the Range object as the following code shows:
The following picture show the end result which may be different for you depending on the styles defined on your system:
When you’re done, to save workbook you invoke the SaveAs method after suppressing any application generated warning as follows:
Finally, you should close the application by invoking the Quit method and do some cleanup to release the COM the object as the following code shows:
You can find the complete PowerShell code in the Script 1 – Excel Automation Basics
Gathering WMI data
There are several reasons why you want to integrate with Excel within scripts. For example you may want to access WMI API in a simple way by using built-in PowerShell cmdlets and prepare some reports about one or more systems by leveraging the pipe and by feeding collected data to an Excel sheet. So let’s build a script to generate a report about local drives size and space used. You start by specifying some parameters that can be passed to the script as follows:
Then you add to the script a small library of common functions, based on what we introduced in the first part of the article, which can be used also for other scripts in future:
The name of the functions should be pretty much self-explaining: Excel-Init creates and initializes a new instance of Excel, Excel-NewWorkBook adds a new workbook to a running instance of Excel, Excel-NewWorkSheet adds a new worksheet to a workbook and Excel-SaveAndExit is used to save the workbook to file and properly close Excel.
You then build a function that queries the WMI Win32_Volumes class and populate a worksheet with the returned data as the following code shows:
To increase readability, you can create a function to add some formatting to the data as follows:
You should note that in this case, to size columns and to change the table style, you didn’t define a Range object. Instead, because you know how you built the worksheet, you can use a shortcut: the UsedRange property of the worksheet returns a Range object with all the cells you populated with data. Also, you can omit all parameters when adding a ListObject to the worksheet and it automatically chooses the used range.
Finally, following is the main code for the script that calls the previously defined functions:
Running the script creates the desired Excel report as shown in the following picture:
You can find the complete PowerShell code in the Script-2-Get-WMI-information-Static
In this first part of this mini-series we’ve covered the basics of automating tasks with Excel using PowerShell and we’ve gone through a small example to automatically build Excel reports from a specific WMI query. In the next article we will see how to dynamically build reports from any WMI query and how to create multi-worksheet workbooks from a configuration file. In other words, we’ll build a tool to automate gathering of common data into Excel reports. Until then, we’d love to hear from you—especially about which topics you’d like us to cover next.
Herbert Albert is co-author of this post.