Hey, Scripting Guy!Learning to Excel

The Microsoft Scripting Guys

Having spent the last month living and working in Australia, this Scripting Guy is constantly amazed by the creativity and overall robustness of my Aussie colleagues. A few nights ago, a friend here took the Scripting Wife and me out to dinner. He said the restaurant was a short walk from our hotel.

Forty minutes later, we arrived at a beautiful restaurant overlooking Sydney Harbor, with the Sydney Opera House glistening like a seashell washed up on a multicolored beach. The Harbour Bridge—the longest single-span bridge in the world—imposes order by holding the two peninsulas together like a massive clamp restraining two warped boards.

Back home, 40 minutes of walking is not a short stroll to dinner. It's a daily workout preceded by 15 minutes of warm-ups and stretching.

One reason this Scripting Guy is enjoying this trip down under is for the different perspective my colleagues here have. They seem to have a unique way of looking at things, which often challenges my preconceived notions of how things should be done.

In scripting, it's all too easy to get into a rut as you stay the course with tried-and-true methodologies. Many times, using the same approach to obtain the same results is exactly what you want.

From a monitoring perspective, for instance, by using Windows PowerShell you can easily obtain a nice snapshot of process utilization on your local computer. You use the Get-Process cmdlet and are greeted with nice, orderly output, as shown in Figure 1.

Figure 1 Looking at local processes using Get-Process

The results of the Get-Process cmdlet are useful in many circumstances. They show the number of open handles, a couple of different views of memory consumption, and a snapshot of CPU utilization. And when Windows PowerShell 2.0 comes along, you will even be able to use Get-Process with a –computername parameter to retrieve this overview from a remote computer. With all this goodness, why in the world would anyone ever bother investigating anything else?

The problem is there is a whole lot of detail hidden in the long columns of data. All that data tends to mask the more important details. And while it's great that in the future Windows Power­Shell 2.0 will support the –computername parameter, right now that does absolutely nothing for a network administrator. Therefore, we'll just have to use Windows Management Instrumentation (WMI) and the Win32_Process WMI class to do our monitoring of remote systems and present the information in a way that's truly useful. If you thought the output from Get-Process was bountiful, get a load of the output from Win32_Process, shown in Figure 2.

Figure 2 Using WMI to view processes

So what's a poor ol' network administrator to do when all he wants is an easy-to-read report of the amount of memory being consumed? This is where you need to think outside the box, get out of your shell, and begin to Excel at automation. More than likely, you have Microsoft Office Excel installed on your computer. If you are like us, you are not an expert, but it is part of the Microsoft Office system and you might as well make use of it.

How hard is it to automate Excel? It is actually rather easy because Microsoft has created an automation model specifically for working with Excel. The program ID is Excel.Application, and it is a COM object. When you create an instance of the Excel.Application object, by default Excel will start and run, but it will not be visible. However, you can use the visible property to make Excel visible.

Here's how to create the Excel.Application object, query the status of the visible property, and then set the visible property to true:

PS C:\> $excel = New-Object -ComObject Excel.Application
PS C:\> $excel.Visible
False
PS C:\> $excel.Visible = $true

When you've done that, you are greeted with a rather odd view of Excel, which looks like just a shell of the Excel application (see Figure 3). There are no workbooks, no spreadsheets—nothing but naked Excel.

Figure 3 Naked Excel—no workbooks or spreadsheets

We need to add a workbook to the application. To do this, we use the add method from the workbook object. The workbook object is accessed from the main Excel.Application object, as you can see here where we store the workbook object in a variable named $workbook:

$workbook = $excel.Workbooks.add()

Now we need to connect to a specific spreadsheet. By default, when a workbook is added to Excel, three spreadsheets are added to the workbook. These spreadsheets can be addressed by number. In this line of code, we connect to the first spreadsheet and store the returned spreadsheet object in a variable named $sheet:

$sheet = $workbook.worksheets.Item(1)

Now we can write data to that spreadsheet. Information in Excel spreadsheets is stored in cells. Because cells live in spreadsheets, you use the spreadsheet object that is stored in the $sheet variable to gain access to a specific cell. You do so by using numbers that refer to rows and columns in the spreadsheet. One thing that is a bit confusing is that in Excel spreadsheets, rows are numbers and columns are letters. When using the automation model, however, both rows and columns are numbers. The first number is the row and the second number is the column. You can write to the cell by simply making a value assignment to that specific cell:

$sheet.cells.item(1,1) = "Test"

After adding a workbook to our Excel.Application object and data to a cell in the spreadsheet, our Excel workbook looks like the one in Figure 4.

Figure 4 Adding a value to a cell

With all of this in mind, let's put together something useful. Let's get a collection of process information from WMI, write the name and memory consumption of each process to an Excel spreadsheet, and then create a chart to highlight the memory used. That's just what WriteProcessInformation­ToExcel.ps1 does. You will find the whole script on the TechNet Magazine Web site.

We begin the script by using the Get-WmiObject cmdlet to retrieve a collection of information about processes. We use the Win32_Process WMI class to obtain this information, and we store it in the $processes variable:

$processes = Get-WmiObject -class Win32_Process

Now we create an instance of the Excel.Application object and store it in the variable $excel, then make the application visible and add a workbook. You would normally perform these steps for any Excel automation you decided to do. Here are the lines of code:

$excel = new-object -comobject excel.application
$excel.visible = $true
$workbook = $excel.workbooks.add()

One of our annoyances with Excel is the way that a workbook always creates three spreadsheets. We consider this to be wasteful because we can barely use a single spreadsheet, much less three. Luckily, with automation, we can simply delete the extra spreadsheets using the worksheets collection to connect to the third spreadsheet and calling the delete method. The same can be done to delete the second spreadsheet:

$workbook.workSheets.item(3).delete()
$workbook.WorkSheets.item(2).delete()

Next, we rename the remaining spreadsheet. This is important because if you ever decide to use ActiveX data objects (ADO) to query the Excel spreadsheet, you use the spreadsheet name in the connection string. Therefore, to make the code readable and intuitive, the spreadsheet should have a logical name. To rename the spreadsheet, simply assign a new value for the name property of the specific spreadsheet. Here, we rename the first spreadsheet to "Processes":

$workbook.WorkSheets.item(1).Name = "Processes"

Now we need to connect to the renamed spreadsheet. Use the Item method from the worksheets object and give it the name of the spreadsheet:

$sheet = $workbook.WorkSheets.Item("Processes")

The first row of the spreadsheet will contain header information. We will draw a border and make the property names bold. As a result, the data will begin in row two, so we assign the value of 2 to the counter variable $x:

$x = 2

Next, four lines of code create four enumeration types. Enumeration types are used to tell Excel which values are allowed for specific types of options. As an example, xlLineStyle enumeration is used to determine the kind of line to draw: double, dashed, and so on. These enumeration values are documented on MSDN.

To make the code easier to read, we create shortcut aliases for each of the four enumeration types we will be using. Essentially, we're casting a string that represents the name of the enumeration to a [type]. This technique is actually a pretty cool trick:

$lineStyle = "microsoft.office.interop.excel.xlLineStyle" -as [type]
$colorIndex = "microsoft.office.interop.excel.xlColorIndex" -as [type]
$borderWeight = "microsoft.office.interop.excel.xlBorderWeight" -as [type]
$chartType = "microsoft.office.interop.excel.xlChartType" -as [type]

Now we need to format the first row. We make the font bold, define the line as xlDashDot, allow the color to be automatically specified, and set the border weight to medium thickness:

For($b = 1 ; $b -le 2 ; $b++)
{
 $sheet.cells.item(1,$b).font.bold = $true
 $sheet.cells.item(1,$b).borders.LineStyle = $lineStyle::xlDashDot
 $sheet.cells.item(1,$b).borders.ColorIndex = $colorIndex::xlColorIndexAutomatic
 $sheet.cells.item(1,$b).borders.weight = $borderWeight::xlMedium
}

When that's done, we assign values to the first row by selecting the cell with the item method and giving the coordinates of the row and column. Next, we do a straight value assignment to write the column headers:

$sheet.cells.item(1,1) = "Name of Process"
$sheet.cells.item(1,2) = "Working Set Size"

Now we need to put the process information stored in the $processes variable that was created as a result of our WMI query into the appropriate cells. We use the foreach statement to walk through the collection of process information. We define the variable $process to be our enumerator (placeholder) for the collection, and we choose the name and the workingSetSize properties to write to the first and second column respectively.

Here is where the $x variable comes into play. We begin with the second row, and as we walk through the collection of processes, we increment the value of the $x variable so that it always points to the current row in the collection. This is how we are able to sort through all the data stored in the $processes collection of process information:

Foreach($process in $processes)
{
 $sheet.cells.item($x, 1) = $process.name
 $sheet.cells.item($x,2) = $process.workingSetSize
 $x++
} #end foreach

After we have filled out the Excel spreadsheet, we want to adjust the size of the columns so that the cells are the same size as the data that is stored there. To do this, we could create a range by specifying the column coordinates to use; we can, however, simply use the usedRange property from the spreadsheet. When we have created our range object, we choose the EntireColumn property and use the AutoFit method to resize the columns. Because this method always returns data, we pipe the results to the Out-Null cmdlet. This keeps from cluttering up the console with useless information. Here's the code we use:

$range = $sheet.usedRange
$range.EntireColumn.AutoFit() | out-null

At this point, we could stop and we would have a nice spreadsheet with the name and memory working set of all our processes. But let's go ahead and create a chart. It's easy. We use the add method of the charts object from the workbook. Because this method also returns information we do not want to see, we pipeline the results to the Out-Null cmdlet, as shown here:

$workbook.charts.add() | out-null  

The above command adds a line chart. If you want to define a different type of chart, you need to use one of the chart type enumeration values. To do this, you can make use of one of the microsoft.office.interop.excel.xlChartType enumeration values, such as the xl3DPieExploded type. The xl3DPie­Exploded type creates, not surprisingly, a three-dimensional pie chart that is exploded. We assign this enumeration type to the ActiveChart object's chartType property. Then we assign the data source for the chart to be the range we defined in the $range variable. The result is that you see the line chart flash briefly, and then the 3D pie chart explodes on the screen. Here's the code:

$workbook.ActiveChart.chartType = $chartType::xl3DPieExploded
$workbook.ActiveChart.SetSourceData($range)

As long as we are playing around, let's rotate the pie chart. We can do this by using the rotation property of the ActiveChart object. We use a for statement to increment the count to 360 by 15. 360 is the number of degrees in a circle; the chart will rotate a complete circle 15 degrees at a time. It actually looks pretty cool. Here is the code to perform this feat:

For($i = 1 ; $i -le 360 ; $i +=15)
{
 $workbook.ActiveChart.rotation = $i
}

The last thing we need to do is to save the spreadsheet. To do this, we use the Test-Path cmdlet to see if the spreadsheet already exists. If it does, we delete the old one by using the Remove-Item cmdlet, and then we save the current workbook to the location stored in the $strPath variable. We use the ActiveWorkbook object from the Excel.Application object and the SaveAs method to save the workbook. If there is not already a copy of the spreadsheet saved, we use the SaveAs method from the ActiveWorkbook object and save it directly:

IF(Test-Path $strPath)
  { 
   Remove-Item $strPath
   $Excel.ActiveWorkbook.SaveAs($strPath)
  }
ELSE
  {
   $Excel.ActiveWorkbook.SaveAs($strPath)
  }

When the script runs, you are presented with a chart just like the one shown in Figure 5.

Figure 5 The Processes exploded pie chart

The spreadsheet itself is on the Processes tab. Figure 6 shows the column headers, the dot/dash line style we selected for the border, and the bold column heads. The process names and the working set size properties are the two columns of data we have displayed.

Figure 6 The finished spreadsheet

As you can see, with the Excel.Application automation model, you have the tools you need to let you process data from your servers in a manner that can take advantage of the analysis and charting tools from this rich and powerful application.

Ed Wilson is a Senior Consultant at Microsoft and a well-known scripting expert. He is a Microsoft Certified Trainer who delivers a popular Windows PowerShell workshop to Microsoft Premier customers worldwide. He has written eight books, including several on Windows scripting, and has contributed to almost a dozen other books. Ed holds more than 20 industry certifications.

Craig Liebendorfer is a wordsmith and longtime Microsoft Web editor. Craig still can't believe there's a job that pays him to work with words every day. One of his favorite things is irreverent humor, so he should fit right in here. He considers his greatest accomplishment in life to be his magnificent daughter.