ArgumentCompletion.ps1

function ColorCompletion {
    param($commandName, $parameterName, $wordToComplete, $commandAst, $fakeBoundParameter)
    [System.Drawing.KnownColor].GetFields() | Where-Object {$_.IsStatic -and $_.name -like "$wordToComplete*" } |
        Sort-Object name | ForEach-Object {New-CompletionResult $_.name $_.name
    }
}

function ListFonts {
    [CmdletBinding()]
    [Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSAvoidUsingEmptyCatchBlock", "")]
    param($commandName, $parameterName, $wordToComplete, $commandAst, $fakeBoundParameter)
    if (-not $script:FontFamilies) {
        $script:FontFamilies = @("","")
        try {
            $script:FontFamilies = (New-Object System.Drawing.Text.InstalledFontCollection).Families.Name
        }
        catch {}
    }
    $script:FontFamilies.where({$_ -Gt "" -and $_ -like "$wordToComplete*"} ) | ForEach-Object {
        New-Object -TypeName System.Management.Automation.CompletionResult -ArgumentList "'$_'" , $_ ,
        ([System.Management.Automation.CompletionResultType]::ParameterValue) , $_
    }
}

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[$_]
    }
}

function WorksheetArgumentCompleter {
    param($commandName, $parameterName, $wordToComplete, $commandAst, $fakeBoundParameter)
    $xlPath = $fakeBoundParameter['Path']
    if (Test-Path -Path $xlPath) {
        $xlpkg = Open-ExcelPackage -ReadOnly -Path $xlPath
        $WorksheetNames = $xlPkg.Workbook.Worksheets.Name
        Close-ExcelPackage -nosave -ExcelPackage $xlpkg
        $WorksheetNames.where( { $_ -like "*$wordToComplete*" }) | foreach-object {
            New-Object -TypeName System.Management.Automation.CompletionResult -ArgumentList "'$_'",
            $_ , ([System.Management.Automation.CompletionResultType]::ParameterValue) , $_
        }
    }
}

if   (Get-Command -ErrorAction SilentlyContinue -name Register-ArgumentCompleter) {
    Register-ArgumentCompleter -CommandName Export-Excel               -ParameterName TitleBackgroundColor   -ScriptBlock $Function:ColorCompletion
    Register-ArgumentCompleter -CommandName Add-ConditionalFormatting  -ParameterName BackgroundColor        -ScriptBlock $Function:ColorCompletion
    Register-ArgumentCompleter -CommandName Add-ConditionalFormatting  -ParameterName DataBarColor           -ScriptBlock $Function:ColorCompletion
    Register-ArgumentCompleter -CommandName Add-ConditionalFormatting  -ParameterName ForeGroundColor        -ScriptBlock $Function:ColorCompletion
    Register-ArgumentCompleter -CommandName Add-ConditionalFormatting  -ParameterName PatternColor           -ScriptBlock $Function:ColorCompletion
    Register-ArgumentCompleter -CommandName Compare-Worksheet          -ParameterName AllDataBackgroundColor -ScriptBlock $Function:ColorCompletion
    Register-ArgumentCompleter -CommandName Compare-Worksheet          -ParameterName BackgroundColor        -ScriptBlock $Function:ColorCompletion
    Register-ArgumentCompleter -CommandName Compare-Worksheet          -ParameterName FontColor              -ScriptBlock $Function:ColorCompletion
    Register-ArgumentCompleter -CommandName Compare-Worksheet          -ParameterName TabColor               -ScriptBlock $Function:ColorCompletion
    Register-ArgumentCompleter -CommandName Join-Worksheet             -ParameterName TitleBackgroundColor   -ScriptBlock $Function:ColorCompletion
    Register-ArgumentCompleter -CommandName Merge-Worksheet            -ParameterName AddBackgroundColor     -ScriptBlock $Function:ColorCompletion
    Register-ArgumentCompleter -CommandName Merge-Worksheet            -ParameterName ChangeBackgroundColor  -ScriptBlock $Function:ColorCompletion
    Register-ArgumentCompleter -CommandName Merge-Worksheet ` -ParameterName DeleteBackgroundColor  -ScriptBlock $Function:ColorCompletion
    Register-ArgumentCompleter -CommandName Merge-MulipleSheets        -ParameterName KeyFontColor           -ScriptBlock $Function:ColorCompletion
    Register-ArgumentCompleter -CommandName Merge-MulipleSheets        -ParameterName AddBackgroundColor     -ScriptBlock $Function:ColorCompletion
    Register-ArgumentCompleter -CommandName Merge-MulipleSheets        -ParameterName ChangeBackgroundColor  -ScriptBlock $Function:ColorCompletion
    Register-ArgumentCompleter -CommandName Merge-MulipleSheets ` -ParameterName DeleteBackgroundColor  -ScriptBlock $Function:ColorCompletion
    Register-ArgumentCompleter -CommandName Merge-MulipleSheets        -ParameterName KeyFontColor           -ScriptBlock $Function:ColorCompletion
    Register-ArgumentCompleter -CommandName New-ConditionalText        -ParameterName PatternColor           -ScriptBlock $Function:ColorCompletion
    Register-ArgumentCompleter -CommandName New-ConditionalText        -ParameterName BackgroundColor        -ScriptBlock $Function:ColorCompletion
    Register-ArgumentCompleter -CommandName New-ConditionalText        -ParameterName ConditionalTextColor   -ScriptBlock $Function:ColorCompletion
    Register-ArgumentCompleter -CommandName New-ExcelStyle             -ParameterName BackgroundColor        -ScriptBlock $Function:ColorCompletion
    Register-ArgumentCompleter -CommandName New-ExcelStyle             -ParameterName FontColor              -ScriptBlock $Function:ColorCompletion
    Register-ArgumentCompleter -CommandName New-ExcelStyle             -ParameterName BorderColor            -ScriptBlock $Function:ColorCompletion
    Register-ArgumentCompleter -CommandName New-ExcelStyle             -ParameterName PatternColor           -ScriptBlock $Function:ColorCompletion
    Register-ArgumentCompleter -CommandName Set-ExcelRange             -ParameterName BackgroundColor        -ScriptBlock $Function:ColorCompletion
    Register-ArgumentCompleter -CommandName Set-ExcelRange             -ParameterName FontColor              -ScriptBlock $Function:ColorCompletion
    Register-ArgumentCompleter -CommandName Set-ExcelRange             -ParameterName BorderColor            -ScriptBlock $Function:ColorCompletion
    Register-ArgumentCompleter -CommandName Set-ExcelRange             -ParameterName PatternColor           -ScriptBlock $Function:ColorCompletion
    Register-ArgumentCompleter -CommandName Set-ExcelColumn            -ParameterName BackgroundColor        -ScriptBlock $Function:ColorCompletion
    Register-ArgumentCompleter -CommandName Set-ExcelColumn            -ParameterName FontColor              -ScriptBlock $Function:ColorCompletion
    Register-ArgumentCompleter -CommandName Set-ExcelColumn            -ParameterName PatternColor           -ScriptBlock $Function:ColorCompletion
    Register-ArgumentCompleter -CommandName Set-ExcelRow               -ParameterName BackgroundColor        -ScriptBlock $Function:ColorCompletion
    Register-ArgumentCompleter -CommandName Set-ExcelRow               -ParameterName FontColor              -ScriptBlock $Function:ColorCompletion
    Register-ArgumentCompleter -CommandName Set-ExcelRow               -ParameterName PatternColor           -ScriptBlock $Function:ColorCompletion
    Register-ArgumentCompleter -CommandName New-ExcelStyle             -ParameterName FontName               -ScriptBlock $Function:ListFonts
    Register-ArgumentCompleter -CommandName Set-ExcelColumn            -ParameterName FontName               -ScriptBlock $Function:ListFonts
    Register-ArgumentCompleter -CommandName Set-ExcelRange             -ParameterName FontName               -ScriptBlock $Function:ListFonts
    Register-ArgumentCompleter -CommandName Set-ExcelRow               -ParameterName FontName               -ScriptBlock $Function:ListFonts
    Register-ArgumentCompleter -CommandName Add-ConditionalFormatting  -ParameterName NumberFormat           -ScriptBlock $Function:NumberFormatCompletion
    Register-ArgumentCompleter -CommandName Export-Excel               -ParameterName NumberFormat           -ScriptBlock $Function:NumberFormatCompletion
    Register-ArgumentCompleter -CommandName New-ExcelStyle             -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
    Register-ArgumentCompleter -CommandName Import-Excel               -ParameterName WorksheetName          -ScriptBlock $Function:WorksheetArgumentCompleter
}