< Back
Set-ExcelColumn
Post
NAME Set-ExcelColumn
SYNOPSIS
Adds or modifies a column in an Excel worksheet, filling values, setting formatting and/or creating named ranges.
SYNTAX
Set-ExcelColumn -ExcelPackage <ExcelPackage> [-Worksheetname <String>] [-Column <Object>] [-StartRow <Int32>]
[-Value <Object>] [-Heading <Object>] [-NumberFormat <Object>] [-BorderAround {None | Hair | Dotted | DashDot |
Thin | DashDotDot | Dashed | MediumDashDotDot | MediumDashed | MediumDashDot | Thick | Medium | Double}]
[-FontColor <Object>] [-Bold] [-Italic] [-Underline] [-UnderLineType {None | Single | Double | SingleAccounting |
DoubleAccounting}] [-StrikeThru] [-FontShift {None | Baseline | Subscript | Superscript}] [-FontName <String>]
[-FontSize <Single>] [-BackgroundColor <Object>] [-BackgroundPattern {None | Solid | DarkGray | MediumGray |
LightGray | Gray125 | Gray0625 | DarkVertical | DarkHorizontal | DarkDown | DarkUp | DarkGrid | DarkTrellis |
LightVertical | LightHorizontal | LightDown | LightUp | LightGrid | LightTrellis}] [-PatternColor <Object>]
[-WrapText] [-HorizontalAlignment {General | Left | Center | CenterContinuous | Right | Fill | Distributed |
Justify}] [-VerticalAlignment {Top | Center | Bottom | Distributed | Justify}] [-TextRotation <Int32>] [-AutoSize]
[-Width <Single>] [-AutoNameRange] [-Hide] [-Specified] [-PassThru] [<CommonParameters>]
Set-ExcelColumn -Worksheet <ExcelWorksheet> [-Column <Object>] [-StartRow <Int32>] [-Value <Object>] [-Heading
<Object>] [-NumberFormat <Object>] [-BorderAround {None | Hair | Dotted | DashDot | Thin | DashDotDot | Dashed |
MediumDashDotDot | MediumDashed | MediumDashDot | Thick | Medium | Double}] [-FontColor <Object>] [-Bold]
[-Italic] [-Underline] [-UnderLineType {None | Single | Double | SingleAccounting | DoubleAccounting}]
[-StrikeThru] [-FontShift {None | Baseline | Subscript | Superscript}] [-FontName <String>] [-FontSize <Single>]
[-BackgroundColor <Object>] [-BackgroundPattern {None | Solid | DarkGray | MediumGray | LightGray | Gray125 |
Gray0625 | DarkVertical | DarkHorizontal | DarkDown | DarkUp | DarkGrid | DarkTrellis | LightVertical |
LightHorizontal | LightDown | LightUp | LightGrid | LightTrellis}] [-PatternColor <Object>] [-WrapText]
[-HorizontalAlignment {General | Left | Center | CenterContinuous | Right | Fill | Distributed | Justify}]
[-VerticalAlignment {Top | Center | Bottom | Distributed | Justify}] [-TextRotation <Int32>] [-AutoSize] [-Width
<Single>] [-AutoNameRange] [-Hide] [-Specified] [-PassThru] [<CommonParameters>]
DESCRIPTION
Set-ExcelColumn can take a value which is either a string containing a value or formula or a scriptblock which
evaluates to a string, and optionally a column number and fills that value down the column.
A column heading can be specified, and the column can be made a named range.
The column can be formatted in the same operation.
PARAMETERS
-ExcelPackage <ExcelPackage>
If specifying the worksheet by name, the ExcelPackage object which contains the worksheet also needs to be
passed.
Required? true
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-Worksheetname <String>
The sheet to update can be given as a name or an Excel Worksheet object - this sets it by name.
Required? false
Position? named
Default value Sheet1
Accept pipeline input? False
Accept wildcard characters? false
-Worksheet <ExcelWorksheet>
This passes the worksheet object instead of passing a sheet name and an Excelpackage object.
Required? true
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-Column <Object>
Column to fill down - the first column is 1. 0 will be interpreted as first empty column.
Required? false
Position? named
Default value 0
Accept pipeline input? True (ByValue)
Accept wildcard characters? false
-StartRow <Int32>
First row to fill data in.
Required? false
Position? named
Default value 0
Accept pipeline input? False
Accept wildcard characters? false
-Value <Object>
A value, formula or scriptblock to fill in. A script block can use $worksheet, $row, $column [number],
$columnName [letter(s)], $startRow, $startColumn, $endRow, $endColumn.
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-Heading <Object>
Optional column heading.
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-NumberFormat <Object>
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.
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-BorderAround <ExcelBorderStyle>
Style of border to draw around the row.
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-FontColor <Object>
Colour for the text - if none specified it will be left as it it is.
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-Bold [<SwitchParameter>]
Make text bold; use -Bold:$false to remove bold.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-Italic [<SwitchParameter>]
Make text italic; use -Italic:$false to remove italic.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-Underline [<SwitchParameter>]
Underline the text using the underline style in -UnderlineType; use -Underline:$false to remove underlining.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-UnderLineType <ExcelUnderLineType>
Specifies whether underlining should be single or double, normal or accounting mode. The default is "Single".
Required? false
Position? named
Default value Single
Accept pipeline input? False
Accept wildcard characters? false
-StrikeThru [<SwitchParameter>]
Strike through text; use -StrikeThru:$false to remove strike through.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-FontShift <ExcelVerticalAlignmentFont>
Subscript or Superscript (or None).
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-FontName <String>
Font to use - Excel defaults to Calibri.
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-FontSize <Single>
Point size for the text.
Required? false
Position? named
Default value 0
Accept pipeline input? False
Accept wildcard characters? false
-BackgroundColor <Object>
Change background color.
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-BackgroundPattern <ExcelFillStyle>
Background pattern - "Solid" by default.
Required? false
Position? named
Default value Solid
Accept pipeline input? False
Accept wildcard characters? false
-PatternColor <Object>
Secondary color for background pattern.
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-WrapText [<SwitchParameter>]
Turn on Text-Wrapping; use -WrapText:$false to turn off wrapping.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-HorizontalAlignment <ExcelHorizontalAlignment>
Position cell contents to Left, Right, Center etc. Default is "General".
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-VerticalAlignment <ExcelVerticalAlignment>
Position cell contents to Top, Bottom or Center.
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-TextRotation <Int32>
Degrees to rotate text; up to +90 for anti-clockwise ("upwards"), or to -90 for clockwise.
Required? false
Position? named
Default value 0
Accept pipeline input? False
Accept wildcard characters? false
-AutoSize [<SwitchParameter>]
Attempt to auto-fit cells to the width their contents.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-Width <Single>
Set cells to a fixed width, ignored if -AutoSize is specified.
Required? false
Position? named
Default value 0
Accept pipeline input? False
Accept wildcard characters? false
-AutoNameRange [<SwitchParameter>]
Set the inserted data to be a named range.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-Hide [<SwitchParameter>]
Hide the column.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-Specified [<SwitchParameter>]
If specified, returns the range of cells which were affected.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-PassThru [<SwitchParameter>]
If specified, return an object representing the Column, to allow further work to be done on it.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
<CommonParameters>
This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216).
INPUTS
OUTPUTS
OfficeOpenXml.ExcelColumn
System.String
NOTES
-------------------------- EXAMPLE 1 --------------------------
PS\\> Set-ExcelColumn -Worksheet $ws -Column 5 -NumberFormat 'Currency'
$ws contains a worksheet object - and column "E" is set to use the local currency format.
Intelisense will complete the names of predefined number formats.
You can see how currency is interpreted on the local computer with the command Expand-NumberFormat currency
-------------------------- EXAMPLE 2 --------------------------
PS\\> Set-ExcelColumn -Worksheet $ws -Heading "WinsToFastLaps" -Value {"=E$row/C$row"} -Column 7 -AutoSize
-AutoNameRange
Here, $WS already contains a worksheet which holds counts of races won and fastest laps recorded by racing drivers
(in columns C and E). Set-ExcelColumn specifies that Column 7 should have a heading of "WinsToFastLaps" and the
data cells should contain =E2/C2 , =E3/C3 etc the new data cells should become a named range, which will also be
named "WinsToFastLaps" and the column width will be set automatically.
When a value begins with "=", it is treated as a formula.
If value is a script block it will be evaluated, so here the string "=E$row/C$Row" will have the number of the
current row inserted; see the value parameter for a list of variables which can be used.
Note than when evaluating an expression in a string, it is necessary to wrap it in $() so $row is valid but
$($row+1) is needed.
To preventVariables merging into other parts of the string, use the back tick "$columnName`4" will be "G4" -
without the backtick the string will look for a variable named "columnName4"
-------------------------- EXAMPLE 3 --------------------------
Set-ExcelColumn -Worksheet $ws -Heading "Link" -Value {"https://en.wikipedia.org" +
$worksheet.cells["B$Row"].value } -AutoSize
In this example, the worksheet in $ws has partial links to Wikipedia pages in column B.
The -Value parameter is a script block which outputs a string beginning "https..." and ending with the value of
the cell at column B in the current row.
When given a valid URI, Set-ExcelColumn makes it a hyperlink.
The column will be autosized to fit the links.
-------------------------- EXAMPLE 4 --------------------------
4..6 | Set-ExcelColumn -Worksheet $ws -AutoNameRange
Again $ws contains a worksheet. Here columns 4 to 6 are made into named ranges, row 1 is used for the range name
and the rest of the column becomes the range.
RELATED LINKS
SYNOPSIS
Adds or modifies a column in an Excel worksheet, filling values, setting formatting and/or creating named ranges.
SYNTAX
Set-ExcelColumn -ExcelPackage <ExcelPackage> [-Worksheetname <String>] [-Column <Object>] [-StartRow <Int32>]
[-Value <Object>] [-Heading <Object>] [-NumberFormat <Object>] [-BorderAround {None | Hair | Dotted | DashDot |
Thin | DashDotDot | Dashed | MediumDashDotDot | MediumDashed | MediumDashDot | Thick | Medium | Double}]
[-FontColor <Object>] [-Bold] [-Italic] [-Underline] [-UnderLineType {None | Single | Double | SingleAccounting |
DoubleAccounting}] [-StrikeThru] [-FontShift {None | Baseline | Subscript | Superscript}] [-FontName <String>]
[-FontSize <Single>] [-BackgroundColor <Object>] [-BackgroundPattern {None | Solid | DarkGray | MediumGray |
LightGray | Gray125 | Gray0625 | DarkVertical | DarkHorizontal | DarkDown | DarkUp | DarkGrid | DarkTrellis |
LightVertical | LightHorizontal | LightDown | LightUp | LightGrid | LightTrellis}] [-PatternColor <Object>]
[-WrapText] [-HorizontalAlignment {General | Left | Center | CenterContinuous | Right | Fill | Distributed |
Justify}] [-VerticalAlignment {Top | Center | Bottom | Distributed | Justify}] [-TextRotation <Int32>] [-AutoSize]
[-Width <Single>] [-AutoNameRange] [-Hide] [-Specified] [-PassThru] [<CommonParameters>]
Set-ExcelColumn -Worksheet <ExcelWorksheet> [-Column <Object>] [-StartRow <Int32>] [-Value <Object>] [-Heading
<Object>] [-NumberFormat <Object>] [-BorderAround {None | Hair | Dotted | DashDot | Thin | DashDotDot | Dashed |
MediumDashDotDot | MediumDashed | MediumDashDot | Thick | Medium | Double}] [-FontColor <Object>] [-Bold]
[-Italic] [-Underline] [-UnderLineType {None | Single | Double | SingleAccounting | DoubleAccounting}]
[-StrikeThru] [-FontShift {None | Baseline | Subscript | Superscript}] [-FontName <String>] [-FontSize <Single>]
[-BackgroundColor <Object>] [-BackgroundPattern {None | Solid | DarkGray | MediumGray | LightGray | Gray125 |
Gray0625 | DarkVertical | DarkHorizontal | DarkDown | DarkUp | DarkGrid | DarkTrellis | LightVertical |
LightHorizontal | LightDown | LightUp | LightGrid | LightTrellis}] [-PatternColor <Object>] [-WrapText]
[-HorizontalAlignment {General | Left | Center | CenterContinuous | Right | Fill | Distributed | Justify}]
[-VerticalAlignment {Top | Center | Bottom | Distributed | Justify}] [-TextRotation <Int32>] [-AutoSize] [-Width
<Single>] [-AutoNameRange] [-Hide] [-Specified] [-PassThru] [<CommonParameters>]
DESCRIPTION
Set-ExcelColumn can take a value which is either a string containing a value or formula or a scriptblock which
evaluates to a string, and optionally a column number and fills that value down the column.
A column heading can be specified, and the column can be made a named range.
The column can be formatted in the same operation.
PARAMETERS
-ExcelPackage <ExcelPackage>
If specifying the worksheet by name, the ExcelPackage object which contains the worksheet also needs to be
passed.
Required? true
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-Worksheetname <String>
The sheet to update can be given as a name or an Excel Worksheet object - this sets it by name.
Required? false
Position? named
Default value Sheet1
Accept pipeline input? False
Accept wildcard characters? false
-Worksheet <ExcelWorksheet>
This passes the worksheet object instead of passing a sheet name and an Excelpackage object.
Required? true
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-Column <Object>
Column to fill down - the first column is 1. 0 will be interpreted as first empty column.
Required? false
Position? named
Default value 0
Accept pipeline input? True (ByValue)
Accept wildcard characters? false
-StartRow <Int32>
First row to fill data in.
Required? false
Position? named
Default value 0
Accept pipeline input? False
Accept wildcard characters? false
-Value <Object>
A value, formula or scriptblock to fill in. A script block can use $worksheet, $row, $column [number],
$columnName [letter(s)], $startRow, $startColumn, $endRow, $endColumn.
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-Heading <Object>
Optional column heading.
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-NumberFormat <Object>
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.
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-BorderAround <ExcelBorderStyle>
Style of border to draw around the row.
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-FontColor <Object>
Colour for the text - if none specified it will be left as it it is.
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-Bold [<SwitchParameter>]
Make text bold; use -Bold:$false to remove bold.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-Italic [<SwitchParameter>]
Make text italic; use -Italic:$false to remove italic.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-Underline [<SwitchParameter>]
Underline the text using the underline style in -UnderlineType; use -Underline:$false to remove underlining.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-UnderLineType <ExcelUnderLineType>
Specifies whether underlining should be single or double, normal or accounting mode. The default is "Single".
Required? false
Position? named
Default value Single
Accept pipeline input? False
Accept wildcard characters? false
-StrikeThru [<SwitchParameter>]
Strike through text; use -StrikeThru:$false to remove strike through.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-FontShift <ExcelVerticalAlignmentFont>
Subscript or Superscript (or None).
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-FontName <String>
Font to use - Excel defaults to Calibri.
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-FontSize <Single>
Point size for the text.
Required? false
Position? named
Default value 0
Accept pipeline input? False
Accept wildcard characters? false
-BackgroundColor <Object>
Change background color.
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-BackgroundPattern <ExcelFillStyle>
Background pattern - "Solid" by default.
Required? false
Position? named
Default value Solid
Accept pipeline input? False
Accept wildcard characters? false
-PatternColor <Object>
Secondary color for background pattern.
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-WrapText [<SwitchParameter>]
Turn on Text-Wrapping; use -WrapText:$false to turn off wrapping.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-HorizontalAlignment <ExcelHorizontalAlignment>
Position cell contents to Left, Right, Center etc. Default is "General".
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-VerticalAlignment <ExcelVerticalAlignment>
Position cell contents to Top, Bottom or Center.
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-TextRotation <Int32>
Degrees to rotate text; up to +90 for anti-clockwise ("upwards"), or to -90 for clockwise.
Required? false
Position? named
Default value 0
Accept pipeline input? False
Accept wildcard characters? false
-AutoSize [<SwitchParameter>]
Attempt to auto-fit cells to the width their contents.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-Width <Single>
Set cells to a fixed width, ignored if -AutoSize is specified.
Required? false
Position? named
Default value 0
Accept pipeline input? False
Accept wildcard characters? false
-AutoNameRange [<SwitchParameter>]
Set the inserted data to be a named range.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-Hide [<SwitchParameter>]
Hide the column.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-Specified [<SwitchParameter>]
If specified, returns the range of cells which were affected.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-PassThru [<SwitchParameter>]
If specified, return an object representing the Column, to allow further work to be done on it.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
<CommonParameters>
This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216).
INPUTS
OUTPUTS
OfficeOpenXml.ExcelColumn
System.String
NOTES
-------------------------- EXAMPLE 1 --------------------------
PS\\> Set-ExcelColumn -Worksheet $ws -Column 5 -NumberFormat 'Currency'
$ws contains a worksheet object - and column "E" is set to use the local currency format.
Intelisense will complete the names of predefined number formats.
You can see how currency is interpreted on the local computer with the command Expand-NumberFormat currency
-------------------------- EXAMPLE 2 --------------------------
PS\\> Set-ExcelColumn -Worksheet $ws -Heading "WinsToFastLaps" -Value {"=E$row/C$row"} -Column 7 -AutoSize
-AutoNameRange
Here, $WS already contains a worksheet which holds counts of races won and fastest laps recorded by racing drivers
(in columns C and E). Set-ExcelColumn specifies that Column 7 should have a heading of "WinsToFastLaps" and the
data cells should contain =E2/C2 , =E3/C3 etc the new data cells should become a named range, which will also be
named "WinsToFastLaps" and the column width will be set automatically.
When a value begins with "=", it is treated as a formula.
If value is a script block it will be evaluated, so here the string "=E$row/C$Row" will have the number of the
current row inserted; see the value parameter for a list of variables which can be used.
Note than when evaluating an expression in a string, it is necessary to wrap it in $() so $row is valid but
$($row+1) is needed.
To preventVariables merging into other parts of the string, use the back tick "$columnName`4" will be "G4" -
without the backtick the string will look for a variable named "columnName4"
-------------------------- EXAMPLE 3 --------------------------
Set-ExcelColumn -Worksheet $ws -Heading "Link" -Value {"https://en.wikipedia.org" +
$worksheet.cells["B$Row"].value } -AutoSize
In this example, the worksheet in $ws has partial links to Wikipedia pages in column B.
The -Value parameter is a script block which outputs a string beginning "https..." and ending with the value of
the cell at column B in the current row.
When given a valid URI, Set-ExcelColumn makes it a hyperlink.
The column will be autosized to fit the links.
-------------------------- EXAMPLE 4 --------------------------
4..6 | Set-ExcelColumn -Worksheet $ws -AutoNameRange
Again $ws contains a worksheet. Here columns 4 to 6 are made into named ranges, row 1 is used for the range name
and the rest of the column becomes the range.
RELATED LINKS