PowerShell Out-ExcelPivotTable – Quickly Create Pivot Tables in Microsoft Excel

** Updated Code 06/03/2010 **

Bradley requested two features. How to save the pivot table to a file and close excel.

Download the new scripts below and here is how to use it.

$data | .\Out-ExcelPivotTable -saveAs "$pwd\data.xls" -autoCloseXl

Thanks for the feature request Bradley.

The Data – People.csv

Use Import-Csv and type the data with Select

 

Create the Excel Pivot Table

$people | .\Out-ExcelPivotTable

 

The easiest way to get a Pivot Table.

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

 

 

$people | .\Out-ExcelPivotTable name dept salary

 

Same data.

You control what goes in the different pivot fields. Row (name), Column (dept) and Data (salary).

 

 

 

$people | .\Out-ExcelPivotTable -values YearsEmployeed

 

You control the pivot data field with the –values parameter letting  Out-ExcelPivotTable do the rest.

Name is nested in Department.

 

 

 

 

ToDo

• Set up Out-ExcelPivotTable parameters to take arrays
• Formatting of value fields
• Naming of Row and Column Labels
• Enable Types of aggregates for Value field
• Improve data layout performance

I have only tried this on the latest version of Excel.

Download It