Recently, I had to automate a complete test suite for a very complex system which uses lot of internal and external tools and generate different kind of reports. PowerShell is a primary tool for writing the automation scripts so we started using it. I found it very powerful although any nice editor (free) or proper documentation, best practices etc not available. So I explored many things with my team and I will be sharing few things here. In the coming couple of posts, I will discuss about creating excel charts using PowerShell  and in this post we will start with creating a basic chart.

To create a chart, we need the data in a tabular format which is to be used to create a chart. Lets understand what all basic steps need to be followed for creating the chart.

• Open the excel and correct sheet .
• Add a new sheet (Optional)
• Provide the name to sheet (Optional)
• Add a chart in the sheet
• Set the Title if required
• Save the excel
• Releasing the com objects

Now let’s go straight to the example. Say we have a excel with data like this

Now its time to see the real script. Refer inline comments for detail

# Creating excel com object
$xl = new-object -ComObject Excel.Application   
$fileName = <path of the excel file>
$wb = $xl.Workbooks.Open($fileName)
#Open the first sheet of the excel 
$wsChart = $wb.WorkSheets.item(1) 

# Activating the Data sheet
$wsChart.activate() 

# Adding the Chart
$chart = $wsChart.Shapes.AddChart().Chart

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

# Providing the Title for the chart
$chart.ChartTitle.Text = "Domain controller's usage"

# Save the sheet
$wb.Save()  

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

Now its time to run the script and see the chart.

Looking perfect.

Note – You may not be able to run the script as it may show the script execution permission denied. For that you need to run the following command (Details here).

Set-ExecutionPolicy Unrestricted

Above code is pretty basic and there are couple of things which is taken by default like Chart Type (which is Bar chart), Data range used for chart (Since single data set is present), position of the chart etc.

In the next post, we will take a step further and use more APIs.

Cheers,
Brij