MetaNullUtils

0.23.10.0

MetaNullUtils.psm1


# Module Constants

# Time, in minutes, for how long cached data is considered valid
Set-Variable CCredentialCacheDuration -option Constant -value ([int]43200)

Function Get-HtmlDoctype {
<#
    .SYNOPSIS
        Get the XML doctype for a HTML document
    .DESCRIPTION
        Get the XML doctype for a HTML document, including the lis tof HTML entities
    .Example
        Get-HtmlDoctype
#>
[CmdletBinding()]
[OutputType([string[]])]
param ()
Process {
    Write-Output '<!DOCTYPE xml [ '
    Get-HtmlEntity | ForEach-Object {
        Write-Output "<!ENTITY $($_) """"> "
    }
    Write-Output ']>'
}
}
Function Get-HtmlEntity {
<#
    .SYNOPSIS
        Get a list of HTML entities
    .Example
        Get-HtmlEntity
#>
[CmdletBinding()]
[OutputType([string[]])]
param ()
Process {
    Write-Output @(
        'aacute'
        'Aacute'
        'Acirc'
        'acirc'
        'Aelig'
        'aelig'
        'agrave'
        'Agrave'
        'aring'
        'Aring'
        'atilde'
        'Atilde'
        'auml'
        'Auml'
        'bdquo'
        'brvbar'
        'bull'
        'ccedil'
        'clubs'
        'copy'
        'darr'
        'diams'
        'eacute'
        'Eacute'
        'ecirc'
        'Ecirc'
        'egrave'
        'Egrave'
        'euml'
        'Euml'
        'hearts'
        'hellip'
        'iacute'
        'Iacute'
        'icirc'
        'Icirc'
        'igrave'
        'Igrave'
        'iuml'
        'Iuml'
        'laquo'
        'larr'
        'ldquo'
        'lsaquo'
        'lsquo'
        'mbash'
        'middot'
        'nbsp'
        'ndash'
        'nearr'
        'ntilde'
        'Ntilde'
        'nwarr'
        'oacute'
        'Oacute'
        'ocirc'
        'Ocirc'
        'ograve'
        'Ograve'
        'otilde'
        'Otilde'
        'ouml'
        'Ouml'
        'quot'
        'raquo'
        'rarr'
        'rdquo'
        'rsaquo'
        'rsquo'
        'sbquo'
        'searr'
        'spades'
        'swarr'
        'szlig'
        'trade'
        'uacute'
        'Uacute'
        'uarr'
        'Uarr'
        'Ucirc'
        'ucirc'
        'ugrave'
        'Ugrave'
        'uuml'
        'Uuml'
    )
}
}
Function Get-SecureStringByteArray {
<#
    .SYNOPSIS
        Get bytes from a secure string to permit comparison
    .Description
        Get bytes from a secure string to permit comparison
    .PARAMETER SecureString
        A secure string
    .Example
        Get-SecureStringByteArray $Credential.Password
#>
[CmdletBinding()]
[OutputType([PSCustomObject])]
param (
    [Parameter(Position=0,ValueFromPipeline)]
    [Security.SecureString]$SecureString
)
Process {
    $BSTR = [Runtime.InteropServices.Marshal]::SecureStringToBSTR($SecureString)
    $Size = ([Runtime.InteropServices.Marshal]::ReadInt32($BSTR,-4))
    [PSCustomObject]@{
        BSTR = $BSTR
        Size = $Size
        Bytes = (0..($Size-1) | Foreach-Object {
            [Runtime.InteropServices.Marshal]::ReadByte($BSTR,$_)
        })
    } | Write-Output
}
}
Function Read-WorksheetContent {
<#
    .Synopsis
        Load an excel worksheet into a variable.
 
    .Description
        Load an excel worksheet into a variable. This method requires that the workbook COM object is already opened. You are responsible for closing and releasing COM objects.
        For a more user friendly method, please see the function Read-WorkbookContent
 
    .PARAMETER Workbook
        A Workbook COM object
 
    .PARAMETER Worksheet
        The name or index of the worksheet to load
 
    .PARAMETER NamedColumns
        If set, the function uses the first line as headers for the data. Otherwise it is returned as a simple array
 
    .Example
        # Load the first sheet of a workbook
        $Excel = New-Object -ComObject Excel.Application
        $Excel.Visible = $true
        $Workbook = $Excel.Workbooks.Open("MyXlsFile.xlsx")
        $data = Read-WorksheetContent -Workbook $Workbook -Worksheet 1
        $Workbook.Close
 
    .Example
        # Load a named sheet of a workbook, use the first line a column headers
        $Excel = New-Object -ComObject Excel.Application
        $Excel.Visible = $true
        $Workbook = $Excel.Workbooks.Open("MyXlsFile.xlsx")
        $data = Read-WorksheetContent -Workbook $Workbook -Worksheet "Name of the Sheet" -NamedColumns
        $Workbook.Close
#>
param(
    [parameter(Mandatory)]
    [__ComObject]$Workbook,

    $Worksheet = 1,

    [switch]$NamedColumns
)
Process {
    $data = @()

    $activity = "Loading worksheet [$($Worksheet)] from workbook [$($Workbook.Name)]"
    Write-Progress -Id 1 -ParentId 0 -Activity $activity -PercentComplete 0
    try {
        $sheet = $Workbook.Sheets.item($Worksheet);
        $sheet.Activate();
        $endColumn = $sheet.UsedRange.SpecialCells(11).Column
        $endRow = $sheet.UsedRange.SpecialCells(11).Row
        if($NamedColumns) {
            # Load Column Titles
            $columnTitles = @()
            for($i=1; $i -le 1; $i++) {
                for($j=1; $j -le $endColumn; $j++) {
                    Write-Progress -Id 1 -ParentId 0 -Activity $activity -PercentComplete ([int](($j/$endColumn*100)*(20/100)))
                    Write-Progress -Id 2 -ParentId 1 -Activity "Loading Column ${j}" -PercentComplete ([int]($j/$endColumn*100))
                    $columnTitles+=$sheet.Rows.Item($i).Columns.Item($j).Text
                }
            }
            # Load Rows
            Write-Progress -Id 1 -ParentId 0 -Activity "Loading Worksheet $($Worksheet)" -PercentComplete 10
            for($i=2; $i -le $endRow; $i++) {
                $row = @{}
                for($j=1; $j -le $endColumn; $j++) {
                    Write-Progress -Id 1 -ParentId 0 -Activity $activity -PercentComplete ([int](20+(($i/$endRow*100)*(80/100))))
                    Write-Progress -Id 2 -ParentId 1 -Activity "Loading Row ${i}" -PercentComplete ([int]($i/$endRow*100))
                    $row.Add(($columnTitles[$j-1] -replace "[\W]+","_"), $sheet.Rows.Item($i).Columns.Item($j).Text)
                }
                $data+=[pscustomobject]$row
            }
        } else {
            # Load Rows
            for($i=1; $i -le $endRow; $i++) {
                $row = [ordered]@{}
                for($j=1; $j -le $endColumn; $j++) {
                    Write-Progress -Id 1 -ParentId 0 -Activity $activity -PercentComplete ([int]($i/$endRow*100))
                    Write-Progress -Id 2 -ParentId 1 -Activity "Loading Row ${i}" -PercentComplete ([int]($i/$endRow*100))
                    $row.Add($j, $sheet.Rows.Item($i).Columns.Item($j).Text)
                }
                $data+=[pscustomobject]$row
            }
        }
    } finally {
        Write-Progress -Id 1 -ParentId 0 -Activity $activity -PercentComplete 100 -Complete
    }
    return $data
}
}
Function Compare-SecureString {
<#
    .SYNOPSIS
        Compare two secure string, without decrypting them
    .Description
        Compare two secure string, without decrypting them
    .PARAMETER SecureString1
        The first secure string
    .PARAMETER SecureString2
        The second secure string
    .Example
        Compare-SecureString $ss1 $ss2
#>
[CmdletBinding()]
[OutputType([bool])]
param (
    [Parameter(Position=0)]
    [Security.SecureString]$LeftSecureString,

    [Parameter(Position=1)]
    [Security.SecureString]$RightSecureString
)
Process {
    $Left = Get-SecureStringByteArray $LeftSecureString
    $Right = Get-SecureStringByteArray $RightSecureString
    if($Left.Size -eq $Right.Size) {
        ((0..(($Left.Size)-1) | Where-Object { $Left.Bytes[$_] -ne $Right.Bytes[$_]}).Length -eq 0) | Write-Output
    } else {
        $false | Write-Output
    }
}
}
Function ConvertFrom-HtmlEncoded {
<#
    .SYNOPSIS
        Replaces all html entities by the characters they represent
    .Description
        Replaces all html entities by the characters they represent
    .PARAMETER InputString
        The input string
    .Example
        'H&eacute; mec!' | ConvertFrom-HtmlEncoded
#>
[CmdletBinding()]
[OutputType([string])]
param (
    [Parameter(ValueFromPipeline, Position=0)]
    [AllowEmptyString()]
    [Alias('String')]
    [String]$InputString
)
Process {
    if($InputString) {
        [System.Web.HttpUtility]::HtmlDecode($InputString) | Write-Output
    }
}
}
Function ConvertFrom-UrlEncoded {
<#
    .SYNOPSIS
        Replaces all url encoded characters by the characters they represent
    .Description
        Replaces all url encoded characters by the characters they represent
    .PARAMETER InputString
        The input string
    .Example
        'Father+&amp;+son' | ConvertFrom-UrlEncoded
#>
[CmdletBinding()]
[OutputType([string])]
param (
    [Parameter(ValueFromPipeline, Position=0)]
    [AllowEmptyString()]
    [Alias('String')]
    [String]$InputString
)
Process {
    if($InputString) {
        [System.Web.HttpUtility]::UrlDecode($InputString) | Write-Output
    }
}
}
Function ConvertTo-CamelCase {
<#
    .SYNOPSIS
        Convert a string to CamelCase
    .Description
        Convert a string to CamelCase and remove non alpha-numeric characters
    .PARAMETER InputString
        The input string
    .Example
        # Convert 'hello world-123' to 'HelloWorld123'
        'hello world 123' | ConvertTo-CamelCase
#>
[CmdletBinding()]
[OutputType([string])]
param (
    [Parameter(ValueFromPipeline, Position=0)]
    [AllowEmptyString()]
    [Alias('String')]
    [String]$InputString,

    [switch]$KeepNonAlphanumericCharacters
)
Process {
    if($Inputstring) {
        $CamelCase = (Get-Culture).TextInfo.ToTitleCase($InputString)
        if($KeepNonAlphanumericCharacters) {
            $CamelCase | Write-Output
        } else {
            $CamelCase -Replace '\W' | Write-Output
        }
    }
}
}
Function ConvertTo-CamelCaseIndexedHashtable {
<#
    .SYNOPSIS
        Convert each Key of a hashtable to CamelCase
    .Description
        Convert each Key of a hashtable to CamelCase and remove non alpha-numeric characters
    .PARAMETER InputHashtable
        The input hashtable
    .Example
        @{'hello world'='Hello World'} | ConvertTo-CamelCaseIndexedHashtable
#>
[CmdletBinding()]
[OutputType([hashtable])]
param (
    [Parameter(ValueFromPipeline, Position=0)]
    [AllowEmptyCollection()]
    [AllowNull()]
    [Alias('Input','Hashtable')]
    [hashtable]$InputHashtable
)
Process {
    [hashtable]$OutputHashTable
    if($InputHashtable) {
        foreach($k in $InputHashtable.Keys) {
            $OutputHashTable += @{
                ($k | ConvertTo-CamelCase) = $InputHashtable.$k
            }
        }
    }
    $OutputHashTable | Write-Output
}
}
Function ConvertTo-CamelCaseList {
<#
    .SYNOPSIS
        Convert each value in a list to CamelCase
    .Description
        Convert each value in a list to CamelCase and remove non alpha-numeric characters
    .PARAMETER InputList
        The input list
    .Example
        @{'hello world','Hello World'} | ConvertTo-CamelCaseList
#>
[CmdletBinding()]
[OutputType([string[]])]
param (
    [Parameter(ValueFromPipeline, Position=0)]
    [AllowEmptyCollection()]
    [AllowNull()]
    [Alias('Input','List')]
    [string[]]$InputList
)
Process {
    [string[]]$OutputList = @()
    if($InputList) {
        foreach($v in $InputList) {
            $OutputList += ($v | ConvertTo-CamelCase)
        }
    }
    $OutputList | Write-Output
}
}
Function ConvertTo-FileName {
<#
    .SYNOPSIS
        Modifiy a string to make it suitable for a filename
    .Description
        Modifiy a string to make it suitable for a filename by removing or replacing any forbidden characeter
    .PARAMETER InputString
        The input string
    .Example
        'Meta\Null.txt' | ConvertTo-FileName
#>
[CmdletBinding()]
[OutputType([string])]
param (
    [Parameter(ValueFromPipeline, Position=0)]
    [AllowEmptyString()]
    [Alias('String')]
    [String]$InputString,

    [Parameter(Position=1)]
    [AllowEmptyString()]
    [String]$ReplaceBy = [string]::empty,

    [switch]$SwallowConsecutive
)
Begin {
    $InvalidChars = [System.IO.Path]::GetInvalidFileNameChars() -join ''
    if($SwallowConsecutive) {
        $RegExInvalid = "[{0}]+" -f [RegEx]::Escape($InvalidChars)
    } else {
        $RegExInvalid = "[{0}]" -f [RegEx]::Escape($InvalidChars)
    }
}
Process {
    if($InputString) {
        $InputString -replace $RegExInvalid,$ReplaceBy | Write-Output
    }
}
}
Function ConvertTo-Hash {
<#
    .Synopsis
        Convert a string to a Hash
    .Description
        Convert a string to a Hash using powershell's default algorithm (SHA256, at the time of writing)
    .PARAMETER InputString
        The input string
    .Example
        'Hello MetaNull' | ConvertTo-Hash
#>
[CmdletBinding()]
[OutputType([String])]
param (
    # String to encode
    [Parameter(ValueFromPipeline, Position=0)]
    [AllowEmptyString()]
    [Alias('String')]
    [string]$InputString
)
Process {
    if($InputString) {
        $stringAsStream = [System.IO.MemoryStream]::new()
        $writer = [System.IO.StreamWriter]::new($stringAsStream)
        $writer.write($InputString)
        $writer.Flush()
        $stringAsStream.Position = 0
        Get-FileHash -InputStream $stringAsStream | Select-Object -ExpandProperty Hash | Write-Output
    }
}
}
Function ConvertTo-HtmlEncoded {
<#
    .SYNOPSIS
        Replaces all unsupported html characters by hmtl entities
    .Description
        Replaces all unsupported html characters by hmtl entities
    .PARAMETER InputString
        The input string
    .Example
        'Hé mec!' | ConvertTo-HtmlEncoded
#>
[CmdletBinding()]
[OutputType([string])]
param (
    [Parameter(ValueFromPipeline, Position=0)]
    [AllowEmptyString()]
    [Alias('String')]
    [String]$InputString
)
Process {
    if($InputString) {
        [System.Web.HttpUtility]::HtmlEncode($InputString) | Write-Output
    }
}
}
Function ConvertTo-Label {
<#
    .Synopsis
        Convert a string to a "label".
    .Description
        Convert a string to a "label":
        - The label is made of lower case alpha numerical characters
            separated by single dash characters.
        - It always starts by a letter.
        - No trailing dashes at the end.
        The function can optionally convert &|.@ characters in their
        text representation (and, or, dot, at)
    .Example
        # Format a string
        "SC.IIT.DIS.3" | ConvertTo-Label
    .Example
        # Format an email address
        "pascal.havelange@eesc.europa.eu" | ConvertTo-Label -ReplacePunctuation
#>
[CmdletBinding()]
[OutputType([String])]
param (
    # String to encode
    [Parameter(ValueFromPipeline, Position=0)]
    [AllowEmptyString()]
    [Alias('String')]
    [string]$InputString,

    # Replace &,|,@,. by and,or,at,dot
    [switch]$ReplacePunctuation
)

Process {
    if($InputString) {
        # Remove non ASCII (note: mind the "C" in "-creplace" !)
        [string]$OutputString = ($InputString -creplace "\P{IsBasicLatin}")

        if($ReplacePunctuation) {
            $OutputString = ($OutputString -replace "(\s*\.+\s*)+"," dot ")
            $OutputString = ($OutputString -replace "(\s*[&]+\s*)+"," and ")
            $OutputString = ($OutputString -replace "(\s*[\|]+\s*)+"," or ")
            $OutputString = ($OutputString -replace "(\s*[@]+\s*)+"," at ")
        }
        # Remove non alnum characters
        $OutputString = ($OutputString -replace "[\W]+","-")
        # Remove head/tail dashes, remove head numbers
        $OutputString = ($OutputString -replace "(^[\d-]*|[-]*$)")

        # Return lower case string
        $OutputString.ToLower() | Write-Output
    }
}
}
Function ConvertTo-LabelIndexedHashtable {
<#
    .SYNOPSIS
        Convert each Key of a hashtable to "label"
    .Description
        Convert each Key of a hashtable to "label"
        - The label is made of lower case alpha numerical characters
            separated by single dash characters.
        - It always starts by a letter.
        - No trailing dashes at the end.
        The function can optionally convert &|.@ characters in their
        text representation (and, or, dot, at)
    .PARAMETER InputString
        The input string
    .Example
        @{'hello world'='Hello World'} | ConvertTo-LabelIndexedHashtable
#>
[CmdletBinding()]
[OutputType([hashtable])]
param (
    [Parameter(ValueFromPipeline, Position=0)]
    [AllowEmptyCollection()]
    [AllowNull()]
    [Alias('Input','Hashtable')]
    [hashtable]$InputHashtable,

    # Replace &,|,@,. by and,or,at,dot
    [switch]$ReplacePunctuation
)
Process {
    [hashtable]$OutputHashTable
    if($InputHashtable) {
        foreach($k in $InputHashtable.Keys) {
            $OutputHashTable += @{
                ($k | ConvertTo-Label -ReplacePunctuation:$ReplacePunctuation) = $InputHashtable.$k
            }
        }
    }
    $OutputHashTable | Write-Output
}
}
Function ConvertTo-LabelList {
<#
    .SYNOPSIS
        Convert each value in a list to a "label"
    .Description
        Convert each value in a list to a "label"
        - The label is made of lower case alpha numerical characters
            separated by single dash characters.
        - It always starts by a letter.
        - No trailing dashes at the end.
        The function can optionally convert &|.@ characters in their
        text representation (and, or, dot, at)
    .PARAMETER InputList
        The input list
    .Example
        @{'hello world','Hello World'} | ConvertTo-CamelCaseList
#>
[CmdletBinding()]
[OutputType([string[]])]
param (
    [Parameter(ValueFromPipeline, Position=0)]
    [AllowEmptyCollection()]
    [AllowNull()]
    [Alias('Input','List')]
    [string[]]$InputList,

    # Replace &,|,@,. by and,or,at,dot
    [switch]$ReplacePunctuation
)
Process {
    [string[]]$OutputList = @()
    if($InputList) {
        foreach($v in $InputList) {
            $OutputList += ($v | ConvertTo-Label -ReplacePunctuation:$ReplacePunctuation)
        }
    }
    $OutputList | Write-Output
}
}
Function ConvertTo-NormalizedString {
<#
    .SYNOPSIS
        Normalizes the stringt using Unicode Form D, Remove diacritics (accents)
    .Description
        Normalizes the stringt using Unicode Form D, Remove diacritics (accents)
    .PARAMETER InputString
        The input string
    .Example
        'C'est été à la plage' | ConvertTo-NormalizedString
#>
[CmdletBinding()]
[OutputType([string])]
param (
    [Parameter(ValueFromPipeline, Position=0)]
    [AllowEmptyString()]
    [Alias('String')]
    [String]$InputString
)
Process {
    if($InputString) {
        $Normalized = $InputString.Normalize( [Text.NormalizationForm]::FormD )
        $Buffer = new-object Text.StringBuilder
        $Normalized.ToCharArray() | Foreach-Object { 
            if( [Globalization.CharUnicodeInfo]::GetUnicodeCategory($_) -ne [Globalization.UnicodeCategory]::NonSpacingMark) {
                [void]$Buffer.Append($_)
            }
        }
        $Buffer.ToString() | Write-Output
    }
}
}
Function ConvertTo-UrlEncoded {
<#
    .SYNOPSIS
        Replaces all unsupported url characters by their escaped representation
    .Description
        Replaces all unsupported url characters by their escaped representation
    .PARAMETER InputString
        The input string
    .Example
        'Father & Son' | ConvertTo-UrlEncoded
#>
[CmdletBinding()]
[OutputType([string])]
param (
    [Parameter(ValueFromPipeline, Position=0)]
    [AllowEmptyString()]
    [Alias('String')]
    [String]$InputString
)
Process {
    if($InputString) {
        [System.Web.HttpUtility]::UrlEncode($InputString) | Write-Output
    }
}
}
Function ConvertTo-Version {
<#
    .Synopsis
        Converts a string into a [version] object.
    .Description
        Converts a string into a [version] object.
        Missing Version indicators are replaced by zero (0)
    .PARAMETER InputString
        The input string
    .Example
        '1.0','1.2.3','1.x.x',12 | ConvertTo-Version
#>
[CmdletBinding()]
[OutputType([version])]
param (
    # String to encode
    [Parameter(ValueFromPipeline, Position=0)]
    [AllowEmptyString()]
    [Alias('Version','String')]
    [string]$InputString
)
Process {
    $VersionArray = @(0,0,0,0)
    if($InputString -match '^\s*(?:(?:v|v\.|ver\.|ver|version)\s*)?(\d+(?:\.\d+)*)') {
        $InputArray = ($Matches[1] -split '\.',5)
        for($i = 0; ($i -lt $VersionArray.Length) -and ($i -lt $InputArray.Length); $i ++) {
            $VersionArray[$i] = [int]$InputArray[$i]
        }
    }
    return ([version]::new($VersionArray -join '.'))
}
}
Function ConvertTo-XDocument {
<#
    .SYNOPSIS
        Convert a piece of html into a XDocument
    .Description
        Convert a piece of (well formed) html into a XDocument
    .PARAMETER InputString
        The input string. It must contain a valid piece of XHTML
#>
[CmdletBinding()]
[OutputType([System.Xml.Linq.XDocument])]
param (
    [Parameter(ValueFromPipeline, Position=0)]
    [AllowEmptyString()]
    [Alias('String','Html')]
    [String[]]$InputString,

    [Parameter(Position = 1)]
    [AllowEmptyCollection()]
    [AllowNull()]
    [string[]]$Namespaces
)
Process {

    [UtilsXDocument]::HtmlToXDocument($InputString,$Namespaces) | Write-Output
<#
    $Document = Get-HtmlDoctype
    $Document += '<xml'
    $Namespaces | Where-Object {$_ } | ForEach-Object {
        $Document += "xmlns:$($_)=""$($_)"""
    }
    $Document += '>'
    $InputString | Foreach-Object {
        $Document += $_
    }
    $Document += '</xml>'
 
    [System.Xml.Linq.XDocument]::Parse($Document) | Write-Output
#>
}
}
Function Format-FixedWidthString {
<#
    .SYNOPSIS
        Successively Trim and Pad a string to make it fit a certain number of charaters
    .Description
        Successively Trim and Pad a string to make it fit a certain number of charaters
    .PARAMETER InputString
        The input string
    .Example
        'Hello World!' | Format-FixedWidthString -Length 7
        # Returns: "Hell..."
#>
[CmdletBinding()]
[OutputType([string])]
param (
    [Parameter(ValueFromPipeline, Position=0)]
    [AllowEmptyString()]
    [AllowNull()]
    [Alias('String')]
    [String]$InputString,

    [Parameter(Mandatory)]
    [int] $Length
)
Process {
    if($null -eq $InputString) {
        # If empty, return a blank string of $Length characters
        [string]::empty.PadRight($Length,' ') | Write-Output
    } elseif($InputString.Length -gt ($Length)) {
        # If longer than $Length, Trim the excess characters, and add '...'
        $InputString.SubString(0,$Length -3).PadRight($Length,'.') | Write-Output
    } else {
        # If Smaller than $Length, Pad string with spaces to match the requested $Length
        $InputString.PadRight($Length,' ') | Write-Output
    }
}
}
Function Get-CachedCredential {
<#
    .SYNOPSIS
        List or Retrieve (securely) stored credentials from the Registry
    .Description
        List Retrieve (securely) stored credentials from the Registry
    .PARAMETER Name
        The credentials' name
    .PARAMETER List
        Get a list of the stored credentials
    .PARAMETER RegistryHive
        The Registry hive to use (Default: HKCU:\SOFTWARE\MetaNullUtils)
    .Example
        Get-CachedCredential -Name 'AtlassianToken'
#>
[CmdletBinding(DefaultParameterSetName='List')]
[OutputType([System.Management.Automation.PSCredential])]
[OutputType([PSCustomObject[]])]
param (
    [Parameter(Mandatory,Position=0,ParameterSetName='Named',ValueFromPipelineByPropertyName)]
    [string]$Name,

    [Parameter(Mandatory=$false,ParameterSetName='List')]
    [switch]$List,

    [Parameter(Mandatory=$false)]
    [string]$RegistryHive = "HKCU:\SOFTWARE\MetaNullUtils"
)
Begin {
    $RegistryKey = "$RegistryHive\Credential"
}
Process {
    if($List) {
        Get-Item -Path $RegistryKey | Get-ChildItem | Foreach-Object {
            [PSCustomObject]@{ Name = $_.PSChildName; Credential = (New-Object System.Management.Automation.PSCredential -ArgumentList (($_ | Get-ItemPropertyValue -Name 'UserName'), (($_ | Get-ItemPropertyValue -Name 'Password') | ConvertTo-SecureString))) }
        }
    } else {
        Get-Item -Path $RegistryKey | Get-ChildItem | Where-Object {
            ($_.PSChildName -eq ($Name -replace '\W+','-'))
        } | Foreach-Object {
            $_ | Set-ItemProperty -Name LastAccess -Value (Get-Date -Format 'yyyyMMddHHmmss') | Out-Null
            New-Object System.Management.Automation.PSCredential -ArgumentList (($_ | Get-ItemPropertyValue -Name 'UserName'), (($_ | Get-ItemPropertyValue -Name 'Password') | ConvertTo-SecureString))
        }
    }
}
}
Function Get-Country {
<#
.SYNOPSIS
    Load the list of Country from the repository of the Publication Office
.DESCRIPTION
    Load the list of Country from the repository of the Publication Office
.PARAMETER EU
    Limit the resultset to EU Country
.PARAMETER EFTA
    Limit the resultset to EURO Zone Country
.PARAMETER NATO
    Limit the resultset to NATO Country
.EXAMPLE
    # Get All Countries
    Get-Country | Format-Table
.EXAMPLE
    # Get EU Countries
    Get-Country -EU | Format-Table
.EXAMPLE
    # Get EU Countries, keeping only the firs toccurence of each country
    Get-UtilsCountry -EU | Group-Object Country | Foreach-Object { $_.Group | Select-Object -First 1 } | Format-table
.EXAMPLE
    # Get all Countries from the European Continent
    Get-Country | Where-Object { $_.Continent -match 'EUROPE' }
.NOTES
    Documentation:
    https://codyburleson.com/blog/sparql-examples-select
    https://publications.europa.eu/webapi/rdf/sparql
    https://publications.europa.eu/resource/authority/country
#>
[CmdletBinding(DefaultParameterSetName='All')]
[OutputType([UtilsCountry[]])]
param(
    [Parameter(Mandatory,Position=0,ParameterSetName='Identifier_Search')]
    [Alias('Id')]
    [string] $Identifier,

    [Parameter(Mandatory,ParameterSetName='EU')]
    [switch] $EU,

    [Parameter(Mandatory,ParameterSetName='EURO')]
    [switch] $EURO,

    [Parameter(Mandatory,ParameterSetName='NATO')]
    [switch] $NATO,

    [Parameter(Mandatory,ParameterSetName='Schengen')]
    [switch] $Schengen,

    [Parameter(Mandatory,ParameterSetName='OTHER')]
    [switch] $OTHER,

    [Parameter(Mandatory=$false,ParameterSetName='All')]
    [switch] $All
)
Begin {
    [UtilsCountry]::ClearAllCountries()

    $SparqlQuery = @"
PREFIX authority: <http://publications.europa.eu/resource/authority/>
PREFIX ns9: <http://publications.europa.eu/ontology/authority/>
PREFIX ns5: <http://publications.europa.eu/ontology/euvoc#>
PREFIX ogcgs: <http://www.opengis.net/ont/geosparql#>
SELECT DISTINCT
    ?country
    ?identifier
    ?protocol
    ?name_en
    ?iso_3166_1_alpha_2
    ?iso_3166_1_alpha_3
    ?iso_3166_1_num
    ?phone_prefix
    ?iana_domain
    ?unsd_geoscheme
    ?status
    ?alt_name_en
    ?continent
    ?language
    ?classification
FROM <http://publications.europa.eu/resource/authority/country>
WHERE {
    ?country dc:identifier ?identifier ;
            ns5:status ?status ;
            dcterms:type ?classification ;
            dcterms:language ?language .
 
    ?country skos:notation ?iso_3166_1_alpha_2 .
        FILTER (datatype(?iso_3166_1_alpha_2) = <http://publications.europa.eu/ontology/euvoc#ISO_3166_1_ALPHA_2>)
 
    ?country skos:notation ?iso_3166_1_alpha_3 .
        FILTER (datatype(?iso_3166_1_alpha_3) = <http://publications.europa.eu/ontology/euvoc#ISO_3166_1_ALPHA_3>)
 
    ?country skos:notation ?iso_3166_1_num .
        FILTER (datatype(?iso_3166_1_num) = <http://publications.europa.eu/ontology/euvoc#ISO_3166_1_NUM>)
 
    OPTIONAL {
        ?country skos:notation ?phone_prefix .
        FILTER (datatype(?phone_prefix) = <http://publications.europa.eu/ontology/euvoc#PHONE_PREFIX>)
    }
 
    OPTIONAL {
        ?country skos:notation ?iana_domain .
        FILTER (datatype(?iana_domain) = <http://publications.europa.eu/ontology/euvoc#IANA_DOMAIN>)
    }
 
    OPTIONAL {
        ?country skos:notation ?unsd_geoscheme .
        FILTER (datatype(?unsd_geoscheme) = <http://publications.europa.eu/ontology/euvoc#UNSD_GEOSCHEME>)
    }
 
    OPTIONAL {
        ?country ogcgs:sfWithin ?continent .
    }
 
    # ?country authority:op-code ?code ;
 
    OPTIONAL { # Get the EU protocol display order
        ?country ns9:protocol.order ?protocol .
    }
    OPTIONAL { # Get the name in english
        ?country skos:prefLabel ?name_en .
        FILTER (langMatches(lang(?name_en), "en"))
    }
    OPTIONAL { # Get the alternate name in english
        ?country skos:altLabel ?alt_name_en .
        FILTER (langMatches(lang(?alt_name_en), "en"))
    }
 
    # NO! # ?country ns5:context <http://publications.europa.eu/resource/authority/use-context/PUB> ; # PUBLIC Context ?? not complete list
    ?country ns5:status <http://publications.europa.eu/resource/authority/concept-status/CURRENT> . # CURRENT data only (no deprecated data)
 
    ## FILTERS ##
}
ORDER BY ?protocol
"@

}
Process {
    <#
    <dcterms:type rdf:resource="http://publications.europa.eu/resource/authority/membership-classification/EURO"/>
<dcterms:type rdf:resource="http://publications.europa.eu/resource/authority/membership-classification/SCHENGEN"/>
<dcterms:type rdf:resource="http://publications.europa.eu/resource/authority/membership-classification/NATO"/>
<dcterms:type rdf:resource="http://publications.europa.eu/resource/authority/membership-classification/EU"/>
<dcterms:type rdf:resource="http://publications.europa.eu/resource/authority/membership-classification/EEA"/>
    #>
    if($Identifier) {
        $Query = $SparqlQuery.Replace('## FILTERS ##',"?country dc:identifier ""$Identifier""")
    } elseif( $EU ) {
        $Query = $SparqlQuery.Replace('## FILTERS ##',"?country dcterms:type <http://publications.europa.eu/resource/authority/membership-classification/EU> . # EU Languages Only")
    } elseif( $EURO ) {
        $Query = $SparqlQuery.Replace('## FILTERS ##',"?country dcterms:type <http://publications.europa.eu/resource/authority/membership-classification/EURO> . # EFTA Languages Only")
    } elseif( $NATO ) {
        $Query = $SparqlQuery.Replace('## FILTERS ##',"?country dcterms:type <http://publications.europa.eu/resource/authority/membership-classification/NATO> . # Non-EU Languages Only")
    } elseif( $Schengen) {
        $Query = $SparqlQuery.Replace('## FILTERS ##',"?country dcterms:type <http://publications.europa.eu/resource/authority/membership-classification/SCHENGEN> . # EFTA Languages Only")
    } elseif( $OTHER ) {
        $Query = $SparqlQuery.Replace('## FILTERS ##',"?country dcterms:type <http://publications.europa.eu/resource/authority/membership-classification/OTHER> . # Non-EU Languages Only")
    } else {
        $Query = $SparqlQuery
    }
    $Query | Write-Debug

    $Groupable = 'continent','language','classification','unsd_geoscheme'
    $Query | Invoke-PublicationOffice | Group-Object country | Foreach-Object {
        $Group = $_.Group
        $Country = $Group[0]
        $Groupable | Foreach-Object {
            $property = $_
            $Country.$property = ($Group | Group-Object $property | Select-Object -ExpandProperty Name -Unique <#| Foreach-Object { $_|Write-Verbose ; $_ }#>)
        }
        [UtilsCountry]::FromCustomObject($Country)
    }
}






}
Function Get-FileNameExtension {
<#
    .SYNOPSIS
        Get the 'extension' from a filename
    .Description
        Get the 'extension' from a filename
    .PARAMETER InputString
        The input string
    .Example
        'MetaNull.txt' | Get-FileNameExtension
#>
[CmdletBinding()]
[OutputType([bool])]
param (
    # string to process
    [Parameter(ValueFromPipeline, Position=0)]
    [AllowEmptyString()]
    [Alias('String','Path','Leaf','Filename')]
    [String]$InputString
)
Process {
    [System.IO.Path]::GetExtension($InputString) | Write-Output
}
}
Function Get-Language {
<#
.SYNOPSIS
    Load the list of Language from the repository of the Publication Office
.DESCRIPTION
    Load the list of Language from the repository of the Publication Office
.PARAMETER EU
    Limit the resultset to EU Languages
.PARAMETER EFTA
    Limit the resultset to EFTA Languages
.PARAMETER Other
    Limit the resultset to non EU and non EFTA Languages
.PARAMETER All
    Get all languages
.EXAMPLE
    # Get All Languages
    Get-Language | Format-Table
.EXAMPLE
    # Get EU Languages
    Get-Language -EU | Format-Table
.EXAMPLE
    # Get all Germanic Languages
     Get-Language | Where-Object { $_.Definition -match 'germanic' } | Select-Object Identifier,Name,Definition
.NOTES
    Documentation:
    https://codyburleson.com/blog/sparql-examples-select
    https://publications.europa.eu/webapi/rdf/sparql
    https://publications.europa.eu/resource/authority/language
#>
[CmdletBinding(DefaultParameterSetName='All')]
[OutputType([UtilsLanguage[]])]
param(
    [Parameter(Mandatory,Position=0,ParameterSetName='Identifier_Search')]
    [Alias('Id')]
    [string] $Identifier,

    [Parameter(Mandatory,ParameterSetName='EU')]
    [switch] $EU,

    [Parameter(Mandatory,ParameterSetName='EFTA')]
    [switch] $EFTA,

    [Parameter(Mandatory,ParameterSetName='OTHER')]
    [switch] $OTHER,

    [Parameter(Mandatory=$false,ParameterSetName='All')]
    [switch] $All
)
Begin {
    [UtilsLanguage]::ClearAllLanguages()
    
    $SparqlQuery = @"
PREFIX authority: <http://publications.europa.eu/resource/authority/>
PREFIX ns9: <http://publications.europa.eu/ontology/authority/>
PREFIX ns5: <http://publications.europa.eu/ontology/euvoc#>
SELECT
    ?language
    ?identifier
    ?protocol
    ?name_en
    ?definition_en
    ?name
    ?iso
    ?iso_639_1
    ?iso_639_3
    ?status
    ?classification
    # ?code ?iso_639_2b ?iso_639_2t
FROM <http://publications.europa.eu/resource/authority/language>
WHERE {
    ?language dc:identifier ?identifier ;
            ns5:status ?status ;
            dcterms:type ?classification .
 
    ?language skos:notation ?iso_639_1 .
        FILTER (datatype(?iso_639_1) = <http://publications.europa.eu/ontology/euvoc#ISO_639_1>)
 
    ?language skos:notation ?iso_639_3 .
        FILTER (datatype(?iso_639_3) = <http://publications.europa.eu/ontology/euvoc#ISO_639_3>)
 
    ?language skos:notation ?iso .
        FILTER (datatype(?iso) = <http://publications.europa.eu/ontology/euvoc#PUB_LNGISO>)
 
    # ?language authority:op-code ?code ;
    # ?language skos:notation ?iso_639_2b .
    # FILTER (datatype(?iso_639_2b) = <http://publications.europa.eu/ontology/euvoc#ISO_639_2B>)
    #
    # ?language skos:notation ?iso_639_2t .
    # FILTER (datatype(?iso_639_2t) = <http://publications.europa.eu/ontology/euvoc#ISO_639_2T>)
 
    OPTIONAL { # Get the EU protocol display order
        ?language ns9:protocol.order ?protocol .
    }
    OPTIONAL { # Get the name and definition in english
        ?language skos:prefLabel ?name_en ;
                skos:definition ?definition_en .
        FILTER (langMatches(lang(?name_en), "en"))
        FILTER (langMatches(lang(?definition_en), "en"))
    }
    OPTIONAL { # Get the name in its original language
        ?language skos:notation ?iso_639_1 ;
                skos:prefLabel ?name .
        FILTER (datatype(?iso_639_1) = <http://publications.europa.eu/ontology/euvoc#ISO_639_1>)
        FILTER (langMatches(lang(?name), ?iso_639_1))
    }
 
    ?language ns5:context <http://publications.europa.eu/resource/authority/use-context/PUB> ; # PUBLIC Context
            ns5:status <http://publications.europa.eu/resource/authority/concept-status/CURRENT> . # CURRENT data only (no deprecated data)
 
    ## FILTERS ##
}
# LIMIT 100
"@

}
Process {
    if($Identifier) {
        $Query = $SparqlQuery.Replace('## FILTERS ##',"?language dc:identifier ""$Identifier""")
    } elseif( $EU ) {
        $Query = $SparqlQuery.Replace('## FILTERS ##',"?language dcterms:type <http://publications.europa.eu/resource/authority/membership-classification/EU> . # EU Languages Only")
    } elseif( $EFTA ) {
        $Query = $SparqlQuery.Replace('## FILTERS ##',"?language dcterms:type <http://publications.europa.eu/resource/authority/membership-classification/EFTA> . # EFTA Languages Only")
    } elseif( $OTHER ) {
        $Query = $SparqlQuery.Replace('## FILTERS ##',"?language dcterms:type <http://publications.europa.eu/resource/authority/membership-classification/OTHER> . # Non-EU Languages Only")
    } else {
        $Query = $SparqlQuery
    }
    $Query | Write-Debug

    $Groupable = 'iso','classification'
    $Query | Invoke-PublicationOffice | Group-Object language | Foreach-Object {
        $Group = $_.Group
        $Language = $Group[0]
        $Groupable | Foreach-Object {
            $property = $_
            $Language.$property = ($Group | Group-Object $property | Select-Object -ExpandProperty Name -Unique <#| Foreach-Object { $_|Write-Verbose ; $_ }#>)
        }
        [UtilsLanguage]::FromCustomObject($Language)
    }
}






}
Function Get-ServiceExt {
<#
    .SYNOPSIS
        Find a service by Name, DisplayName or BinaryPathName
    .PARAMETER PathExpression
        Search using REGEX on the service's binary Path
    .PARAMETER NameExpression
        Search using REGEX on the service's name
    .PARAMETER DisplayNameExpression
        Search using REGEX on the service's DisplayName
    .PARAMETER Service
        Search using the passed Service object
#>
[CmdletBinding(DefaultParameterSetName = 'Name')]
[OutputType([System.ServiceProcess.ServiceController])]
param(
    [Parameter(Position=0,Mandatory=$false,ValueFromPipeline,ParameterSetName='Name')]
    [AllowEmptyString()]
    [AllowNull()]
    [string]$Name,

    [Parameter(Position=0,Mandatory,ParameterSetName='Path')]
    [string]$Path,

    [Parameter(Position=0,Mandatory,ParameterSetName='NameExpression')]
    [string]$NameExpression,

    [Parameter(Position=0,Mandatory,ParameterSetName='DisplayName')]
    [string]$DisplayName,

    [Parameter(Position=0,Mandatory,ParameterSetName='DisplayNameExpression')]
    [string]$DisplayNameExpression,

    [Parameter(Position=0,Mandatory,ValueFromPipeline,ParameterSetName='Service')]
    [System.ServiceProcess.ServiceController]$Service
)
Begin {
    $ErrorActionPreference = 'SilentlyContinue'
}
Process {
    Get-Service | Where-Object {
            ($PsCmdlet.ParameterSetName -eq 'Name' -and -not $Name) `
        -or ($PsCmdlet.ParameterSetName -eq 'Name' -and $_.Name -eq $Name) `
        -or ($PsCmdlet.ParameterSetName -eq 'Path' -and $_.Path -match "^(?:^|"")?($([regex]::Escape($Path)))(?:""|$|\b)?")  `
        -or ($PsCmdlet.ParameterSetName -eq 'NameExpression' -and $_.Name -match $NameExpression) `
        -or ($PsCmdlet.ParameterSetName -eq 'DisplayName' -and $_.DisplayName -eq $DisplayName) `
        -or ($PsCmdlet.ParameterSetName -eq 'DisplayNameExpression' -and $_.DisplayName -match $DisplayNameExpression) `
        -or ($PsCmdlet.ParameterSetName -eq 'Service' -and $_.ServiceName -eq $Service.ServiceName ) 
    } | Foreach-Object {
        # if($PSVersionTable.PSVersion.Major -lt 6) {
        if(-not $_.BinaryPathName) {
            $Cim = Get-CimInstance -Query "select * from Win32_Service where Name = ""$($_.Name)""" -Verbose:$false 
            $_ | Add-Member -MemberType NoteProperty -Name BinaryPathName -Value $Cim.PathName | Out-Null
        }
        $_ | Write-Output
    }
}
}
Function Invoke-PublicationOffice {
<#
    .SYNOPSIS
        Perform a SPARQL query upon the Publication Office
    .PARAMETER SparqlQuery
        The SPARQL query string
    .EXAMPLE
        # Get a list of EU languages
 
        # $Query = @"
        # PREFIX authority: <http://publications.europa.eu/resource/authority/>
        # PREFIX ns9: <http://publications.europa.eu/ontology/authority/>
        # SELECT ?language ?identifier ?name ?protocol ?classification
        # FROM <http://publications.europa.eu/resource/authority/language>
        # WHERE {
        # ?language dc:identifier ?identifier ;
        # dcterms:type ?classification ;
        # skos:prefLabel ?name .
        # FILTER (langMatches(lang(?name), "en")) # Get the name in English (generally available)
        # OPTIONAL { # Get the EU protocol display order (only available for EU languages)
        # ?language ns9:protocol.order ?protocol .
        # }
        # ?language dcterms:type <http://publications.europa.eu/resource/authority/membership-classification/EU> . # EU Language Only
        # }
        # "@
         
        $Query | Invoke-PublicationOffice | Format-Table
#>
[CmdletBinding()]
[OutputType([PSCustomObject[]])]
param(
    [Parameter(Mandatory,ValueFromPipeline,Position=0)]
    [Alias('Sparql','Query','q')]
    [string] $SparqlQuery
)
Begin {
    $ServiceUri = [uri]::new('https://publications.europa.eu/webapi/rdf/sparql')
}
Process {
    $Arguments = @{
        'default-graph-uri' = $null
        query = $SparqlQuery
        format = 'application/json'
        timeout = '0'
        debug = 'on'
    }
    $QueryUri = [System.UriBuilder]::new($ServiceUri)
    $QueryUri.Query = (($Arguments.Keys | Foreach-Object { "$($_)=$([System.Web.HttpUtility]::UrlEncode($Arguments.$_))" }) -join '&')
    
    $QueryUri.Uri.ToString() | Write-Verbose
    
    $JsonResponse = Invoke-WebRequest $QueryUri.Uri.ToString() | ConvertFrom-Json

    $JsonResponse.results.bindings | Foreach-Object {
        $Binding = $_
        $Line = New-Object psobject
        $JsonResponse.head.vars | Foreach-Object {
            $Variable = $_
            switch($Binding.$Variable.type) {
                "uri" {
                    $Line | Add-Member -MemberType NoteProperty -Name $Variable -Value ([uri]::new($Binding.$Variable.value))
                }
                default {
                    $Line | Add-Member -MemberType NoteProperty -Name $Variable -Value ($Binding.$Variable.value)
                }
            }
        }
        $Line | Write-Output
    }
}
}
Function Join-String {
<#
    .Synopsis
        Join pipeline input into a single string
    .Description
        Join pipeline input into a single string
    .Example
        # Join a list into a string
        'a','b','c' | Join-String
#>
[CmdletBinding()]
[OutputType([String])]
param (
    # String to encode
    [Parameter(ValueFromPipeline, Position=0)]
    [Alias('string')]
    [string]$InputString,

    [Parameter(Mandatory=$false,Position=1)]
    [string]$Glue
)

Begin {
    $Collect = @()
}
End {
    if(-not $Glue) {
        $Glue = ''
    }
    $Collect -join $Glue
}
Process {
    $Collect += $InputString
}
}
Function New-CachedCredential {
<#
    .SYNOPSIS
        Stores credentials (securely) in the Registry
    .Description
        Stores credentials (securely) in the Registry
    .PARAMETER InputCredential
        The credentials to store
    .PARAMETER Name
        The credentials' name
    .PARAMETER RegistryHive
        The Registry hive to use (Default: HKCU:\SOFTWARE\MetaNullUtils)
    .Example
        Get-Credential -Message 'Your credential' | New-CachedCredential -Name 'MyCredential'
    .Example
        New-CachedCredential -Name 'MyCredential' -Credential (Get-Credential -Message 'Your credential')
    .Example
        New-CachedCredential -Name 'MyCredential' -Credential "John Doe"
#>
[CmdletBinding(SupportsShouldProcess)]
param (
    [Parameter(Mandatory,Position=0)]
    [String]$Name,
    
    [Parameter(Mandatory,ValueFromPipeline,Position=1)]
    [Alias('Credential')]
    [ValidateNotNull()]
    [System.Management.Automation.PSCredential]
    [System.Management.Automation.Credential()]
    $InputCredential,

    [Parameter(Mandatory=$false)]
    [string]$RegistryHive = "HKCU:\SOFTWARE\MetaNullUtils"
)
Process {
    $PSHPName = "$RegistryHive\Credential$($Name -replace '\W+','-')"
    if($PSCmdlet.ShouldProcess("Registry Key '$($PSHPName)' ")){
        $RegistryKey = "$RegistryHive\Credential"
        if(-not (Test-Path -Path $RegistryKey)) {
            New-Item -Path $RegistryKey -ErrorAction Stop -Force | Out-Null
        }
        New-Item -Path $RegistryKey -Name ($Name -replace '\W+','-') -ErrorAction Stop | Foreach-Object {
            $_ | New-ItemProperty -PropertyType String -Name UserName -Value $InputCredential.UserName | Out-Null
            $_ | New-ItemProperty -PropertyType String -Name Password -Value ($InputCredential.Password | ConvertFrom-SecureString) | Out-Null
            $_ | New-ItemProperty -PropertyType String -Name LastAccess -Value (Get-Date -Format 'yyyyMMddHHmmss') | Out-Null
            $_ | Get-Item | Write-Output
        }
    }
}
}
Function Read-WorkbookContent {
<#
    .Synopsis
        Load an excel workbook into a variable.
 
    .Description
        This function loads one or several sheets from an excel file. The XLS file must not be opened by another user.
 
    .PARAMETER WorkbookUrl
        The Path or Url of the Excel file
 
    .PARAMETER Worksheets
        An ordered hashtable representing the sheets to load. Keys of the hashtable are used as index in the returned array. Values of the hashtables are the names or indexes of the sheets.
 
    .PARAMETER NamedColumns
        If set, the function uses the first line as headers for the data. Otherwise it is returned as a simple array (This paramtere is applied to all the loaded sheets).
 
    .PARAMETER FrefreshAll
        If set, all Queries in the workbook will be refreshed (Depending on the queries' sources, Excel may prompt for authentication)
 
    .Example
        # Load the first sheet of a workbook
        $data = Read-WorkbookContent -WorkbookUrl "MyXlsFile.xlsx"
 
    .Example
        # Load the first three sheets of a workbook
        $data = Read-WorkbookContent -WorkbookUrl "MyXlsFile.xlsx" -Worksheets @{"sheet1"=1;"sheet2"=2;"sheet3"=3})
 
    .Example
        # Load two named sheets of a workbook, use the first line a column headers
        $data = Read-WorkbookContent -WorkbookUrl "MyXlsFile.xlsx" -Worksheets (@{"sheet1"="Name of the First Sheet";"sheet2"="Name of the Second Sheet"}) -NamedColumns
 
    .Example
        # Load two named sheets of a workbook, use the first line a column headers
        $sheets = [ordered] @{"sheet1"="Name of the First Sheet";"sheet2"="Name of the Second Sheet"}
        $data = Read-WorkbookContent -WorkbookUrl "MyXlsFile.xlsx" -Worksheets $sheets -NamedColumns
#>

param(
    [parameter(Mandatory=$true,ValueFromPipeline=$true)]$WorkbookUrl,
    $Worksheets = [ordered]@{"data"="1"},
    [switch]$NamedColumns,
    [switch]$RefreshAll
)

Process {
    $activity = "Loading workbook from [$($WorkbookUrl)]"
    Write-Progress -Id 0 -Activity $activity -PercentComplete 0
    Try {
        $Excel = New-Object -ComObject Excel.Application
        $Excel.Visible = $false
        $Workbook = $Excel.Workbooks.Open($WorkbookUrl)

        if($RefreshAll) {
            Write-Progress -Id 1 -ParentId 0 -Activity "Refreshing all queries" -PercentComplete 0
            #$Excel.Visible = $false
            $Workbook.RefreshAll()
            #$Excel.Visible = $true
            Write-Progress -Id 1 -ParentId 0 -Activity "Refreshing all queries" -PercentComplete 100 -Complete
        }

        $sheetIndex = 1
        $sheetCount = $Worksheets.Count
        $WorkbookData=[ordered]@{}
        foreach($sheet in $Worksheets.GetEnumerator()) {
            Write-Progress -Id 0 -Activity $activity -PercentComplete ([int]($sheetIndex++/$sheetCount)*100)
            if($NamedColumns) {
                $content = (Read-WorksheetContent -Workbook $Workbook -Worksheet ($sheet.Value) -NamedColumns)
            } else {
                $content = (Read-WorksheetContent -Workbook $Workbook -Worksheet ($sheet.Value))
            }
            $WorkbookData.Add($sheet.Key, $content)
        }
        return [pscustomobject]$WorkbookData
    } Finally {
        Write-Progress -Id 1 -ParentId 0 -Activity "Closing Workbook" -PercentComplete 0
        if( $Workbook -ne $null ) {
            $Workbook.Close($false)
            [System.Runtime.InteropServices.Marshal]::ReleaseComObject($Workbook) | Out-Null
            $Workbook = $null
        }
        if( $Excel -ne $null ) {
            $Excel.Visible = $true
            [System.Runtime.InteropServices.Marshal]::ReleaseComObject($Excel) | Out-Null
            $Excel = $null
        }
        [System.GC]::Collect()
        [System.GC]::WaitForPendingFinalizers()
        Write-Progress -Id 1 -ParentId 0 -Activity "Closing Workbook" -PercentComplete 100  -Complete
        Write-Progress -Id 0 -Activity $activity -PercentComplete 100  -Complete
    }
}
}
Function Remove-CachedCredential {
<#
    .SYNOPSIS
        Remove (securely) stored credentials from the Registry
    .Description
        Remove (securely) stored credentials from the Registry
    .PARAMETER Name
        The credentials' name
    .PARAMETER RegistryHive
        The Registry hive to use (Default: HKCU:\SOFTWARE\MetaNullUtils)
    .Example
        Remove-CachedCredential -Name 'AtlassianToken'
    .Example
        # Remove all
        Remove-CachedCredential
#>
[CmdletBinding(SupportsShouldProcess)]
param (
    [Parameter(Mandatory,Position=0,ValueFromPipelineByPropertyName)]
    [string]$Name,

    [Parameter(Mandatory=$false)]
    [string]$RegistryHive = "HKCU:\SOFTWARE\MetaNullUtils"
)
Begin {
    $RegistryKey = "$RegistryHive\Credential"
}
Process {
    Get-Item -Path $RegistryKey | Get-ChildItem | Where-Object {
        ($_.PSChildName -eq ($Name -replace '\W+','-'))
    } | Foreach-Object {
        if($PSCmdlet.ShouldProcess("Registry Key '$($_.Name)' ")){
            $_ | Remove-Item | Write-Output
        }
    } 
}
}
Function Remove-ServiceExt {
<#
    .SYNOPSIS
        Remove a service by Name
    .PARAMETER PathExpression
        Search using REGEX on the service's binary Path
    .PARAMETER NameExpression
        Search using REGEX on the service's name
    .PARAMETER DisplayNameExpression
        Search using REGEX on the service's DisplayName
    .PARAMETER Service
        Search using the passed Service object
#>
[CmdletBinding(DefaultParameterSetName = 'Name',SupportsShouldProcess)]
[OutputType([void])]
param(
    [Parameter(Position=0,Mandatory=$false,ValueFromPipeline,ParameterSetName='Name')]
    [AllowEmptyString()]
    [AllowNull()]
    [string]$Name,

    [Parameter(Position=0,Mandatory,ParameterSetName='Path')]
    [string]$Path,

    [Parameter(Position=0,Mandatory,ParameterSetName='NameExpression')]
    [string]$NameExpression,

    [Parameter(Position=0,Mandatory,ParameterSetName='DisplayName')]
    [string]$DisplayName,

    [Parameter(Position=0,Mandatory,ParameterSetName='DisplayNameExpression')]
    [string]$DisplayNameExpression,

    [Parameter(Position=0,Mandatory,ValueFromPipeline,ParameterSetName='Service')]
    [System.ServiceProcess.ServiceController]$Service,

    [switch]$Stop
)
Process {
    $GetServiceExtParameters = $PSBoundParameters.PSObject.Copy()
    $GetServiceExtParameters.Remove('Stop') | Out-Null
    Get-ServiceExt @GetServiceExtParameters | Foreach-Object {
        if($PSCmdlet.ShouldProcess("Windows Service '$($_.ServiceName)' (Status: $($_.Status))")){
            if($Stop -and $_.Status -notin ([System.ServiceProcess.ServiceControllerStatus]::Stopped,[System.ServiceProcess.ServiceControllerStatus]::StopPending)) {
                "Stopping service '$($_.ServiceName)'" | Write-Verbose
                $_ | Stop-Service
            }

            "Removing service ServiceName" | Write-Verbose
            #if($PSVersionTable.PSVersion.Major -ge 6) {
            if( (Get-Command Remove-Service -ErrorAction SilentlyContinue)) {
                $_ | Remove-Service
            } else {
                &(Get-Command sc.exe) delete ($_.ServiceName)
                if( $LASTEXITCODE -ne 0 ) {
                    "Error removing service '$($_.ServiceName)' (sc.exe returned #$LASTEXITCODE)" | Write-Error
                }
            }
        }
    }
}
}
Function Select-Xpath {
<#
    .SYNOPSIS
        Use XPath on a blob of (xhtml) text
    .Description
        Use XPath on a blob of (xhtml) text.
    .PARAMETER InputDocument
        The input XML onwhich to run xpath
    .PARAMETER Xpath
        The xpath query string
    .PARAMETER Namespaces
        A list of additional NS to declare
    .EXAMPLE
        # Get URL from all hyperlinks in a page
        $Html | ConvertTo-XDocument | Select-Xpath -XPath '//a/@href/text()'
#>
[CmdletBinding()]
[OutputType([object[]])]
param (
    [Parameter(Mandatory,Position=0)]
    [string]$XPath,

    [Parameter(Position=1)]
    [AllowEmptyCollection()]
    [AllowNull()]
    [string[]]$Namespaces,

    [Parameter(ValueFromPipeline,Mandatory,Position=2)]
    [Alias('Document','Xml','Node')]
    [object]$InputDocument
)
Process {
    if($null -ne $Namespaces -and $Namespaces.Length) {
        $NS = @{}
        $Namespaces | ForEach-Object {
            $NS.$_ = $_
        }
        $InputDocument | Select-Xml -Namespace $NS -XPath $Xpath
    } else {
        $InputDocument | Select-Xml -XPath $Xpath
    }
}
}
Function Set-CachedCredential {
<#
    .SYNOPSIS
        Update stored credentials (securely) in the Registry
    .Description
        Update stored credentials (securely) in the Registry
    .PARAMETER InputCredential
        The credentials to store
    .PARAMETER Name
        The credentials' name
    .PARAMETER RegistryHive
        The Registry hive to use (Default: HKCU:\SOFTWARE\MetaNullUtils)
    .Example
        Set-CachedCredential -Name MyCredential -Credential (Get-Credential -Message 'Your credential')
#>
[CmdletBinding(SupportsShouldProcess)]
param (
    [Parameter(Mandatory,Position=0)]
    [String]$Name,

    [Parameter(Mandatory,ValueFromPipeline,Position=1)]
    [Alias('Credential')]
    [ValidateNotNull()]
    [System.Management.Automation.PSCredential]
    [System.Management.Automation.Credential()]
    $InputCredential,

    [Parameter(Mandatory=$false)]
    [string]$RegistryHive = "HKCU:\SOFTWARE\MetaNullUtils"
)
Process {
    $PSHPName = "$RegistryHive\Credential$($Name -replace '\W+','-')"
    if($PSCmdlet.ShouldProcess("Registry Key '$($PSHPName)' ")){
        $RegistryKey = "$RegistryHive\Credential"
        if(-not (Test-Path -Path $RegistryKey)) {
            New-Item -Path $RegistryKey -ErrorAction Stop -Force | Out-Null
        }
        Get-Item -Path (Join-Path $RegistryKey ($Name -replace '\W+','-')) -ErrorAction Stop | Foreach-Object {
            $_ | Set-ItemProperty -Name UserName -Value $InputCredential.UserName | Out-Null
            $_ | Set-ItemProperty -Name Password -Value ($InputCredential.Password | ConvertFrom-SecureString) | Out-Null
            $_ | Set-ItemProperty -Name LastAccess -Value (Get-Date -Format 'yyyyMMddHHmmss') | Out-Null
            $_ | Get-Item | Write-Output
        }
    }
}
}
Function Test-CachedCredential {
<#
    .SYNOPSIS
        Test the existance of a stored credentials
    .Description
        Test the existance of a stored credentials
    .PARAMETER Name
        The credentials' name
    .PARAMETER RegistryHive
        The Registry hive to use (Default: HKCU:\SOFTWARE\MetaNullUtils)
    .Example
        Test-CachedCredential -Name 'AtlassianToken'
#>
[CmdletBinding()]
[OutputType([System.Management.Automation.PSCredential])]
param (
    [Parameter(Mandatory,Position=0,ValueFromPipelineByPropertyName)]
    [string]$Name,

    [Parameter(Mandatory=$false)]
    [string]$RegistryHive = "HKCU:\SOFTWARE\MetaNullUtils"
)
Begin {
    $RegistryKey = "$RegistryHive\Credential"
}
Process {
    $Exists = Get-Item -Path $RegistryKey | Get-ChildItem | Where-Object {
        ($_.PSChildName -eq ($Name -replace '\W+','-'))
    }
    if($Exists) {
        return $true
    }
    return $false
}
}
Function Test-ElevatedPrivilege {
<#
    .SYNOPSIS
        Test if the current powershell is running with Elevated Privileges
    .Description
        Test if the current powershell is running with Elevated Privileges
    .Example
        Test-ElevatedPrivilege
#>
[CmdletBinding()]
[OutputType([bool])]
param (
)
Process {
    ([Security.Principal.WindowsPrincipal][Security.Principal.WindowsIdentity]::GetCurrent()).IsInRole([Security.Principal.WindowsBuiltInRole]::Administrator) | Write-Output
}
}
Function Test-FileName {
<#
    .SYNOPSIS
        Test if a string is suitable for a filename
    .Description
        Test if a string is suitable for a filename by checking if it contains any forbidden characeter
    .PARAMETER InputString
        The input string
    .Example
        'MetaNull.txt' | Test-FileName
#>
[CmdletBinding()]
[OutputType([bool])]
param (
    # string to process
    [Parameter(ValueFromPipeline, Position=0)]
    [AllowEmptyString()]
    [Alias('String')]
    [String]$InputString
)
Begin {
    $InvalidChars = [System.IO.Path]::GetInvalidFileNameChars() -join ''
    $RegExInvalid = "[{0}]" -f [RegEx]::Escape($InvalidChars)
}
Process {
    $InputString -notmatch $RegExInvalid | Write-Output
}
}
Function Test-Label {
<#
    .SYNOPSIS
        Test the format of a "label".
    .DESCRIPTION
        Test the format of a "label".
        - The label is made of lower case alpha numerical characters
            separated by single dash characters.
        - It always starts by a letter.
        - No trailing dashes at the end.
    .EXAMPLE
        # Test the format of a label
        "SC.IIT.DIS.3" | Test-Label # -> $false
    .EXAMPLE
        # Test the format of a label
        "sc-iit-dis-3" | Test-Label # -> $true
#>
[CmdletBinding()]
[OutputType([String])]
param (
    # String to encode
    [Parameter(ValueFromPipeline, Position=0)]
    [AllowEmptyString()]
    [Alias('String')]
    [string]$InputString
)
Process {
    $InputString -cmatch '^[a-z][a-z0-9-]+[a-z0-9]$' | Write-Output
}
}