As system admins, we are used to digging through heaps of searching, selecting, sorting, filtering, until we got what we were looking for. My point is, while you might appreciate nicely presented, If we are providing the technical details in paragraph format will usually get bored and things they don’t understand, this what we will do at most of the time. Most of the management peoples like simple and colorful representation like charts. Basically it will be easily understandable without reading long texts.
Below 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. Before executing the script, we need the data in a tabular format which is to be used to create a chart in the Excel. Let you create the excel sheet similar like below before we moving to the scripting part.
First let you Open the excel and add a chart details in the sheet as shown below. If required Set the Title and the Save the excel. Finally close the excel.
Full Code :
#######################################################################
#Project : How to create Basic Chart by reading excel using PowerShell
#Developer : Thiyagu S (dotnet-helpers.com)
#Tools : PowerShell 5.1.15063.1155
#E-Mail : mail2thiyaguji@gmail.com
######################################################################
# Creating excel com object
$xlChart=[Microsoft.Office.Interop.Excel.XLChartType]
#You can use New-Object to work with Component Object Model (COM) components.
#To create a COM object, you need to specify the ComObject parameter with the Programmatic Identifier of the COM class that you want to use
$excelObj = New-object -ComObject Excel.Application
#Assign the the file path of the Excel
$fileName = 'C:\dotnet-helpers\TutorialDetails.xlsx'
#Open Excel
$workbook = $excelObj.Workbooks.Open($fileName)
#Open the first sheet of the excel
$worksheetChart = $workbook.WorkSheets.item(1)
#Makes the current sheet the active sheet.
$worksheetChart.activate()
# Adding the Chart
$basicchart = $worksheetChart.Shapes.AddChart().Chart
# Set it true if you want to have chart Title
$basicchart.HasTitle = $true
# Assigning the Title for the chart
$basicchart.ChartTitle.Text = "Tutorial Views Report"
# Save the sheet
$workbook.Save()
# Closing the work book and ComObject
$workbook.close()
$excelObj.Quit()
# Releasting the excel com object
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excelObj)
As system admins, we are used to digging through heaps of searching, selecting, sorting, filtering, until we got what we were looking for. My point is, while you might appreciate nicely presented, If we are providing the technical details in paragraph format will usually get bored and things they don’t understand, this what we will do most of the time. Most of the management peoples like simple and colorful representations like charts. Basically, it will be easily understandable without reading long texts.
Microsoft Chart Controls (MCCs) is a great tool to show up reports and you can create a wide range of different chart types, with a custom design. You can display your chart in a GUI (e.g. Windows Forms) or save it as a graphics file to include it in an HTML report or email. A standard chart created with MCCs basically consists of three elements that are Chart object, a ChartArea object, and one or more data Series.
From MSDN, Two important properties of the Chart class are the Series and ChartAreas properties, both of which are collection properties. The Series collection property stores Series objects, which are used to store data that is to be displayed, along with attributes of that data. The ChartAreas collection property stores ChartArea objects, which are primarily used to draw one or more charts using one set of axes.
How to create a simple Memory Usage Chart and save it in your local drive: In this post, we will create a chart that shows your pc’s top 5 processes by memory usage and save it as a *.png-file.
STEP #1
Let you loading the necessary assembly and determine the path whether the PNG need to save.
In this example we won’t display this one in a GUI so you only need to set a few properties like black color and size. Here we going to create instance of the .NET-object we’ve just created and named “$chartobject” and set the Width, Height, back color. The System.Windows.Forms.DataVisualization.Charting namespace contains methods and properties for the Chart Windows forms control.
Next, you need to add a title to the chart. A Chart object can have multiple titles (e.g. a title and a subtitle, or a title for each ChartArea attached to this Chart object), so we create it by adding it with title collection.
# Set Chart title [void]$chartobject.Titles.Add(“dotnet-helpers chart-Memory Usage”) $chartobject.Titles[0].Font = “Arial,13pt” $chartobject.Titles[0].Alignment = “topLeft”
STEP #4
As mentioned before, a Chart object can have multiple ChartArea objects. Per default, they will automatically share the available space of the Chart object (determined by the Chart object’s height and width properties). But you can also customize the location and size of each ChartArea by setting the “$ChartArea.Position.Auto” – property to $false and set size and position yourself
# create a chartarea to draw on and add to chart $chartareaobject = New-Object System.Windows.Forms.DataVisualization.Charting.ChartArea $chartareaobject.Name = “ChartArea1” $chartareaobject.AxisY.Title = “dotnet-helpers chart – Memory(MB)” $chartareaobject.AxisX.Title = “dotnet-helpers chart – Process Name” $chartareaobject.AxisY.Interval = 100 $chartareaobject.AxisX.Interval = 1 $chartobject.ChartAreas.Add($chartareaobject)
STEP #5
Typically, if you want to add a legend along with a chart,I will avoid having anything on the actual chart itself and leave the description for each piece to be in the legend. Because we’ll have two data Series in the same ChartArea and it’s probably a good idea to be represent in the legend way.
# Creating legend for the chart $chartlegend = New-Object system.Windows.Forms.DataVisualization.Charting.Legend $legend.name = “Legend1” $chartobject.Legends.Add($legend)
STEP #6
Here we getting the using process details by get-process cmdlet, sort by private memory size and take the top five entries of the list.
# Getting the top 5 CPU utilzation process details $topCPUUtilization = Get-Process | sort PrivateMemorySize -Descending | Select-Object -First 5
STEP #7
Finally, we need to set the data series to chart object. We create it by setting a name for the Series and adding it directly to the chart’s Series collection. For this example, i had set the chart type to column. When choosing the type, there is one important thing to consider: You know already that a ChartArea can have multiple Series.
To provide the best experiences, we use technologies like cookies to store and/or access device information. Consenting to these technologies will allow us to process data such as browsing behavior or unique IDs on this site. Not consenting or withdrawing consent, may adversely affect certain features and functions.
