PowerShell and Excel: Adding a Chart and Header Filter to a Report

Posted on December 24, 2012 by Boe Prox

In the previous articles I have shown you how to use Excel with PowerShell to make a nice disk space report, complete with color coded rows for disks that are running low on space. I am now going to take this a step farther by showing how to add a chart (an exploded 3D pie chart in this case) and make some configuration changes to that chart. Another item is adding a filter for the headers to make it easier to narrow the scope of data that you want to view.

Previous Articles:

PowerShell and Excel: Creating Your Initial Report

PowerShell and Excel: Adding Some Formatting To Your Report

Like the previous article, I will push through all of the existing code and offer more explanations with the new code for the chart and filtering.

Lets kick off the excel reporting!

##Reference: http://msdn.microsoft.com/en-us/library/ms262200(v=office.11).aspx

#Create excel COM object
$excel = New-Object -ComObject excel.application

#Make Visible
$excel.Visible = $True
$excel.DisplayAlerts = $False

#Add a workbook
$workbook = $excel.Workbooks.Add()

#Remove other worksheets
1..2 | ForEach {
    $Workbook.worksheets.item(2).Delete()
}

#Connect to first worksheet to rename and make active
$serverInfoSheet = $workbook.Worksheets.Item(1)
$serverInfoSheet.Activate() | Out-Null

#Create a Title for the first worksheet
$row = 1
$Column = 1
$serverInfoSheet.Cells.Item($row,$column)= 'Disk Space Information'

$range = $serverInfoSheet.Range("a1","h2")
$range.Merge() | Out-Null
$range.VerticalAlignment = -4160

#Give it a nice Style so it stands out
$range.Style = 'Title'

#Increment row for next set of data
$row++;$row++

#Save the initial row so it can be used later to create a border
$initalRow = $row

#Create a header for Disk Space Report; set each cell to Bold and add a background color
$serverInfoSheet.Cells.Item($row,$column)= 'Computername'
$serverInfoSheet.Cells.Item($row,$column).Interior.ColorIndex =48
$serverInfoSheet.Cells.Item($row,$column).Font.Bold=$True
$Column++
$serverInfoSheet.Cells.Item($row,$column)= 'DeviceID'
$serverInfoSheet.Cells.Item($row,$column).Interior.ColorIndex =48
$serverInfoSheet.Cells.Item($row,$column).Font.Bold=$True
$Column++
$serverInfoSheet.Cells.Item($row,$column)= 'VolumeName'
$serverInfoSheet.Cells.Item($row,$column).Interior.ColorIndex =48
$serverInfoSheet.Cells.Item($row,$column).Font.Bold=$True
$Column++
$serverInfoSheet.Cells.Item($row,$column)= 'TotalSizeGB'
$serverInfoSheet.Cells.Item($row,$column).Interior.ColorIndex =48
$serverInfoSheet.Cells.Item($row,$column).Font.Bold=$True
$Column++
$serverInfoSheet.Cells.Item($row,$column)= 'UsedSpaceGB'
$serverInfoSheet.Cells.Item($row,$column).Interior.ColorIndex =48
$serverInfoSheet.Cells.Item($row,$column).Font.Bold=$True
$Column++
$serverInfoSheet.Cells.Item($row,$column)= 'FreeSpaceGB'
$serverInfoSheet.Cells.Item($row,$column).Interior.ColorIndex =48
$serverInfoSheet.Cells.Item($row,$column).Font.Bold=$True
$Column++
$serverInfoSheet.Cells.Item($row,$column)= '%Free'
$serverInfoSheet.Cells.Item($row,$column).Interior.ColorIndex =48
$serverInfoSheet.Cells.Item($row,$column).Font.Bold=$True
$Column++
$serverInfoSheet.Cells.Item($row,$column)= 'State'
$serverInfoSheet.Cells.Item($row,$column).Interior.ColorIndex =48
$serverInfoSheet.Cells.Item($row,$column).Font.Bold=$True

Ok, same old stuff here. The only exception is now I added a new column called State that will be used to help create the chart later on in this article. Setting up the filtering actually is pretty simple. I just need to select all of the headers and use the AutoFilter() method.

#Set up a header filter
$headerRange = $serverInfoSheet.Range("a3","h3")
$headerRange.AutoFilter() | Out-Null

image

Now it doesn’t do us a lot of good without some sort of data in the report, but once we start pulling in some disk space information, it will be easy to only look at what we want. At the end of the article, I will show the benefits of adding this to your report.

I am going to cheat a little on this portion by using the same 2 systems a number of times. This will give you a better idea on how the chart will look as well as the filtering. So just imagine that all of the systems are different. Smile

#Increment Row and reset Column back to first column
$row++
$Column = 1

#Get the drives and filter out CD/DVD drives
$diskDrives = Get-WmiObject win32_LogicalDisk -Filter "DriveType='3'" -ComputerName $env:COMPUTERNAME,DC1,$env:COMPUTERNAME,DC1,$env:COMPUTERNAME,DC1

#Process each disk in the collection and write to spreadsheet
ForEach ($disk in $diskDrives) {
    $serverInfoSheet.Cells.Item($row,$column)= $disk.__Server
    $Column++
    $serverInfoSheet.Cells.Item($row,$column)= $disk.DeviceID
    $Column++
    $serverInfoSheet.Cells.Item($row,$column)= $disk.VolumeName
    $Column++
    $serverInfoSheet.Cells.Item($row,$column)= [math]::Round(($disk.Size /1GB),2)
    $Column++
    $serverInfoSheet.Cells.Item($row,$column)= [math]::Round((($disk.Size - $disk.FreeSpace)/1GB),2)
    $Column++
    $serverInfoSheet.Cells.Item($row,$column)= [math]::Round(($disk.FreeSpace / 1GB),2)
    $Column++
    $serverInfoSheet.Cells.Item($row,$column)= ("{0:P}" -f ($disk.FreeSpace / $disk.Size))  
    $Column++  

    #Determine if disk needs to be flagged for warning or critical alert
    If ($disk.FreeSpace -lt 40GB -AND ($disk.FreeSpace / $disk.Size) -lt .45) {
        $serverInfoSheet.Cells.Item($row,$column) = "Critical"
        #Check to see if space is near empty and use appropriate background colors
        $range = $serverInfoSheet.Range(("A{0}"  -f $row),("H{0}"  -f $row))
        $range.Select() | Out-Null    
        #Critical threshold         
        $range.Interior.ColorIndex = 3
    } ElseIf ($disk.FreeSpace -lt 60GB -AND ($disk.FreeSpace / $disk.Size) -lt .98) {
        $serverInfoSheet.Cells.Item($row,$column) = "Warning"
        $range = $serverInfoSheet.Range(("A{0}"  -f $row),("H{0}"  -f $row))
        $range.Select() | Out-Null        
        #Warning threshold 
        $range.Interior.ColorIndex = 6
    } Else {
        $serverInfoSheet.Cells.Item($row,$column) = "Good"
    }

    #Increment to next row and reset Column to 1
    $Column = 1
    $row++
}

#Add a border for data cells
$row--
$dataRange = $serverInfoSheet.Range(("A{0}"  -f $initalRow),("H{0}"  -f $row))
7..12 | ForEach {
    $dataRange.Borders.Item($_).LineStyle = 1
    $dataRange.Borders.Item($_).Weight = 2
}
$lastRow = row

All of the data has been collected and added to the report. Basically, we are now at the same point as the previous article.

image

Now we can take a look at the filter that was set up.

image

In this case, I want to see all Critical drives, so I keep only Critical selected from the data set.

image

Now I have only the Critical drives available to look at and decide how I want to deal with this. Great for whoever receives this report as they can filter for any sort of information they need without much work.

The final piece that needs to be added is a chart. The chart I am looking at using is a pie chart that shows the number of critical,warning and good states for each drive. Before I can start working with this chart, I need to set up a temporary data source to count each number of states in the State column.

#Create a holder to count the number of States
$row++;$row++
$beginChartRow = $Row
$serverInfoSheet.Cells.Item($row,$Column) = 'Critical'
$Column++
$serverInfoSheet.Cells.Item($row,$Column) = 'Warning'
$Column++
$serverInfoSheet.Cells.Item($row,$Column) = 'Good'
$Column = 1
$row++

image

First thing is to set up a header for each State before actually adding the data. I don’t bother with any fancy font on this as it is only going to be visible during its initial creation. Next up is to setup a formula on each follow row for each cell. Notice that I use the $lastRow variable to handle the number of rows to look at in the formula. This will ensure that the data will be accurate.

#Critical formula
$serverInfoSheet.Cells.Item($row,$Column).FormulaR1C1 = ("=COUNTIF(R[-{0}]C[7]:R[3989]C[7],""Critical"")" -f $lastRow)
$Column++
#Warning formula
$serverInfoSheet.Cells.Item($row,$Column).FormulaR1C1 = ("=COUNTIF(R[-{0}]C[6]:R[3989]C[6],""Warning"")" -f $lastRow)
$Column++
#Good formula
$serverInfoSheet.Cells.Item($row,$Column).FormulaR1C1 = ("=COUNTIF(R[-{0}]C[5]:R[3989]C[5],""Good"")" -f $lastRow)
$endChartRow = $row
$chartRange = $serverInfoSheet.Range(("A{0}" -f $beginChartRow),("C{0}" -f $endChartRow))

If you are wondering how I know how to do this as well as working with the charts, I relied heavily on using Excel macros to show me the code used and then translating that code into PowerShell. Trust me, this is not a simple walk in the park that I was able to come up with in a couple of minutes. This required reading through the macros as well as some trial and error and reviewing the Excel Interop documentation out on the MSDN page. Others such as enums were found by running a code snippet that you will see later to determine the correct values that are needed for specific properties.

Now it is time to add a chart to the report.

##Add a chart to the workbook
#Open a sheet for charts
$temp = $excel.Charts.Add()
$temp.Delete()
$chart = $serverInfoSheet.Shapes.AddChart().Chart
$serverInfoSheet.Activate()

Determining the chart type required a little bit of work to translate the chart type to an integer.

[Enum]::getvalues([Microsoft.Office.Interop.Excel.XlChartType]) | 
select @{n="Name";e={"$_"}},value__ | ft -auto

Chart Types

Name value__
xlArea 1
xlLine 4
xlPie 5
xlBubble 15
xlColumnClustered 51
xlColumnStacked 52
xlColumnStacked100 53
xl3DColumnClustered 54
xl3DColumnStacked 55
xl3DColumnStacked100 56
xlBarClustered 57
xlBarStacked 58
xlBarStacked100 59
xl3DBarClustered 60
xl3DBarStacked 61
xl3DBarStacked100 62
xlLineStacked 63
xlLineStacked100 64
xlLineMarkers 65
xlLineMarkersStacked 66
xlLineMarkersStacked100 67
xlPieOfPie 68
xlPieExploded 69
xl3DPieExploded 70
xlBarOfPie 71
xlXYScatterSmooth 72
xlXYScatterSmoothNoMarkers 73
xlXYScatterLines 74
xlXYScatterLinesNoMarkers 75
xlAreaStacked 76
xlAreaStacked100 77
xl3DAreaStacked 78
xl3DAreaStacked100 79
xlDoughnutExploded 80
xlRadarMarkers 81
xlRadarFilled 82
xlSurface 83
xlSurfaceWireframe 84
xlSurfaceTopView 85
xlSurfaceTopViewWireframe 86
xlBubble3DEffect 87
xlStockHLC 88
xlStockOHLC 89
xlStockVHLC 90
xlStockVOHLC 91
xlCylinderColClustered 92
xlCylinderColStacked 93
xlCylinderColStacked100 94
xlCylinderBarClustered 95
xlCylinderBarStacked 96
xlCylinderBarStacked100 97
xlCylinderCol 98
xlConeColClustered 99
xlConeColStacked 100
xlConeColStacked100 101
xlConeBarClustered 102
xlConeBarStacked 103
xlConeBarStacked100 104
xlConeCol 105
xlPyramidColClustered 106
xlPyramidColStacked 107
xlPyramidColStacked100 108
xlPyramidBarClustered 109
xlPyramidBarStacked 110
xlPyramidBarStacked100 111
xlPyramidCol 112
xlXYScatter -4169
xlRadar -4151
xlDoughnut -4120
xl3DPie -4102
xl3DLine -4101
xl3DColumn -4100
xl3DArea -4098

In this case, I am looking for an exploded 3D pie chart to display. Looking at the values, I can see that this is 70.

##Use a 3D Pie Chart
$chart.ChartType = 70
$chart.Elevation = 40
#Give it some color
$serverInfoSheet.Shapes.Item("Chart 1").Fill.ForeColor.TintAndShade = .34
$serverInfoSheet.Shapes.Item("Chart 1").Fill.ForeColor.ObjectThemeColor = 5
$serverInfoSheet.Shapes.Item("Chart 1").Fill.BackColor.TintAndShade = .765
$serverInfoSheet.Shapes.Item("Chart 1").Fill.ForeColor.ObjectThemeColor = 5

I adjust the angle of the chart so it is easier to read by others and then give it a different background color other than plain white.

I also want to give it a gradient look, so I have to figure out the values of the gradient direction.

[Enum]::getvalues([Microsoft.Office.Core.MsoGradientStyle]) | 
select @{n="Name";e={"$_"}},value__ | ft -auto
Name value__
msoGradientHorizontal 1
msoGradientVertical 2
msoGradientDiagonalUp 3
msoGradientDiagonalDown 4
msoGradientFromCorner 5
msoGradientFromTitle 6
msoGradientFromCenter 7
msoGradientMixed -2 
$serverInfoSheet.Shapes.Item("Chart 1").Fill.TwoColorGradient(1,1)

image

Its coming along, but not quite there yet. I need to move it out of the way of the data. This took some trial and error to move the chart. But I was able to find something that I was happy with as far as the Top and Left placement.

$serverInfoSheet.Shapes.Item("Chart 1").Top = 30
$serverInfoSheet.Shapes.Item("Chart 1").Left = 600

image

I am going to configure the chart to remain at the same size regardless of what happens with the cells/rows/columns in the spreadsheet. By default, the chart will adjust to whatever happens in the spreadsheet. First I have to determine the correct value to use with this.

[Enum]::getvalues([Microsoft.Office.Interop.Excel.XlPlacement]) | 
select @{n="Name";e={"$_"}},value__ | ft -auto
Name value__
xlMoveAndSize 1
xlMove 2
xlFreeFloating 3

Now I know that I need to set the Placement property value to 2.

$serverInfoSheet.Shapes.Item("Chart 1").Placement = 2

Now I will go ahead and add the report data into the chart.

$chart.SetSourceData($chartRange)
$chart.HasTitle = $True
Start-Sleep -seconds 2

Next up is to adjust the look of the chart. Here I will add a title, display both the percentage and the value in the pie chart as well as adding a legend to the chart.

$chart.ApplyLayout(6,69)
$chart.ChartTitle.Text = "Disk Space Report"
$chart.ChartStyle = 26
$chart.PlotVisibleOnly = $False
$chart.SeriesCollection(1).DataLabels().ShowValue = $True
$chart.SeriesCollection(1).DataLabels().Separator = ("{0}" -f [char]10)

In this instance, I want the labels on the outside of the pic chart instead of being on the inside. I just need to know what the correct value is.

[Enum]::getvalues([Microsoft.Office.Interop.Excel.XlDataLabelPosition]) | 
select @{n="Name";e={"$_"}},value__ | ft -auto
Name value__
xlLabelPositionAbove 0
xlLabelPositionBelow 1
xlLabelPositionOutsideEnd 2
xlLabelPositionInsideEnd 3
xlLabelPositionInsideBase 4
xlLabelPositionBestFit 5
xlLabelPositionMixed 6
xlLabelPositionCustom 7
xlLabelPositionRight -4152
xlLabelPositionLeft -4131
xlLabelPositionCenter -4108

Looks like I need to use the value 2 to get the information on the outside.

$chart.SeriesCollection(1).DataLabels().Position = 2

image

Its coming together nicely, but I am just not completely satisfied with the chart yet. I want the pie chart to have the same colors as the data colors (red, yellow and then green (even though it is not used in the data) so it matches up more.

$chart.SeriesCollection(1).DataLabels().Position = 2
#Critical
$chart.SeriesCollection(1).Points(1).Format.Fill.ForeColor.RGB = 255
#Warning
$chart.SeriesCollection(1).Points(2).Format.Fill.ForeColor.RGB = 65535
#Good
$chart.SeriesCollection(1).Points(3).Format.Fill.ForeColor.RGB = 5287936

image

Much better. Now to do some cleanup with the data used to create the chart. I am simply going to hide the Warning, Critical and Good header and data in the spreadsheet using my existing range $chartRange and setting the EntireRow.Hidden property to $True. I also need to name my spreadsheet and that is it!

#Hide the data
$chartRange.EntireRow.Hidden = $True

$serverInfoSheet.Name = 'DiskInformation'
#Auto fit everything so it looks better
$usedRange = $serverInfoSheet.UsedRange															
$usedRange.EntireColumn.AutoFit() | Out-Null

image

All that is left to do is to save this report off and close out of Excel with some cleanup of the COM object and now you have an excellent report that can be emailed out to others to view and take action on the drive space issues.

When creating a chart with a report or even working with Excel for that matter, be sure to look at using a macro initially to get a better idea on what you need to use for the code. Many times the macro has most of the code in front of you and all you need to do is convert it to PowerShell and use it.

Full Code:

##Reference: http://msdn.microsoft.com/en-us/library/ms262200(v=office.11).aspx

#Create excel COM object
$excel = New-Object -ComObject excel.application

#Make Visible
$excel.Visible = $True
$excel.DisplayAlerts = $False

#Add a workbook
$workbook = $excel.Workbooks.Add()

#Remove other worksheets
1..2 | ForEach {
    $Workbook.worksheets.item(2).Delete()
}

#Connect to first worksheet to rename and make active
$serverInfoSheet = $workbook.Worksheets.Item(1)
$serverInfoSheet.Activate() | Out-Null

#Create a Title for the first worksheet
$row = 1
$Column = 1
$serverInfoSheet.Cells.Item($row,$column)= 'Disk Space Information'

$range = $serverInfoSheet.Range("a1","h2")
$range.Merge() | Out-Null
$range.VerticalAlignment = -4160

#Give it a nice Style so it stands out
$range.Style = 'Title'

#Increment row for next set of data
$row++;$row++

#Save the initial row so it can be used later to create a border
$initalRow = $row

#Create a header for Disk Space Report; set each cell to Bold and add a background color
$serverInfoSheet.Cells.Item($row,$column)= 'Computername'
$serverInfoSheet.Cells.Item($row,$column).Interior.ColorIndex =48
$serverInfoSheet.Cells.Item($row,$column).Font.Bold=$True
$Column++
$serverInfoSheet.Cells.Item($row,$column)= 'DeviceID'
$serverInfoSheet.Cells.Item($row,$column).Interior.ColorIndex =48
$serverInfoSheet.Cells.Item($row,$column).Font.Bold=$True
$Column++
$serverInfoSheet.Cells.Item($row,$column)= 'VolumeName'
$serverInfoSheet.Cells.Item($row,$column).Interior.ColorIndex =48
$serverInfoSheet.Cells.Item($row,$column).Font.Bold=$True
$Column++
$serverInfoSheet.Cells.Item($row,$column)= 'TotalSizeGB'
$serverInfoSheet.Cells.Item($row,$column).Interior.ColorIndex =48
$serverInfoSheet.Cells.Item($row,$column).Font.Bold=$True
$Column++
$serverInfoSheet.Cells.Item($row,$column)= 'UsedSpaceGB'
$serverInfoSheet.Cells.Item($row,$column).Interior.ColorIndex =48
$serverInfoSheet.Cells.Item($row,$column).Font.Bold=$True
$Column++
$serverInfoSheet.Cells.Item($row,$column)= 'FreeSpaceGB'
$serverInfoSheet.Cells.Item($row,$column).Interior.ColorIndex =48
$serverInfoSheet.Cells.Item($row,$column).Font.Bold=$True
$Column++
$serverInfoSheet.Cells.Item($row,$column)= '%Free'
$serverInfoSheet.Cells.Item($row,$column).Interior.ColorIndex =48
$serverInfoSheet.Cells.Item($row,$column).Font.Bold=$True
$Column++
$serverInfoSheet.Cells.Item($row,$column)= 'State'
$serverInfoSheet.Cells.Item($row,$column).Interior.ColorIndex =48
$serverInfoSheet.Cells.Item($row,$column).Font.Bold=$True

#Set up a header filter
$headerRange = $serverInfoSheet.Range("a3","h3")
$headerRange.AutoFilter() | Out-Null

#Increment Row and reset Column back to first column
$row++
$Column = 1

#Get the drives and filter out CD/DVD drives
$diskDrives = Get-WmiObject win32_LogicalDisk -Filter "DriveType='3'" -ComputerName $env:COMPUTERNAME,DC1,$env:COMPUTERNAME,DC1,$env:COMPUTERNAME,DC1

#Process each disk in the collection and write to spreadsheet
ForEach ($disk in $diskDrives) {
    $serverInfoSheet.Cells.Item($row,$column)= $disk.__Server
    $Column++
    $serverInfoSheet.Cells.Item($row,$column)= $disk.DeviceID
    $Column++
    $serverInfoSheet.Cells.Item($row,$column)= $disk.VolumeName
    $Column++
    $serverInfoSheet.Cells.Item($row,$column)= [math]::Round(($disk.Size /1GB),2)
    $Column++
    $serverInfoSheet.Cells.Item($row,$column)= [math]::Round((($disk.Size - $disk.FreeSpace)/1GB),2)
    $Column++
    $serverInfoSheet.Cells.Item($row,$column)= [math]::Round(($disk.FreeSpace / 1GB),2)
    $Column++
    $serverInfoSheet.Cells.Item($row,$column)= ("{0:P}" -f ($disk.FreeSpace / $disk.Size))  
    $Column++  

    #Determine if disk needs to be flagged for warning or critical alert
    If ($disk.FreeSpace -lt 40GB -AND ($disk.FreeSpace / $disk.Size) -lt 45) {
        $serverInfoSheet.Cells.Item($row,$column) = "Critical"
        #Check to see if space is near empty and use appropriate background colors
        $range = $serverInfoSheet.Range(("A{0}"  -f $row),("H{0}"  -f $row))
        $range.Select() | Out-Null    
        #Critical threshold         
        $range.Interior.ColorIndex = 3
    } ElseIf ($disk.FreeSpace -lt 60GB -AND ($disk.FreeSpace / $disk.Size) -lt 98) {
        $serverInfoSheet.Cells.Item($row,$column) = "Warning"
        $range = $serverInfoSheet.Range(("A{0}"  -f $row),("H{0}"  -f $row))
        $range.Select() | Out-Null        
        #Warning threshold 
        $range.Interior.ColorIndex = 6
    } Else {
        $serverInfoSheet.Cells.Item($row,$column) = "Good"
    }

    #Increment to next row and reset Column to 1
    $Column = 1
    $row++
}

#Add a border for data cells
$row--
$dataRange = $serverInfoSheet.Range(("A{0}"  -f $initalRow),("H{0}"  -f $row))
7..12 | ForEach {
    $dataRange.Borders.Item($_).LineStyle = 1
    $dataRange.Borders.Item($_).Weight = 2
}

#Auto fit everything so it looks better
$usedRange = $serverInfoSheet.UsedRange															
$usedRange.EntireColumn.AutoFit() | Out-Null

#Create a holder to count the number of States
$row++;$row++
$beginChartRow = $Row
$serverInfoSheet.Cells.Item($row,$Column) = 'Critical'
$Column++
$serverInfoSheet.Cells.Item($row,$Column) = 'Warning'
$Column++
$serverInfoSheet.Cells.Item($row,$Column) = 'Good'
$Column = 1
$row++
#Critical formula
$serverInfoSheet.Cells.Item($row,$Column).FormulaR1C1 = "=COUNTIF(R[-8]C[7]:R[3989]C[7],""Critical"")"
$Column++
#Warning formula
$serverInfoSheet.Cells.Item($row,$Column).FormulaR1C1 = "=COUNTIF(R[-8]C[6]:R[3989]C[6],""Warning"")"
$Column++
#Good formula
$serverInfoSheet.Cells.Item($row,$Column).FormulaR1C1 = "=COUNTIF(R[-8]C[5]:R[3989]C[5],""Good"")"
$endChartRow = $row
$chartRange = $serverInfoSheet.Range(("A{0}" -f $beginChartRow),("C{0}" -f $endChartRow))

##Add a chart to the workbook
#Open a sheet for charts
$temp = $excel.Charts.Add()
$temp.Delete()
$chart = $serverInfoSheet.Shapes.AddChart().Chart
$serverInfoSheet.Activate()

#Configure the chart
##Use a 3D Pie Chart
$chart.ChartType = 70
$chart.Elevation = 40
#Give it some color
$serverInfoSheet.Shapes.Item("Chart 1").Fill.ForeColor.TintAndShade = .34
$serverInfoSheet.Shapes.Item("Chart 1").Fill.ForeColor.ObjectThemeColor = 5
$serverInfoSheet.Shapes.Item("Chart 1").Fill.BackColor.TintAndShade = .765
$serverInfoSheet.Shapes.Item("Chart 1").Fill.ForeColor.ObjectThemeColor = 5

$serverInfoSheet.Shapes.Item("Chart 1").Fill.TwoColorGradient(1,1)

#Set the location of the chart
$serverInfoSheet.Shapes.Item("Chart 1").Placement = 3
$serverInfoSheet.Shapes.Item("Chart 1").Top = 30
$serverInfoSheet.Shapes.Item("Chart 1").Left = 600

$chart.SetSourceData($chartRange)
$chart.HasTitle = $True

$chart.ApplyLayout(6,69)
$chart.ChartTitle.Text = "Disk Space Report"
$chart.ChartStyle = 26
$chart.PlotVisibleOnly = $False
$chart.SeriesCollection(1).DataLabels().ShowValue = $True
$chart.SeriesCollection(1).DataLabels().Separator = ("{0}" -f [char]10)

$chart.SeriesCollection(1).DataLabels().Position = 2
#Critical
$chart.SeriesCollection(1).Points(1).Format.Fill.ForeColor.RGB = 255
#Warning
$chart.SeriesCollection(1).Points(2).Format.Fill.ForeColor.RGB = 65535
#Good
$chart.SeriesCollection(1).Points(3).Format.Fill.ForeColor.RGB = 5287936

#Hide the data
$chartRange.EntireRow.Hidden = $True

$serverInfoSheet.Name = 'DiskInformation'

#Quit the application
$excel.Quit()

#Release COM Object
[System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$excel) | Out-Null
Posted in Excel, Office, powershell | Tagged , , , | 13 Comments

PowerShell and Excel: Adding Some Formatting To Your Report

Posted on December 20, 2012 by Boe Prox

Continuing from my previous Excel article, I will now take the report that I created and make more pleasant to the eyes and really show why this is different from just piping the output into Export-CSV to a CSV file.

If you recall, the finished product from my previous article gave you essentially the same type of report that you would find from a typical CSV report generated by Export-CSV. The report was a common report showing the drive spaces of a system.

image

There will be some re-hashing of code as I am going to perform the same query and the same type of inputting of values in each cell for Excel. The differences are how I format the cells in Excel to give it a nicer look.

#Create excel COM object
$excel = New-Object -ComObject excel.application

#Make Visible
$excel.Visible = $True

#Add a workbook
$workbook = $excel.Workbooks.Add()

#Remove other worksheets
1..2 | ForEach {
    $Workbook.worksheets.item(2).Delete()
}

#Connect to first worksheet to rename and make active
$serverInfoSheet = $workbook.Worksheets.Item(1)
$serverInfoSheet.Name = 'DiskInformation'
$serverInfoSheet.Activate() | Out-Null

Nothing new here as I am connecting to the Excel COM object and then removing all of my workbooks.

Instead of first creating my headers for the data for each drive, I want to add a title to announce what type of report this is.

#Create a Title for the first worksheet and adjust the font
$row = 1
$Column = 1
$serverInfoSheet.Cells.Item($row,$column)= 'Disk Space Information'

image

Ok, so I can’t really call this a title by any means. We need to format this and give a more “title” look. I will be using the Font property of the cell object to make this look a lot better.

$serverInfoSheet.Cells.Item($row,$column).Font.Size = 18
$serverInfoSheet.Cells.Item($row,$column).Font.Bold=$True
$serverInfoSheet.Cells.Item($row,$column).Font.Name = "Cambria"
$serverInfoSheet.Cells.Item($row,$column).Font.ThemeFont = 1
$serverInfoSheet.Cells.Item($row,$column).Font.ThemeColor = 4
$serverInfoSheet.Cells.Item($row,$column).Font.ColorIndex = 55
$serverInfoSheet.Cells.Item($row,$column).Font.Color = 8210719

image

Now that is more like it! This gives it a little more pop and better separates itself from the rest of the data in the worksheet. Here I made the font bold, changed the font type to Cambria as well as updating some colors on the font as well. For those paying attention, this is the same as setting the Style to Title, which can also be done by the following code:

$range = $serverInfoSheet.Range("a1","h2")
$range.Style = 'Title'

However, there is just one more thing that I want to do to clean it up a bit.

$range = $serverInfoSheet.Range("a1","g2")
$range.Merge() | Out-Null
$range.VerticalAlignment = -4160

image

What I’ve done is I selected an extra row as well as selecting as many columns as I know I will be using with my headers and data. Once I have that, I merged everything and then set the verticalalignment to align at the top. So how in the world did I know how to use the correct value for the top alignment when it is not so easily readable (-4160)?

I first ran the following command to figure out the type of Enum that I needed to work with:

$range | Get-Member -Name verticalalignment

   TypeName: System.__ComObject#{00020846-0000-0000-c000-000000000046}

Name              MemberType Definition                              
—-              ———- ———-                              
VerticalAlignment Property   Variant VerticalAlignment () {get} {set}

Next I went out to the MSDN site for the excel COM object and looked up the enumeration of this type. Unfortunately, I was not able to get the values that relate to the type of alignment. The solution was to work with the Enum using the following code:

[Enum]::getvalues([Microsoft.Office.Interop.Excel.XLVAlign]) | 
select @{n="Name";e={"$_"}},value__

VerticalAlignment

Name

value__

xlVAlignTop

-4160

xlVAlignJustify

-4130

xlVAlignDistributed

-4117

xlVAlignCenter

-4108

xlVAlignBottom

-4107

Now I know exactly what value is needed to get the alignment right! This technique will be used a number of times during this article to quickly find out what is the correct value needed to configure a specific property.

Next up we get to add the headers for each data set, but this time with a few additions to really let it stand out as a header.

#Increment row for next set of data
$row++;$row++

#Save the initial row so it can be used later to create a border
$initalRow = $row

#Create a header for Disk Space Report; set each cell to Bold and add a background color
$serverInfoSheet.Cells.Item($row,$column)= 'Computername'
$serverInfoSheet.Cells.Item($row,$column).Interior.ColorIndex =48
$serverInfoSheet.Cells.Item($row,$column).Font.Bold=$True
$Column++
$serverInfoSheet.Cells.Item($row,$column)= 'DeviceID'
$serverInfoSheet.Cells.Item($row,$column).Interior.ColorIndex =48
$serverInfoSheet.Cells.Item($row,$column).Font.Bold=$True
$Column++
$serverInfoSheet.Cells.Item($row,$column)= 'VolumeName'
$serverInfoSheet.Cells.Item($row,$column).Interior.ColorIndex =48
$serverInfoSheet.Cells.Item($row,$column).Font.Bold=$True
$Column++
$serverInfoSheet.Cells.Item($row,$column)= 'TotalSize(GB)'
$serverInfoSheet.Cells.Item($row,$column).Interior.ColorIndex =48
$serverInfoSheet.Cells.Item($row,$column).Font.Bold=$True
$Column++
$serverInfoSheet.Cells.Item($row,$column)= 'UsedSpace(GB)'
$serverInfoSheet.Cells.Item($row,$column).Interior.ColorIndex =48
$serverInfoSheet.Cells.Item($row,$column).Font.Bold=$True
$Column++
$serverInfoSheet.Cells.Item($row,$column)= 'FreeSpace(GB)'
$serverInfoSheet.Cells.Item($row,$column).Interior.ColorIndex =48
$serverInfoSheet.Cells.Item($row,$column).Font.Bold=$True
$Column++
$serverInfoSheet.Cells.Item($row,$column)= 'PercentFree'
$serverInfoSheet.Cells.Item($row,$column).Interior.ColorIndex =48
$serverInfoSheet.Cells.Item($row,$column).Font.Bold=$True

image

Making the font Bold and adjusting the background color (using the Interior.ColorIndex property of each cell) really helps to make the headers appear more prominent on the worksheet.

Now it is time to add the data into the worksheet!

#Increment Row and reset Column back to first column
$row++
$Column = 1

#Get the drives
$diskDrives = Get-CimInstance -ClassName Cim_LogicalDisk

#Process each disk in the collection and write to spreadsheet
ForEach ($disk in $diskDrives) {
    $serverInfoSheet.Cells.Item($row,$column)= $disk.__Server
    $Column++
    $serverInfoSheet.Cells.Item($row,$column)= $disk.DeviceID
    $Column++
    $serverInfoSheet.Cells.Item($row,$column)= $disk.VolumeName
    $Column++
    $serverInfoSheet.Cells.Item($row,$column)= [math]::Round(($disk.Size /1GB),2)
    $Column++
    $serverInfoSheet.Cells.Item($row,$column)= [math]::Round((($disk.Size - $disk.FreeSpace)/1GB),2)
    $Column++
    $serverInfoSheet.Cells.Item($row,$column)= [math]::Round(($disk.FreeSpace / 1GB),2)
    $Column++
    $serverInfoSheet.Cells.Item($row,$column)= ("{0:P}" -f ($disk.FreeSpace / $disk.Size))
    
    #Check to see if space is near empty and use appropriate background colors
    $range = $serverInfoSheet.Range(("A{0}"  -f $row),("G{0}"  -f $row))
    $range.Select() | Out-Null
    
    #Determine if disk needs to be flagged for warning or critical alert
    If ($disk.FreeSpace -lt 65GB -AND ($disk.FreeSpace / $disk.Size) -lt 75) {
        #Critical threshold 
        $range.Interior.ColorIndex = 3
    } ElseIf ($disk.FreeSpace -lt 80GB -AND ($disk.FreeSpace / $disk.Size) -lt 80) {
        #Warning threshold 
        $range.Interior.ColorIndex = 6
    }
    
    #Increment to next row and reset Column to 1
    $Column = 1
    $row++
}

I am using PowerShell V3 to get the data with Get-CIMInstance; for V2 you should still use Get-WmiObject.

image

Some big changes here. Instead of just having plain old text with no color. I decided to track which drives might be having issues with space. Ok, I know that this is not the best representation of running out of space, but as you can see in the code, I can set a threshold based off of free space and percentage free to make a determination if the disk space is Critical or a Warning and highlight the entire row accordingly. I also hard code the range of the row as it will never change to make it easier to highlight the row. This makes it easier now to quickly locate drives that need some attention and resolve the space issue.

We are not done yet! I want to add some borders to this to give it a cleaner look.

$row--
$dataRange = $serverInfoSheet.Range(("A{0}"  -f $initalRow),("G{0}"  -f $row))
7..12 | ForEach {
    $dataRange.Borders.Item($_).LineStyle = 1
    $dataRange.Borders.Item($_).Weight = 2
}

image

How did I know which Border index to use to make the borders look like this? By using the same trick that I used with the VerticalAlignment property.

[Enum]::getvalues([Microsoft.Office.Interop.Excel.XlBordersIndex]) | 
select @{n="Name";e={"$_"}},value__

Border Index

Name

value__

xlDiagonalDown

5

xlDiagonalUp

6

xlEdgeLeft

7

xlEdgeTop

8

xlEdgeBottom

9

xlEdgeRight

10

xlInsideVertical

11

xlInsideHorizontal

12

 

The same method applies when it comes to deciding what the LineStyle and Weight are for the borders.

[Enum]::getvalues([Microsoft.Office.Interop.Excel.XLLineStyle]) | 
select @{n="Name";e={"$_"}},value__

LineStyle

Name

value__

xlContinuous

1

xlDashDot

4

xlDashDotDot

5

xlSlantDashDot

13

xlLineStyleNone

-4142

xlDouble

-4119

xlDot

-4118

xlDash

-4115

 

[Enum]::getvalues([Microsoft.Office.Interop.Excel.XLBorderWeight]) | 
select @{n="Name";e={"$_"}},value__

BorderWeight

Name

value__

xlHairline

1

xlThin

2

xlThick

4

xlMedium

-4138

 

The last thing that needs to be done is to auto fit everything so there will be no need for re-sizing of the columns.

#Auto fit everything so it looks better
$usedRange = $serverInfoSheet.UsedRange	
$usedRange.EntireColumn.AutoFit() | Out-Null

image

Much better now! Now we have a nice report that makes it easier to read and identify drives that are in need of attention. Now to save it off and clean up the excel COM object connection and we are set.

Save the file
$workbook.SaveAs("C:\temp\DiskSpace.xlsx")

#Quit the application
$excel.Quit()

#Release COM Object
[System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$excel) | Out-Null

Hopefully this helps you out with creating a nice report using Excel and PowerShell. My next article will touch on adding some more features to this report such as a chart and filtering headers.

Posted in Excel, Office, powershell | Tagged , , | 12 Comments

PowerShell Deep Dives Now In Early Release (MEAP)

Posted on December 18, 2012 by Boe Prox

A PowerShell book that I contributed a couple of chapters to has just been released to MEAP, meaning that some chapters are now available for early review by the public. This has been a great experience and fun project to be a part of.

 

 

The chapters that I wrote are:

4. TCP port communication with PowerShell

28. WSUS and PowerShell

I am looking forward to the finished product and hearing what everyone says about the book as well as reading all of the other chapters. Hopefully everyone enjoys it!

You can find the book available at the link below:

http://www.manning.com/hicks/

Posted in News, powershell | Tagged , , , | 1 Comment

PowerShell and Excel: Creating Your Initial Report

Posted on December 16, 2012 by Boe Prox

One of the most common methods to generating a report (at least for me) is to write out the data to a CSV file using Export-CSV. While this is a simple and effective method of getting most data out to a report that can be sent to others, there are other ways to accomplish this and provide it in a nicer fashion or combine multiple reports into one workbook that can be sent out to others. This alternative is using Excel to create and format reports prior to being sent out. This article will dive into creating the excel object and then writing data to a worksheet.

Using excel with PowerShell is made by using the available excel.application COM object that will allow us to work with excel to add data and format that data.

$excel = New-Object -ComObject excel.application

While you do not have to do this, but for this example I will make excel visible so it is easier to troubleshoot and see what is happening when I begin adding and formatting data.

$excel.visible = $True

image

Ok, so having Excel open without a workbook or worksheet doesn’t do us much good, so we will dig into the workbooks property and use its Add() method to add a workbook into Excel.

$workbook = $excel.Workbooks.Add()

This adds a default  of 3 worksheets to the workbook that was created. For reference, if you want add another worksheet, you can use the Add() method that is in the Worksheets property of the workbook object we created.

$workbook.Worksheets.Add()

Removing a worksheet requires the knowledge of what worksheet you want to remove and its order in the workbook. This can be confusing when you add a new worksheet into the workbook as shown in the example below.

$i=0;$workbook.Worksheets | %{$i++;[ordered]@{$_.name=$i}}

image

Sheet4, which was added is now the first item in the workbook. Lets say I want to remove sheet1 for whatever reason. We can do that by using the Delete() method.

$excel.DisplayAlerts = $False
$workbook.Worksheets.Item(2).Delete()

Before I attempt to delete this worksheet, I set the DisplayAlerts to $False, otherwise you would receive a confirmation to delete the worksheet. Note that you would not see the confirmation if you had kept the excel visibility to hidden.

Writing to each cell is as simple as knowing the coordinates of the cell you want to write to.  But before that we can simplify what worksheet we are going to write to by getting to that object. 

$diskSpacewksht= $workbook.Worksheets.Item(1)

As you can tell by my variable, I am going to go with a common item that is usually reported: Disk Space.

Lets go ahead and rename this worksheet to something that will be more in line with what I am writing to it.

$diskSpacewksht.Name = 'DriveSpace'

image

Since I am looking at drivespace, I will look at the following properties that will be used as the header for the worksheet: DeviceID, VolumeName, Size and FreeSpace.

Starting with the first row and column, I will create the headers in the worksheet using the Cells.Item(<row number>,<column number>) property.

$diskSpacewksht.Cells.Item(1,1) = 'DeviceID'
$diskSpacewksht.Cells.Item(1,2) = 'VolumeName'
$diskSpacewksht.Cells.Item(1,3) = 'Size(GB)'
$diskSpacewksht.Cells.Item(1,4) = 'FreeSpace(GB)'

image

For the header, I had to manually input the cell coordinates, but when we start writing out the data for the report, I can set up a counter for both the row and column to make things a little easier.

$row = 2
$column = 1
Get-CimInstance -ClassName Cim_LogicalDisk | ForEach {
    #DeviceID
    $diskSpacewksht.Cells.Item($row,$column) = $_.DeviceID
    $column++
    #VolumeName
    $diskSpacewksht.Cells.Item($row,$column) = $_.VolumeName
    $column++
    #Size
    $diskSpacewksht.Cells.Item($row,$column) = ($_.Size /1GB)
    $column++
    #FreeSpace
    $diskSpacewksht.Cells.Item($row,$column) = ($_.FreeSpace /1GB)
    #Increment to next Row and reset Column
    $row++
    $column = 1
}

image

I increment each column so that I am able to write each value in the correct cell. At the end of writing all of the data for the object, I then increment the row and reset the column value back to 1 and start the process over again until completion.

This report is just about finished, but before I save it off I want to clean it up just a bit by auto-sizing everything so the cells display everything.

$usedRange = $diskSpacewksht.UsedRange						
$usedRange.EntireColumn.AutoFit() | Out-Null

image

That is better, now we can save this off to a file that you can send or review later on.

$workbook.SaveAs("C:\temp\DiskSpace.xlsx")
$excel.Quit()

So there you have it, you now have a simple report saved to an excel spreadsheet. I know, it is not really that exciting yet and you can do the same thing with Export-CSV, but my next article on this will show you how to format the data to make it more impressive looking and future articles will show how to write to other worksheets, making a link to other worksheets as well as creating graphs to further enhance your report! Until then feel free to use my code and experiment on your own.

Posted in Excel, Office, powershell | Tagged , , , , | 24 Comments

PowerShell and WPF: ListBox Part 2–DataTriggers and ObservableCollection

Posted on December 8, 2012 by Boe Prox

I wanted to share a couple of more things when working with ListBoxes that I did not cover in my previous article. The items that I will cover are:

  • Using an Observable Collection to handle data in the ListBox
  • Using DataTriggers to change the row color based on the object property

Using an Observable Collection

An observable collection is a specialized collection that accepts a specific type of object (like using generics) and has an event called CollectionChanged that notifies whenever the collection changes. When using this through the console, you need to use Register-ObjectEvent to handle this type of change. The following example will take you through creating the collection and then using Register-ObjectEvent to handle whenever the collection changes as well as showing what happens when you attempt to add an object that is not valid for the collection (in this case I am only allowing Integers).

#Create an observable collection that only accepts integers
$observableCollection = New-Object System.Collections.ObjectModel.ObservableCollection[int]

#Show that it only accepts integers
$observableCollection.Add("Test") #This will fail
$observableCollection.Add(2) #This will succeed

#Set up an event watcher
Register-ObjectEvent -InputObject $observableCollection -EventName CollectionChanged -Action {
    $Global:test = $Event
    Switch ($test.SourceEventArgs.Action) {
        "Add" {
            $test.SourceEventArgs.NewItems | ForEach {
                Write-Host ("{0} was added" -f $_) -ForegroundColor Yellow -BackgroundColor Black
            }
        }
        "Remove" {
            $test.SourceEventArgs.OldItems | ForEach {
                Write-Host ("{0} was removed" -f $_) -ForegroundColor Yellow -BackgroundColor Black
            }
        }
        Default {
            Write-Host ("The following action occurred: {0}" -f $test.SourceEventArgs.Action) -ForegroundColor Yellow -BackgroundColor Black
        }
    }
}

#Add another integer and the event will kick off
$observableCollection.Add(10) | Out-Null

#Remove an integer
$observableCollection.Remove(2) | Out-Null

#Clear the collection
$observableCollection.Clear()

Get-EventSubscriber | Unregister-Event                                                      
Get-Job | Remove-Job -Force

 

image

As you can see, my initial attempt at adding a string to this collection failed as it was not an Integer. After creating the event watcher, you can see what happens when I add, remove and clear the collection.

Using this with a Listbox in a GUI is actually much simpler than this. All you have to do is bind the collection to the ItemsSource property of the Listbox and it will handle the rest. No need for creating an event to handle whenever the collection changes. All of that is handled by the collection automatically and the changes are made to the ListBox. Very cool stuff! The example below shows it in action.

#Build the GUI
[xml]$xaml = @"
<Window 
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    x:Name="Window" Title="Initial Window" WindowStartupLocation = "CenterScreen" 
    Width = "313" Height = "800" ShowInTaskbar = "True" Background = "lightgray"> 
    <ScrollViewer VerticalScrollBarVisibility="Auto">
        <StackPanel >
            <TextBox  IsReadOnly="True" TextWrapping="Wrap">
                Type something and click Add
            </TextBox>
            <TextBox x:Name = "inputbox"/>
            <Button x:Name="button1" Content="Add"/>
            <Button x:Name="button2" Content="Remove"/>
            <ListBox x:Name="listbox" SelectionMode="Extended" />
        </StackPanel>
    </ScrollViewer >
</Window>
"@
 
$reader=(New-Object System.Xml.XmlNodeReader $xaml)
$Window=[Windows.Markup.XamlReader]::Load( $reader )
 
#Connect to Controls
$inputbox = $Window.FindName('inputbox')
$button1 = $Window.FindName('button1')
$button2 = $Window.FindName('button2')
$listbox = $Window.FindName('listbox')

$Window.Add_Activated({
    #Have to have something initially in the collection
    $Script:observableCollection = New-Object System.Collections.ObjectModel.ObservableCollection[string]
    $listbox.ItemsSource = $observableCollection
    $inputbox.Focus()
})
 
#Events
$button1.Add_Click({
     $observableCollection.Add($inputbox.text)
     $inputbox.Clear()
})
$button2.Add_Click({
    ForEach ($item in @($listbox.SelectedItems)) {
        $observableCollection.Remove($item)
    }
}) 
$Window.ShowDialog() | Out-Null
     $inputbox.Clear()
})
$button2.Add_Click({
    ForEach ($item in @($listbox.SelectedItems)) {
        $observableCollection.Remove($item)
    }
}) 
$Window.ShowDialog() | Out-Null

 

image

This behaves exactly like the Listbox example I showed in the previous Listbox article. The exception is that instead of writing to the Items property of the Listbox, everything happens with the ObservableCollection and that object notifies the Listbox to update its collection.

DataTrigger with a Listbox

The next section shows how you can use DataTriggers on a Listbox to automatically change the row color based on a given property. The results will look something like the following example.

image

While it does not actually show the property, the list of services in the Listbox are color coded by the Status of the service. Red means the service is stopped and Green is running. I use data binding to only show the DisplayName in the Listbox even though I actually use the entire service object using the DisplayMemberPath property.

Setting the up DateTrigger requires some more XAML code that is added to the Listbox to determine what part of the Listbox will be looked at (ListBoxItem in this case). This code is used on the ItemContainerStyle property of the Listbox and then proceeds to set up the Triggers that then uses the DataTrigger for each property and action. The XAML code below shows how I setup the Triggers for the Status to look at either Running or Stopped and how it sets the background color.

<ListBox.ItemContainerStyle>
    <Style TargetType="{x:Type ListBoxItem}">
        <Style.Triggers>
            <DataTrigger Binding="{Binding Path=Status}" Value="Running">
                <Setter Property="ListBoxItem.Background" Value="Green" />
            </DataTrigger>
            <DataTrigger Binding="{Binding Path=Status}" Value="Stopped">
                <Setter Property="ListBoxItem.Background" Value="Red" />
            </DataTrigger>                                
        </Style.Triggers>
    </Style>
</ListBox.ItemContainerStyle>

Again, we have to bind the path of the property to Status so the trigger will know where to look to see if the service is either Running or Stopped and then using the Setter property to change the background color to Red or Green as shown in the picture above. Full code that does this is below.

#Build the GUI
[xml]$xaml = @"
<Window 
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    x:Name="Window" Title="Initial Window" WindowStartupLocation = "CenterScreen" 
    Width = "313" Height = "800" ShowInTaskbar = "True" Background = "lightgray"> 
    <ScrollViewer VerticalScrollBarVisibility="Auto">
        <StackPanel >
            <TextBox  IsReadOnly="True" TextWrapping="Wrap">
                Click button to get Services
            </TextBox>
            <TextBox x:Name = "inputbox"/>
            <Button x:Name="button1" Content="Get-Services"/>
            <Button x:Name="button2" Content="Refresh"/>
            <Expander IsExpanded="True">
                <ListBox x:Name="listbox" SelectionMode="Extended" DisplayMemberPath="DisplayName">            
                    <ListBox.ItemContainerStyle>
                        <Style TargetType="{x:Type ListBoxItem}">
                            <Style.Triggers>
                                <DataTrigger Binding="{Binding Path=Status}" Value="Running">
                                    <Setter Property="ListBoxItem.Background" Value="Green" />
                                </DataTrigger>
                                <DataTrigger Binding="{Binding Path=Status}" Value="Stopped">
                                    <Setter Property="ListBoxItem.Background" Value="Red" />
                                </DataTrigger>                                
                            </Style.Triggers>
                        </Style>
                    </ListBox.ItemContainerStyle>
                </ListBox>
            </Expander >
        </StackPanel>
    </ScrollViewer >
</Window>
"@
 
$reader=(New-Object System.Xml.XmlNodeReader $xaml)
$Window=[Windows.Markup.XamlReader]::Load( $reader )
 
#Connect to Controls
$button1 = $Window.FindName('button1')
$button2 = $Window.FindName('button2')
$Global:listbox = $Window.FindName('listbox')

$Window.Add_Loaded({
    #Have to have something initially in the collection
    $Global:observableCollection = New-Object System.Collections.ObjectModel.ObservableCollection[System.Object]
    $listbox.ItemsSource = $observableCollection
})
 
#Events
$button1.Add_Click({
    $observableCollection.Clear()
    Get-Service | ForEach {
        $observableCollection.Add($_)
    }
})
$button2.Add_Click({
    $observableCollection | ForEach {
        Write-Host ("Refreshing {0}" -f $_.displayName) -BackgroundColor Black -ForegroundColor Yellow    
        $_.Refresh()
    }    
    [System.Windows.Data.CollectionViewSource]::GetDefaultView( $Listbox.ItemsSource ).Refresh()
}) 
$Window.ShowDialog() | Out-Null

As you have seen, the ideas of techniques that you can use with Listboxes are many. Using Observable Collections and/or DataTriggers are just another tool in building a GUI to make it more usable and user friendly based on your requirements.

Posted in GUI, powershell, WPF | Tagged , , , , | 6 Comments