SetFormat.ps1
|
Function Set-Format { <# .SYNOPSIS Applies Number, font, alignment and colour formatting to a range of Excel Cells .EXAMPLE $sheet.Column(3) | Set-Format -HorizontalAlignment Right -NumberFormat "#,###" Selects column 3 from a sheet object (within a workbook object, which is a child of the ExcelPackage object) and passes it to Set-Format which formats as an integer with comma seperated groups .EXAMPLE Set-Format -Address $sheet.Cells["E1:H1048576"] -HorizontalAlignment Right -NumberFormat "#,###" Instead of piping the address in this version specifies a block of cells and applies similar formatting #> Param ( #One or more row(s), Column(s) and/or block(s) of cells to format [Parameter(ValueFromPipeline = $true,ParameterSetName="Address",Mandatory=$True,Position=0)] $Address , #The worksheet where the format is to be applied [Parameter(ParameterSetName="SheetAndRange",Mandatory=$True)] [OfficeOpenXml.ExcelWorksheet]$WorkSheet , #The area of the worksheet where the format is to be applied [Parameter(ParameterSetName="SheetAndRange",Mandatory=$True)] [OfficeOpenXml.ExcelAddress]$Range, #Number format to apply to cells e.g. "dd/MM/yyyy HH:mm", "£#,##0.00;[Red]-£#,##0.00", "0.00%" , "##/##" , "0.0E+0" etc [Alias("NFormat")] $NumberFormat, #Style of border to draw around the range [OfficeOpenXml.Style.ExcelBorderStyle]$BorderAround, [System.Drawing.Color]$BorderColor=[System.Drawing.Color]::Black, [OfficeOpenXml.Style.ExcelBorderStyle]$BorderBottom, [OfficeOpenXml.Style.ExcelBorderStyle]$BorderTop, [OfficeOpenXml.Style.ExcelBorderStyle]$BorderLeft, [OfficeOpenXml.Style.ExcelBorderStyle]$BorderRight, #Colour for the text - if none specified it will be left as it it is [System.Drawing.Color]$FontColor, #Value for the cell $Value, #Formula for the cell $Formula, #Clear Bold, Italic, StrikeThrough and Underline and set colour to black [switch]$ResetFont, #Make text bold; use -Bold:$false to remove bold [switch]$Bold, #Make text italic; use -Italic:$false to remove italic [switch]$Italic, #Underline the text using the underline style in -underline type; use -Underline:$false to remove underlining [switch]$Underline, #Should Underline use single or double, normal or accounting mode : default is single normal [OfficeOpenXml.Style.ExcelUnderLineType]$UnderLineType = [OfficeOpenXml.Style.ExcelUnderLineType]::Single, #Strike through text; use -Strikethru:$false to remove Strike through [switch]$StrikeThru, #Subscript or superscript (or none) [OfficeOpenXml.Style.ExcelVerticalAlignmentFont]$FontShift, #Font to use - Excel defaults to Calibri [String]$FontName, #Point size for the text [float]$FontSize, #Change background colour [System.Drawing.Color]$BackgroundColor, #Background pattern - solid by default [OfficeOpenXml.Style.ExcelFillStyle]$BackgroundPattern = [OfficeOpenXml.Style.ExcelFillStyle]::Solid , #Secondary colour for background pattern [Alias("PatternColour")] [System.Drawing.Color]$PatternColor, #Turn on text wrapping; use -WrapText:$false to turn off word wrapping [switch]$WrapText, #Position cell contents to left, right, center etc. default is 'General' [OfficeOpenXml.Style.ExcelHorizontalAlignment]$HorizontalAlignment, #Position cell contents to top bottom or center [OfficeOpenXml.Style.ExcelVerticalAlignment]$VerticalAlignment, #Degrees to rotate text. Up to +90 for anti-clockwise ("upwards"), or to -90 for clockwise. [ValidateRange(-90, 90)] [int]$TextRotation , #Autofit cells to width (columns or ranges only) [Alias("AutoFit")] [Switch]$AutoSize, #Set cells to a fixed width (columns or ranges only), ignored if Autosize is specified [float]$Width, #Set cells to a fixed hieght (rows or ranges only) [float]$Height, #Hide a row or column (not a range); use -Hidden:$false to unhide [switch]$Hidden ) begin { #Allow Set-Format to take Worksheet and range parameters (like Add Contitional formatting) - convert them to an address if ($WorkSheet -and $Range) {$Address = $WorkSheet.Cells[$Range] } } process { if ($Address -is [Array]) { [void]$PSBoundParameters.Remove("Address") $Address | Set-Format @PSBoundParameters } else { if ($ResetFont) { $Address.Style.Font.Color.SetColor("Black") $Address.Style.Font.Bold = $false $Address.Style.Font.Italic = $false $Address.Style.Font.UnderLine = $false $Address.Style.Font.Strike = $false } if ($PSBoundParameters.ContainsKey('Underline')) { $Address.Style.Font.UnderLine = [boolean]$Underline $Address.Style.Font.UnderLineType = $UnderLineType } if ($PSBoundParameters.ContainsKey('Bold')) { $Address.Style.Font.Bold = [boolean]$bold } if ($PSBoundParameters.ContainsKey('Italic')) { $Address.Style.Font.Italic = [boolean]$italic } if ($PSBoundParameters.ContainsKey('StrikeThru')) { $Address.Style.Font.Strike = [boolean]$StrikeThru } if ($PSBoundParameters.ContainsKey('FontSize')){ $Address.Style.Font.Size = $FontSize } if ($PSBoundParameters.ContainsKey('FontShift')){ $Address.Style.Font.VerticalAlign = $FontShift } if ($PSBoundParameters.ContainsKey('FontColor')){ $Address.Style.Font.Color.SetColor( $FontColor) } if ($PSBoundParameters.ContainsKey('NumberFormat')) { $Address.Style.Numberformat.Format = $NumberFormat } if ($PSBoundParameters.ContainsKey('TextRotation')) { $Address.Style.TextRotation = $TextRotation } if ($PSBoundParameters.ContainsKey('WrapText')) { $Address.Style.WrapText = [boolean]$WrapText } if ($PSBoundParameters.ContainsKey('HorizontalAlignment')) { $Address.Style.HorizontalAlignment = $HorizontalAlignment } if ($PSBoundParameters.ContainsKey('VerticalAlignment')) { $Address.Style.VerticalAlignment = $VerticalAlignment } if ($PSBoundParameters.ContainsKey('Value')) { $Address.Value = $Value } if ($PSBoundParameters.ContainsKey('Formula')) { $Address.Formula = $Formula } if ($PSBoundParameters.ContainsKey('BorderAround')) { $Address.Style.Border.BorderAround($BorderAround, $BorderColor) } if ($PSBoundParameters.ContainsKey('BorderBottom')) { $Address.Style.Border.Bottom.Style=$BorderBottom $Address.Style.Border.Bottom.Color.SetColor($BorderColor) } if ($PSBoundParameters.ContainsKey('BorderTop')) { $Address.Style.Border.Top.Style=$BorderTop $Address.Style.Border.Top.Color.SetColor($BorderColor) } if ($PSBoundParameters.ContainsKey('BorderLeft')) { $Address.Style.Border.Left.Style=$BorderLeft $Address.Style.Border.Left.Color.SetColor($BorderColor) } if ($PSBoundParameters.ContainsKey('BorderRight')) { $Address.Style.Border.Right.Style=$BorderRight $Address.Style.Border.Right.Color.SetColor($BorderColor) } if ($PSBoundParameters.ContainsKey('BackgroundColor')) { $Address.Style.Fill.PatternType = $BackgroundPattern $Address.Style.Fill.BackgroundColor.SetColor($BackgroundColor) if ($PatternColor) { $Address.Style.Fill.PatternColor.SetColor( $PatternColor) } } if ($PSBoundParameters.ContainsKey('Height')) { if ($Address -is [OfficeOpenXml.ExcelRow] ) {$Address.Height = $Height } elseif ($Address -is [OfficeOpenXml.ExcelRange] ) { ($Address.Start.Row)..($Address.Start.Row + $Address.Rows) | ForEach-Object {$Address.WorkSheet.Row($_).Height = $Height } } else {Write-Warning -Message ("Can set the height of a row or a range but not a {0} object" -f ($Address.GetType().name)) } } if ($Autosize) { if ($Address -is [OfficeOpenXml.ExcelColumn]) {$Address.AutoFit() } elseif ($Address -is [OfficeOpenXml.ExcelRange] ) { $Address.AutoFitColumns() } else {Write-Warning -Message ("Can autofit a column or a range but not a {0} object" -f ($Address.GetType().name)) } } elseif ($PSBoundParameters.ContainsKey('Width')) { if ($Address -is [OfficeOpenXml.ExcelColumn]) {$Address.Width = $Width} elseif ($Address -is [OfficeOpenXml.ExcelRange] ) { ($Address.Start.Column)..($Address.Start.Column + $Address.Columns - 1) | ForEach-Object { #$ws.Column($_).Width = $Width $Address.Worksheet.Column($_).Width = $Width } } else {Write-Warning -Message ("Can set the width of a column or a range but not a {0} object" -f ($Address.GetType().name)) } } if ($PSBoundParameters.ContainsKey('$Hidden')) { if ($Address -is [OfficeOpenXml.ExcelRow] -or $Address -is [OfficeOpenXml.ExcelColumn] ) {$Address.Hidden = [boolean]$Hidden} else {Write-Warning -Message ("Can hide a row or a column but not a {0} object" -f ($Address.GetType().name)) } } } } } |