Powershell从EXCEL创建折线图 [英] Powershell to create line chart from EXCEL

问题描述

我的powershell脚本将数据输入到EXCEL工作表中

My powershell script inputs data into EXCEL worksheet

并且我正在尝试创建类似于

and I am trying to create a line chart that will resemble

但是，这是我到目前为止的代码:

However, this is the code I have so far:

$xlConditionValues=[Microsoft.Office.Interop.Excel.XLConditionValueTypes]
$xlTheme=[Microsoft.Office.Interop.Excel.XLThemeColor]
$xlChart=[Microsoft.Office.Interop.Excel.XLChartType]
$xlIconSet=[Microsoft.Office.Interop.Excel.XLIconSet]
$xlDirection=[Microsoft.Office.Interop.Excel.XLDirection]

...

$chart=$ws.Shapes.AddChart().Chart
$chart.chartType=$xlChart::xlBarClustered

$start=$ws.range("A1")

$Y=$ws.Range($start,$start.End($xlDirection::xlDown))

$start=$ws.range("B2")

$X=$ws.Range($start,$start.End($xlDirection::xlDown))

$chartdata=$ws.Range("A$($Y.item(1).Row):A$($Y.item($Y.count).Row),B$($X.item(1).Row):B$($X.item($X.count).Row)")
$chart.SetSourceData($chartdata)

#add labels
$chart.seriesCollection(1).Select() | Out-Null
$chart.SeriesCollection(1).ApplyDataLabels() | out-Null

#modify the chart title
$chart.ChartTitle.Text = "Number of Computer"


$ws.shapes.item("Chart 1").top=40

这是它生成的图形

我什至如何开始解决此问题?有帮助的教程吗?

How do I even begin to fix this? Any helpful tutorials?

推荐答案

根据我在VBA上的经验，似乎首先要尝试的是更改图表类型.您有$chart.chartType=$xlChart::xlBarClustered.基于类似的VBA命令，我将尝试将其更改为$chart.chartType=$xlChart::xlLine.那应该有很大的不同，让您了解还有哪些需要调整的地方.

Based on my experience with VBA, it appears that the first thing to try is changing your chart type. You have $chart.chartType=$xlChart::xlBarClustered. Based on the similar VBA commands, I would try changing this to $chart.chartType=$xlChart::xlLine. That should make a big difference, and let you see what else needs to be tweaked.

这篇关于Powershell从EXCEL创建折线图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！