In the previous post I showed how you could export data to an XLSx file using the Export-Excel command in Doug Finke’s ImportExcel module (Install it from the PowerShell gallery!). The command supports the creation of Pivot tables and Pivot charts. Picking up from where part 2 left off, I can get data about running processes, export them to a worksheet and then set up a pivot table
$mydata = Get-Process | Select-Object -Property Name, WS, CPU, Description, Company, StartTime
$mydata | Export-Excel -Path .\demo.xlsx -KillExcel -WorkSheetname "Processes" -ClearSheet `
-IncludePivotTable -PivotRows "Company" -PivotData @{"WS"="Sum"} -show
To add a pivot chart the command line becomes $mydata | Export-Excel -Path .\demo.xlsx -KillExcel -WorkSheetname "Processes" -ClearSheet `
-IncludePivotTable -PivotRows "Company" -PivotData @{"WS"="Sum"} `
-IncludePivotChart -ChartType Pie -ShowPercent -show
The chart types are also suggested by intellisense, note that some of them don’t support -ShowPercent or ‑ShowCategory options and “bad” combinations will result in an error on opening Excel. Re-creating existing Pivot charts can cause an error as well.
There is an alternative way of creating Pivot tables and charts –which is particularly useful when we want more than one in the same workbook
del .\demo.xlsx
$xl = $mydata | Export-Excel -Path .\demo.xlsx -WorkSheetname "Processes" -PassThru
$Pt1 = New-PivotTableDefinition -PivotTableName "WS" -PivotData @{"WS" ="Sum"} -SourceWorkSheet "Processes" `
-PivotRows Company -IncludePivotChart -ChartType ColumnClustered -NoLegend
$Pt2 = New-PivotTableDefinition -PivotTableName "CPU" -PivotData @{"CPU"="Sum"} -SourceWorkSheet "Processes" `
-PivotRows Company -IncludePivotChart -ChartType ColumnClustered -NoLegend
$xl = Export-Excel -ExcelPackage $xl -WorkSheetname "Processes" -PivotTableDefinition $Pt1 -PassThru
Export-Excel -ExcelPackage $xl -WorkSheetname "Processes" -PivotTableDefinition $Pt2 -Show
New-PivotTableDefinition builds the table definition as a hash table – we could equally well write a large hash table with multiple pivots defined in it, like this
del .\demo.xlsx
$mydata | Export-Excel -Path .\demo.xlsx -WorkSheetname "Processes" -Show -PivotTableDefinition @{
"WS" = @{"SourceWorkSheet" = "Processes" ;
"PivotRows" = "Company" ;
"PivotData" = @{"WS"="Sum"} ;
"IncludePivotChart" = $true ;
"ChartType" = "ColumnClustered";
"NoLegend" = $true};
"CPU" = @{"SourceWorkSheet" = "Processes" ;
"PivotRows" = "Company" ;
"PivotData" = @{"CPU"="Sum"} ;
"IncludePivotChart" = $true ;
"ChartType" = "ColumnClustered";
"NoLegend" = $true }
}
Export-Excel allows [non-pivot] charts to be defined and passed as a parameter in similar same way -in the following example we’re going to query a database for a list of the most successful racing drivers, the SQL for the query looks like this: $Sql = "SELECT TOP 25 WinningDriver, Count(RaceDate) AS Wins
FROM races
GROUP BY WinningDriver
ORDER BY count(raceDate) DESC"
Then we define the chart and feed the result of the query into Export-Excel (I’m using my GetSQL module from the PowerShell Gallery for this but there are multiple ways ) $chartDef = New-ExcelChart -Title "Race Wins" -ChartType ColumnClustered
-XRange WinningDriver -YRange Wins -Width 1500 -NoLegend -Column 3
Get-SQL $Sql | Select-Object -property winningDriver, Wins |
Export-Excel -path .\demo2.xlsx -AutoSize -AutoNameRange -ExcelChartDefinition $chartDef -Show
The important thing here is that the chart definition refers to named ranges in the spreadsheet – “Winning Driver” and “Wins” and the Export-Excel command is run with –AutoNameRanges so the first column of is a range named “Winning Driver” and the second “Wins” – you can see in the screen shot “Wins” has been selected in the “Name” box (underneath the File menu) and the data in the Wins column is selected. The chart doesn’t need a legend and positioned the right of column 3
I found that he EEPLUS object which Doug uses can insert a Data table object directly into a worksheet, which should be more efficient, and it also saves using a select-object command to remove the database housekeeping properties which are in every row of data as I had to do in the example above. It didn’t take much to a command to Doug’s module to put SQL data into a spreadsheet without having to pipe the data into Export-Excel from another command. And I cheated by passing the resulting object through to Export-Excel so that I could use parameters found in Export-Excel and pass the Worksheet object and parameters on and get Export-Excel to finish the job so I write something like this: Send-SQLDataToExcel -SQL $sql -Session $session -path .\demo2.xlsx -WorkSheetname "Winners" `
-AutoSize -AutoNameRange -ExcelChartDefinition $chartDef -Show
In this example I use an existing session with a database – the online help shows you how to use different connection strings with ODBC or the SQL Server native client.
I also added commands to set values along a row or down a column – for an example we can expand the racing data to not just how many wins, but also how many fastest laps and how many pole positions, export this data and use the -Passthrough switch to get an Excel Package object back $SQL = "SELECT top 25 DriverName, Count(RaceDate) as Races ,
Count(Win) as Wins, Count(Pole) as Poles,
Count(FastestLap) as Fastlaps
FROM Results
GROUP BY DriverName
ORDER BY (count(win)) desc"
$Excel = Send-SQLDataToExcel -SQL $sql -Session $session -path .\demo3.xlsx `
-WorkSheetname "Winners" -AutoSize -AutoNameRange -Passthru
Having done this, we can add columns to calculate the ratios of two pairs of existing columns
$ws = $Excel.Workbook.Worksheets["Winners"]
Set-Row -Worksheet $ws -Heading "Average" -Value {"=Average($columnName`2:$columnName$endrow)"} `
-NumberFormat "0.0" -Bold
Set-Column -Worksheet $ws -Heading "WinsToPoles" -Value {"=D$row/C$row"} -Column 6 -AutoSize -AutoNameRange
Set-Column -Worksheet $ws -Heading "WinsToFast" -Value {"=E$row/C$row"} -Column 7 -AutoSize -AutoNameRange
Set-Format -WorkSheet $ws -Range "F2:G50" -NumberFormat "0.0%"
In the examples above the value parameter is a Script block, when this is evaluated $row and $column are available so if the value is being inserted in row 5, {"=E$row/C$row"} becomes =E5/C5
The script block can use $row , $column (current row and column numbers) $columnName (current column letter), $StartRow/$EndRow $StartColumn/$EndColumn (column and row numbers)
If the value begins with “=” it is treated as a formula rather than a value; we don’t normally want to put in a fixed formula – without the “=” the value inserted down the column or across the row will be a constant
The set-Column command supports range naming, and both commands support formatting – or we can use the ‑PassThru switch and pipe the results of setting the column into Set-Format. There seems to be a bug in the underlying library where applying number formatting to column after formatting a row applies the same formatting to the column and to the row from the previous operation. So, the example above uses a third way to apply the format which is to specify the range of cells in Set-Format.
Finally we can output this data, and make use of the names given to the newly added columns in a new chart.
$chart = New-ExcelChart -NoLegend -ChartType XYScatter -XRange WinsToFast -YRange WinsToPoles `
-Column 7 -Width 2000 -Height 700
Export-Excel -ExcelPackage $Excel -WorkSheetname "Winners"-Show -AutoSize -AutoNameRange `
-ExcelChartDefinition $chart
So there you have it; PowerShell objects or SQL data goes in – possibly over multiple sheets; headings and filters get added, panes arranged: extra calculated rows and columns are inserted , and formatting applied, pivot tables and charts inserted – and if Excel itself is available you can export them. No doubt someone will ask before too long if I get the the charts out of Excel and into PowerPoint slides ready for a management meeting … And since the all of this only works with XLSX files, not legacy XLS ones there might me another post soon about reading those files.
James O'Neill's Blog 