Issue while creating a stock like excel chart using PowerShell

Posted on October 4, 2016 by Brij

Although this post is not in continuation of my previous posts on Excel charts and PowerShell but discusses a specific issue while creating Stock like chart. As topics are same I will advice you to go through the previous posts as I will be referring few things from that. The links of my previous posts are below

So to create a basic stock like chart we need to provide the data is a specific format.
b4-1

So lets write the script for the same

$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
$DataforChart = $wsData.Range("A1").CurrentRegion

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

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

# Providing the chart types
$stockChart.ChartType = $xlChart::xlStockHLC

#Providing the source data
$stockChart.SetSourceData($DataforChart)

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

# Providing the Title for the chart
$stockChart.ChartTitle.Text = "Stock like 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

# 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)

So the above code looks fine as similar to our previous posts but when we run it throws an exception as
exceptionb4

It does not show any specific details for this exception and the error code also does not lead to any details but we do get default bar chart.

So it a look a little while to find the cause as it was not a common as for other charts it was working very smoothly.

For stock charts, it expects the data before providing chart types so if we change the below lines


# Providing the chart types
$stockChart.ChartType = $xlChart::xlStockHLC

#Providing the source data
$stockChart.SetSourceData($DataforChart)


# Providing the source data
$stockChart.SetSourceData($DataforChart)

# Providing the chart types
$stockChart.ChartType = $xlChart::xlStockHLC

Its starts running. Let’s see a quick look on resultant chart
stock-chart

So it was very peculiar as the resolution does not seem to have any logic but next time you face similar issue, it may be helpful.

Cheers,
Brij

How to create Excel Chart using PowerShell – Part 3

Posted on September 27, 2016 by Brij

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

How to create Excel Chart using PowerShell – Part 1

Posted on September 20, 2016 by Brij

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

chartExcel

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.

barchart

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