問題描述
Tại sao tôi không thể tạo biểu đồ này trong excel (sử dụng powershell) (Why can't I create this chart in excel (using powershell))
Trying to add a chart in excel using powershell. I used the following link for guidance:
http://theolddogscriptingblog.wordpress.com/2010/07/03/how‑do‑i‑change‑the‑size‑or‑position‑of‑my‑chart‑with‑powershell/
Here is the code:
# <‑‑‑‑ Start Code ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑>
$xl = New‑Object ‑comobject Excel.Application
# Show Excel
$xl.visible = $true
$xl.DisplayAlerts = $False
# Open a workbook
$wb = $xl.workbooks.add()
#Create Worksheets
$ws = $wb.Worksheets.Item(1) # Opens Excel and 3 empty Worksheets
1..8 | % { $ws.Cells.Item(1,$_) = $_ } # adds some data
1..8 | % { $ws.Cells.Item(2,$_) = 9‑$_ } # adds some data
$range = $ws.range("a${xrow}:h$yrow") # sets the Data range we want to chart
# create and assign the chart to a variable
#$ch = $xl.charts.add() # This will open a new sheet
$ch = $ws.shapes.addChart().chart # This will put the Chart in the selected WorkSheet
$ch.chartType = 58
$ch.setSourceData($range)
$RngToCover = $ws.Range("D5:J19") # This is where we want the chart
$ChtOb = $ch.Parent # This selects the current Chart
$ChtOb.Top = $RngToCover.Top # This moves it up to row 5
$ChtOb.Left = $RngToCover.Left # and to column D
$ChtOb.Height = $RngToCover.Height # resize This sets the height of your chart to Rows 5 ‑ 19
$ChtOb.Width = $RngToCover.Width # resize This sets the width to Columns D ‑ J
<‑‑‑‑‑‑‑‑‑‑‑‑‑ End Code ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑>
This works on one machine I have which uses Excel 2010, but on another machine using Excel 2003 it fails with the following message:
"Method invocation failed because [System.__ComObject] doesn't contain a method named 'addchart'."
Is this a limitation of the version of Excel I'm using? Using powershell, how can I add a chart within a worksheet in Excel 2003?
參考解法
方法 1:
According to the documentation the method was added with Excel 2007, so it's not available in Excel 2003. Try recording chart creation as a macro in Excel 2003 and translate the result into PowerShell. You'll probably get something like this:
$chart = $xl.Charts.Add
$chart.ChartType = ...
$chart.SetSourceData $xl.Sheets(1).Range(...), ...
$chart.Location ...
(by user2230237、Ansgar Wiechers)