Tag Archives: Petri

Change is in the air

Petri_162x87If you have read my blog for any length of time, or followed me, then you know that I have contributed a lot of content to a number of online resources over the years, often on a recurring basis. You may not realize it, but spreading out my workload over several sites can be quite demanding. After a lot of deliberation, I’ve decided to consolidate all of my online writing efforts with the Petri IT Knowledgebase.

The Petri IT Knowledgebase has been around since 1999 and has always been a source of high quality content for IT Pros.  For the longest time it had a co.il domain name. But now you can find it at Petri.com. I will be publishing all of my online content exclusively at Petri where I will be a contributing editor. The site is being re-launched, lots of exciting things are in the works and I’m looking forward to being a part of it. At Petri, I will be primarily responsible for PowerShell related content. But you can expect to see me write about Active Directory, Group Policy, WMI, Hyper-V and Windows Server 2012 (and later).

I will still be blogging here and I will continue to create courses at Pluralsight. You will still find me at conferences like TechEd (or whatever it gets renamed) and the PowerShell Summit. I’ll still be doing PowerShell-related consulting and training on my own as well. Naturally, all of my existing content “in the wild” will remain where it is.

In most regards, nothing really changes other than the fact that you only have one place to check for my latest work. I hope you’ll come along for a great ride.

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.