API Reference
Cmdlet
Add-OfficeExcelSubtotalSummary
Adds grouped subtotal summary rows for a worksheet data range.
Remarks
Adds grouped subtotal summary rows for a worksheet data range.
Examples
Create subtotal rows below a grouped data range.
PS>
ExcelSheet Data { Add-OfficeExcelSubtotalSummary -GroupColumn Region -ValueColumn Sales -DataEndRow 20 }
Writes SUBTOTAL formulas below the data range and applies row outline metadata to each group.
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-OfficeExcelSubtotalSummary [-DataEndRow <Nullable`1>] [-DataStartRow <Nullable`1>] [-Function <Sum>] [-GrandTotalLabel <String>] -GroupColumn <String> [-HeaderRow <Nullable`1>] [-HideDetailRows] [-LabelSuffix <String>] [-NoGrandTotal] [-NoHeader] [-NoOutline] [-OutlineLevel <Int32>] [-PassThru] [-SummaryStartRow <Nullable`1>] -ValueColumn <String[]> [<CommonParameters>]Parameter set:
ContextParameters
- DataEndRow Nullable`1
- Last data row. Defaults to the last row of the used range.
- DataStartRow Nullable`1
- First data row. Defaults to the row after HeaderRow.
- Function String
- Subtotal function.
- Possible values:
Sum,Average,Count,CountNonBlank,Max,Min - GrandTotalLabel String
- Label used for the optional grand total row.
- GroupColumn String
- Group column as a 1-based index, column letter, or header name.
- HeaderRow Nullable`1
- Header row that contains source labels. Defaults to the first row of the used range.
- HideDetailRows SwitchParameter
- Hide detail rows when applying outline metadata.
- LabelSuffix String
- Text appended to each group key in the subtotal label cell.
- NoGrandTotal SwitchParameter
- Skip writing a grand total row.
- NoHeader SwitchParameter
- Skip writing a summary header row.
- NoOutline SwitchParameter
- Skip applying outline metadata to detail rows.
- OutlineLevel Int32
- Outline level used for grouped detail rows.
- PassThru SwitchParameter
- Emit OfficeIMO subtotal generation metadata.
- SummaryStartRow Nullable`1
- First row for the generated summary block.
- ValueColumn String[]
- Value columns as 1-based indexes, column letters, or header names.
Outputs
OfficeIMO.Excel.ExcelSubtotalResult
Add-OfficeExcelSubtotalSummary [-DataEndRow <Nullable`1>] [-DataStartRow <Nullable`1>] -Document <ExcelDocument> [-Function <Sum>] [-GrandTotalLabel <String>] -GroupColumn <String> [-HeaderRow <Nullable`1>] [-HideDetailRows] [-LabelSuffix <String>] [-NoGrandTotal] [-NoHeader] [-NoOutline] [-OutlineLevel <Int32>] [-PassThru] [-Sheet <String>] [-SheetIndex <Nullable`1>] [-SummaryStartRow <Nullable`1>] -ValueColumn <String[]> [<CommonParameters>]Parameter set:
DocumentParameters
- DataEndRow Nullable`1
- Last data row. Defaults to the last row of the used range.
- DataStartRow Nullable`1
- First data row. Defaults to the row after HeaderRow.
- Document ExcelDocument
- Workbook to operate on outside the DSL context.
- Function String
- Subtotal function.
- Possible values:
Sum,Average,Count,CountNonBlank,Max,Min - GrandTotalLabel String
- Label used for the optional grand total row.
- GroupColumn String
- Group column as a 1-based index, column letter, or header name.
- HeaderRow Nullable`1
- Header row that contains source labels. Defaults to the first row of the used range.
- HideDetailRows SwitchParameter
- Hide detail rows when applying outline metadata.
- LabelSuffix String
- Text appended to each group key in the subtotal label cell.
- NoGrandTotal SwitchParameter
- Skip writing a grand total row.
- NoHeader SwitchParameter
- Skip writing a summary header row.
- NoOutline SwitchParameter
- Skip applying outline metadata to detail rows.
- OutlineLevel Int32
- Outline level used for grouped detail rows.
- PassThru SwitchParameter
- Emit OfficeIMO subtotal generation metadata.
- Sheet String
- Worksheet name when using Document.
- SheetIndex Nullable`1
- Worksheet index (0-based) when using Document.
- SummaryStartRow Nullable`1
- First row for the generated summary block.
- ValueColumn String[]
- Value columns as 1-based indexes, column letters, or header names.
Outputs
OfficeIMO.Excel.ExcelSubtotalResult