Cmdlet

Set-OfficeExcelColumnStyleByHeader

Aliases: ExcelColumnStyle, ExcelColumnStyleByHeader

Module: PSWriteOffice
Aliases: ExcelColumnStyle ExcelColumnStyleByHeader
Inputs: OfficeIMO.Excel.ExcelDocument
Source: SetOfficeExcelColumnStyleByHeaderCommand.cs:24 Edit on GitHub

Applies common number, fill, font, and status styles to a worksheet column resolved by header text.

Syntax

Set-OfficeExcelColumnStyleByHeader
[-Alignment <Left|Center|Right>]
[-AutoFit]
[-BackgroundByText <Hashtable>]
[-BackgroundColor <String>]
[-Bold]
[-BoldByText <String[]>]
[-CaseSensitive]
[-CultureName <String>]
[-Decimals <Int32>]
[-FontColor <String>]
[-FontColorByText <Hashtable>]
[-Header] <String>
[-IgnoreMissing]
[-IncludeHeader]
[-NumberFormat <String>]
[-PassThru]
[-Pattern <String>]
[-Style <OfficeExcelColumnStylePreset>]
[-Width <Double>]
[<CommonParameters>]

Parameter set: Context

Set-OfficeExcelColumnStyleByHeader
[-Alignment <Left|Center|Right>]
[-AutoFit]
[-BackgroundByText <Hashtable>]
[-BackgroundColor <String>]
[-Bold]
[-BoldByText <String[]>]
[-CaseSensitive]
[-CultureName <String>]
[-Decimals <Int32>]
-Document <ExcelDocument>
[-FontColor <String>]
[-FontColorByText <Hashtable>]
[-Header] <String>
[-IgnoreMissing]
[-IncludeHeader]
[-NumberFormat <String>]
[-PassThru]
[-Pattern <String>]
[-Sheet <String>]
[-SheetIndex <Int32>]
[-Style <OfficeExcelColumnStylePreset>]
[-Width <Double>]
[<CommonParameters>]

Parameter set: Document

Description

Uses the OfficeIMO header resolver so scripts can style report columns without calculating column letters or ranges.

Examples

Format common report columns by header.

ExcelSheet 'Data' {
  Set-OfficeExcelColumnStyleByHeader -Header Revenue -Style Currency -CultureName en-US -AutoFit
  Set-OfficeExcelColumnStyleByHeader -Header Status -BackgroundByText @{ Ready = '#D4EDDA'; Blocked = '#F8D7DA' } -BoldByText Blocked
}

Styles Revenue as currency and colors Status cells by their text.

Parameters

-Alignment OfficeExcelColumnAlignment optional: Align cell content in the resolved column.; Possible values: Left, Center, Right
-AutoFit SwitchParameter optional: Auto-fit the resolved column after applying styles.
-BackgroundByText Hashtable optional: Background colors keyed by matching cell text.
-BackgroundColor String optional: Apply a solid background color to the whole resolved column.
-Bold SwitchParameter optional: Apply bold text to the whole resolved column.
-BoldByText String[] optional: Values that should be bolded when the cell text matches.
-CaseSensitive SwitchParameter optional: Use case-sensitive matching for text maps.
-CultureName String optional: Culture used by currency formatting, such as en-US or pl-PL.
-Decimals Int32 optional: Decimal places for number, percent, and currency styles.
-FontColor String optional: Apply a font color to the whole resolved column.
-FontColorByText Hashtable optional: Font colors keyed by matching cell text.
-Header String requiredposition: 0: Header caption used to resolve the target column.
-IgnoreMissing SwitchParameter optional: Do nothing when the header cannot be found instead of throwing.
-IncludeHeader SwitchParameter optional: Include the header cell in the applied formatting.
-NumberFormat String optional: Custom number format. Also used when Style is NumberFormat.
-PassThru SwitchParameter optional: Emit the object created or changed by the command.
-Pattern String optional: Date or DateTime number format pattern.
-Style OfficeExcelColumnStylePreset optional: Preset number style to apply.; Possible values: Number, Integer, Percent, Currency, Date, DateTime, Time, DurationHours, Text, NumberFormat
-Width Double optional: Set the resolved column width.
-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.