Tag Archives: Excel

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.

Quick and Dirty Excel from PowerShell

I continue to tinker with Office applications and Windows PowerShell. I was looking at an Excel issue related to opening a new workbook. To verify the problem wasn’t PowerShell related I offered a suggestion to try creating an Excel workbook in VBScript.

In VBScript creating a blank workbook in Microsoft Excel can be accomplished with three lines of code.


set xl=wscript.createObject("Excel.Application")
set wb=xl.workbooks.add()
xl.Visible=vbTrue

In Windows PowerShell we essentially use the same 3 lines.


$xl=new-object -com "excel.application"
$sb=$xl.Workbooks.Add()
$xl.Visible=$True

But PowerShell is all about the objects, so how about a quick and dirty approach? Something perhaps in one line:


PS S:\> ((new-object -com "excel.application").Workbooks.Add()).application.Visible=$True

You could easily build a simple function to run the 3 lines it takes to open a new Excel workbook but one-liners like this make me smile. Granted, I’m probably not likely to use it, but if nothing else I think it reinforces the object nature of Windows PowerShell.

Each expression nested in () is treated as an object. The inner most expression using New-Object, creates the Excel application object which has a Workbooks property that is an object with an Add() method. Invoking this method creates the workbook object. If I stopped there Excel would be running but not visible. So I get the Application property of the workbook object, and set the Visible property to True.

This command doesn’t offer any way to continue working with Excel from PowerShell; it only opens a new workbook. Let me be clear, I’m not advocating that this is how you work with Excel from PowerShell. I think you will need scripts like the ones I posted from my Deep Dive session. But this one-liner demonstrates what is possible with PowerShell and maybe it will add a little smile to your day as well.