Set-ExcelRange

SYNOPSIS

Applies number, font, alignment and/or color formatting, values or formulas to a range of Excel cells.

SYNTAX

Set-ExcelRange [[-Range] <Object>] [-Worksheet <ExcelWorksheet>] [-NumberFormat <Object>]
 [-BorderAround <ExcelBorderStyle>] [-BorderColor <Object>] [-BorderBottom <ExcelBorderStyle>]
 [-BorderTop <ExcelBorderStyle>] [-BorderLeft <ExcelBorderStyle>] [-BorderRight <ExcelBorderStyle>]
 [-FontColor <Object>] [-Value <Object>] [-Formula <Object>] [-ArrayFormula] [-ResetFont] [-Bold] [-Italic]
 [-Underline] [-UnderLineType <ExcelUnderLineType>] [-StrikeThru] [-FontShift <ExcelVerticalAlignmentFont>]
 [-FontName <String>] [-FontSize <Single>] [-BackgroundColor <Object>] [-BackgroundPattern <ExcelFillStyle>]
 [-PatternColor <Object>] [-WrapText] [-HorizontalAlignment <ExcelHorizontalAlignment>]
 [-VerticalAlignment <ExcelVerticalAlignment>] [-TextRotation <Int32>] [-AutoSize] [-Width <Single>]
 [-Height <Single>] [-Hidden] [-Locked] [-Merge] [<CommonParameters>]

DESCRIPTION

Set-ExcelRange was created to set the style elements for a range of cells, this includes auto-sizing and hiding, setting font elements (Name, Size, Bold, Italic, Underline & UnderlineStyle and Subscript & SuperScript), font and background colors, borders, text wrapping, rotation, alignment within cells, and number format.

It was orignally named "Set-Format", but it has been extended to set Values, Formulas and ArrayFormulas (sometimes called Ctrl-shift-Enter [CSE] formulas); because of this, the name has become Set-ExcelRange but the old name of Set-Format is preserved as an alias.

EXAMPLES

EXAMPLE 1

PS\> $sheet.Column(3) | Set-ExcelRange -HorizontalAlignment Right -NumberFormat "#,###" -AutoFit

Selects column 3 from a sheet object (within a workbook object, which is a child of the ExcelPackage object) and passes it to Set-ExcelRange which formats numbers as a integers with comma-separated groups, aligns it right, and auto-fits the column to the contents.

EXAMPLE 2

PS\> Set-ExcelRange -Range $sheet.Cells["E1:H1048576"]  -HorizontalAlignment Right -NumberFormat "#,###"

Instead of piping the address, this version specifies a block of cells and applies similar formatting.

EXAMPLE 3

PS\> Set-ExcelRange $excel.Workbook.Worksheets[1].Tables["Processes"] -Italic

This time instead of specifying a range of cells, a table is selected by name and formatted as italic.

PARAMETERS

-Range

One or more row(s), Column(s) and/or block(s) of cells to format.

Type: System.Object
Parameter Sets: (All)
Aliases: Address

Required: False
Position: 1
Default value: None
Accept pipeline input: True (ByValue)
Accept wildcard characters: False

-NumberFormat

Number format to apply to cells for example "dd/MM/yyyy HH:mm", "£#,##0.00;[Red]-£#,##0.00", "0.00%" , "##/##" or "0.0E+0" etc.

Type: System.Object
Parameter Sets: (All)
Aliases: NFormat

Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

-BorderAround

Style of border to draw around the range.

Type: OfficeOpenXml.Style.ExcelBorderStyle
Parameter Sets: (All)
Aliases:
Accepted values: None, Hair, Dotted, DashDot, Thin, DashDotDot, Dashed, MediumDashDotDot, MediumDashed, MediumDashDot, Thick, Medium, Double

Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

-BorderColor

Color of the border.

Type: System.Object
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: [System.Drawing.Color]::Black
Accept pipeline input: False
Accept wildcard characters: False

-BorderBottom

Style for the bottom border.

Type: OfficeOpenXml.Style.ExcelBorderStyle
Parameter Sets: (All)
Aliases:
Accepted values: None, Hair, Dotted, DashDot, Thin, DashDotDot, Dashed, MediumDashDotDot, MediumDashed, MediumDashDot, Thick, Medium, Double

Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

-BorderTop

Style for the top border.

Type: OfficeOpenXml.Style.ExcelBorderStyle
Parameter Sets: (All)
Aliases:
Accepted values: None, Hair, Dotted, DashDot, Thin, DashDotDot, Dashed, MediumDashDotDot, MediumDashed, MediumDashDot, Thick, Medium, Double

Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

-BorderLeft

Style for the left border.

Type: OfficeOpenXml.Style.ExcelBorderStyle
Parameter Sets: (All)
Aliases:
Accepted values: None, Hair, Dotted, DashDot, Thin, DashDotDot, Dashed, MediumDashDotDot, MediumDashed, MediumDashDot, Thick, Medium, Double

Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

-BorderRight

Style for the right border.

Type: OfficeOpenXml.Style.ExcelBorderStyle
Parameter Sets: (All)
Aliases:
Accepted values: None, Hair, Dotted, DashDot, Thin, DashDotDot, Dashed, MediumDashDotDot, MediumDashed, MediumDashDot, Thick, Medium, Double

Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

-FontColor

Colour for the text - if none is specified it will be left as it is.

Type: System.Object
Parameter Sets: (All)
Aliases: ForegroundColor

Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

-Value

Value for the cell.

Type: System.Object
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

-Formula

Formula for the cell.

Type: System.Object
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

-ArrayFormula

Specifies formula should be an array formula (a.k.a CSE [ctrl-shift-enter] formula).

Type: System.Management.Automation.SwitchParameter
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-ResetFont

Clear Bold, Italic, StrikeThrough and Underline and set color to Black.

Type: System.Management.Automation.SwitchParameter
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-Bold

Make text bold; use -Bold:$false to remove bold.

Type: System.Management.Automation.SwitchParameter
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-Italic

Make text italic; use -Italic:$false to remove italic.

Type: System.Management.Automation.SwitchParameter
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-Underline

Underline the text using the underline style in -UnderlineType; use -Underline:$false to remove underlining.

Type: System.Management.Automation.SwitchParameter
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-UnderLineType

Specifies whether underlining should be single or double, normal or accounting mode. The default is "Single".

Type: OfficeOpenXml.Style.ExcelUnderLineType
Parameter Sets: (All)
Aliases:
Accepted values: None, Single, Double, SingleAccounting, DoubleAccounting

Required: False
Position: Named
Default value: Single
Accept pipeline input: False
Accept wildcard characters: False

-StrikeThru

Strike through text; use -Strikethru:$false to remove Strike through

Type: System.Management.Automation.SwitchParameter
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-FontShift

Subscript or Superscript (or none).

Type: OfficeOpenXml.Style.ExcelVerticalAlignmentFont
Parameter Sets: (All)
Aliases:
Accepted values: None, Baseline, Subscript, Superscript

Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

-FontName

Font to use - Excel defaults to Calibri.

Type: System.String
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

-FontSize

Point size for the text.

Type: System.Single
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: 0
Accept pipeline input: False
Accept wildcard characters: False

-BackgroundColor

Change background color.

Type: System.Object
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

-BackgroundPattern

Background pattern - Solid by default.

Type: OfficeOpenXml.Style.ExcelFillStyle
Parameter Sets: (All)
Aliases:
Accepted values: None, Solid, DarkGray, MediumGray, LightGray, Gray125, Gray0625, DarkVertical, DarkHorizontal, DarkDown, DarkUp, DarkGrid, DarkTrellis, LightVertical, LightHorizontal, LightDown, LightUp, LightGrid, LightTrellis

Required: False
Position: Named
Default value: Solid
Accept pipeline input: False
Accept wildcard characters: False

-PatternColor

Secondary color for background pattern.

Type: System.Object
Parameter Sets: (All)
Aliases: PatternColour

Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

-WrapText

Turn on Text-Wrapping; use -WrapText:$false to turn off wrapping.

Type: System.Management.Automation.SwitchParameter
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-HorizontalAlignment

Position cell contents to Left, Right, Center etc. default is 'General'.

Type: OfficeOpenXml.Style.ExcelHorizontalAlignment
Parameter Sets: (All)
Aliases:
Accepted values: General, Left, Center, CenterContinuous, Right, Fill, Distributed, Justify

Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

-VerticalAlignment

Position cell contents to Top, Bottom or Center.

Type: OfficeOpenXml.Style.ExcelVerticalAlignment
Parameter Sets: (All)
Aliases:
Accepted values: Top, Center, Bottom, Distributed, Justify

Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

-TextRotation

Degrees to rotate text; up to +90 for anti-clockwise ("upwards"), or to -90 for clockwise.

Type: System.Int32
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: 0
Accept pipeline input: False
Accept wildcard characters: False

-AutoSize

Autofit cells to width (columns or ranges only).

Type: System.Management.Automation.SwitchParameter
Parameter Sets: (All)
Aliases: AutoFit

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-Width

Set cells to a fixed width (columns or ranges only), ignored if Autosize is specified.

Type: System.Single
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: 0
Accept pipeline input: False
Accept wildcard characters: False

-Height

Set cells to a fixed height (rows or ranges only).

Type: System.Single
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: 0
Accept pipeline input: False
Accept wildcard characters: False

-Hidden

Hide a row or column (not a range); use -Hidden:$false to unhide.

Type: System.Management.Automation.SwitchParameter
Parameter Sets: (All)
Aliases: Hide

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-Locked

Locks cells. Cells are locked by default use -locked:$false on the whole sheet and then lock specific ones, and enable protection on the sheet.

Type: System.Management.Automation.SwitchParameter
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-Merge

Merges cells - it is recommended that you explicitly set -HorizontalAlignment

Type: System.Management.Automation.SwitchParameter
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-Worksheet

The worksheet where the format is to be applied.

Type: OfficeOpenXml.ExcelWorksheet
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

CommonParameters

This cmdlet supports the common parameters: -Debug, -ErrorAction, -ErrorVariable, -InformationAction, -InformationVariable, -OutVariable, -OutBuffer, -PipelineVariable, -Verbose, -WarningAction, and -WarningVariable. For more information, see about_CommonParameters.

INPUTS

OUTPUTS

NOTES

RELATED LINKS