Integrating Microsoft Excel with PowerShell: Build a Basic Report

Not too long ago I wrote about Integrating Microsoft Word with PowerShell, but I know that after text files, the most used tool for Windows administration is Microsoft Excel. Based on what I see in forums and talking with people at conferences, there is a lot of information tucked away in Excel spreadsheets. There’s no shortage of people who want to save information to Excel or read from it. Normally, I tell people that if they are using PowerShell, they can simply export results to a CSV file and then open that in Excel, but perhaps you really need an Excel file.
I’m going to take the next few columns and explore how to integrate Microsoft Excel with Windows PowerShell. Today we’ll go into building a basic report with Microsoft Excel and Windows PowerShell. In part two, I’ll go over how to 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

Microsoft Excel has a very complex COM object model that we can interact with in Windows PowerShell. Let’s start at the beginning and create an object for the Excel application.

​PS C:\> $xl=New-Object -ComObject "Excel.Application"

Right now Excel is running in the background, even though there isn’t an interactive window.

​PS C:\> get-process excel
Handles  NPM(K)    PM(K)      WS(K) VM(M)   CPU(s)     Id ProcessName
-------  ------    -----      ----- -----   ------     -- -----------
    203      23    16392      24340   267     0.28   1280 EXCEL

Next, we’ll create a workbook object.

​PS C:\> $wb=$xl.Workbooks.Add()


And from here, we’ll create a worksheet object.

​PS C:\> $ws=$wb.ActiveSheet

You can pipe any of these objects to Get-Member to learn more about them. For now, let’s make the application visible.

​PS C:\> $xl.Visible=$True

When you get to scripting you don’t need this, but it will help to see the results from our PowerShell commands. There are a variety of ways to enter information into the spreadsheet. For simple tasks, I’m going to show how to do this using the cell objects.

​PS C:\> $cells=$ws.Cells

Each cell object can be retrieved as an item using row and column coordinates.

​PS C:\> $cells.item(1,1)

If you try this, you’ll get a lot of information. We’ll go ahead and enter some information into the first cell.

​PS C:\> $cells.item(1,1)=$env:computername

Your computer name should now be in cell A1. Let’s add a bit more data.

​PS C:\> $cells.item(1,2)=$env:username
PS C:\> $cells.item(2,1)=(get-Date)

It’s really that easy. You just need to keep track of where you are. If you need some basic formatting, you can use each cell’s Font property.

​PS C:\> $cells.item(1,1).font.bold=$True
PS C:\> $cells.item(1,2).font.bold=$True
PS C:\> $cells.item(1,1).font.size=16
PS C:\> $cells.item(1,2).font.size=16

When it is time, we can save the file using the SaveAs() method from the WorkBook object.

​PS C:\> $wb.SaveAs("c:\work\test.xlsx")

To fully exit, we’ll close the workbook and quit Excel.

​PS C:\> $wb.Close()
PS C:\> $xl.Quit()

If you check processes, you might still see Excel running but it should exit within 5 to 10 minutes, at least based on my experience. Those are the basic nuts and bolts, but before we wrap up, let me leave you with a sample script you can download from the site to pull all of this material together.

​Param([string]$computer=$env:computername)
​#get disk data
$disks=Get-WmiObject -Class Win32_LogicalDisk -ComputerName $computer -Filter "DriveType=3"
$xl=New-Object -ComObject "Excel.Application"
$wb=$xl.Workbooks.Add()
$ws=$wb.ActiveSheet
$cells=$ws.Cells
$cells.item(1,1)="{0} Disk Drive Report" -f $disks[0].SystemName
$cells.item(1,1).font.bold=$True
$cells.item(1,1).font.size=18
#define some variables to control navigation
$row=3
$col=1
#insert column headings
"Drive","SizeGB","FreespaceGB","UsedGB","%Free","%Used" | foreach {
    $cells.item($row,$col)=$_
    $cells.item($row,$col).font.bold=$True
    $col++
}
foreach ($drive in $disks) {
    $row++
    $col=1
    $cells.item($Row,$col)=$drive.DeviceID
    $col++
    $cells.item($Row,$col)=$drive.Size/1GB
    $cells.item($Row,$col).NumberFormat="0"
    $col++
    $cells.item($Row,$col)=$drive.Freespace/1GB
    $cells.item($Row,$col).NumberFormat="0.00"
    $col++
    $cells.item($Row,$col)=($drive.Size - $drive.Freespace)/1GB
    $cells.item($Row,$col).NumberFormat="0.00"
    $col++
    $cells.item($Row,$col)=($drive.Freespace/$drive.size)
    $cells.item($Row,$col).NumberFormat="0.00%"
    $col++
    $cells.item($Row,$col)=($drive.Size - $drive.Freespace) / $drive.size
    $cells.item($Row,$col).NumberFormat="0.00%"
}
$xl.Visible=$True
$filepath=Read-Host "Enter a path and filename to save the file"
if ($filepath) {
    $wb.SaveAs($filepath)
}

This is something you might want to do in PowerShell: use WMI to get disk usage information and record it into an Excel spreadsheet. The script takes a computername as a parameter but defaults to the localhost. It then uses Get-WMIObject to retrieve disk information.

The first part of the script should look familiar, as it creates the Excel application and objects. The script inserts a title in A1.

$cells.item(1,1)="{0} Disk Drive Report" -f $disks[0].SystemName
$cells.item(1,1).font.bold=$True
$cells.item(1,1).font.size=18

The main part of the script needs to take the data for each logical disk and write a few properties to Excel. Because I’m going to use cell objects with references to rows and columns, I’ll define some variables to help with navigation.

​$row=3
$col=1

With these, I can insert my table headings.

​"Drive","SizeGB","FreespaceGB","UsedGB","%Free","%Used" | foreach {
    $cells.item($row,$col)=$_
    $cells.item($row,$col).font.bold=$True
    $col++
}

Each time through the loop, $col is increased by one so I keep “moving” to the right. Now I need to iterate through the collection of disks. Each time through, I need to move “down” one row and make sure I start at the first column.

​foreach ($drive in $disks) {
    $row++
    $col=1
    $cells.item($Row,$col)=$drive.DeviceID
    $col++
    $cells.item($Row,$col)=$drive.Size/1GB
    $cells.item($Row,$col).NumberFormat="0"
    $col++
...

I then insert the appropriate WMI property into the appropriate cell. By incrementing the column, I can “move” to the left and continue the process. Notice I’m also formatting the value of each cell using the NumberFormat property. One way to discover things like this is to record an Excel macro making all the changes you want, and then look at the resulting VBA code. With a little practice, you can translate the commands into PowerShell.
When the script has finished writing the data to the spreadsheet, I make it visible and prompt the user for a file name. If one is entered, then the file is saved. Otherwise, you can adjust the spreadsheet further and then save it manually. This demo script does not automatically close Excel. The script should produce something like Figure 1.
PowerShell export to Excel
Figure 1: A PowerShell Generated Excel Report

Conclusion

I know this is a lot to tackle, so I’ll wrap it up for now. In Part 2, we’ll look at some additional formatting options and other items that take advantage of Microsoft Excel. If you are going to create Excel documents from PowerShell, you might as well make the most of it.