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 <Int32>] [-DataStartRow <Int32>] [-Function <Average>] [-GrandTotalLabel <String>] -GroupColumn <String> [-HeaderRow <Int32>] [-HideDetailRows] [-LabelSuffix <String>] [-NoGrandTotal] [-NoHeader] [-NoOutline] [-OutlineLevel <Int32>] [-PassThru] [-SummaryStartRow <Int32>] -ValueColumn <String[]> [<CommonParameters>]Parameter set:
ContextParameters
- DataEndRow Int32
- Last data row. Defaults to the last row of the used range.
- DataStartRow Int32
- First data row. Defaults to the row after HeaderRow.
- Function ExcelSubtotalFunction
- Subtotal function.
- Possible values:
Average,Count,CountNonBlank,Max,Min,Sum - 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 Int32
- 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 Int32
- 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 <Int32>] [-DataStartRow <Int32>] -Document <ExcelDocument> [-Function <Average>] [-GrandTotalLabel <String>] -GroupColumn <String> [-HeaderRow <Int32>] [-HideDetailRows] [-LabelSuffix <String>] [-NoGrandTotal] [-NoHeader] [-NoOutline] [-OutlineLevel <Int32>] [-PassThru] [-Sheet <String>] [-SheetIndex <Int32>] [-SummaryStartRow <Int32>] -ValueColumn <String[]> [<CommonParameters>]Parameter set:
DocumentParameters
- DataEndRow Int32
- Last data row. Defaults to the last row of the used range.
- DataStartRow Int32
- First data row. Defaults to the row after HeaderRow.
- Document ExcelDocument
- Workbook to operate on outside the DSL context.
- Function ExcelSubtotalFunction
- Subtotal function.
- Possible values:
Average,Count,CountNonBlank,Max,Min,Sum - 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 Int32
- 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 Int32
- Worksheet index (0-based) when using Document.
- SummaryStartRow Int32
- First row for the generated summary block.
- ValueColumn String[]
- Value columns as 1-based indexes, column letters, or header names.
Outputs
OfficeIMO.Excel.ExcelSubtotalResult