Submitted By: Kent Finkle

Creates and then formats a basic column chart in Microsoft Excel.

PowerShell
Edit|Remove
$comments = @'
Script name: Format-ColumnChart.ps1
Created on: Sunday, June 03, 2007
Author: Kent Finkle
Purpose: How can I use Windows Powershell to
Format the Basic Column Chart?
'@
#-----------------------------------------------------
function Release-Ref ($ref) {
([System.Runtime.InteropServices.Marshal]::ReleaseComObject(
[System.__ComObject]$ref) -gt 0)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers() 
}
#-----------------------------------------------------
$xlCategory = 1
$xlValue = 2
$xlSeriesAxis = 3
 
$objExcel = new-object -comobject excel.application
$objExcel.Visible = $True
$objWorkbook = $objExcel.Workbooks.Add()
$objWorksheet = $objWorkbook.Worksheets.Item(1)
 
$objWorksheet.Cells.Item(1,1) = "Operating System"
$objWorksheet.Cells.Item(2,1) = "Windows Server 2003"
$objWorksheet.Cells.Item(3,1) = "Windows XP"
$objWorksheet.Cells.Item(4,1) = "Windows 2000"
$objWorksheet.Cells.Item(5,1) = "Windows NT 4.0"
$objWorksheet.Cells.Item(6,1) = "Other"
$objWorksheet.Cells.Item(1,2) = "Number of Computers"
$objWorksheet.Cells.Item(2,2) = 145
$objWorksheet.Cells.Item(3,2) = 987
$objWorksheet.Cells.Item(4,2) = 611
$objWorksheet.Cells.Item(5,2) = 41
$objWorksheet.Cells.Item(6,2) = 56
$objRange = $objWorksheet.UsedRange
$a = $objRange.Select()
 
$colCharts = $objExcel.Charts
$objChart = $colCharts.Add()
$a = $objChart.Activate
 
$objChart.HasLegend = $False
$objChart.ChartTitle.Text = "Operating System Use"
$objChart.ChartType = -4100
 
$objChart.ChartArea.Interior.ColorIndex = 1
$objChart.ChartTitle.Font.Size = 24
$objChart.ChartTitle.Font.ColorIndex = 2
$objChart.Walls.Interior.ColorIndex = 37
$objChart.Floor.Interior.ColorIndex = 37
 
$objChart.SeriesCollection(1).Interior.ColorIndex = 3
$objChart.SeriesCollection(1).Name = "2004"
 
$objChart.PlotArea.Interior.ColorIndex = 40
$objChart.PlotArea.Border.Weight = 4
$objChart.PlotArea.Border.ColorIndex = 46
 
$objChart.Axes($xlValue).TickLabels.Font.Bold = $True
$objChart.Axes($xlCategory).TickLabels.Font.Bold = $True
$objChart.Axes($xlSeriesAxis).TickLabels.Font.Bold = $True
 
$a = Release-Ref($objChart)
$a = Release-Ref($objRange)
$a = Release-Ref($objWorksheet)
$a = Release-Ref($objWorkbook)
$a = Release-Ref($objExcel)