How to create Excel Chart using PowerShell – Part 3

This post is in continuation of my previous two posts on creating excel chart using PowerShell and in the first post we created a basic chart then in the next post we we added some more features to our chart and added two charts. The link of the previous posts are given below

Today we will see some more useful features.

Let’s first discuss the chart types. In our previous post, we created two charts in which first one was Bar chart which was default and second one was line where we provided the chart type as 4 which turned into Line chart. The better way to use the chart enums for that. To use that first we need to get the chart type as

$xlChart=[Microsoft.Office.Interop.Excel.XLChartType]

Now we can assign the chart type as

$chart.ChartType=$xlChart::xlBarClustered

One key difference here is property name casing, when we provide the number it was chartType and now ChartType which takes enum as above.

Note- It may not work for you as is as show that it is not able to load/recognize Microsoft.Office.Interop.Excel.XLChartType so you need to add the type as

<em>Add-Type -AssemblyName Microsoft.Office.Interop.Excel</em>
/sourcecode]
In our example, I have chart types as

$firstChart.ChartType = $xlChart::xlBarClustered
$secondChart.ChartType = $xlChart::xlLine
$thirdChart.ChartType = $xlChart::xlAreaStacked

It looks more professional. To know the complete list of chart types click here

In this post, we will create three different charts and put it in a new sheet. Adding a sheet in the excel is pretty simple and can be added as

$wsChart = $wb.Sheets.Add();
$wsChart.Name = "Charts"

In the second line, I have provided the sheet name.

Now we will be adding charts in this new sheet as

$firstChart = $wsChart.Shapes.AddChart().Chart
$secondChart = $wsChart.Shapes.AddChart().Chart
$thirdChart = $wsChart.Shapes.AddChart().Chart

So lets just see the data in our sheet

dataforchart-3

Now let’s run script and see the charts

threecharts

So lets see the complete script

$xlChart=[Microsoft.Office.Interop.Excel.XLChartType]

$xl = new-object -ComObject Excel.Application   
$fileName = ''
$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 theenire table
$DataforFirstChart = $wsData.Range("A1").CurrentRegion
$DataforSecondChart = $wsData.Range("A11").CurrentRegion
$DataforThirdChart = $wsData.Range("A21").CurrentRegion

# Adding a new sheet where the chart would be created
$wsChart = $wb.Sheets.Add();
$wsChart.Name = "Charts"

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

# Providing the chart types
$firstChart.ChartType = $xlChart::xlBarClustered
$secondChart.ChartType = $xlChart::xlLine
$thirdChart.ChartType = $xlChart::xlAreaStacked

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

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

# Providing the Title for the chart
$firstChart.ChartTitle.Text = "Domain controller's usage- Bar Chart"
$secondChart.ChartTitle.Text = "Events- Line Chart"
$thirdChart.ChartTitle.Text = "Events Daily- Stacked Area 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
$wsChart.shapes.item("Chart 1").top = 0
$wsChart.shapes.item("Chart 1").left = 0

$wsChart.shapes.item("Chart 2").top = 250
$wsChart.shapes.item("Chart 2").left = 0

$wsChart.shapes.item("Chart 3").top = 500
$wsChart.shapes.item("Chart 3").left = 0


# Saving the sheet
$wb.Save();

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

Above script is self explanatory. We provided different positions (top and left) for each chart so that it gets located at properly on sheet.There are more customizations available which we can use based on our need.

Cheers,
Brij