Submitted By: Kent Finkle
Adds data to an Excel spreadsheet and then creates a line chart based on that data.
$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)
$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)