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 :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
####################################################################### #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) |
OUTPUT
Thats nice, could you suggest the command to add data labels to it.