Cmdlet

Add-OfficeExcelSubtotalSummary

Aliases: ExcelSubtotals, ExcelSubtotalSummary

Module PSWriteOffice

Aliases

ExcelSubtotals ExcelSubtotalSummary

Inputs

OfficeIMO.Excel.ExcelDocument

Outputs

OfficeIMO.Excel.ExcelSubtotalResult

Source

AddOfficeExcelSubtotalSummaryCommand.cs:18 Edit on GitHub

Adds grouped subtotal summary rows for a worksheet data range.

Syntax

Add-OfficeExcelSubtotalSummary [-DataEndRow <Int32>] [-DataStartRow <Int32>] [-Function <ExcelSubtotalFunction>] [-GrandTotalLabel <String>] -GroupColumn <String> [-HeaderRow <Int32>] [-HideDetailRows] [-LabelSuffix <String>] [-NoGrandTotal] [-NoHeader] [-NoOutline] [-OutlineLevel <Int32>] [-PassThru] [-SummaryStartRow <Int32>] -ValueColumn <String[]> [<CommonParameters>]

Parameter set: Context

Add-OfficeExcelSubtotalSummary [-DataEndRow <Int32>] [-DataStartRow <Int32>] -Document <ExcelDocument> [-Function <ExcelSubtotalFunction>] [-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: Document

Examples

Authored help example

Create subtotal rows below a grouped data range.

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.

Parameters

-DataEndRow Int32 optional: Last data row. Defaults to the last row of the used range.
-DataStartRow Int32 optional: First data row. Defaults to the row after HeaderRow.
-Function ExcelSubtotalFunction optional: Subtotal function.; Possible values: Average, Count, CountNonBlank, Max, Min, Sum
-GrandTotalLabel String optional: Label used for the optional grand total row.
-GroupColumn String requiredaliases: By, GroupBy: Group column as a 1-based index, column letter, or header name.
-HeaderRow Int32 optional: Header row that contains source labels. Defaults to the first row of the used range.
-HideDetailRows SwitchParameter optional: Hide detail rows when applying outline metadata.
-LabelSuffix String optional: Text appended to each group key in the subtotal label cell.
-NoGrandTotal SwitchParameter optional: Skip writing a grand total row.
-NoHeader SwitchParameter optional: Skip writing a summary header row.
-NoOutline SwitchParameter optional: Skip applying outline metadata to detail rows.
-OutlineLevel Int32 optional: Outline level used for grouped detail rows.
-PassThru SwitchParameter optional: Emit OfficeIMO subtotal generation metadata.
-SummaryStartRow Int32 optional: First row for the generated summary block.
-ValueColumn String[] requiredaliases: AggregateColumn, AggregateColumns, ValueColumns: Value columns as 1-based indexes, column letters, or header names.
-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.