Change chart colours using ADD-EXCELCHART in Powershell module IMPORTEXCEL

PowerShell Help
1

Hi all,

I’ve been using IMPORTEXCEL in Powershell recently, to create Excel charts but I’m stuck on the syntax to change one bar chart column point colour. In VBA, I recorded a macro whilst carrying out the action and it created this:

ActiveSheet.ChartObjects(“Chart77DF”).Activate
ActiveChart.FullSeriesCollection(1).Select
ActiveChart.FullSeriesCollection(1).Points(8).Select
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 0, 0)
.Transparency = 0
.Solid
End With
Application.CommandBars(“Format Object”).Visible = False

Below is an extract from my Powershell script that works. The last line changes the colour of the entire chart series to RED but I cannot work out how to emulate the VBA script to change just one column point of the bar chart. Any help would be much appreciated.

$YearlyTotals = ConvertFrom-Csv @"
Age,Total
“Under 1`n year”,“10”
“1-2`n years”,“100”
“2-3`n years”,“200”
“3-4`n years”,“300”
“4-5`n years”,“400”
“5-6`n years”,“500”
“6-7`n years”,“600”
“Over 7`n years”,“700”
“Archived`n data”,"“1000”
"@

$tableName = ‘DataAge’

$excel = $YearlyTotals | Export-Excel -Path $ExportFilePath -WorksheetName ‘Chart’ -AutoSize -TableStyle Medium9 -TableName $tableName -PassThru

$ChartDef2 = @{
Title = “Live Data”
ChartType = ‘ColumnStacked’
XRange = “$tableName[Age]”
YRange = “$tableName[Total]”
SeriesHeader = “Size`n(GB)”
Row = 27
Column = 5
Width = 700
Height = 500
}
$chart = Add-ExcelChart @ChartDef2 -Worksheet $excel.Chart -PassThru
$chart.PlotArea.Fill.Color = [System.Drawing.Color]::LightYellow
$chart.Fill.Color = [System.Drawing.Color]::LightBlue
$chart.Series[0].Fill.Color = [System.Drawing.Color]::Red

2

Hi, welcome to the forum :wave:

Firstly, when posting code in the forum, please can you use the preformatted text </> button. It really helps us with readability, and copying and pasting your code (we don’t have to faff about replacing curly quote marks to get things working).

To change the colour of an individual datapoint you need to modify the XML that defines the chart.
The code below should be appended to your example script, hence I have not defined $chart in my code.

This will change the colour of the 5th datapoint (index 4) to green:

$dataPoint = '4'
$colourAsHex = '00FF00'

$xmlText = @'
<c:dPt xmlns:c="http://schemas.openxmlformats.org/drawingml/2006/chart">
<c:idx val="{0}" />
<c:spPr>
    <a:solidFill xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main">
        <a:srgbClr val="{1}" />
    </a:solidFill>
</c:spPr>
</c:dPt>
'@

$node = [xml]($xmlText -f $dataPoint,$colourAsHex)
$imported = $chart.ChartXml.ImportNode($node.DocumentElement, $true)
$chart.ChartXml.chartSpace.chart.plotArea.barChart.ser.AppendChild($imported)

I based this solution on these posts which you might also find useful:

3

Many thanks Matt, I will give that a try and I take on board your point about preformatted text.

Just one question, I’ve seen code before that makes reference to http://schemas.openxmlformats.org but I’ve never fully understood what it is. Does this mean my script will always require internet access to run or is this looking something up under the hood of the chart?.

I ask because there may be times when it needs to be ran offline.

4

No, you don’t need Internet access to run it but if you don’t declare the namespace, you’ll get an error such as:

Error: "'c' is an undeclared prefix. Line 1, position 2."
5

That works perfectly. Thanks again Matt.

6

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.