** 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.
{ 1 trackback }
{ 6 comments… read them below or add one }
Pretty Schweet !!!
Another gem !
Wonderful! But make sure you’re on Powershell 2.0 everyone.
I hope someone sees this… how can i make it automatically save the pivot table to a file and close excel.
Excellent Tutorial. Hats off to you..
Great Script! Another useful tip is formatting the data type-ing it:
$events = Import-Csv .\events.csv |
select @{
n=”Date”
e={“{0:dd MMM}” -f [DateTime]$_.Date}
}, @{
n=”Start”
e={“{0:HH:mm}” -f [DateTime]$_.Start}
}, @{
n=”Minutes”
e={“{0:N0}” -f [Double]$_.Minutes}
}
Question – is there a similar COM call to make a pivot chart from this? Couldn’t find anything obvious (was hoping for CreatePivotChart()…