Submitted By: Kent Finkle

Adds data to an Excel spreadsheet and then creates a line chart based on that data.

PowerShell
Edit|Remove
$comments = @'
Script name: Create-LineChart.ps1
Created on: Thursday, May 31, 2007
Author: Kent Finkle
Purpose: How can I use Windows Powershell to
Create a Line Chart in Microsoft Excel?
'@
#-----------------------------------------------------
function Release-Ref ($ref) {
([System.Runtime.InteropServices.Marshal]::ReleaseComObject(
[System.__ComObject]$ref) -gt 0)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers() 
}
#-----------------------------------------------------
$objExcel = new-object -comobject excel.application
$objExcel.Visible = $True

$objWorkbook = $objExcel.Workbooks.Add()

$objWorksheet = $objWorkbook.Worksheets.Item(1)
 
$objWorksheet.Cells.Item(1,2).Value() = "Monday"
$objWorksheet.Cells.Item(1,3).Value() = "Tuesday"
$objWorksheet.Cells.Item(1,4).Value() = "Wednesday"
$objWorksheet.Cells.Item(1,5).Value() = "Thursday"
$objWorksheet.Cells.Item(1,6).Value() = "Friday"
$objWorksheet.Cells.Item(2,1).Value() = "atl-dc-01"
$objWorksheet.Cells.Item(2,2).Value() = 82
$objWorksheet.Cells.Item(2,3).Value() = 85
$objWorksheet.Cells.Item(2,4).Value() = 91
$objWorksheet.Cells.Item(2,5).Value() = 178
$objWorksheet.Cells.Item(2,6).Value() = 91
$objWorksheet.Cells.Item(3,1).Value() = "atl-dc-02"
$objWorksheet.Cells.Item(3,2).Value() = 104
$objWorksheet.Cells.Item(3,3).Value() = 87
$objWorksheet.Cells.Item(3,4).Value() = 93
$objWorksheet.Cells.Item(3,5).Value() = 100
$objWorksheet.Cells.Item(3,6).Value() = 96
$objWorksheet.Cells.Item(4,1).Value() = "atl-dc-03"
$objWorksheet.Cells.Item(4,2).Value() = 78
$objWorksheet.Cells.Item(4,3).Value() = 76
$objWorksheet.Cells.Item(4,4).Value() = 95
$objWorksheet.Cells.Item(4,5).Value() = 90
$objWorksheet.Cells.Item(4,6).Value() = 91
 
$objRange = $objWorksheet.UsedRange
$a = $objRange.Select()
 
$colCharts = $objExcel.Charts
$objChart = $colCharts.Add()
$a = $objChart.Activate
 
$objChart.ChartType = 65
 
$a = Release-Ref($objChart)
$a = Release-Ref($objRange)
$a = Release-Ref($objWorksheet)
$a = Release-Ref($objWorkbook)
$a = Release-Ref($objExcel)