Designing and coding

Menu

How to create Excel Chart using PowerShell – Part 1

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

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.