This post is in continuation of my previous post on creating excel charts using PowerShell where we discussed how can we create a basic excel chart. Refer below link for first post

How to create Excel Chart using PowerShell – Part 1

Today we will see some more useful features. Let me start with a question

What if there are multiple data sets available in the sheet?

As in previous post, we didn’t provide any data source to the chart but in an ideal case we should. Now we have multiple sets of data and we want to create a chart based on each set. Say our sheet looks like

We want to create two charts based on the two data tables as above. So we need to provide it as a data source. The easy way to select data, is using the current region property. Lets see that

#Select the first cell via Range and select CurrentRegion which selects the entire table
$DataforFirstChart = $wsData.Range("A1").CurrentRegion

Here we DataforFirstChart refers the first table. A1 is the first cell of the table and current region returns the complete table. Similarly we can get the second table

$DataforSecondChart = $wsData.Range("A11").CurrentRegion

First cell of the second table is A11 so we used the same.

As we are going to create two charts we need to define the positions as well. It provides two properties top and left which can be used as

$wschart.shapes.item("Chart 1").top = 0
$wschart.shapes.item("Chart 1").left = 0

What about chart types? In previous post, we didn’t provide any chart type and by default the bar chart was rendered. We can provide the chart type based on our requirement. We will set the second chart as Line chart and we will discuss it in bit more details in next post.

$secondChart.chartType = 4

So let’s the complete code

$xl = new-object -ComObject Excel.Application	
$fileName = 'D:\TestP\DataforCharts.xlsx'
$wb = $xl.Workbooks.Open($fileName)	
$wsData = $wb.WorkSheets.item(1) 

# Activating the Data sheet
$wsData.activate() 

# Selecting the source data - We cn select the first cell with Range and select CurrentRegion 
# which selects the entire table
$DataforFirstChart = $wsData.Range("A1").CurrentRegion
$DataforSecondChart = $wsData.Range("A11").CurrentRegion

# Adding the Charts
$firstChart = $wsData.Shapes.AddChart().Chart
$secondChart = $wsData.Shapes.AddChart().Chart

# Providing the chart type - Line chart
$secondChart.chartType = 4

# Providing the source data
$firstChart.SetSourceData($DataforFirstChart)
$secondChart.SetSourceData($DataforSecondChart)

# Set it true if want to have chart Title
$firstChart.HasTitle = $true
$secondChart.HasTitle = $true

# Providing the Title for the chart
$firstChart.ChartTitle.Text = "Domain controller's usage- Bar Chart"
$secondChart.ChartTitle.Text = "Events- Line Chart"

# Setting up the position of chart (Not required if the sheet has just one chart). 
# It will create the chart at top left corner
$wsData.shapes.item("Chart 1").top = 0
$wsData.shapes.item("Chart 1").left = 350

$wsData.shapes.item("Chart 2").top = 250
$wsData.shapes.item("Chart 2").left = 350

# Saving the sheet
$wb.Save();

# Closing the work book and xl
$wb.close()	
$xl.Quit()
# Releasting the xl object
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)

Above code is self explanatory, we provided different positions for the both charts. Now it’s time to run the script and see the output.

Awesome, so we can see two charts one is Bar and another line chart as expected. In the next post, we will add some more features to it and see more options.

Cheers,
Brij