DevelopmentInformation Technology

Creating an Excel Pivot Table Using PowerShell

Doug Finke has a new post about a PowerShell script he wrote to create an Excel Pivot Table.

Out-ExcelPivotTable inspects the piped data, sets up the numeric properties as data fields and string properties as row fields (nested).

Doug includes screenshot of the output, in case you are unfamiliar with Pivot Tables. The actual script he built can bedownloaded here.

Sample Usage:

Function New-Person ($dept, $name, [double]$salary, [int]$yearsEmployeed) { 
    New-Object PSObject  | 
        Add-Member -PassThru Noteproperty Dept $dept | 
        Add-Member -PassThru Noteproperty Name $name | 
        Add-Member -PassThru Noteproperty Salary $salary | 
        Add-Member -PassThru Noteproperty YearsEmployeed $yearsEmployeed  
} 
 
$( 
    New-Person IT Doug 100 10 
    New-Person IT John 200 5 
    New-Person IT Tom 300 6  
    New-Person IT Dick 400 7 
 
 
    New-Person Sales Jane 1100 8  
    New-Person Sales Tina 1200 9 
    New-Person Sales Tammy 1300 11 
    New-Person Sales Dawn 1400 12 
 
) | .\Out-ExcelPivotTable