Building Excel Reports with PowerShell

Last year I wrote a series of articles for the Petri IT KnowledgeBase on using Microsoft Excel with PowerShell. Today I received an email from a reader who had a question about article that showed how to build a drive usage report in Excel. In the article I suggest it wouldn’t be too difficult to modify my sample code to create a worksheet for each computer. Perhaps I was a bit too glib. So I decided to revise my sample script so that you could pass an array of computernames and build a single workbook with a named worksheet for each computer.


#Requires -version 2.0

<#
this version takes an array of computer names and creates a new
worksheet for each one.
#>

[cmdletbinding()]
Param([string[]]$computername=$env:computername)

#I hope it goes without saying that Excel needs to be installed
Write-Verbose "Creating Excel application"
$xl=New-Object -ComObject "Excel.Application"
$wb=$xl.Workbooks.Add()

#we'll need some constants
$xlConditionValues=[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]

Foreach ($computer in $computername) {
#get disk data
Write-Verbose "Getting disk data from $computer"
$disks=Get-WmiObject -Class Win32_LogicalDisk -ComputerName $computer -Filter "DriveType=3"

Write-Verbose "Adding Worksheet"
$ws=$wb.Worksheets.Add()

$cells=$ws.Cells

$cells.item(1,1)="Disk Drive Report"

#define some variables to control navigation
$row=3
$col=1

#insert column headings
Write-Verbose "Adding drive headings"

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

Write-Verbose "Adding drive data"
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%"
}

Write-Verbose "Adding some style"

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

#adjust some column widths
Write-Verbose "Adjusting column widths"
$ws.columns.item("C:C").columnwidth=15
$ws.columns.item("D:F").columnwidth=10.5
$ws.columns.item("B:B").EntireColumn.AutoFit() | out-null

#add some conditional formatting
Write-Verbose "Adding conditional formatting"

#get the starting cell
$start=$ws.range("F4")
#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

#insert a graph
Write-Verbose "Creating a graph"
$chart=$ws.Shapes.AddChart().Chart
$chart.chartType=$xlChart::xlBarClustered

$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))

$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)

#add labels
$chart.seriesCollection(1).Select() | Out-Null
$chart.SeriesCollection(1).ApplyDataLabels() | out-Null
#modify the chart title
$chart.ChartTitle.Text = "Utilization"
Write-Verbose "Repositioning graph"
$ws.shapes.item("Chart 1").top=40
$ws.shapes.item("Chart 1").left=400

Write-Verbose "Renaming the worksheet"
#rename the worksheet
$name=$disks[0].SystemName
$ws.Name=$name
#select A1
$ws.Range("A1").Select() | Out-Null
} #foreach

#delete extra sheets
Write-Verbose "Deleting extra worksheets"
$xl.worksheets.Item("Sheet1").Delete()
$xl.Worksheets.Item("Sheet2").Delete()
$xl.Worksheets.Item("Sheet3").Delete()

#make Excel visible
$xl.Visible=$True

$filepath=Read-Host "Enter a path and filename to save the file"

if ($filepath) {
Write-Verbose "Saving file to $filepath"
$wb.SaveAs($filepath)
$xl.displayAlerts=$False
$wb.Close()
$xl.Quit()
}

#end of script

If you looked at the original post, here are the changes I made. First, I modified the script parameters to take an array of computer names.


Param([string[]]$computername=$env:computername)

I decided to change the parameter name as well which worked out well because I could keep most of my code for each computer and simply insert a ForEach loop.


Foreach ($computer in $computername) {
#get disk data
Write-Verbose "Getting disk data from $computer"
$disks=Get-WmiObject -Class Win32_LogicalDisk -ComputerName $computer -Filter "DriveType=3"
...

This also meant I needed to move some code to before this loop since I only have to create the Excel application and workbook objects once. For each computer I use WMI to get disk data and create a new worksheet.


Write-Verbose "Adding Worksheet"
$ws=$wb.Worksheets.Add()

The rest of the Excel-related code remains the same for each computer. But I did alter the code to rename each worksheet.


#rename the worksheet
$name=$disks[0].SystemName
$ws.Name=$name
...

I also decided to delete the “extra” sheets. Unless you’ve modified Excel’s startup behavior new workbooks have Sheet1, Sheet2 and Sheet3 which I don’t need so I delete them.


#delete extra sheets
Write-Verbose "Deleting extra worksheets"
$xl.worksheets.Item("Sheet1").Delete()
$xl.Worksheets.Item("Sheet2").Delete()
$xl.Worksheets.Item("Sheet3").Delete()

With this new script I can run a command like this:


PS Scripts:\> .\New-ExcelDiskSpace2A.ps1 -computer novo8,jdhit-dc01 -Verbose

Which gives me a single workbook with a worksheet for each computer:

MultiExcelDiskReport

Download my revised script New-ExcelDiskSpace2A.

15 thoughts on “Building Excel Reports with PowerShell”

    1. Thanks for the link. The point of the original series was to demonstrate how to use Excel with PowerShell. The script in this post isn’t necessarily designed as a finished, production ready tool but rather as a proof of concept.

  1. Very nice, I have been working with PowerShell and Excel for a couple of years. I really like the way it works and the clean reports you get. I work in a corporate environment where Excel Macros are forbidden, so this is my way of recording “Macros”.

    1. Use Get-Content. I typically use a command like this to avoid any blank lines:

      get-content c:\work\computers.txt | where {$_} | do something with each name

      Or even more thorough would be to use a regular expression.

      get-content c:\work\computers.txt | where {$_ -match “^\S+$”} | do something with each name

    1. Maybe. It depends on how you are using the text file. Are you trying to modify the script or use it with a list of computers?

    2. Can’t you do this?

      PS C:\> $computers = get-content c:\work\computers.txt | where {$_ -match “^\S+$”}
      PS C:\> c:\scripts\New-ExcelDiskSpace2A.ps1 -computer $computers

  2. Hi Jeff, I would like to modify the script (awesome job on it) so I can just add to the text file if I want to add another machine…

    1. I see. All you need to do is set a new default value for the computername parameter. Try this:

      Param([string[]]$computername=(get-content YOUR-PATH\YOUR-FILE.txt | where {$_ -match "^\S+$"}))

  3. Jeff, your a hero, works like a champ… I am still a noobee at this stuff so the help is much appreciated…
    The only thing left for me is possibly appending server data to the excel spreadsheet for historical information and send it via an e-mail.. I should be able to handle that…

Comments are closed.