powershell不能向excel图表添加多个图例条目（系列） [英] powershell cannot add more than one legend entry (series) to an excel chart

问题描述

我有一个问题添加多个系列到excel图表对象中的seriescollection通过powershell这里是我的代码：

  [threading.thread] :: CurrentThread.CurrentCulture ='en-US'
 
 $ excel = New-Object -comobject Excel.Application 
 
 $ workbook = $ excel.workbooks .add（）
 
 $ datasheet = $ workbook.Worksheets.Item（2）
 $ chartsheet = $ workbook.Worksheets.Item（1）
 
 [datetime ] $ startDate =2012-11-29 00:00:00
 [datetime] $ finishDate =2012-12-07 00:00:00
 [datetime] $ dayCounter = $ startDate 
 
 $ startRow = 2 
 $ startColumn = 2 
 
 $ columnCounter = 2 
 $ rowCounter = 2 
 while（$ dayCounter -le $ finishDate）
 {
 $ datasheet.Cells.Item（$ rowCounter，$ columnCounter）= $ dayCounter.ToShortDateString（）
 $ datasheet.Cells.Item（$ rowCounter + 1，$ columnCounter ）= $ columnCounter 
 $ datasheet.Cells.Item（$ rowCounter + 2，$ columnCounter）= 2 * $ columnCounter 
 $ columnCounter ++ 
 $ dayCounter = $ dayCounter.AddDays（1）
} 
 
 $ datasheet.Range（$ rowCounter.ToString（）+：+ $ rowCounter.ToString（））。NumberFormat =m / d / yyyy
 
 $ excel.application.DisplayAlerts = $ False 
 $ chart = $ chartsheet.Shapes.addChart（）。
 $ chart.hasTitle = $ true 
 $ chart.chartTitle.text = Ramp Example
 $ chartType = [Microsoft.Office.Interop.Excel.XlChartType] :: xlLine 
 $ chart.chartType = $ chartType 
 
 $ startCell = $ datasheet。 Cells.Item（3,2）.Address（$ false，$ false）
 $ endCell = $ datasheet.Cells.Item（3,10）.Address（$ false，$ false）
 
 $ startCell +，+ $ endCell 
 
 $ datarange = $ datasheet.Range（$ startCell，$ endCell）
 $ chart.SetSourceData（$ datarange）
 $ chart.SeriesCollection（1）.Name =First
 $ chart.SeriesCollection（1）.XValues = $ datasheet.Range（B2，J2）
 
 $ newSeries = $ chart.SeriesCollection（）。NewSeries 
 $ chart.SeriesCollection（2）.Values = $ datasheet.Range（B4，J4）
 $ chart.SeriesCollection（2）.Name =第二个
 $ chart.SeriesCollection（2）.XValues = $ datasheet.Range（B2，J2）
 
 
 $ excel.Visible = $ True 
  

错误：

异常调用SeriesCollection带有1参数：无效参数
在C：\localwork\tfs\OpenExcel.ps1：49字符：24
+ $ chart.SeriesCollection< ;& （2）.Values = $ datasheet.Range（B4，J4）
+ CategoryInfo：NotSpecified：（:) []，MethodInvocationException
+ FullyQualifiedErrorId：ComMethodTargetInvocation

异常调用具有1参数的SeriesCollection：无效参数
在C：\localwork\tfs\OpenExcel.ps1：50字符：24
+ $ chart.SeriesCollection<<<< （2）.Name =Second
+ CategoryInfo：NotSpecified：（:) []，MethodInvocationException
+ FullyQualifiedErrorId：ComMethodTargetInvocation

异常调用带有1参数的SeriesCollection：无效参数
在C：\localwork\tfs\OpenExcel.ps1：51字符：24
+ $ chart.SeriesCollection< << （2）.XValues = $ datasheet.Range（B2，J2）
+ CategoryInfo：NotSpecified：（:) []，MethodInvocationException
+ FullyQualifiedErrorId：ComMethodTargetInvocation

问题是; 如何使用powershell代码为SeriesCollection添加额外的条目？

任何帮助都将受到欢迎

<解决方案问题解决了，不得不在newSeries上调用Invoke（） - 如下：

  $ chart.SeriesCollection（）。NewSeries.Invoke（）
  

我猜在调用VBA在我在Excel中的宏开始这个开发有一些区别：

  ActiveSheet.Shapes.AddChart .Select 
 ActiveChart.ChartType = xlLine 
 ActiveChart.SetSourceData Source：= Sheets（Sheet2）。Range（B3：P3）
 ActiveChart.SeriesCollection（1）.Name = =First
 ActiveChart.SeriesCollection.NewSeries 
 ActiveChart.SeriesCollection（2）.Values == Sheet2！$ B $ 4：$ P $ 4
 ActiveChart.SeriesCollection ）.Name ==Second
 ActiveChart.SeriesCollection（2）.XValues == Sheet2！$ B $ 2：$ P $ 2
  pre> 
 
 
要在powershell中调用它，我没有找到一个很好的例子来动态添加系列到WEB上的Excel图表！
 
I have a problem adding more than one series to the seriescollection in excels chart object through powershell here is my code:
[threading.thread]::CurrentThread.CurrentCulture = 'en-US'

$excel = New-Object -comobject Excel.Application

$workbook = $excel.workbooks.add()

$datasheet  = $workbook.Worksheets.Item(2)
$chartsheet = $workbook.Worksheets.Item(1)

[datetime] $startDate  = "2012-11-29 00:00:00" 
[datetime] $finishDate = "2012-12-07 00:00:00"
[datetime] $dayCounter = $startDate

$startRow = 2
$startColumn = 2

$columnCounter = 2
$rowCounter = 2
while ($dayCounter -le $finishDate)
{
  $datasheet.Cells.Item($rowCounter, $columnCounter) = $dayCounter.ToShortDateString()
  $datasheet.Cells.Item($rowCounter+1, $columnCounter) = $columnCounter
  $datasheet.Cells.Item($rowCounter+2, $columnCounter) = 2 * $columnCounter
  $columnCounter++
  $dayCounter = $dayCounter.AddDays(1)
}

$datasheet.Range($rowCounter.ToString() + ":" + $rowCounter.ToString()).NumberFormat = "m/d/yyyy"

$excel.application.DisplayAlerts=$False
$chart = $chartsheet.Shapes.addChart().chart
$chart.hasTitle = $true
$chart.chartTitle.text = "Ramp Example"
$chartType = [Microsoft.Office.Interop.Excel.XlChartType]::xlLine
$chart.chartType = $chartType

$startCell = $datasheet.Cells.Item(3,2).Address($false,$false)
$endCell   = $datasheet.Cells.Item(3,10).Address($false,$false)

$startCell + ", " + $endCell

$datarange = $datasheet.Range($startCell, $endCell)
$chart.SetSourceData($datarange)
$chart.SeriesCollection(1).Name    = "First"
$chart.SeriesCollection(1).XValues = $datasheet.Range("B2", "J2")

$newSeries = $chart.SeriesCollection().NewSeries
$chart.SeriesCollection(2).Values  = $datasheet.Range("B4", "J4")
$chart.SeriesCollection(2).Name    = "Second"
$chart.SeriesCollection(2).XValues = $datasheet.Range("B2", "J2")


$excel.Visible = $True
THE ERRORS:

Exception calling "SeriesCollection" with "1" argument(s): "Invalid Parameter"
At C:\localwork\tfs\OpenExcel.ps1:49 char:24
+ $chart.SeriesCollection <<<< (2).Values  = $datasheet.Range("B4", "J4")
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ComMethodTargetInvocation

Exception calling "SeriesCollection" with "1" argument(s): "Invalid Parameter"
At C:\localwork\tfs\OpenExcel.ps1:50 char:24
+ $chart.SeriesCollection <<<< (2).Name    = "Second"
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ComMethodTargetInvocation

Exception calling "SeriesCollection" with "1" argument(s): "Invalid Parameter"
At C:\localwork\tfs\OpenExcel.ps1:51 char:24
+ $chart.SeriesCollection <<<< (2).XValues = $datasheet.Range("B2", "J2")
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ComMethodTargetInvocation

The question is; how do I get an extra entry into the SeriesCollection with powershell code?

Any help will be apprecitated
解决方案
Problem solved, had to call Invoke() on the newSeries - so: 
$chart.SeriesCollection().NewSeries.Invoke()
And that's it, I guess there's some difference from calling the VBA in the macro I made in Excel to begin this development:
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=Sheets("Sheet2").Range("B3:P3")
ActiveChart.SeriesCollection(1).Name = "=""First"""
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Values = "=Sheet2!$B$4:$P$4"
ActiveChart.SeriesCollection(2).Name = "=""Second"""
ActiveChart.SeriesCollection(2).XValues = "=Sheet2!$B$2:$P$2"
To calling it in powershell, I have not found one good example on doing this adding of series dynamically to an Excel Chart on the WEB!

                        
这篇关于powershell不能向excel图表添加多个图例条目（系列）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！