| external help file | ImportExcel-help.xml |
|---|---|
| Module Name | ImportExcel |
| online version | |
| schema | 2.0.0 |
Fills values into a [new] row in an Excel spreadsheet, and sets row formats.
Set-ExcelRow -ExcelPackage <ExcelPackage> [-Worksheetname <Object>] [-Row <Object>] [-StartColumn <Int32>] [-Value <Object>] [-Heading <Object>] [-HeadingBold] [-HeadingSize <Int32>] [-NumberFormat <Object>] [-BorderAround <ExcelBorderStyle>] [-BorderColor <Object>] [-BorderBottom <ExcelBorderStyle>] [-BorderTop <ExcelBorderStyle>] [-BorderLeft <ExcelBorderStyle>] [-BorderRight <ExcelBorderStyle>] [-FontColor <Object>] [-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>] [-Height <Single>] [-Hide] [-ReturnRange] [-PassThru] [<CommonParameters>]
Set-ExcelRow -Worksheet <ExcelWorksheet> [-Row <Object>] [-StartColumn <Int32>] [-Value <Object>] [-Heading <Object>] [-HeadingBold] [-HeadingSize <Int32>] [-NumberFormat <Object>] [-BorderAround <ExcelBorderStyle>] [-BorderColor <Object>] [-BorderBottom <ExcelBorderStyle>] [-BorderTop <ExcelBorderStyle>] [-BorderLeft <ExcelBorderStyle>] [-BorderRight <ExcelBorderStyle>] [-FontColor <Object>] [-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>] [-Height <Single>] [-Hide] [-ReturnRange] [-PassThru] [<CommonParameters>]
Set-ExcelRow accepts either a Worksheet object or an ExcelPackage object returned by Export-Excel and the name of a sheet, and inserts the chosen contents into a row of the sheet.
The contents can be a constant, like "42", a formula or a script block which is converted into a constant or a formula.
The first cell of the row can optionally be given a heading.
PS\> Set-ExcelRow -Worksheet $ws -Heading Total -Value {"=sum($columnName`2:$columnName$endrow)" }
$Ws contains a worksheet object, and no Row number is specified so Set-ExcelRow will select the next row after the end of the data in the sheet.
The first cell in the row will contain "Total", and each of the other cells will contain =Sum(xx2:xx99) where xx is the column name, and 99 is the last row of data.
Note the use of the backtick in the script block (`2) to Prevent 2 becoming part of the variable "ColumnName"
The script block can use $Worksheet, $Row, $Column (number), $ColumnName (letter), $StartRow/Column and $EndRow/Column.
PS\> Set-ExcelRow -Worksheet $ws -Heading Total -HeadingBold -Value {"=sum($columnName`2:$columnName$endrow)" } -NumberFormat 'Currency' -StartColumn 2 -Bold -BorderTop Double -BorderBottom Thin
This builds on the previous example, but this time the label "Total" appears in column 2 and the formula fills from column 3 onwards.
The formula and heading are set in bold face, and the formula is formatted for the local currency, and given a double line border above and single line border below.
An Excel package object - for example from Export-Excel -PassThru - if specified requires a sheet name to be passed a parameter.
Type: ExcelPackage
Parameter Sets: Package
Aliases:
Required: True
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: FalseThe name of the sheet to update in the package.
Type: Object
Parameter Sets: Package
Aliases:
Required: False
Position: Named
Default value: Sheet1
Accept pipeline input: False
Accept wildcard characters: FalseA worksheet object instead of passing a name and package.
Type: ExcelWorksheet
Parameter Sets: Sheet
Aliases:
Required: True
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: FalseRow to fill right - first row is 1. 0 will be interpreted as first unused row.
Type: Object
Parameter Sets: (All)
Aliases:
Required: False
Position: Named
Default value: 0
Accept pipeline input: True (ByValue)
Accept wildcard characters: FalsePosition in the row to start from.
Type: Int32
Parameter Sets: (All)
Aliases:
Required: False
Position: Named
Default value: 0
Accept pipeline input: False
Accept wildcard characters: FalseValue, Formula or ScriptBlock to fill in. A ScriptBlock can use $worksheet, $row, $Column [number], $ColumnName [letter(s)], $startRow, $startColumn, $endRow, $endColumn.
Type: Object
Parameter Sets: (All)
Aliases:
Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: FalseOptional row-heading.
Type: Object
Parameter Sets: (All)
Aliases:
Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: FalseSet the heading in bold type.
Type: SwitchParameter
Parameter Sets: (All)
Aliases:
Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: FalseChange the font-size of the heading.
Type: Int32
Parameter Sets: (All)
Aliases:
Required: False
Position: Named
Default value: 0
Accept pipeline input: False
Accept wildcard characters: FalseNumber format to apply to cells e.g. "dd/MM/yyyy HH:mm", "£#,##0.00;[Red]-£#,##0.00", "0.00%" , "##/##" , "0.0E+0" etc.
Type: Object
Parameter Sets: (All)
Aliases: NFormat
Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: FalseStyle of border to draw around the row.
Type: 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: FalseColor of the border.
Type: Object
Parameter Sets: (All)
Aliases:
Required: False
Position: Named
Default value: [System.Drawing.Color]::Black
Accept pipeline input: False
Accept wildcard characters: FalseStyle for the bottom border.
Type: 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: FalseStyle for the top border.
Type: 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: FalseStyle for the left border.
Type: 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: FalseStyle for the right border.
Type: 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: FalseColor for the text - if not specified the font will be left as it it is.
Type: Object
Parameter Sets: (All)
Aliases:
Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: FalseMake text bold; use -Bold:$false to remove bold.
Type: SwitchParameter
Parameter Sets: (All)
Aliases:
Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: FalseMake text italic; use -Italic:$false to remove italic.
Type: SwitchParameter
Parameter Sets: (All)
Aliases:
Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: FalseUnderline the text using the underline style in -UnderlineType; use -Underline:$false to remove underlining.
Type: SwitchParameter
Parameter Sets: (All)
Aliases:
Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: FalseSpecifies whether underlining should be single or double, normal or accounting mode. The default is "Single".
Type: 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: FalseStrike through text; use -StrikeThru:$false to remove strike through.
Type: SwitchParameter
Parameter Sets: (All)
Aliases:
Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: FalseSubscript or Superscript (or none).
Type: 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: FalseFont to use - Excel defaults to Calibri.
Type: String
Parameter Sets: (All)
Aliases:
Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: FalsePoint size for the text.
Type: Single
Parameter Sets: (All)
Aliases:
Required: False
Position: Named
Default value: 0
Accept pipeline input: False
Accept wildcard characters: FalseChange background color.
Type: Object
Parameter Sets: (All)
Aliases:
Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: FalseBackground pattern - solid by default.
Type: 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: FalseSecondary color for background pattern.
Type: Object
Parameter Sets: (All)
Aliases: PatternColour
Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: FalseTurn on Text-Wrapping; use -WrapText:$false to turn off wrapping.
Type: SwitchParameter
Parameter Sets: (All)
Aliases:
Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: FalsePosition cell contents to Left, Right, Center etc. default is 'General'.
Type: 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: FalsePosition cell contents to Top, Bottom or Center.
Type: 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: FalseDegrees to rotate text. Up to +90 for anti-clockwise ("upwards"), or to -90 for clockwise.
Type: Int32
Parameter Sets: (All)
Aliases:
Required: False
Position: Named
Default value: 0
Accept pipeline input: False
Accept wildcard characters: FalseSet cells to a fixed height.
Type: Single
Parameter Sets: (All)
Aliases:
Required: False
Position: Named
Default value: 0
Accept pipeline input: False
Accept wildcard characters: FalseHide the row.
Type: SwitchParameter
Parameter Sets: (All)
Aliases:
Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: FalseIf sepecified, returns the range of cells which were affected.
Type: SwitchParameter
Parameter Sets: (All)
Aliases:
Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: FalseIf Specified, return a row object to allow further work to be done.
Type: SwitchParameter
Parameter Sets: (All)
Aliases:
Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: FalseThis cmdlet supports the common parameters: -Debug, -ErrorAction, -ErrorVariable, -InformationAction, -InformationVariable, -OutVariable, -OutBuffer, -PipelineVariable, -Verbose, -WarningAction, and -WarningVariable. For more information, see about_CommonParameters.