Cmdlet

Add-OfficeExcelPivotTable

Aliases: ExcelPivotTable

Module: PSWriteOffice
Aliases: ExcelPivotTable
Inputs: OfficeIMO.Excel.ExcelDocument
Source: AddOfficeExcelPivotTableCommand.cs:32 Edit on GitHub

Adds a pivot table to a worksheet.

Syntax

Add-OfficeExcelPivotTable
[-ColumnField <String[]>]
[-ColumnHeaderCaption <String>]
[-CustomListSort]
[-DataDisplayName <String[]>]
[-DataField <String[]>]
[-DataFunction <ExcelPivotDataFunction[]>]
[-DataNumberFormat <String[]>]
[-DataOnColumns]
[-DataOnRows]
-DestinationCell <String>
[-DisableDrill]
[-EnableDrill]
[-ErrorCaption <String>]
[-FieldCompact <String[]>]
[-FieldHiddenItems <Hashtable>]
[-FieldHideDropDowns <String[]>]
[-FieldInsertBlankRow <String[]>]
[-FieldInsertPageBreak <String[]>]
[-FieldListSortAscending]
[-FieldListSortDescending]
[-FieldNoDefaultSubtotal <String[]>]
[-FieldOutline <String[]>]
[-FieldSort <Hashtable>]
[-FieldSubtotalTop <String[]>]
[-FieldVisibleItems <Hashtable>]
[-GrandTotalCaption <String>]
[-HideDataDropDown]
[-HideDataTips]
[-HideDrill]
[-HideDropZones]
[-HideEmptyColumns]
[-HideEmptyRows]
[-HideHeaders]
[-HideMemberPropertyTips]
[-Layout <Compact|Outline|Tabular>]
[-MissingCaption <String>]
[-Name <String>]
[-NoColumnGrandTotals]
[-NoCustomListSort]
[-NoPreserveFormatting]
[-NoRefreshOnOpen]
[-NoRowGrandTotals]
[-NoSaveSourceData]
[-PageField <String[]>]
[-PageFieldSelection <Hashtable>]
[-PassThru]
[-PivotStyle <String>]
[-PreserveFormatting]
[-RefreshOnOpen]
[-RowField <String[]>]
[-RowHeaderCaption <String>]
[-SaveSourceData]
[-ShowDataDropDown]
[-ShowDataTips]
[-ShowDrill]
[-ShowDropZones]
[-ShowEmptyColumns]
[-ShowEmptyRows]
[-ShowHeaders]
[-ShowMemberPropertyTips]
-SourceRange <String>
[<CommonParameters>]

Parameter set: Context

Add-OfficeExcelPivotTable
[-ColumnField <String[]>]
[-ColumnHeaderCaption <String>]
[-CustomListSort]
[-DataDisplayName <String[]>]
[-DataField <String[]>]
[-DataFunction <ExcelPivotDataFunction[]>]
[-DataNumberFormat <String[]>]
[-DataOnColumns]
[-DataOnRows]
-DestinationCell <String>
[-DisableDrill]
-Document <ExcelDocument>
[-EnableDrill]
[-ErrorCaption <String>]
[-FieldCompact <String[]>]
[-FieldHiddenItems <Hashtable>]
[-FieldHideDropDowns <String[]>]
[-FieldInsertBlankRow <String[]>]
[-FieldInsertPageBreak <String[]>]
[-FieldListSortAscending]
[-FieldListSortDescending]
[-FieldNoDefaultSubtotal <String[]>]
[-FieldOutline <String[]>]
[-FieldSort <Hashtable>]
[-FieldSubtotalTop <String[]>]
[-FieldVisibleItems <Hashtable>]
[-GrandTotalCaption <String>]
[-HideDataDropDown]
[-HideDataTips]
[-HideDrill]
[-HideDropZones]
[-HideEmptyColumns]
[-HideEmptyRows]
[-HideHeaders]
[-HideMemberPropertyTips]
[-Layout <Compact|Outline|Tabular>]
[-MissingCaption <String>]
[-Name <String>]
[-NoColumnGrandTotals]
[-NoCustomListSort]
[-NoPreserveFormatting]
[-NoRefreshOnOpen]
[-NoRowGrandTotals]
[-NoSaveSourceData]
[-PageField <String[]>]
[-PageFieldSelection <Hashtable>]
[-PassThru]
[-PivotStyle <String>]
[-PreserveFormatting]
[-RefreshOnOpen]
[-RowField <String[]>]
[-RowHeaderCaption <String>]
[-SaveSourceData]
[-Sheet <String>]
[-SheetIndex <Int32>]
[-ShowDataDropDown]
[-ShowDataTips]
[-ShowDrill]
[-ShowDropZones]
[-ShowEmptyColumns]
[-ShowEmptyRows]
[-ShowHeaders]
[-ShowMemberPropertyTips]
-SourceRange <String>
[<CommonParameters>]

Parameter set: Document

Examples

Create a pivot table from a report data sheet.

$rows = @(
    [pscustomobject]@{ Region = 'North America'; Product = 'Standard'; Sales = 125000 }
    [pscustomobject]@{ Region = 'EMEA'; Product = 'Standard'; Sales = 98000 }
    [pscustomobject]@{ Region = 'APAC'; Product = 'Premium'; Sales = 143000 }
)
New-OfficeExcel -Path .\SalesPivot.xlsx {
    Add-OfficeExcelSheet -Name Data {
        Add-OfficeExcelTable -InputObject $rows -TableName Sales -AutoFit
        Add-OfficeExcelPivotTable -SourceRange 'A1:C4' -DestinationCell 'E2' -Name 'SalesByRegion' -RowField Region -ColumnField Product -DataField Sales -DataFunction Sum -PivotStyle PivotStyleMedium9
    }
}

Writes source rows to a worksheet and creates a pivot table using the existing OfficeIMO pivot support.

Parameters

-ColumnField String[] optional: Column fields (header names).
-ColumnHeaderCaption String optional: Optional column header caption.
-CustomListSort SwitchParameter optional: Use Excel custom-list sorting.
-DataDisplayName String[] optional: Display names for data fields.
-DataField String[] optional: Data fields (header names). Defaults to the last column when omitted.
-DataFunction ExcelPivotDataFunction[] optional: Aggregation functions (Sum, Count, Average, etc.).; Possible values: Average, Count, CountNumbers, Maximum, Minimum, Product, StandardDeviation, StandardDeviationP, Sum, Variance, VarianceP
-DataNumberFormat String[] optional: Number format codes for data fields.
-DataOnColumns SwitchParameter optional: Show data fields on columns.
-DataOnRows SwitchParameter optional: Show data fields on rows.
-DestinationCell String required: Top-left destination cell for the pivot table (e.g., "F2").
-DisableDrill SwitchParameter optional: Disable pivot detail drill interaction in Excel.
-EnableDrill SwitchParameter optional: Allow users to drill into pivot details in Excel.
-ErrorCaption String optional: Optional error-value caption.
-FieldCompact String[] optional: Fields using compact field layout.
-FieldHiddenItems Hashtable optional: Field item captions to hide, for example @{ Region = @('Legacy') }.
-FieldHideDropDowns String[] optional: Fields whose filter drop-downs should be hidden.
-FieldInsertBlankRow String[] optional: Fields that insert blank rows after items.
-FieldInsertPageBreak String[] optional: Fields that insert page breaks after items.
-FieldListSortAscending SwitchParameter optional: Sort pivot field list ascending.
-FieldListSortDescending SwitchParameter optional: Sort pivot field list descending.
-FieldNoDefaultSubtotal String[] optional: Fields with default subtotal disabled.
-FieldOutline String[] optional: Fields using outline field layout.
-FieldSort Hashtable optional: Field sort map, for example @{ Region = 'Ascending' }.
-FieldSubtotalTop String[] optional: Fields with subtotals shown at the top.
-FieldVisibleItems Hashtable optional: Field item captions to keep visible, hiding other known items.
-GrandTotalCaption String optional: Optional grand total caption.
-HideDataDropDown SwitchParameter optional: Hide the data drop-down.
-HideDataTips SwitchParameter optional: Hide pivot data tips.
-HideDrill SwitchParameter optional: Hide drill indicators.
-HideDropZones SwitchParameter optional: Hide pivot drop zones.
-HideEmptyColumns SwitchParameter optional: Hide empty columns.
-HideEmptyRows SwitchParameter optional: Hide empty rows.
-HideHeaders SwitchParameter optional: Hide field headers.
-HideMemberPropertyTips SwitchParameter optional: Hide member property tips.
-Layout ExcelPivotLayout optional: Pivot layout (Compact, Outline, Tabular).; Possible values: Compact, Outline, Tabular
-MissingCaption String optional: Optional missing-value caption.
-Name String optional: Optional pivot table name.
-NoColumnGrandTotals SwitchParameter optional: Disable column grand totals.
-NoCustomListSort SwitchParameter optional: Disable Excel custom-list sorting.
-NoPreserveFormatting SwitchParameter optional: Do not preserve pivot formatting when Excel refreshes the pivot table.
-NoRefreshOnOpen SwitchParameter optional: Do not refresh the pivot cache when the workbook opens.
-NoRowGrandTotals SwitchParameter optional: Disable row grand totals.
-NoSaveSourceData SwitchParameter optional: Do not save pivot source cache records in the workbook package.
-PageField String[] optional: Page fields (header names) used as filters.
-PageFieldSelection Hashtable optional: Selected page-field item captions, for example @{ Product = 'Standard' }.
-PassThru SwitchParameter optional: Emit the worksheet after creating the pivot table.
-PivotStyle String optional: Optional pivot table style name.
-PreserveFormatting SwitchParameter optional: Preserve pivot formatting when Excel refreshes the pivot table.
-RefreshOnOpen SwitchParameter optional: Refresh the pivot cache when the workbook opens.
-RowField String[] optional: Row fields (header names).
-RowHeaderCaption String optional: Optional row header caption.
-SaveSourceData SwitchParameter optional: Save pivot source cache records in the workbook package.
-ShowDataDropDown SwitchParameter optional: Show the data drop-down.
-ShowDataTips SwitchParameter optional: Show pivot data tips.
-ShowDrill SwitchParameter optional: Show drill indicators.
-ShowDropZones SwitchParameter optional: Show pivot drop zones.
-ShowEmptyColumns SwitchParameter optional: Show empty columns.
-ShowEmptyRows SwitchParameter optional: Show empty rows.
-ShowHeaders SwitchParameter optional: Show field headers.
-ShowMemberPropertyTips SwitchParameter optional: Show member property tips.
-SourceRange String required: Source data range including header row (e.g., "A1:D200").
-Document ExcelDocument requiredpipeline: True (ByValue)sets: Document: Workbook to operate on outside the DSL context.
-Sheet String optionalsets: Document: Worksheet name when using Document.
-SheetIndex Int32 optionalsets: Document: Worksheet index (0-based) when using Document.

Common Parameters

This command supports the common parameters: -Debug, -ErrorAction, -ErrorVariable, -InformationAction, -InformationVariable, -OutVariable, -OutBuffer, -PipelineVariable, -Verbose, -WarningAction, and -WarningVariable.

For more information, see about_CommonParameters.