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

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.

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

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.

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

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

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++

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)

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

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

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

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

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

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

Pingback: PowerShell : CSV – Disk Space Report – Excel | Prashanth Jayaram
Jean-Claude DeMars says:

April 30, 2015 at 1:22 pm

I found a typo when I cut and pasted the sample:

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

should be

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

Pingback: PowerShell – OS Inventory and Disk Info – Consolidated Excel File – EMAIL - SQL Server - SQL Server - Toad World
Pingback: PowerShell – OS Inventory and Disk Info – Consolidated Excel File – EMAIL - SQL Server - SQL Server - Toad World
Pingback: PowerShell – OS Inventory and Disk Info – Consolidated Excel File – EMAIL | Prashanth Jayaram
jahnplay says:

September 24, 2013 at 9:56 am

Good stuff,

if someone is wonder how to get it to pull from a server list in a .txt file here is how.

#Grab servers
$colComputers = get-content “.\Servers.txt”

#Get the drives and filter out CD/DVD drives
foreach ($strComputer in $colComputers)
{
$diskDrives = Get-WmiObject win32_LogicalDisk -Filter “DriveType=’3′” -ComputerName $strComputer

whackdiddy says:

April 10, 2013 at 7:08 pm

Awesome post, will come in very handy.

Thanks for your time and effort in writing this.

bharathmannem says:

April 2, 2013 at 7:26 am

Hi Boe,
Could please explain me how to remove the default percentage values in the pie chart using powershell

Pingback: Powershell и Excel. Часть 2: форматирование ячеек | SMEARG
bharathmannem says:

March 21, 2013 at 8:02 am

Thanks Boe,
Good Post. Learned a lot from this
.(“=COUNTIF(R[-{0}]C[7]:R[3989]C[7],””Critical””)” -f $lastRow)… Could you please Explain this what exactly it is calculating

Pingback: Powershell и Excel. Часть 1: заполнение таблицы « SMEARG
Mohan says:

December 29, 2012 at 12:04 pm

Hey Boe,

Thanks for your hard work.

Can you please let me know how to get the disk space for the list of servers in text file?

i have tried to import the txt file into this code but got lot of errors

Thanks in Advance 🙂

Regards,
Mohan M

- Boe Prox says:
  
  December 29, 2012 at 8:46 pm
  
  Thanks Mohan! I actually have an updated version of this script that I will post next week that should be what you are looking for.