Cmdlet

Add-OfficeExcelSubtotal

Aliases: ExcelSubtotal

Module: PSWriteOffice
Aliases: ExcelSubtotal
Inputs: OfficeIMO.Excel.ExcelDocument
Outputs: OfficeIMO.Excel.Reports.ExcelSubtotalResult
Source: AddOfficeExcelSubtotalCommand.cs:32 Edit on GitHub

Adds grouped subtotal summary rows for a worksheet data range.

Syntax

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

Parameter set: Default

Examples

Subtotal a sorted range

Open-OfficeExcel -Path .\Reports\Q3-Sales.xlsx {
    ExcelSheet 'Orders' {
        ExcelSubtotal -GroupColumn Region -ValueColumn Amount
    }
}

Adds a subtotal row under each region group for the Amount column.

Summarise spend per cost centre

Open-OfficeExcel -Path .\Finance\Expenses-October.xlsx {
    ExcelSheet 'Expenses' {
        ExcelSort CostCentre
        ExcelSubtotal -GroupColumn CostCentre -ValueColumn Amount, Vat -Function Sum -GrandTotalLabel 'All cost centres' -HideDetailRows
    }
}

Sorts the expense lines by cost centre, then adds summed subtotals for amount and VAT with a grand total and collapsed detail rows.

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.
-Document ExcelDocument optionalpipeline: True (ByValue): Workbook to operate on outside the DSL context.
-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 required: 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.
-Sheet OfficeSheetSelector optional: Worksheet name when using Document.
-SummaryStartRow Int32 optional: First row for the generated summary block.
-ValueColumn String[] required: Value columns as 1-based indexes, column letters, or header names.

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.