Tag Archives: PowerShell

Issue while creating a stock like excel chart using PowerShell

Posted on by

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)

to


# 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

Advertisement

How to create Excel Chart using PowerShell – Part 3

Posted on by

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 2

Posted on by

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

MultipleSetofData

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.

multichart-b2

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

How to create Excel Chart using PowerShell – Part 1

Posted on by

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