OfficeIMO

API Reference

Command

Add-OfficeExcelPivotTable

Namespace PSWriteOffice
Inputs
OfficeIMO.Excel.ExcelDocument
Outputs
System.Object

Adds a pivot table to a worksheet.

Remarks

Adds a pivot table to a worksheet.

Examples

Create a basic pivot table.


PS>Add-OfficeExcelPivotTable -SourceRange 'A1:D200' -DestinationCell 'F2' -RowField 'Region' -DataField 'Sales'

Creates a pivot table in F2 using Region as rows and Sales as the data field.

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.

Syntax

Add-OfficeExcelPivotTable [-ColumnField <String[]>] [-DataField <String[]>] [-DataFunction <String[]>] [-DataOnColumns] [-DataOnRows] -DestinationCell <String> [-HideDrill] [-HideEmptyColumns] [-HideEmptyRows] [-HideHeaders] [-Layout <String>] [-Name <String>] [-NoColumnGrandTotals] [-NoRowGrandTotals] [-PageField <String[]>] [-PassThru] [-PivotStyle <String>] [-RowField <String[]>] [-ShowDrill] [-ShowEmptyColumns] [-ShowEmptyRows] [-ShowHeaders] -SourceRange <String> [<CommonParameters>]
#
Parameter set: Context

Parameters

ColumnField String[] optionalposition: namedpipeline: False
Column fields (header names).
DataField String[] optionalposition: namedpipeline: False
Data fields (header names). Defaults to the last column when omitted.
DataFunction String[] optionalposition: namedpipeline: False
Aggregation functions (Sum, Count, Average, etc.).
DataOnColumns SwitchParameter optionalposition: namedpipeline: False
Show data fields on columns.
DataOnRows SwitchParameter optionalposition: namedpipeline: False
Show data fields on rows.
DestinationCell String requiredposition: namedpipeline: False
Top-left destination cell for the pivot table (e.g., "F2").
HideDrill SwitchParameter optionalposition: namedpipeline: False
Hide drill indicators.
HideEmptyColumns SwitchParameter optionalposition: namedpipeline: False
Hide empty columns.
HideEmptyRows SwitchParameter optionalposition: namedpipeline: False
Hide empty rows.
HideHeaders SwitchParameter optionalposition: namedpipeline: False
Hide field headers.
Layout String optionalposition: namedpipeline: False
Pivot layout (Compact, Outline, Tabular).
Name String optionalposition: namedpipeline: False
Optional pivot table name.
NoColumnGrandTotals SwitchParameter optionalposition: namedpipeline: False
Disable column grand totals.
NoRowGrandTotals SwitchParameter optionalposition: namedpipeline: False
Disable row grand totals.
PageField String[] optionalposition: namedpipeline: False
Page fields (header names) used as filters.
PassThru SwitchParameter optionalposition: namedpipeline: False
Emit the worksheet after creating the pivot table.
PivotStyle String optionalposition: namedpipeline: False
Optional pivot table style name.
RowField String[] optionalposition: namedpipeline: False
Row fields (header names).
ShowDrill SwitchParameter optionalposition: namedpipeline: False
Show drill indicators.
ShowEmptyColumns SwitchParameter optionalposition: namedpipeline: False
Show empty columns.
ShowEmptyRows SwitchParameter optionalposition: namedpipeline: False
Show empty rows.
ShowHeaders SwitchParameter optionalposition: namedpipeline: False
Show field headers.
SourceRange String requiredposition: namedpipeline: False
Source data range including header row (e.g., "A1:D200").

Outputs

System.Object

Add-OfficeExcelPivotTable [-ColumnField <String[]>] [-DataField <String[]>] [-DataFunction <String[]>] [-DataOnColumns] [-DataOnRows] -DestinationCell <String> -Document <ExcelDocument> [-HideDrill] [-HideEmptyColumns] [-HideEmptyRows] [-HideHeaders] [-Layout <String>] [-Name <String>] [-NoColumnGrandTotals] [-NoRowGrandTotals] [-PageField <String[]>] [-PassThru] [-PivotStyle <String>] [-RowField <String[]>] [-Sheet <String>] [-SheetIndex <Nullable`1>] [-ShowDrill] [-ShowEmptyColumns] [-ShowEmptyRows] [-ShowHeaders] -SourceRange <String> [<CommonParameters>]
#
Parameter set: Document

Parameters

ColumnField String[] optionalposition: namedpipeline: False
Column fields (header names).
DataField String[] optionalposition: namedpipeline: False
Data fields (header names). Defaults to the last column when omitted.
DataFunction String[] optionalposition: namedpipeline: False
Aggregation functions (Sum, Count, Average, etc.).
DataOnColumns SwitchParameter optionalposition: namedpipeline: False
Show data fields on columns.
DataOnRows SwitchParameter optionalposition: namedpipeline: False
Show data fields on rows.
DestinationCell String requiredposition: namedpipeline: False
Top-left destination cell for the pivot table (e.g., "F2").
Document ExcelDocument requiredposition: namedpipeline: True (ByValue)
Workbook to operate on outside the DSL context.
HideDrill SwitchParameter optionalposition: namedpipeline: False
Hide drill indicators.
HideEmptyColumns SwitchParameter optionalposition: namedpipeline: False
Hide empty columns.
HideEmptyRows SwitchParameter optionalposition: namedpipeline: False
Hide empty rows.
HideHeaders SwitchParameter optionalposition: namedpipeline: False
Hide field headers.
Layout String optionalposition: namedpipeline: False
Pivot layout (Compact, Outline, Tabular).
Name String optionalposition: namedpipeline: False
Optional pivot table name.
NoColumnGrandTotals SwitchParameter optionalposition: namedpipeline: False
Disable column grand totals.
NoRowGrandTotals SwitchParameter optionalposition: namedpipeline: False
Disable row grand totals.
PageField String[] optionalposition: namedpipeline: False
Page fields (header names) used as filters.
PassThru SwitchParameter optionalposition: namedpipeline: False
Emit the worksheet after creating the pivot table.
PivotStyle String optionalposition: namedpipeline: False
Optional pivot table style name.
RowField String[] optionalposition: namedpipeline: False
Row fields (header names).
Sheet String optionalposition: namedpipeline: False
Worksheet name when using Document.
SheetIndex Nullable`1 optionalposition: namedpipeline: False
Worksheet index (0-based) when using Document.
ShowDrill SwitchParameter optionalposition: namedpipeline: False
Show drill indicators.
ShowEmptyColumns SwitchParameter optionalposition: namedpipeline: False
Show empty columns.
ShowEmptyRows SwitchParameter optionalposition: namedpipeline: False
Show empty rows.
ShowHeaders SwitchParameter optionalposition: namedpipeline: False
Show field headers.
SourceRange String requiredposition: namedpipeline: False
Source data range including header row (e.g., "A1:D200").

Outputs

System.Object