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

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.