Building Excel Reports with PowerShell


I found this on;
Blog Name: The Lonely Administrator
Blog URL: http://jdhitsolutions.com/blog
“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" 
$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
###### End Posh Script ######## 

Advertisements

Power Tip of the Day

Quote

Create File Shares Remotely
To create a new file share remotely, you could use the WMI class Win32_Share and its Create() method. Invoke-WmiMethod helps you run WMI methods locally and remotely. To create a new share locally or remotely, try this:

###### Start Posh Script ######## 
$Path = 'C:\'
$Name = 'serviceshare'
$Type = 0
$Maximumallowed = 5
$Description = 'PowerShell Share'
$ComputerName = 'storage1'
$rv = Invoke-WmiMethod -Path 'Win32_Share' -ComputerName $ComputerName -Name Create -ArgumentList $null, $Description, $MaximumAllowed, $Name, $null, $Path, $Type
$rv.ReturnValue
###### End Posh Script ######## 

This would create a new share called “serviceshare” on the machine “storage1”. Remove the parameter -ComputerName if you want to run the method locally on your machine. Add the parameter -Credential if you want to run the command remotely with a different identity.
From: http://powershell.com/cs/

Problem with HTC Phone and Sync


I have a HTC Inspire phone that I use all the time. It is “my phone”, I have no land line. One thing I try to do is keep it and my Outlook contact list in sync. The problem is that frequently, the phone does not see the computer or vice versa.
I tried all sorts of things to get it to work reliably, but no luck. I did find that some cables worked and others did not. I would have thought that all mini USB cables were wired the same, perhaps they are. It could be the connectors on the cables or the USB port on my phone is defective.
However, if you are having issues syncing your phone, try a different cable or two or three. Once you find one that works, label it. I also found cables that would charge the phone, show the phone as a disk drive but not sync. Save those for your car or the USB charger and again label them.
If it is truly not the cable then it might be the USB port on your computer or you might need to remove and reinstall the latest version of HTC Sync. Get it from here ( http://www.htc.com/www/software/htc-sync-manager/download/ ).