Building Excel Reports with PowerShell

I found this on;
Blog Name: The Lonely Administrator
Blog URL:
“Building Excel Reports with PowerShell”

I have yet to figure out how to do a Ping Back so I just copy and paste the whole thing.
Some of the ways he does things are not the way I would do them, but the results are just what you would want.
This script collects Hard Disk usage and displays the results in an Excel file complete with a Graph.
It also puts each computer on your list on its own worksheet.
Lots of good stuff here and much of it can be used for other purposes.

cmdletbinding()] # Coment this and the next line to do just the machine you are running the script on
Param([string[]]$computername=$env:computername) # This will do a list of machines
# $computername=$env:computername # Uncomment to do just the machine you are running the script on
#I hope it goes without saying that Excel needs to be installed
Write-Verbose "Creating Excel application" 
$xl=New-Object -ComObject "Excel.Application" 

#we'll need some constants

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" 


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

    #define some variables to control navigation

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

    "Drive","SizeGB","FreespaceGB","UsedGB","%Free","%Used" | foreach {

    Write-Verbose "Adding drive data" 
    foreach ($drive in $disks) {
        $cells.item($Row,$col)=($drive.Size - $drive.Freespace)/1GB
        $cells.item($Row,$col)=($drive.Size - $drive.Freespace) / $drive.size

    Write-Verbose "Adding some style" 

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

    #adjust some column widths
    Write-Verbose "Adjusting column widths" 
    $ws.columns.item("B:B").EntireColumn.AutoFit() | out-null

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

    #get the starting cell
    #get the last cell
    #add the icon set
    $Selection.FormatConditions.AddIconSetCondition() | Out-Null
    $Selection.FormatConditions.item(1).ReverseOrder = $True
    $Selection.FormatConditions.item(1).ShowIconOnly = $False
    $Selection.FormatConditions.item(1).IconSet = $xlIconSet::xl3TrafficLights1
    #insert a graph
    Write-Verbose "Creating a graph" 

    #get the last cell
    #get the last cell


    #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
    #select A1
    $ws.Range("A1").Select() | Out-Null
} #foreach

#delete extra sheets
Write-Verbose "Deleting extra worksheets"

#make Excel visible 

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

if ($filepath) {
    Write-Verbose "Saving file to $filepath" 

#end of script
###### End Posh Script ######## 

5 thoughts on “Building Excel Reports with PowerShell

  1. Why did I have to find this when I am so close to finishing..!!.. Awesome stuff, saved me a ton of hours on the data labels section.

    Now if only I can get the $chart.ApplyLayout = 4 happening, mind you this is through powercli (vmware).

  2. I just want to store flat text in the field as it’s already been formatted by the query… What would the .Numberformat be for that?

    cheers T.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s