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.

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
About these ads

About Boe Prox

Microsoft PowerShell MVP working as a Senior Systems Administrator
This entry was posted in Excel, Office, powershell and tagged , , , . Bookmark the permalink.

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

  1. jahnplay says:

    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

  2. whackdiddy says:

    Awesome post, will come in very handy.

    Thanks for your time and effort in writing this.

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

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

  5. 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

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

  7. Mohan says:

    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

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s