Create Clustered Colum Chart In Word

PowerShell Help
1

by davehook at 2012-10-10 01:46:11

Hi
Im trying to create a clustered column chart in Word and cant find any PowerShell examples of this. The data im using is in a csv which is basically
Contract 1 Contract 2 Contract 3
07-Feb 100 120 110
07-Mar 305 270 260
07-Apr 225 340 120

i dont know what to do once i have imported the csv and set the charttype to 51, can anybody show me an example or explain?

Many thanks
by JeffH at 2012-10-10 07:08:40
I would start by recording a Word macro that does what you need done. Then use that as a model for a PowerShell script using the Word COM object. Or is this really an Excel problem?
by davehook at 2012-10-11 01:08:04
Jeffrey
I had tried the word macro and all it gives me is

Sub Macro10()
‘
’ Macro10 Macro
‘
’
Selection.InlineShapes.AddChart Type:=51
End Sub

also im new to this so im stumbling around in the dark

Thanks
by davehook at 2012-10-11 02:21:22
ok, i have found a script that works that i need to alter to take in my data source but need help with this
~
sample code
~
[void][Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms.DataVisualization")
$scriptpath = Split-Path -parent $MyInvocation.MyCommand.Definition

# chart object
$chart1 = New-object System.Windows.Forms.DataVisualization.Charting.Chart
$chart1.Width = 600
$chart1.Height = 600
$chart1.BackColor = [System.Drawing.Color]::White

# title
[void]$chart1.Titles.Add("Top 5 - Memory Usage (as: Column)")
$chart1.Titles[0].Font = "Arial,13pt"
$chart1.Titles[0].Alignment = "topLeft"

# chart area
$chartarea = New-Object System.Windows.Forms.DataVisualization.Charting.ChartArea
$chartarea.Name = "ChartArea1"
$chartarea.AxisY.Title = "Memory (MB)"
$chartarea.AxisX.Title = "Process Name"
$chartarea.AxisY.Interval = 100
$chartarea.AxisX.Interval = 1
$chart1.ChartAreas.Add($chartarea)

# legend
$legend = New-Object system.Windows.Forms.DataVisualization.Charting.Legend
$legend.name = "Legend1"
$chart1.Legends.Add($legend)

# data source
$datasource = Get-Process | sort PrivateMemorySize -Descending | Select-Object -First 5

# data series
[void]$chart1.Series.Add("VirtualMem")
$chart1.Series["VirtualMem"].ChartType = "Column"
$chart1.Series["VirtualMem"].BorderWidth = 3
$chart1.Series["VirtualMem"].IsVisibleInLegend = $true
$chart1.Series["VirtualMem"].chartarea = "ChartArea1"
$chart1.Series["VirtualMem"].Legend = "Legend1"
$chart1.Series["VirtualMem"].color = "#62B5CC"
$datasource | ForEach-Object {$chart1.Series["VirtualMem"].Points.addxy( $.Name , ($.VirtualMemorySize / 1000000)) }

# data series
[void]$chart1.Series.Add("PrivateMem")
$chart1.Series["PrivateMem"].ChartType = "Column"
$chart1.Series["PrivateMem"].IsVisibleInLegend = $true
$chart1.Series["PrivateMem"].BorderWidth = 3
$chart1.Series["PrivateMem"].chartarea = "ChartArea1"
$chart1.Series["PrivateMem"].Legend = "Legend1"
$chart1.Series["PrivateMem"].color = "#E3B64C"
$datasource | ForEach-Object {$chart1.Series["PrivateMem"].Points.addxy( $.Name , ($.PrivateMemorySize / 1000000)) }

# save chart
$chart1.SaveImage("$scriptpath\SplineArea.png","png")

~
my data source code
~
# data source
$datasource = import-csv "c:\x.csv"

# data series
[void]$chart1.Series.Add("series 1")
$chart1.Series["VirtualMem"].ChartType = "Column"
$chart1.Series["VirtualMem"].BorderWidth = 3
$chart1.Series["VirtualMem"].IsVisibleInLegend = $true
$chart1.Series["VirtualMem"].chartarea = "ChartArea1"
$chart1.Series["VirtualMem"].Legend = "Legend1"
$chart1.Series["VirtualMem"].color = "#62B5CC"
$datasource | ForEach-Object {[void]$Chart1.Series.Add("series 1")}

# data series
[void]$chart1.Series.Add("series 2")
$chart1.Series["PrivateMem"].ChartType = "Column"
$chart1.Series["PrivateMem"].IsVisibleInLegend = $true
$chart1.Series["PrivateMem"].BorderWidth = 3
$chart1.Series["PrivateMem"].chartarea = "ChartArea1"
$chart1.Series["PrivateMem"].Legend = "Legend1"
$chart1.Series["PrivateMem"].color = "#E3B64C"
$datasource | ForEach-Object {[void]$Chart1.Series.Add("series 2")}
# save chart
$chart1.SaveImage("c:\SplineArea.png","png")


The line i suspect is wrong is
$datasource | ForEach-Object {[void]$Chart1.Series.Add("series 1")}

any ideas???
by JeffH at 2012-10-11 06:57:30
What happens when you run your script? What error do you get? Is this the code you are running?
by JeffH at 2012-10-11 07:14:52
I did a little testing and yes that line is problematic. You are trying to add the chart over and over. Instead you need to set the data points like the sample code is doing:

$datasource | ForEach-Object {
$chart1.Series["VirtualMem"].Points.addxy( $.Name , ($.VirtualMemorySize / 1000000))
}

I think we need to see what code you are trying to run so we can figure out how to revise it. I got the sample code to work, but I don’t know how it relates to your script.
by davehook at 2012-10-12 02:53:26
Thanks Jeff, im struggling to get my data to correspond with that line. I have 4 column headers, a blank one, Contract 1, Contract 2 and Contract 3 and the blank column has 3 rows with 07-Feb, 07-Mar and 07-Apr with standard charectar string data for each field. How do i manipulate the $chart1.Series line?

Thanks
by JeffH at 2012-10-12 04:33:23
First, I don’t think this will work with blank column headings. I think you are going to need to add something like Date as a header. What I don’t know is what you want to go into the data series. If it is one of the contract values I think you would try something like this:

$datasource | ForEach-Object {
$chart1.Series["PrivateMem"].Points.addxy( $.Date , $.Contract1)
}

I suggest you start revising and posting code you are working from so we have some concrete to work with.
by JeffH at 2012-10-12 04:43:49
I was curious to see how this works so I threw something together.


[void][Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms.DataVisualization")
$scriptpath = Split-Path -parent $MyInvocation.MyCommand.Definition

# chart object
$chart1 = New-object System.Windows.Forms.DataVisualization.Charting.Chart
$chart1.Width = 600
$chart1.Height = 600
$chart1.BackColor = [System.Drawing.Color]::White

# title
[void]$chart1.Titles.Add("My Contracts")
$chart1.Titles[0].Font = "Arial,13pt"
$chart1.Titles[0].Alignment = "topLeft"

# chart area
$chartarea = New-Object System.Windows.Forms.DataVisualization.Charting.ChartArea
$chartarea.Name = "ChartArea1"
$chartarea.AxisY.Title = "Memory (MB)"
$chartarea.AxisX.Title = "Process Name"
$chartarea.AxisY.Interval = 100
$chartarea.AxisX.Interval = 1
$chart1.ChartAreas.Add($chartarea)

# legend
$legend = New-Object system.Windows.Forms.DataVisualization.Charting.Legend
$legend.name = "Legend1"
$chart1.Legends.Add($legend)

# data source
$datasource = import-csv C:\work\contract.csv

[void]$chart1.Series.Add("Contract 1")
[void]$chart1.Series.Add("Contract 2")
[void]$chart1.Series.Add("Contract 3")

# data series
[void]$chart1.Series.Add("series 1")
$chart1.Series["Contract 1"].ChartType = "Column"
$chart1.Series["Contract 1"].BorderWidth = 3
$chart1.Series["Contract 1"].IsVisibleInLegend = $true
$chart1.Series["Contract 1"].chartarea = "ChartArea1"
$chart1.Series["Contract 1"].Legend = "Legend1"
$chart1.Series["Contract 1"].color = "#62B5CC"

$datasource | ForEach-Object {
$chart1.Series["Contract 1"].Points.addxy( $.Date ,$.‘Contract 1’)
}

# data series
[void]$chart1.Series.Add("series 2")
$chart1.Series["Contract 2"].ChartType = "Column"
$chart1.Series["Contract 2"].IsVisibleInLegend = $true
$chart1.Series["Contract 2"].BorderWidth = 3
$chart1.Series["Contract 2"].chartarea = "ChartArea1"
$chart1.Series["Contract 2"].Legend = "Legend1"
$chart1.Series["Contract 2"].color = "#E3B64C"

$datasource | ForEach-Object {
$chart1.Series["Contract 2"].Points.addxy( $.Date , $.‘Contract 2’)
}

# data series
[void]$chart1.Series.Add("series 3")
$chart1.Series["Contract 3"].ChartType = "Column"
$chart1.Series["Contract 3"].IsVisibleInLegend = $true
$chart1.Series["Contract 3"].BorderWidth = 3
$chart1.Series["Contract 3"].chartarea = "ChartArea1"
$chart1.Series["Contract 3"].Legend = "Legend1"
$chart1.Series["Contract 3"].color = "#FF0040"

$datasource | ForEach-Object {
$chart1.Series["Contract 3"].Points.addxy( $.Date , $.‘Contract 3’)
}

# save chart
$chart1.SaveImage("c:\work\contracts.png","png")

Invoke-Item C:\work\contracts.png


This is based on a CSV file that looks like this:

"Date","Contract 1","Contract 2","Contract 3"
07-Feb,100,120,110
07-Mar,305,270,260
07-Apr,225,340,120
by davehook at 2012-10-15 02:33:18
Thanks, that works great