Integrating Microsoft Excel with PowerShell: Create a Rich Excel Doc

And we’re back with our series on integrating Microsoft Excel with PowerShell. Last time we looked at building a basic report with Microsoft Excel and Windows PowerShell. In some ways, what we created last time isn’t that much different than creating a CSV and opening it in Excel. So, if you want to use Excel, let’s really use it! In today’s post, I’ll use the basic demonstration script from last time, but create a richer Excel document. Later, in part three I’ll show you how to read data from an Excel file.

How to Integrate Microsoft Excel with PowerShell

As before, we’ll get disk information via WMI and create an Excel application object.

Win32_LogicalDisk -ComputerName $computer -Filter "DriveType=3"
$xl=New-Object -ComObject "Excel.Application"

Like Microsoft Word, a great deal of Excel automation relies on the use in internal constant values like xlDown. I could track down the value and create a variable, or I can load the appropriate classes that contain the required constants. For what I have in mind, I will need to grab the following:

[Microsoft.Office.Interop.Excel.XLConditionValueTypes]
$xlTheme=[Microsoft.Office.Interop.Excel.XLThemeColor]
$xlChart=[Microsoft.Office.Interop.Excel.XLChartType]
$xlIconSet=[Microsoft.Office.Interop.Excel.XLIconSet]
$xlDirection=[Microsoft.Office.Interop.Excel.XLDirection]

Later, when I want to use xlDown, I can specify it as $xlDirection::xlDown, which you’ll see in a bit. For now, I’ll write the disk data as I did in Part 1, but now let’s add some style. Another way of referencing parts of a spreadsheet is to use a Range from the worksheet object. You can either specify a range by a single cell reference like A1 or a range like A1:A10. The Range object has a style property so I’m going to set A1 to the Title style and my table header to “Heading 2”.

$range.Style="Title"
#or set it like this
$ws.Range("A3:F3").Style = "Heading 2"

Another common formatting option is to adjust column widths. We can either set columns to a specific width or autosize it.

$ws.columns.item("D:F").columnwidth=10.5
$ws.columns.item("B:B").EntireColumn.AutoFit() | out-null


By the way, I pipe methods like AutoFit() to Out-Null to suppress any output, since I don’t really need it. Now for the really fun stuff. I thought it might be nice to take advantage of Excel’s conditional formatting. Specifically, I want to use a traffic light icon set to reflect how heavily utilized a particular drive might be. As I said before, if we’re going to create an Excel file, let’s take advantage of it. I’m going to show you the code, but don’t panic.

#get the last cell
$Selection=$ws.Range($start,$start.End($xlDirection::xlDown))
#add the icon set
$Selection.FormatConditions.AddIconSetCondition() | Out-Null
$Selection.FormatConditions.item($($Selection.FormatConditions.Count)).SetFirstPriority()
$Selection.FormatConditions.item(1).ReverseOrder = $True
$Selection.FormatConditions.item(1).ShowIconOnly = $False
$Selection.FormatConditions.item(1).IconSet = $xlIconSet::xl3TrafficLights1
$Selection.FormatConditions.item(1).IconCriteria.Item(2).Type=$xlConditionValues::xlConditionValueNumber
$Selection.FormatConditions.item(1).IconCriteria.Item(2).Value=0.8
$Selection.FormatConditions.item(1).IconCriteria.Item(2).Operator=7
$Selection.FormatConditions.item(1).IconCriteria.Item(3).Type=$xlConditionValues::xlConditionValueNumber
$Selection.FormatConditions.item(1).IconCriteria.Item(3).Value=0.9
$Selection.FormatConditions.item(1).IconCriteria.Item(3).Operator=7

I did not wake up one morning knowing how to do this in PowerShell. Instead, I created an Excel macro to apply the formatting, and then translated that code into PowerShell. I wish I could give you a set of translation rules, but it just takes trial and error and experience. Notice the use of the constant values?
Next, I’m going to add a bar chart style graph to the worksheet:

​$chart=$ws.Shapes.AddChart().Chart
$chart.chartType=$xlChart::xlBarClustered

Again, I ran this process through a macro to discover the methods and correct values. Next, I need to select the data sources for the graph.

​$start=$ws.range("A3")
#get the last cell
$Y=$ws.Range($start,$start.End($xlDirection::xlDown))
$start=$ws.range("F3")
#get the last cell
$X=$ws.Range($start,$start.End($xlDirection::xlDown))

The drive names will be on the Y axis and %Used values will be on the X axis. I then use this collection of ranges to define the chart data.

​$chartdata=$ws.Range("A$($Y.item(1).Row):A$($Y.item($Y.count).Row),F$($X.item(1).Row):F$($X.item($X.count).Row)")
$chart.SetSourceData($chartdata)

The last thing I want with the graph is to include data labels and a chart title.

​$chart.seriesCollection(1).Select() | Out-Null
$chart.SeriesCollection(1).ApplyDataLabels() | out-Null
$chart.ChartTitle.Text = "Utilization"


More than likely, Excel won’t put the graph where you want it, so you can use code like this to move it:

​$ws.shapes.item("Chart 1").top=40
$ws.shapes.item("Chart 1").left=400

Top and Left are positions from the top and left of the Excel window. This may take some trial and error to get the right values, and be sure to test on different computers. The last step is to rename the worksheet with the computername.

​$xl.worksheets.Item("Sheet1").name=$name

Once you understand how all of this Excel magic works, it’s not that much more work to add a worksheet for each computer you want to query. Figure 1 shows the final result.
Excel PowerShell Figure1
Figure 1: An Enhanced Excel Report

Conclusion

You can download my demonstration script and try it out for yourself. If you really need to write to Excel, I encourage you to go all out. It will take some work to master the basics but it is well worth the effort. Next time we’ll look at reading data from Excel files.