SetFormat.ps1

Function Set-ExcelRange {
    <#
      .SYNOPSIS
        Applies number, font, alignment and/or color formatting, values or formulas to a range of Excel cells.
      .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.
      .EXAMPLE
        $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
        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
        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.
    #>

    [cmdletbinding()]
    [Alias("Set-Format")]
    Param   (
        #One or more row(s), Column(s) and/or block(s) of cells to format.
        [Parameter(ValueFromPipeline = $true,Position=0)]
        [Alias("Address")]
        $Range ,
        #The worksheet where the format is to be applied.
        [OfficeOpenXml.ExcelWorksheet]$WorkSheet ,
        #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,
        #Color of the border.
        $BorderColor=[System.Drawing.Color]::Black,
        #Style for the bottom border.
        [OfficeOpenXml.Style.ExcelBorderStyle]$BorderBottom,
        #Style for the top border.
        [OfficeOpenXml.Style.ExcelBorderStyle]$BorderTop,
        #Style for the left border.
        [OfficeOpenXml.Style.ExcelBorderStyle]$BorderLeft,
        #Style for the right border.
        [OfficeOpenXml.Style.ExcelBorderStyle]$BorderRight,
        #Colour for the text - if none is specified it will be left as it is.
        $FontColor,
        #Value for the cell.
        $Value,
        #Formula for the cell.
        $Formula,
        #Specifies formula should be an array formula (a.k.a CSE [ctrl-shift-enter] formula).
        [Switch]$ArrayFormula,
        #Clear Bold, Italic, StrikeThrough and Underline and set color 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 -UnderlineType; use -Underline:$false to remove underlining.
        [Switch]$Underline,
         #Specifies whether underlining should be single or double, normal or accounting mode. The default is "Single".
        [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 color.
        $BackgroundColor,
        #Background pattern - Solid by default.
        [OfficeOpenXml.Style.ExcelFillStyle]$BackgroundPattern = [OfficeOpenXml.Style.ExcelFillStyle]::Solid ,
        #Secondary color for background pattern.
        [Alias("PatternColour")]
        $PatternColor,
        #Turn on Text-Wrapping; use -WrapText:$false to turn off 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 height (rows or ranges only).
        [float]$Height,
        #Hide a row or column (not a range); use -Hidden:$false to unhide.
        [Switch]$Hidden
    )
    process {
        if  ($Range -is [Array])  {
            [void]$PSBoundParameters.Remove("Range")
            $Range | Set-ExcelRange @PSBoundParameters
        }
        else {
            #We should accept, a worksheet and a name of a range or a cell address; a table; the address of a table; a named range; a row, a column or .Cells[ ]
            if ($Range -is [OfficeOpenXml.Table.ExcelTable]) {$Range = $Range.Address}
            elseif ($WorkSheet -and ($Range -is [string] -or $Range -is [OfficeOpenXml.ExcelAddress])) {
                $Range = $WorkSheet.Cells[$Range]
            }
            elseif ($Range -is [string]) {Write-Warning -Message "The range pararameter you have specified also needs a worksheet parameter."}

            if ($ResetFont) {
                $Range.Style.Font.Color.SetColor( ([System.Drawing.Color]::Black))
                $Range.Style.Font.Bold          = $false
                $Range.Style.Font.Italic        = $false
                $Range.Style.Font.UnderLine     = $false
                $Range.Style.Font.Strike        = $false
                $Range.Style.Font.VerticalAlign = [OfficeOpenXml.Style.ExcelVerticalAlignmentFont]::None
            }
            if ($PSBoundParameters.ContainsKey('Underline')) {
                $Range.Style.Font.UnderLine      = [boolean]$Underline
                $Range.Style.Font.UnderLineType  = $UnderLineType
            }
            if ($PSBoundParameters.ContainsKey('Bold')) {
                $Range.Style.Font.Bold           = [boolean]$bold
            }
            if ($PSBoundParameters.ContainsKey('Italic')) {
                $Range.Style.Font.Italic         = [boolean]$italic
            }
            if ($PSBoundParameters.ContainsKey('StrikeThru')) {
                $Range.Style.Font.Strike         = [boolean]$StrikeThru
            }
            if ($PSBoundParameters.ContainsKey('FontSize')){
                $Range.Style.Font.Size           = $FontSize
            }
            if ($PSBoundParameters.ContainsKey('FontName')){
                $Range.Style.Font.Name           = $FontName
            }
            if ($PSBoundParameters.ContainsKey('FontShift')){
                $Range.Style.Font.VerticalAlign  = $FontShift
            }
            if ($PSBoundParameters.ContainsKey('FontColor')){
                if ($FontColor -is [string]) {$FontColor = [System.Drawing.Color]::$FontColor }
                $Range.Style.Font.Color.SetColor(  $FontColor)
            }
            if ($PSBoundParameters.ContainsKey('TextRotation')) {
                $Range.Style.TextRotation        = $TextRotation
            }
            if ($PSBoundParameters.ContainsKey('WrapText')) {
                $Range.Style.WrapText            = [boolean]$WrapText
            }
            if ($PSBoundParameters.ContainsKey('HorizontalAlignment')) {
                $Range.Style.HorizontalAlignment = $HorizontalAlignment
            }
            if ($PSBoundParameters.ContainsKey('VerticalAlignment')) {
                $Range.Style.VerticalAlignment   = $VerticalAlignment
            }
            if ($PSBoundParameters.ContainsKey('Value')) {
                if ($Value -match '^=')      {$PSBoundParameters["Formula"] = $Value -replace '^=','' }
                else {
                    $Range.Value = $Value
                    if ($Value -is [datetime])  { $Range.Style.Numberformat.Format = 'm/d/yy h:mm' }# This is not a custom format, but a preset recognized as date and localized. It might be overwritten in a moment
                    if ($Value -is [timespan])  { $Range.Style.Numberformat.Format = '[h]:mm:ss'   }
                }
            }
            if ($PSBoundParameters.ContainsKey('Formula')) {
                if ($ArrayFormula) {$Range.CreateArrayFormula(($Formula -replace '^=','')) }
                else               {$Range.Formula         =  ($Formula -replace '^=','')  }
            }
            if ($PSBoundParameters.ContainsKey('NumberFormat')) {
                $Range.Style.Numberformat.Format = (Expand-NumberFormat $NumberFormat)
            }
            if ($BorderColor -is [string]) {$BorderColor = [System.Drawing.Color]::$BorderColor }
            if ($PSBoundParameters.ContainsKey('BorderAround')) {
                $Range.Style.Border.BorderAround($BorderAround, $BorderColor)
            }
            if ($PSBoundParameters.ContainsKey('BorderBottom')) {
                $Range.Style.Border.Bottom.Style=$BorderBottom
                $Range.Style.Border.Bottom.Color.SetColor($BorderColor)
            }
            if ($PSBoundParameters.ContainsKey('BorderTop')) {
                $Range.Style.Border.Top.Style=$BorderTop
                $Range.Style.Border.Top.Color.SetColor($BorderColor)
            }
            if ($PSBoundParameters.ContainsKey('BorderLeft')) {
                $Range.Style.Border.Left.Style=$BorderLeft
                $Range.Style.Border.Left.Color.SetColor($BorderColor)
            }
            if ($PSBoundParameters.ContainsKey('BorderRight')) {
                $Range.Style.Border.Right.Style=$BorderRight
                $Range.Style.Border.Right.Color.SetColor($BorderColor)
            }
            if ($PSBoundParameters.ContainsKey('BackgroundColor')) {
                $Range.Style.Fill.PatternType = $BackgroundPattern
                if ($BackgroundColor -is [string]) {$BackgroundColor = [System.Drawing.Color]::$BackgroundColor }
                $Range.Style.Fill.BackgroundColor.SetColor($BackgroundColor)
                if ($PatternColor) {
                    if ($PatternColor -is [string]) {$PatternColor = [System.Drawing.Color]::$PatternColor }
                    $Range.Style.Fill.PatternColor.SetColor( $PatternColor)
                }
            }
            if ($PSBoundParameters.ContainsKey('Height')) {
                if ($Range -is [OfficeOpenXml.ExcelRow]   ) {$Range.Height = $Height }
                elseif ($Range -is [OfficeOpenXml.ExcelRange] ) {
                    ($Range.Start.Row)..($Range.Start.Row + $Range.Rows) |
                        ForEach-Object {$Range.WorkSheet.Row($_).Height = $Height }
                }
                else {Write-Warning -Message ("Can set the height of a row or a range but not a {0} object" -f ($Range.GetType().name)) }
            }
            if ($Autosize) {
                if ($Range -is [OfficeOpenXml.ExcelColumn]) {$Range.AutoFit() }
                elseif ($Range -is [OfficeOpenXml.ExcelRange] ) {
                    $Range.AutoFitColumns()
                }
                else {Write-Warning -Message ("Can autofit a column or a range but not a {0} object" -f ($Range.GetType().name)) }

            }
            elseif ($PSBoundParameters.ContainsKey('Width')) {
                if ($Range -is [OfficeOpenXml.ExcelColumn]) {$Range.Width = $Width}
                elseif ($Range -is [OfficeOpenXml.ExcelRange] ) {
                    ($Range.Start.Column)..($Range.Start.Column + $Range.Columns - 1) |
                        ForEach-Object {
                            #$ws.Column($_).Width = $Width
                            $Range.Worksheet.Column($_).Width = $Width
                        }
                }
                else {Write-Warning -Message ("Can set the width of a column or a range but not a {0} object" -f ($Range.GetType().name)) }
            }
            if ($PSBoundParameters.ContainsKey('Hidden')) {
                if ($Range -is [OfficeOpenXml.ExcelRow] -or
                    $Range -is [OfficeOpenXml.ExcelColumn]  ) {$Range.Hidden = [boolean]$Hidden}
                else {Write-Warning -Message ("Can hide a row or a column but not a {0} object" -f ($Range.GetType().name)) }
            }
        }
    }
}

Function NumberFormatCompletion {
    param($commandName, $parameterName, $wordToComplete, $commandAst, $fakeBoundParameter)
    $numformats = [ordered]@{
        "General"       = "General"      # format ID 0
        "Number"        = "0.00"         # format ID 2
        "Percentage"    = "0.00%"        # format ID 10
        "Scientific"    = "0.00E+00"     # format ID 11
        "Fraction"      = "# ?/?"        # format ID 12
        "Short Date"    = "Localized"    # format ID 14 - will be translated to "mm-dd-yy" which is localized on load by Excel.
        "Short Time"    = "Localized"    # format ID 20 - will be translated to "h:mm" which is localized on load by Excel.
        "Long Time"     = "Localized"    # format ID 21 - will be translated to "h:mm:ss" which is localized on load by Excel.
        "Date-Time"     = "Localized"    # format ID 22 - will be translated to "m/d/yy h:mm" which is localized on load by Excel.
        "Currency"      = [cultureinfo]::CurrentCulture.NumberFormat.CurrencySymbol + "#,##0.00"
        "Text"          = "@"              # format ID 49
        "h:mm AM/PM"    = "h:mm AM/PM"     # format ID 18
        "h:mm:ss AM/PM" = "h:mm:ss AM/PM"  # format ID 19
        "mm:ss"         = "mm:ss"          # format ID 45
        "[h]:mm:ss"     = "Elapsed hours"  # format ID 46
        "mm:ss.0"       = "mm:ss.0"        # format ID 47
        "d-mmm-yy"      = "Localized"      # format ID 15 which is localized on load by Excel.
        "d-mmm"         = "Localized"      # format ID 16 which is localized on load by Excel.
        "mmm-yy"        = "mmm-yy"         # format ID 17 which is localized on load by Excel.
        "0"             = "Whole number"                       # format ID 1
        "0.00"          = "Number, 2 decimals"                 # format ID 2 or "number"
        "#,##0"         = "Thousand separators"                # format ID 3
        "#,##0.00"      = "Thousand separators and 2 decimals" # format ID 4
        "#,"            = "Whole thousands"
        "#.0,,"         = "Millions, 1 Decimal"
        "0%"            = "Nearest whole percentage"           # format ID 9
        "0.00%"         = "Percentage with decimals"           # format ID 10 or "Percentage"
        "00E+00"        = "Scientific"                         # format ID 11 or "Scientific"
        "# ?/?"         = "One Digit fraction"                 # format ID 12 or "Fraction"
        "# ??/??"       = "Two Digit fraction"                 # format ID 13
        "@"             = "Text"                               # format ID 49 or "Text"
    }
    $numformats.keys.where({$_ -like "$wordToComplete*"} ) | ForEach-Object {
        New-Object -TypeName System.Management.Automation.CompletionResult -ArgumentList "'$_'" , $_ ,
        ([System.Management.Automation.CompletionResultType]::ParameterValue) , $numformats[$_]
    }
}
if (Get-Command -ErrorAction SilentlyContinue -name Register-ArgumentCompleter) {
    Register-ArgumentCompleter -CommandName Add-ConditionalFormatting  -ParameterName NumberFormat        -ScriptBlock $Function:NumberFormatCompletion
    Register-ArgumentCompleter -CommandName Export-Excel               -ParameterName NumberFormat        -ScriptBlock $Function:NumberFormatCompletion
    Register-ArgumentCompleter -CommandName Set-ExcelRange             -ParameterName NumberFormat        -ScriptBlock $Function:NumberFormatCompletion
    Register-ArgumentCompleter -CommandName Set-ExcelColumn            -ParameterName NumberFormat        -ScriptBlock $Function:NumberFormatCompletion
    Register-ArgumentCompleter -CommandName Set-ExcelRow               -ParameterName NumberFormat        -ScriptBlock $Function:NumberFormatCompletion
    Register-ArgumentCompleter -CommandName Add-PivotTable             -ParameterName PivotNumberFormat   -ScriptBlock $Function:NumberFormatCompletion
    Register-ArgumentCompleter -CommandName New-PivotTableDefinition   -ParameterName PivotNumberFormat   -ScriptBlock $Function:NumberFormatCompletion
    Register-ArgumentCompleter -CommandName New-ExcelChartDefinition   -ParameterName XAxisNumberformat   -ScriptBlock $Function:NumberFormatCompletion
    Register-ArgumentCompleter -CommandName New-ExcelChartDefinition   -ParameterName YAxisNumberformat   -ScriptBlock $Function:NumberFormatCompletion
    Register-ArgumentCompleter -CommandName Add-ExcelChart             -ParameterName XAxisNumberformat   -ScriptBlock $Function:NumberFormatCompletion
    Register-ArgumentCompleter -CommandName Add-ExcelChart             -ParameterName YAxisNumberformat   -ScriptBlock $Function:NumberFormatCompletion
}

Function Expand-NumberFormat {
    <#
      .SYNOPSIS
        Converts short names for number formats to the formatting strings used in Excel
      .DESCRIPTION
        Where you can type a number format you can write, for example, 'Short-Date'
        and the module will translate it into the format string used by Excel.
        Some formats, like Short-Date change how they are presented when Excel
        loads (so date will use the local ordering of year, month and Day). Other
        formats change how they appear when loaded with different cultures
        (depending on the country "," or "." or " " may be the thousand seperator
        although Excel always stores it as ",")
      .EXAMPLE
        Expand-NumberFormat percentage
 
        Returns "0.00%"
      .EXAMPLE
        Expand-NumberFormat Currency
 
        Returns the currency format specified in the local regional settings. This
        may not be the same as Excel uses. The regional settings set the currency
        symbol and then whether it is before or after the number and separated with
        a space or not; for negative numbers the number may be wrapped in parentheses
        or a - sign might appear before or after the number and symbol.
        So this returns $#,##0.00;($#,##0.00) for English US, #,##0.00 €;€#,##0.00-
        for French. (Note some Eurozone countries write €1,23 and others 1,23€ )
        In French the decimal point will be rendered as a "," and the thousand
        separator as a space.
    #>

    [cmdletbinding()]
    [OutputType([String])]
    param  (
        #the format string to Expand
        $NumberFormat
    )
    switch ($NumberFormat) {
        "Currency"      {
            #https://msdn.microsoft.com/en-us/library/system.globalization.numberformatinfo.currencynegativepattern(v=vs.110).aspx
            $sign = [cultureinfo]::CurrentCulture.NumberFormat.CurrencySymbol
            switch ([cultureinfo]::CurrentCulture.NumberFormat.CurrencyPositivePattern) {
                0  {$pos = "$Sign#,##0.00"  ; break }
                1  {$pos = "#,##0.00$Sign"  ; break }
                2  {$pos = "$Sign #,##0.00" ; break }
                3  {$pos = "#,##0.00 $Sign" ; break }
            }
            switch ([cultureinfo]::CurrentCulture.NumberFormat.CurrencyPositivePattern) {
                0  {return "$pos;($Sign#,##0.00)"  }
                1  {return "$pos;-$Sign#,##0.00"   }
                2  {return "$pos;$Sign-#,##0.00"   }
                3  {return "$pos;$Sign#,##0.00-"   }
                4  {return "$pos;(#,##0.00$Sign)"  }
                5  {return "$pos;-#,##0.00$Sign"   }
                6  {return "$pos;#,##0.00-$Sign"   }
                7  {return "$pos;#,##0.00$Sign-"   }
                8  {return "$pos;-#,##0.00 $Sign"  }
                9  {return "$pos;-$Sign #,##0.00"  }
               10  {return "$pos;#,##0.00 $Sign-"  }
               11  {return "$pos;$Sign #,##0.00-"  }
               12  {return "$pos;$Sign -#,##0.00"  }
               13  {return "$pos;#,##0.00- $Sign"  }
               14  {return "$pos;($Sign #,##0.00)" }
               15  {return "$pos;(#,##0.00 $Sign)" }
            }
        }
        "Number"        {return  "0.00"       } # format id 2
        "Percentage"    {return  "0.00%"      } # format id 10
        "Scientific"    {return  "0.00E+00"   } # format id 11
        "Fraction"      {return  "# ?/?"      } # format id 12
        "Short Date"    {return  "mm-dd-yy"   } # format id 14 localized on load by Excel.
        "Short Time"    {return  "h:mm"       } # format id 20 localized on load by Excel.
        "Long Time"     {return  "h:mm:ss"    } # format id 21 localized on load by Excel.
        "Date-Time"     {return  "m/d/yy h:mm"} # format id 22 localized on load by Excel.
        "Text"          {return  "@"          } # format ID 49
        Default         {return  $NumberFormat}
    }
}