#Requires -version 2.0਀ #Integrating Excel with PowerShell Part 2 Sample Script਀ [cmdletbinding()]਀倀愀爀愀洀⠀嬀猀琀爀椀渀最崀␀挀漀洀瀀甀琀攀爀㴀␀攀渀瘀㨀挀漀洀瀀甀琀攀爀渀愀洀攀⤀ ਀⌀最攀琀 搀椀猀欀 搀愀琀愀 Write-Verbose "Getting disk data from $computer" ਀␀搀椀猀欀猀㴀䜀攀琀ⴀ圀洀椀伀戀樀攀挀琀 ⴀ䌀氀愀猀猀 圀椀渀㌀㈀开䰀漀最椀挀愀氀䐀椀猀欀 ⴀ䌀漀洀瀀甀琀攀爀一愀洀攀 ␀挀漀洀瀀甀琀攀爀 ⴀ䘀椀氀琀攀爀 ∀䐀爀椀瘀攀吀礀瀀攀㴀㌀∀ ਀圀爀椀琀攀ⴀ嘀攀爀戀漀猀攀 ∀䌀爀攀愀琀椀渀最 䔀砀挀攀氀 愀瀀瀀氀椀挀愀琀椀漀渀∀  $xl=New-Object -ComObject "Excel.Application" ਀ #we'll need some constansts਀␀砀氀䌀漀渀搀椀琀椀漀渀嘀愀氀甀攀猀㴀嬀䴀椀挀爀漀猀漀昀琀⸀伀昀昀椀挀攀⸀䤀渀琀攀爀漀瀀⸀䔀砀挀攀氀⸀堀䰀䌀漀渀搀椀琀椀漀渀嘀愀氀甀攀吀礀瀀攀猀崀 $xlTheme=[Microsoft.Office.Interop.Excel.XLThemeColor]਀␀砀氀䌀栀愀爀琀㴀嬀䴀椀挀爀漀猀漀昀琀⸀伀昀昀椀挀攀⸀䤀渀琀攀爀漀瀀⸀䔀砀挀攀氀⸀堀䰀䌀栀愀爀琀吀礀瀀攀崀 $xlIconSet=[Microsoft.Office.Interop.Excel.XLIconSet]਀␀砀氀䐀椀爀攀挀琀椀漀渀㴀嬀䴀椀挀爀漀猀漀昀琀⸀伀昀昀椀挀攀⸀䤀渀琀攀爀漀瀀⸀䔀砀挀攀氀⸀堀䰀䐀椀爀攀挀琀椀漀渀崀 ਀圀爀椀琀攀ⴀ嘀攀爀戀漀猀攀 ∀䄀搀搀椀渀最 圀漀爀欀猀栀攀攀琀∀  $wb=$xl.Workbooks.Add()਀␀眀猀㴀␀眀戀⸀䄀挀琀椀瘀攀匀栀攀攀琀 ਀␀挀攀氀氀猀㴀␀眀猀⸀䌀攀氀氀猀 ਀␀挀攀氀氀猀⸀椀琀攀洀⠀㄀Ⰰ㄀⤀㴀∀䐀椀猀欀 䐀爀椀瘀攀 刀攀瀀漀爀琀∀ ਀⌀搀攀昀椀渀攀 猀漀洀攀 瘀愀爀椀愀戀氀攀猀 琀漀 挀漀渀琀爀漀氀 渀愀瘀椀最愀琀椀漀渀 $row=3਀␀挀漀氀㴀㄀ ਀⌀椀渀猀攀爀琀 挀漀氀甀洀渀 栀攀愀搀椀渀最猀 Write-Verbose "Adding drive headings" ਀ "Drive","SizeGB","FreespaceGB","UsedGB","%Free","%Used" | foreach {਀    ␀挀攀氀氀猀⸀椀琀攀洀⠀␀爀漀眀Ⰰ␀挀漀氀⤀㴀␀开 $cells.item($row,$col).font.bold=$True਀    ␀挀漀氀⬀⬀ }਀ Write-Verbose "Adding drive data" ਀昀漀爀攀愀挀栀 ⠀␀搀爀椀瘀攀 椀渀 ␀搀椀猀欀猀⤀ 笀 $row++਀    ␀挀漀氀㴀㄀ $cells.item($Row,$col)=$drive.DeviceID਀    ␀挀漀氀⬀⬀ $cells.item($Row,$col)=$drive.Size/1GB਀    ␀挀攀氀氀猀⸀椀琀攀洀⠀␀刀漀眀Ⰰ␀挀漀氀⤀⸀一甀洀戀攀爀䘀漀爀洀愀琀㴀∀ ∀ $col++਀    ␀挀攀氀氀猀⸀椀琀攀洀⠀␀刀漀眀Ⰰ␀挀漀氀⤀㴀␀搀爀椀瘀攀⸀䘀爀攀攀猀瀀愀挀攀⼀㄀䜀䈀 $cells.item($Row,$col).NumberFormat="0.00"਀    ␀挀漀氀⬀⬀ $cells.item($Row,$col)=($drive.Size - $drive.Freespace)/1GB਀    ␀挀攀氀氀猀⸀椀琀攀洀⠀␀刀漀眀Ⰰ␀挀漀氀⤀⸀一甀洀戀攀爀䘀漀爀洀愀琀㴀∀ ⸀  ∀ $col++਀    ␀挀攀氀氀猀⸀椀琀攀洀⠀␀刀漀眀Ⰰ␀挀漀氀⤀㴀⠀␀搀爀椀瘀攀⸀䘀爀攀攀猀瀀愀挀攀⼀␀搀爀椀瘀攀⸀猀椀稀攀⤀ $cells.item($Row,$col).NumberFormat="0.00%"਀    ␀挀漀氀⬀⬀ $cells.item($Row,$col)=($drive.Size - $drive.Freespace) / $drive.size਀    ␀挀攀氀氀猀⸀椀琀攀洀⠀␀刀漀眀Ⰰ␀挀漀氀⤀⸀一甀洀戀攀爀䘀漀爀洀愀琀㴀∀ ⸀  ─∀ }਀ Write-Verbose "Adding some style" ਀ #add some style਀␀爀愀渀最攀㴀␀眀猀⸀爀愀渀最攀⠀∀䄀㄀∀⤀ $range.Style="Title"਀⌀漀爀 猀攀琀 椀琀 氀椀欀攀 琀栀椀猀 $ws.Range("A3:F3").Style = "Heading 2"਀ #adjust some column widths਀圀爀椀琀攀ⴀ嘀攀爀戀漀猀攀 ∀䄀搀樀甀猀琀椀渀最 挀漀氀甀洀渀 眀椀搀琀栀猀∀  $ws.columns.item("C:C").columnwidth=15਀␀眀猀⸀挀漀氀甀洀渀猀⸀椀琀攀洀⠀∀䐀㨀䘀∀⤀⸀挀漀氀甀洀渀眀椀搀琀栀㴀㄀ ⸀㔀 $ws.columns.item("B:B").EntireColumn.AutoFit() | out-null਀ #add some conditional formatting਀圀爀椀琀攀ⴀ嘀攀爀戀漀猀攀 ∀䄀搀搀椀渀最 挀漀渀搀椀琀椀漀渀愀氀 昀漀爀洀愀琀琀椀渀最∀  ਀⌀最攀琀 琀栀攀 猀琀愀爀琀椀渀最 挀攀氀氀 $start=$ws.range("F4")਀⌀最攀琀 琀栀攀 氀愀猀琀 挀攀氀氀 $Selection=$ws.Range($start,$start.End($xlDirection::xlDown))਀⌀愀搀搀 琀栀攀 椀挀漀渀 猀攀琀 $Selection.FormatConditions.AddIconSetCondition() | Out-Null਀␀匀攀氀攀挀琀椀漀渀⸀䘀漀爀洀愀琀䌀漀渀搀椀琀椀漀渀猀⸀椀琀攀洀⠀␀⠀␀匀攀氀攀挀琀椀漀渀⸀䘀漀爀洀愀琀䌀漀渀搀椀琀椀漀渀猀⸀䌀漀甀渀琀⤀⤀⸀匀攀琀䘀椀爀猀琀倀爀椀漀爀椀琀礀⠀⤀ $Selection.FormatConditions.item(1).ReverseOrder = $True਀␀匀攀氀攀挀琀椀漀渀⸀䘀漀爀洀愀琀䌀漀渀搀椀琀椀漀渀猀⸀椀琀攀洀⠀㄀⤀⸀匀栀漀眀䤀挀漀渀伀渀氀礀 㴀 ␀䘀愀氀猀攀 $Selection.FormatConditions.item(1).IconSet = $xlIconSet::xl3TrafficLights1਀␀匀攀氀攀挀琀椀漀渀⸀䘀漀爀洀愀琀䌀漀渀搀椀琀椀漀渀猀⸀椀琀攀洀⠀㄀⤀⸀䤀挀漀渀䌀爀椀琀攀爀椀愀⸀䤀琀攀洀⠀㈀⤀⸀吀礀瀀攀㴀␀砀氀䌀漀渀搀椀琀椀漀渀嘀愀氀甀攀猀㨀㨀砀氀䌀漀渀搀椀琀椀漀渀嘀愀氀甀攀一甀洀戀攀爀 $Selection.FormatConditions.item(1).IconCriteria.Item(2).Value=0.8਀␀匀攀氀攀挀琀椀漀渀⸀䘀漀爀洀愀琀䌀漀渀搀椀琀椀漀渀猀⸀椀琀攀洀⠀㄀⤀⸀䤀挀漀渀䌀爀椀琀攀爀椀愀⸀䤀琀攀洀⠀㈀⤀⸀伀瀀攀爀愀琀漀爀㴀㜀 $Selection.FormatConditions.item(1).IconCriteria.Item(3).Type=$xlConditionValues::xlConditionValueNumber਀␀匀攀氀攀挀琀椀漀渀⸀䘀漀爀洀愀琀䌀漀渀搀椀琀椀漀渀猀⸀椀琀攀洀⠀㄀⤀⸀䤀挀漀渀䌀爀椀琀攀爀椀愀⸀䤀琀攀洀⠀㌀⤀⸀嘀愀氀甀攀㴀 ⸀㤀 $Selection.FormatConditions.item(1).IconCriteria.Item(3).Operator=7਀ ਀⌀椀渀猀攀爀琀 愀 最爀愀瀀栀 Write-Verbose "Creating a graph" ਀␀挀栀愀爀琀㴀␀眀猀⸀匀栀愀瀀攀猀⸀䄀搀搀䌀栀愀爀琀⠀⤀⸀䌀栀愀爀琀 $chart.chartType=$xlChart::xlBarClustered਀ $start=$ws.range("A3")਀⌀最攀琀 琀栀攀 氀愀猀琀 挀攀氀氀 $Y=$ws.Range($start,$start.End($xlDirection::xlDown))਀␀猀琀愀爀琀㴀␀眀猀⸀爀愀渀最攀⠀∀䘀㌀∀⤀ #get the last cell਀␀堀㴀␀眀猀⸀刀愀渀最攀⠀␀猀琀愀爀琀Ⰰ␀猀琀愀爀琀⸀䔀渀搀⠀␀砀氀䐀椀爀攀挀琀椀漀渀㨀㨀砀氀䐀漀眀渀⤀⤀ ਀␀挀栀愀爀琀搀愀琀愀㴀␀眀猀⸀刀愀渀最攀⠀∀䄀␀⠀␀夀⸀椀琀攀洀⠀㄀⤀⸀刀漀眀⤀㨀䄀␀⠀␀夀⸀椀琀攀洀⠀␀夀⸀挀漀甀渀琀⤀⸀刀漀眀⤀Ⰰ䘀␀⠀␀堀⸀椀琀攀洀⠀㄀⤀⸀刀漀眀⤀㨀䘀␀⠀␀堀⸀椀琀攀洀⠀␀堀⸀挀漀甀渀琀⤀⸀刀漀眀⤀∀⤀ $chart.SetSourceData($chartdata)਀ #add labels਀␀挀栀愀爀琀⸀猀攀爀椀攀猀䌀漀氀氀攀挀琀椀漀渀⠀㄀⤀⸀匀攀氀攀挀琀⠀⤀ 簀 伀甀琀ⴀ一甀氀氀 $chart.SeriesCollection(1).ApplyDataLabels() | out-Null਀⌀洀漀搀椀昀礀 琀栀攀 挀栀愀爀琀 琀椀琀氀攀 $chart.ChartTitle.Text = "Utilization"਀圀爀椀琀攀ⴀ嘀攀爀戀漀猀攀 ∀刀攀瀀漀猀椀琀椀漀渀椀渀最 最爀愀瀀栀∀  $ws.shapes.item("Chart 1").top=40਀␀眀猀⸀猀栀愀瀀攀猀⸀椀琀攀洀⠀∀䌀栀愀爀琀 ㄀∀⤀⸀氀攀昀琀㴀㐀   ਀圀爀椀琀攀ⴀ嘀攀爀戀漀猀攀 ∀刀攀渀愀洀椀渀最 琀栀攀 眀漀爀欀猀栀攀攀琀∀   #rename the worksheet਀␀渀愀洀攀㴀␀搀椀猀欀猀嬀 崀⸀匀礀猀琀攀洀一愀洀攀 $xl.worksheets.Item("Sheet1").name=$name਀ #select A1਀␀眀猀⸀刀愀渀最攀⠀∀䄀㄀∀⤀⸀匀攀氀攀挀琀⠀⤀ 簀 伀甀琀ⴀ一甀氀氀 ਀⌀洀愀欀攀 䔀砀挀攀氀 瘀椀猀椀戀氀攀  $xl.Visible=$True਀ $filepath=Read-Host "Enter a path and filename to save the file"਀ if ($filepath) {਀    圀爀椀琀攀ⴀ嘀攀爀戀漀猀攀 ∀匀愀瘀椀渀最 昀椀氀攀 琀漀 ␀昀椀氀攀瀀愀琀栀∀  $wb.SaveAs($filepath)਀    ␀砀氀⸀搀椀猀瀀氀愀礀䄀氀攀爀琀猀㴀␀䘀愀氀猀攀 $wb.Close()਀    ␀砀氀⸀儀甀椀琀⠀⤀ }