Tag Archives: Petri

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.

Importing Hyper-V VM from a PowerShell Backup

My Petri article this week is on importing Hyper-V VMs from a backup. http://bit.ly/PRE0pk I have many more articles on Hyper-V on Windows 8 on the site as well. You can find all of my recent posts here.

New Petri Author

New Petri Author

I trust many of you are familiar with the Petri web site. This has long been a very valuable site for IT Pros. You can always find a wealth of reliable and practical information. Starting in December, I will be adding my voice to the mix. I will be contributing content that I hope you find valuable and worth your time. I expect my articles will be wide ranging, and not limited to Windows PowerShell. With the impending arrival of Windows 8 there will be much to write about. I hope you'll join me for the ride.

Petri IT Knowledgebase

One of the world's leading MCSE and IT related knowledge bases with thousands of Windows, Exchange, and Virtualization related tips, tricks and how-to articles.