Cmdlet

Set-OfficeExcelColumn

Aliases: ExcelColumn

Module: PSWriteOffice
Aliases: ExcelColumn
Inputs: OfficeIMO.Excel.ExcelDocument
Source: SetOfficeExcelColumnCommand.cs:35 Edit on GitHub

Writes values down a column and sets its width, visibility, number format and style, by position or header.

Syntax

Set-OfficeExcelColumn
[-Alignment <Left|Center|Right>]
[-AutoFit]
[-BackgroundByText <Hashtable>]
[-BackgroundColor <OfficeColor>]
[-Bold]
[-BoldByText <String[]>]
[-CaseSensitive]
[-Color <OfficeColor>]
[-ColorByText <Hashtable>]
[-Column] <Object>
[-Culture <CultureInfo>]
[-Decimals <Int32>]
[-Document <ExcelDocument>]
[-Hidden]
[-NumberFormat <String>]
[-PassThru]
[-Pattern <String>]
[-Sheet <OfficeSheetSelector>]
[-StartRow <Int32>]
[-Style <OfficeExcelColumnStylePreset>]
[-Values <Object[]>]
[-Width <ExcelColumnWidth>]
[-WrapText]
[<CommonParameters>]

Parameter set: Column

Set-OfficeExcelColumn
[-Alignment <Left|Center|Right>]
[-AutoFit]
[-BackgroundByText <Hashtable>]
[-BackgroundColor <OfficeColor>]
[-Bold]
[-BoldByText <String[]>]
[-CaseSensitive]
[-Color <OfficeColor>]
[-ColorByText <Hashtable>]
[-Culture <CultureInfo>]
[-Decimals <Int32>]
[-Document <ExcelDocument>]
-Header <String>
[-Hidden]
[-IgnoreMissing]
[-IncludeHeader]
[-NumberFormat <String>]
[-PassThru]
[-Pattern <String>]
[-Sheet <OfficeSheetSelector>]
[-StartRow <Int32>]
[-Style <OfficeExcelColumnStylePreset>]
[-Table <String>]
[-Values <Object[]>]
[-Width <ExcelColumnWidth>]
[-WrapText]
[<CommonParameters>]

Parameter set: Header

Examples

Set a column width

Open-OfficeExcel -Path .\Reports\Changes.xlsx {
    ExcelSheet 'Changes' {
        ExcelColumn B -Width 60 -WrapText
    }
}

Makes column B wide enough for long descriptions and wraps nothing else.

Style table columns by header

Open-OfficeExcel -Path .\Reports\Q3-Sales.xlsx {
    ExcelSheet 'Orders' {
        ExcelColumn -Header Amount -Table Orders -Style Currency -Decimals 2
        ExcelColumn -Header Status -Table Orders -BackgroundByText @{ Overdue = '#FEE2E2'; Paid = '#DCFCE7' }
    }
}

Formats the Amount column of the Orders table as currency and colours the Status column 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 column width after updates.
-BackgroundByText Hashtable optional: Background colors keyed by matching cell text.
-BackgroundColor OfficeColor optional: Cell fill colour expressed as a literal OfficeColor or theme role.
-Bold SwitchParameter optional: Applies bold text. An explicit false value clears bold; omission preserves the native value.
-BoldByText String[] optional: Values that should be bolded when the cell text matches.
-CaseSensitive SwitchParameter optional: Matches names and text using ordinal case sensitivity. Omission ignores case.
-Color OfficeColor optional: Text or rule colour expressed as a literal OfficeColor or theme role.
-ColorByText Hashtable optional: Maps matching cell text to font colours or theme roles.
-Column Object requiredposition: 0sets: Column: 1-based column index.
-Culture CultureInfo optional: Culture used for value conversion and number-format presets.
-Decimals Int32 optional: Decimal places for number, percent, and currency styles.
-Document ExcelDocument optionalpipeline: True (ByValue): Caller-owned workbook; omission uses the enclosing Excel content block.
-Hidden SwitchParameter optional: Hide or show the column.
-NumberFormat String optional: Custom number format. Also used when Style is NumberFormat.
-PassThru SwitchParameter optional: Emits the native result after the operation.
-Pattern String optional: Date or DateTime number format pattern.
-Sheet OfficeSheetSelector optional: Worksheet name, one-based position or native worksheet object. Multi-sheet commands also accept wildcard names.
-StartRow Int32 optional: Starting row index (1-based) for values.
-Style OfficeExcelColumnStylePreset optional: Preset number style to apply.; Possible values: Number, Integer, Percent, Currency, Date, DateTime, Time, DurationHours, Text, NumberFormat
-Values Object[] optional: Values to write down the column. Numeric, Boolean, and date values retain their native Excel types; strings remain text.
-Width ExcelColumnWidth optional: Column width in Excel character units.
-WrapText SwitchParameter optional: Apply or clear wrap text across the target row span.
-Header String requiredsets: Header: Header text identifying the target column.
-IgnoreMissing SwitchParameter optionalsets: Header: Do nothing when the header cannot be found instead of throwing.
-IncludeHeader SwitchParameter optionalsets: Header: Includes the header cell in the resolved column range.
-Table String optionalsets: Header: Native Excel table used to resolve the source range or header.

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.