PSWriteOffice.psm1
|
. $PSScriptRoot\PSWriteOffice.Libraries.ps1 function ConvertFrom-Color { [alias('Convert-FromColor')] [CmdletBinding()] param ([ValidateScript({ if ($($_ -in $Script:RGBColors.Keys -or $_ -match "^#([A-Fa-f0-9]{6})$" -or $_ -eq "") -eq $false) { throw "The Input value is not a valid colorname nor an valid color hex code." } else { $true } })] [alias('Colors')][string[]] $Color, [switch] $AsDecimal, [switch] $AsDrawingColor) $Colors = foreach ($C in $Color) { $Value = $Script:RGBColors."$C" if ($C -match "^#([A-Fa-f0-9]{6})$") { $C continue } if ($null -eq $Value) { continue } $HexValue = Convert-Color -RGB $Value Write-Verbose "Convert-FromColor - Color Name: $C Value: $Value HexValue: $HexValue" if ($AsDecimal) { [Convert]::ToInt64($HexValue, 16) } elseif ($AsDrawingColor) { [System.Drawing.Color]::FromArgb("#$($HexValue)") } else { "#$($HexValue)" } } $Colors } function Get-FileName { <# .SYNOPSIS Short description .DESCRIPTION Long description .PARAMETER Extension Parameter description .PARAMETER Temporary Parameter description .PARAMETER TemporaryFileOnly Parameter description .EXAMPLE Get-FileName -Temporary Output: 3ymsxvav.tmp .EXAMPLE Get-FileName -Temporary Output: C:\Users\pklys\AppData\Local\Temp\tmpD74C.tmp .EXAMPLE Get-FileName -Temporary -Extension 'xlsx' Output: C:\Users\pklys\AppData\Local\Temp\tmp45B6.xlsx .NOTES General notes #> [CmdletBinding()] param([string] $Extension = 'tmp', [switch] $Temporary, [switch] $TemporaryFileOnly) if ($Temporary) { return "$($([System.IO.Path]::GetTempFileName()).Replace('.tmp','')).$Extension" } if ($TemporaryFileOnly) { return "$($([System.IO.Path]::GetRandomFileName()).Split('.')[0]).$Extension" } } function Remove-EmptyValue { [alias('Remove-EmptyValues')] [CmdletBinding()] param([alias('Splat', 'IDictionary')][Parameter(Mandatory)][System.Collections.IDictionary] $Hashtable, [string[]] $ExcludeParameter, [switch] $Recursive, [int] $Rerun, [switch] $DoNotRemoveNull, [switch] $DoNotRemoveEmpty, [switch] $DoNotRemoveEmptyArray, [switch] $DoNotRemoveEmptyDictionary) foreach ($Key in [string[]] $Hashtable.Keys) { if ($Key -notin $ExcludeParameter) { if ($Recursive) { if ($Hashtable[$Key] -is [System.Collections.IDictionary]) { if ($Hashtable[$Key].Count -eq 0) { if (-not $DoNotRemoveEmptyDictionary) { $Hashtable.Remove($Key) } } else { Remove-EmptyValue -Hashtable $Hashtable[$Key] -Recursive:$Recursive } } else { if (-not $DoNotRemoveNull -and $null -eq $Hashtable[$Key]) { $Hashtable.Remove($Key) } elseif (-not $DoNotRemoveEmpty -and $Hashtable[$Key] -is [string] -and $Hashtable[$Key] -eq '') { $Hashtable.Remove($Key) } elseif (-not $DoNotRemoveEmptyArray -and $Hashtable[$Key] -is [System.Collections.IList] -and $Hashtable[$Key].Count -eq 0) { $Hashtable.Remove($Key) } } } else { if (-not $DoNotRemoveNull -and $null -eq $Hashtable[$Key]) { $Hashtable.Remove($Key) } elseif (-not $DoNotRemoveEmpty -and $Hashtable[$Key] -is [string] -and $Hashtable[$Key] -eq '') { $Hashtable.Remove($Key) } elseif (-not $DoNotRemoveEmptyArray -and $Hashtable[$Key] -is [System.Collections.IList] -and $Hashtable[$Key].Count -eq 0) { $Hashtable.Remove($Key) } } } } if ($Rerun) { for ($i = 0; $i -lt $Rerun; $i++) { Remove-EmptyValue -Hashtable $Hashtable -Recursive:$Recursive } } } function Select-Properties { <# .SYNOPSIS Allows for easy selecting property names from one or multiple objects .DESCRIPTION Allows for easy selecting property names from one or multiple objects. This is especially useful with using AllProperties parameter where we want to make sure to get all properties from all objects. .PARAMETER Objects One or more objects .PARAMETER Property Properties to include .PARAMETER ExcludeProperty Properties to exclude .PARAMETER AllProperties All unique properties from all objects .PARAMETER PropertyNameReplacement Default property name when object has no properties .EXAMPLE $Object1 = [PSCustomobject] @{ Name1 = '1' Name2 = '3' Name3 = '5' } $Object2 = [PSCustomobject] @{ Name4 = '2' Name5 = '6' Name6 = '7' } Select-Properties -Objects $Object1, $Object2 -AllProperties #OR: $Object1, $Object2 | Select-Properties -AllProperties -ExcludeProperty Name6 -Property Name3 .EXAMPLE $Object3 = [Ordered] @{ Name1 = '1' Name2 = '3' Name3 = '5' } $Object4 = [Ordered] @{ Name4 = '2' Name5 = '6' Name6 = '7' } Select-Properties -Objects $Object3, $Object4 -AllProperties $Object3, $Object4 | Select-Properties -AllProperties .NOTES General notes #> [CmdLetBinding()] param([Array][Parameter(Position = 0, ValueFromPipeline, ValueFromPipelineByPropertyName)] $Objects, [string[]] $Property, [string[]] $ExcludeProperty, [switch] $AllProperties, [string] $PropertyNameReplacement = '*') Begin { function Select-Unique { [CmdLetBinding()] param([System.Collections.IList] $Object) $New = $Object.ToLower() | Select-Object -Unique $Selected = foreach ($_ in $New) { $Index = $Object.ToLower().IndexOf($_) if ($Index -ne -1) { $Object[$Index] } } $Selected } $ObjectsList = [System.Collections.Generic.List[Object]]::new() } Process { foreach ($Object in $Objects) { $ObjectsList.Add($Object) } } End { if ($ObjectsList.Count -eq 0) { Write-Warning 'Select-Properties - Unable to process. Objects count equals 0.' return } if ($ObjectsList[0] -is [System.Collections.IDictionary]) { if ($AllProperties) { [Array] $All = foreach ($_ in $ObjectsList) { $_.Keys } $FirstObjectProperties = Select-Unique -Object $All } else { $FirstObjectProperties = $ObjectsList[0].Keys } if ($Property.Count -gt 0 -and $ExcludeProperty.Count -gt 0) { $FirstObjectProperties = foreach ($_ in $FirstObjectProperties) { if ($Property -contains $_ -and $ExcludeProperty -notcontains $_) { $_ continue } } } elseif ($Property.Count -gt 0) { $FirstObjectProperties = foreach ($_ in $FirstObjectProperties) { if ($Property -contains $_) { $_ continue } } } elseif ($ExcludeProperty.Count -gt 0) { $FirstObjectProperties = foreach ($_ in $FirstObjectProperties) { if ($ExcludeProperty -notcontains $_) { $_ continue } } } } elseif ($ObjectsList[0].GetType().Name -match 'bool|byte|char|datetime|decimal|double|ExcelHyperLink|float|int|long|sbyte|short|string|timespan|uint|ulong|URI|ushort') { $FirstObjectProperties = $PropertyNameReplacement } else { if ($Property.Count -gt 0 -and $ExcludeProperty.Count -gt 0) { $ObjectsList = $ObjectsList | Select-Object -Property $Property -ExcludeProperty $ExcludeProperty } elseif ($Property.Count -gt 0) { $ObjectsList = $ObjectsList | Select-Object -Property $Property } elseif ($ExcludeProperty.Count -gt 0) { $ObjectsList = $ObjectsList | Select-Object -Property '*' -ExcludeProperty $ExcludeProperty } if ($AllProperties) { [Array] $All = foreach ($_ in $ObjectsList) { $_.PSObject.Properties.Name } $FirstObjectProperties = Select-Unique -Object $All } else { $FirstObjectProperties = $ObjectsList[0].PSObject.Properties.Name } } $FirstObjectProperties } } function Convert-Color { <# .Synopsis This color converter gives you the hexadecimal values of your RGB colors and vice versa (RGB to HEX) .Description This color converter gives you the hexadecimal values of your RGB colors and vice versa (RGB to HEX). Use it to convert your colors and prepare your graphics and HTML web pages. .Parameter RBG Enter the Red Green Blue value comma separated. Red: 51 Green: 51 Blue: 204 for example needs to be entered as 51,51,204 .Parameter HEX Enter the Hex value to be converted. Do not use the '#' symbol. (Ex: 3333CC converts to Red: 51 Green: 51 Blue: 204) .Example .\convert-color -hex FFFFFF Converts hex value FFFFFF to RGB .Example .\convert-color -RGB 123,200,255 Converts Red = 123 Green = 200 Blue = 255 to Hex value #> param([Parameter(ParameterSetName = "RGB", Position = 0)] [ValidateScript({ $_ -match '^([01]?[0-9]?[0-9]|2[0-4][0-9]|25[0-5])$' })] $RGB, [Parameter(ParameterSetName = "HEX", Position = 0)] [ValidateScript({ $_ -match '[A-Fa-f0-9]{6}' })] [string] $HEX) switch ($PsCmdlet.ParameterSetName) { "RGB" { if ($null -eq $RGB[2]) { Write-Error "Value missing. Please enter all three values seperated by comma." } $red = [convert]::Tostring($RGB[0], 16) $green = [convert]::Tostring($RGB[1], 16) $blue = [convert]::Tostring($RGB[2], 16) if ($red.Length -eq 1) { $red = '0' + $red } if ($green.Length -eq 1) { $green = '0' + $green } if ($blue.Length -eq 1) { $blue = '0' + $blue } Write-Output $red$green$blue } "HEX" { $red = $HEX.Remove(2, 4) $Green = $HEX.Remove(4, 2) $Green = $Green.remove(0, 2) $Blue = $hex.Remove(0, 4) $Red = [convert]::ToInt32($red, 16) $Green = [convert]::ToInt32($green, 16) $Blue = [convert]::ToInt32($blue, 16) Write-Output $red, $Green, $blue } } } function New-OfficeExcelTableOld { [cmdletBinding()] param([Array] $DataTable, [Parameter(Mandatory)][int] $Row, [Parameter(Mandatory)][int] $Column, $Worksheet, [switch] $AllProperties, [switch] $ReturnObject, [ClosedXML.Excel.XLTransposeOptions] $Transpose, [switch] $ShowRowStripes, [switch] $ShowColumnStripes, [switch] $DisableAutoFilter, [switch] $HideHeaderRow, [switch] $ShowTotalsRow, [switch] $EmphasizeFirstColumn, [switch] $EmphasizeLastColumn, [string] $Theme) if ($Script:OfficeTrackerExcel -and -not $Worksheet) { $WorkSheet = $Script:OfficeTrackerExcel['WorkSheet'] } [System.Data.DataTable] $Table = [System.Data.DataTable]::new('MyTable') if ($DataTable.Count -eq 0) { return } if ($DataTable[0] -isnot [System.Collections.IDictionary]) { $Properties = Select-Properties -Objects $DataTable -AllProperties:$AllProperties -Property $IncludeProperty -ExcludeProperty $ExcludeProperty -IncludeTypes } else { $Properties = 'Name', 'Value' } if ($Properties.Name -eq '*') { try { $TableOutput = $Worksheet.cell($Row, $Column).InsertTable($DataTable) } catch { if ($PSBoundParameters.ErrorAction -eq 'Stop') { throw } else { Write-Warning -Message "New-OfficeExcelTable - Error occured: $($_.Exception.Message)" } } } else { for ($i = 0; $i -lt $Properties['Name'].Count; $i++) { $KnownTypes = 'bool|byte|char|datetime|decimal|double|float|int|long|sbyte|short|string|timespan|uint|ulong|URI|ushort' if ($Properties['Type'][$i] -match $KnownTypes) { $null = $table.Columns.Add($Properties['Name'][$i], [string]) } else { $null = $table.Columns.Add($Properties['Name'][$i], [string]) } } foreach ($Object in $DataTable) { if ($Object -is [System.Collections.IDictionary]) { foreach ($Entry in $Object.Keys) { $null = $table.Rows.Add($Entry, $Object[$Entry]) } } elseif ($Object.GetType().Name -match 'bool|byte|char|datetime|decimal|double|ExcelHyperLink|float|int|long|sbyte|short|string|timespan|uint|ulong|URI|ushort') {} else { $Values = foreach ($Property in $Properties.Name) { $Value = $Object.$Property if ($Value.Count -gt 1) { $Value = $Value -join ',' } $Value } try { $null = $table.Rows.Add($Values) } catch { Write-Warning -Message "New-OfficeExcelTable - Error when adding values to row $($_.Exception.Message). Skipping." } } } try { $TableOutput = $Worksheet.cell($Row, $Column).InsertTable($Table) } catch { if ($PSBoundParameters.ErrorAction -eq 'Stop') { throw } else { Write-Warning -Message "New-OfficeExcelTable - Error occured: $($_.Exception.Message)" } } } $SplatOptions = @{Table = $TableOutput Transpose = $Transpose ShowRowStripes = $ShowRowStripes.IsPresent ShowColumnStripes = $ShowColumnStripes.IsPresent DisableAutoFilter = $DisableAutoFilter.IsPresent HideHeaderRow = $HideHeaderRow.IsPresent ShowTotalsRow = $ShowTotalsRow.IsPresent EmphasizeFirstColumn = $EmphasizeFirstColumn.IsPresent EmphasizeLastColumn = $EmphasizeLastColumn.IsPresent Theme = $Theme } Remove-EmptyValue -Hashtable $SplatOptions New-OfficeExcelTableOptions @SplatOptions } $Script:ScriptBlockThemes = { param($commandName, $parameterName, $wordToComplete, $commandAst, $fakeBoundParameters) [ClosedXML.Excel.XLTableTheme]::GetAllThemes() | Where-Object { $_ -like "*$wordToComplete*" } } Register-ArgumentCompleter -CommandName New-OfficeExcelTable -ParameterName Theme -ScriptBlock $Script:ScriptBlockThemes $Script:RGBColors = [ordered] @{None = $null AirForceBlue = 93, 138, 168 Akaroa = 195, 176, 145 AlbescentWhite = 227, 218, 201 AliceBlue = 240, 248, 255 Alizarin = 227, 38, 54 Allports = 18, 97, 128 Almond = 239, 222, 205 AlmondFrost = 159, 129, 112 Amaranth = 229, 43, 80 Amazon = 59, 122, 87 Amber = 255, 191, 0 Amethyst = 153, 102, 204 AmethystSmoke = 156, 138, 164 AntiqueWhite = 250, 235, 215 Apple = 102, 180, 71 AppleBlossom = 176, 92, 82 Apricot = 251, 206, 177 Aqua = 0, 255, 255 Aquamarine = 127, 255, 212 Armygreen = 75, 83, 32 Arsenic = 59, 68, 75 Astral = 54, 117, 136 Atlantis = 164, 198, 57 Atomic = 65, 74, 76 AtomicTangerine = 255, 153, 102 Axolotl = 99, 119, 91 Azure = 240, 255, 255 Bahia = 176, 191, 26 BakersChocolate = 93, 58, 26 BaliHai = 124, 152, 171 BananaMania = 250, 231, 181 BattleshipGrey = 85, 93, 80 BayOfMany = 35, 48, 103 Beige = 245, 245, 220 Bermuda = 136, 216, 192 Bilbao = 42, 128, 0 BilobaFlower = 181, 126, 220 Bismark = 83, 104, 114 Bisque = 255, 228, 196 Bistre = 61, 43, 31 Bittersweet = 254, 111, 94 Black = 0, 0, 0 BlackPearl = 31, 38, 42 BlackRose = 85, 31, 47 BlackRussian = 23, 24, 43 BlanchedAlmond = 255, 235, 205 BlizzardBlue = 172, 229, 238 Blue = 0, 0, 255 BlueDiamond = 77, 26, 127 BlueMarguerite = 115, 102, 189 BlueSmoke = 115, 130, 118 BlueViolet = 138, 43, 226 Blush = 169, 92, 104 BokaraGrey = 22, 17, 13 Bole = 121, 68, 59 BondiBlue = 0, 147, 175 Bordeaux = 88, 17, 26 Bossanova = 86, 60, 92 Boulder = 114, 116, 114 Bouquet = 183, 132, 167 Bourbon = 170, 108, 57 Brass = 181, 166, 66 BrickRed = 199, 44, 72 BrightGreen = 102, 255, 0 BrightRed = 146, 43, 62 BrightTurquoise = 8, 232, 222 BrilliantRose = 243, 100, 162 BrinkPink = 250, 110, 121 BritishRacingGreen = 0, 66, 37 Bronze = 205, 127, 50 Brown = 165, 42, 42 BrownPod = 57, 24, 2 BuddhaGold = 202, 169, 6 Buff = 240, 220, 130 Burgundy = 128, 0, 32 BurlyWood = 222, 184, 135 BurntOrange = 255, 117, 56 BurntSienna = 233, 116, 81 BurntUmber = 138, 51, 36 ButteredRum = 156, 124, 56 CadetBlue = 95, 158, 160 California = 224, 141, 60 CamouflageGreen = 120, 134, 107 Canary = 255, 255, 153 CanCan = 217, 134, 149 CannonPink = 145, 78, 117 CaputMortuum = 89, 39, 32 Caramel = 255, 213, 154 Cararra = 237, 230, 214 Cardinal = 179, 33, 52 CardinGreen = 18, 53, 36 CareysPink = 217, 152, 160 CaribbeanGreen = 0, 222, 164 Carmine = 175, 0, 42 CarnationPink = 255, 166, 201 CarrotOrange = 242, 142, 28 Cascade = 141, 163, 153 CatskillWhite = 226, 229, 222 Cedar = 67, 48, 46 Celadon = 172, 225, 175 Celeste = 207, 207, 196 Cello = 55, 79, 107 Cement = 138, 121, 93 Cerise = 222, 49, 99 Cerulean = 0, 123, 167 CeruleanBlue = 42, 82, 190 Chantilly = 239, 187, 204 Chardonnay = 255, 200, 124 Charlotte = 167, 216, 222 Charm = 208, 116, 139 Chartreuse = 127, 255, 0 ChartreuseYellow = 223, 255, 0 ChelseaCucumber = 135, 169, 107 Cherub = 246, 214, 222 Chestnut = 185, 78, 72 ChileanFire = 226, 88, 34 Chinook = 150, 200, 162 Chocolate = 210, 105, 30 Christi = 125, 183, 0 Christine = 181, 101, 30 Cinnabar = 235, 76, 66 Citron = 159, 169, 31 Citrus = 141, 182, 0 Claret = 95, 25, 51 ClassicRose = 251, 204, 231 ClayCreek = 145, 129, 81 Clinker = 75, 54, 33 Clover = 74, 93, 35 Cobalt = 0, 71, 171 CocoaBrown = 44, 22, 8 Cola = 60, 48, 36 ColumbiaBlue = 166, 231, 255 CongoBrown = 103, 76, 71 Conifer = 178, 236, 93 Copper = 218, 138, 103 CopperRose = 153, 102, 102 Coral = 255, 127, 80 CoralRed = 255, 64, 64 CoralTree = 173, 111, 105 Coriander = 188, 184, 138 Corn = 251, 236, 93 CornField = 250, 240, 190 Cornflower = 147, 204, 234 CornflowerBlue = 100, 149, 237 Cornsilk = 255, 248, 220 Cosmic = 132, 63, 91 Cosmos = 255, 204, 203 CostaDelSol = 102, 93, 30 CottonCandy = 255, 188, 217 Crail = 164, 90, 82 Cranberry = 205, 96, 126 Cream = 255, 255, 204 CreamCan = 242, 198, 73 Crimson = 220, 20, 60 Crusta = 232, 142, 90 Cumulus = 255, 255, 191 Cupid = 246, 173, 198 CuriousBlue = 40, 135, 200 Cyan = 0, 255, 255 Cyprus = 6, 78, 64 DaisyBush = 85, 53, 146 Dandelion = 250, 218, 94 Danube = 96, 130, 182 DarkBlue = 0, 0, 139 DarkBrown = 101, 67, 33 DarkCerulean = 8, 69, 126 DarkChestnut = 152, 105, 96 DarkCoral = 201, 90, 73 DarkCyan = 0, 139, 139 DarkGoldenrod = 184, 134, 11 DarkGray = 169, 169, 169 DarkGreen = 0, 100, 0 DarkGreenCopper = 73, 121, 107 DarkGrey = 169, 169, 169 DarkKhaki = 189, 183, 107 DarkMagenta = 139, 0, 139 DarkOliveGreen = 85, 107, 47 DarkOrange = 255, 140, 0 DarkOrchid = 153, 50, 204 DarkPastelGreen = 3, 192, 60 DarkPink = 222, 93, 131 DarkPurple = 150, 61, 127 DarkRed = 139, 0, 0 DarkSalmon = 233, 150, 122 DarkSeaGreen = 143, 188, 143 DarkSlateBlue = 72, 61, 139 DarkSlateGray = 47, 79, 79 DarkSlateGrey = 47, 79, 79 DarkSpringGreen = 23, 114, 69 DarkTangerine = 255, 170, 29 DarkTurquoise = 0, 206, 209 DarkViolet = 148, 0, 211 DarkWood = 130, 102, 68 DeepBlush = 245, 105, 145 DeepCerise = 224, 33, 138 DeepKoamaru = 51, 51, 102 DeepLilac = 153, 85, 187 DeepMagenta = 204, 0, 204 DeepPink = 255, 20, 147 DeepSea = 14, 124, 97 DeepSkyBlue = 0, 191, 255 DeepTeal = 24, 69, 59 Denim = 36, 107, 206 DesertSand = 237, 201, 175 DimGray = 105, 105, 105 DimGrey = 105, 105, 105 DodgerBlue = 30, 144, 255 Dolly = 242, 242, 122 Downy = 95, 201, 191 DutchWhite = 239, 223, 187 EastBay = 76, 81, 109 EastSide = 178, 132, 190 EchoBlue = 169, 178, 195 Ecru = 194, 178, 128 Eggplant = 162, 0, 109 EgyptianBlue = 16, 52, 166 ElectricBlue = 125, 249, 255 ElectricIndigo = 111, 0, 255 ElectricLime = 208, 255, 20 ElectricPurple = 191, 0, 255 Elm = 47, 132, 124 Emerald = 80, 200, 120 Eminence = 108, 48, 130 Endeavour = 46, 88, 148 EnergyYellow = 245, 224, 80 Espresso = 74, 44, 42 Eucalyptus = 26, 162, 96 Falcon = 126, 94, 96 Fallow = 204, 153, 102 FaluRed = 128, 24, 24 Feldgrau = 77, 93, 83 Feldspar = 205, 149, 117 Fern = 113, 188, 120 FernGreen = 79, 121, 66 Festival = 236, 213, 64 Finn = 97, 64, 81 FireBrick = 178, 34, 34 FireBush = 222, 143, 78 FireEngineRed = 211, 33, 45 Flamingo = 233, 92, 75 Flax = 238, 220, 130 FloralWhite = 255, 250, 240 ForestGreen = 34, 139, 34 Frangipani = 250, 214, 165 FreeSpeechAquamarine = 0, 168, 119 FreeSpeechRed = 204, 0, 0 FrenchLilac = 230, 168, 215 FrenchRose = 232, 83, 149 FriarGrey = 135, 134, 129 Froly = 228, 113, 122 Fuchsia = 255, 0, 255 FuchsiaPink = 255, 119, 255 Gainsboro = 220, 220, 220 Gallery = 219, 215, 210 Galliano = 204, 160, 29 Gamboge = 204, 153, 0 Ghost = 196, 195, 208 GhostWhite = 248, 248, 255 Gin = 216, 228, 188 GinFizz = 247, 231, 206 Givry = 230, 208, 171 Glacier = 115, 169, 194 Gold = 255, 215, 0 GoldDrop = 213, 108, 43 GoldenBrown = 150, 113, 23 GoldenFizz = 240, 225, 48 GoldenGlow = 248, 222, 126 GoldenPoppy = 252, 194, 0 Goldenrod = 218, 165, 32 GoldenSand = 233, 214, 107 GoldenYellow = 253, 238, 0 GoldTips = 225, 189, 39 GordonsGreen = 37, 53, 41 Gorse = 255, 225, 53 Gossamer = 49, 145, 119 GrannySmithApple = 168, 228, 160 Gray = 128, 128, 128 GrayAsparagus = 70, 89, 69 Green = 0, 128, 0 GreenLeaf = 76, 114, 29 GreenVogue = 38, 67, 72 GreenYellow = 173, 255, 47 Grey = 128, 128, 128 GreyAsparagus = 70, 89, 69 GuardsmanRed = 157, 41, 51 GumLeaf = 178, 190, 181 Gunmetal = 42, 52, 57 Hacienda = 155, 135, 12 HalfAndHalf = 232, 228, 201 HalfBaked = 95, 138, 139 HalfColonialWhite = 246, 234, 190 HalfPearlLusta = 240, 234, 214 HanPurple = 63, 0, 255 Harlequin = 74, 255, 0 HarleyDavidsonOrange = 194, 59, 34 Heather = 174, 198, 207 Heliotrope = 223, 115, 255 Hemp = 161, 122, 116 Highball = 134, 126, 54 HippiePink = 171, 75, 82 Hoki = 110, 127, 128 HollywoodCerise = 244, 0, 161 Honeydew = 240, 255, 240 Hopbush = 207, 113, 175 HorsesNeck = 108, 84, 30 HotPink = 255, 105, 180 HummingBird = 201, 255, 229 HunterGreen = 53, 94, 59 Illusion = 244, 152, 173 InchWorm = 202, 224, 13 IndianRed = 205, 92, 92 Indigo = 75, 0, 130 InternationalKleinBlue = 0, 24, 168 InternationalOrange = 255, 79, 0 IrisBlue = 28, 169, 201 IrishCoffee = 102, 66, 40 IronsideGrey = 113, 112, 110 IslamicGreen = 0, 144, 0 Ivory = 255, 255, 240 Jacarta = 61, 50, 93 JackoBean = 65, 54, 40 JacksonsPurple = 46, 45, 136 Jade = 0, 171, 102 JapaneseLaurel = 47, 117, 50 Jazz = 93, 43, 44 JazzberryJam = 165, 11, 94 JellyBean = 68, 121, 142 JetStream = 187, 208, 201 Jewel = 0, 107, 60 Jon = 79, 58, 60 JordyBlue = 124, 185, 232 Jumbo = 132, 132, 130 JungleGreen = 41, 171, 135 KaitokeGreen = 30, 77, 43 Karry = 255, 221, 202 KellyGreen = 70, 203, 24 Keppel = 93, 164, 147 Khaki = 240, 230, 140 Killarney = 77, 140, 87 KingfisherDaisy = 85, 27, 140 Kobi = 230, 143, 172 LaPalma = 60, 141, 13 LaserLemon = 252, 247, 94 Laurel = 103, 146, 103 Lavender = 230, 230, 250 LavenderBlue = 204, 204, 255 LavenderBlush = 255, 240, 245 LavenderPink = 251, 174, 210 LavenderRose = 251, 160, 227 LawnGreen = 124, 252, 0 LemonChiffon = 255, 250, 205 LightBlue = 173, 216, 230 LightCoral = 240, 128, 128 LightCyan = 224, 255, 255 LightGoldenrodYellow = 250, 250, 210 LightGray = 211, 211, 211 LightGreen = 144, 238, 144 LightGrey = 211, 211, 211 LightPink = 255, 182, 193 LightSalmon = 255, 160, 122 LightSeaGreen = 32, 178, 170 LightSkyBlue = 135, 206, 250 LightSlateGray = 119, 136, 153 LightSlateGrey = 119, 136, 153 LightSteelBlue = 176, 196, 222 LightYellow = 255, 255, 224 Lilac = 204, 153, 204 Lime = 0, 255, 0 LimeGreen = 50, 205, 50 Limerick = 139, 190, 27 Linen = 250, 240, 230 Lipstick = 159, 43, 104 Liver = 83, 75, 79 Lochinvar = 86, 136, 125 Lochmara = 38, 97, 156 Lola = 179, 158, 181 LondonHue = 170, 152, 169 Lotus = 124, 72, 72 LuckyPoint = 29, 41, 81 MacaroniAndCheese = 255, 189, 136 Madang = 193, 249, 162 Madras = 81, 65, 0 Magenta = 255, 0, 255 MagicMint = 170, 240, 209 Magnolia = 248, 244, 255 Mahogany = 215, 59, 62 Maire = 27, 24, 17 Maize = 230, 190, 138 Malachite = 11, 218, 81 Malibu = 93, 173, 236 Malta = 169, 154, 134 Manatee = 140, 146, 172 Mandalay = 176, 121, 57 MandarianOrange = 146, 39, 36 Mandy = 191, 79, 81 Manhattan = 229, 170, 112 Mantis = 125, 194, 66 Manz = 217, 230, 80 MardiGras = 48, 25, 52 Mariner = 57, 86, 156 Maroon = 128, 0, 0 Matterhorn = 85, 85, 85 Mauve = 244, 187, 255 Mauvelous = 255, 145, 175 MauveTaupe = 143, 89, 115 MayaBlue = 119, 181, 254 McKenzie = 129, 97, 60 MediumAquamarine = 102, 205, 170 MediumBlue = 0, 0, 205 MediumCarmine = 175, 64, 53 MediumOrchid = 186, 85, 211 MediumPurple = 147, 112, 219 MediumRedViolet = 189, 51, 164 MediumSeaGreen = 60, 179, 113 MediumSlateBlue = 123, 104, 238 MediumSpringGreen = 0, 250, 154 MediumTurquoise = 72, 209, 204 MediumVioletRed = 199, 21, 133 MediumWood = 166, 123, 91 Melon = 253, 188, 180 Merlot = 112, 54, 66 MetallicGold = 211, 175, 55 Meteor = 184, 115, 51 MidnightBlue = 25, 25, 112 MidnightExpress = 0, 20, 64 Mikado = 60, 52, 31 MilanoRed = 168, 55, 49 Ming = 54, 116, 125 MintCream = 245, 255, 250 MintGreen = 152, 255, 152 Mischka = 168, 169, 173 MistyRose = 255, 228, 225 Moccasin = 255, 228, 181 Mojo = 149, 69, 53 MonaLisa = 255, 153, 153 Mongoose = 179, 139, 109 Montana = 53, 56, 57 MoodyBlue = 116, 108, 192 MoonYellow = 245, 199, 26 MossGreen = 173, 223, 173 MountainMeadow = 28, 172, 120 MountainMist = 161, 157, 148 MountbattenPink = 153, 122, 141 Mulberry = 211, 65, 157 Mustard = 255, 219, 88 Myrtle = 25, 89, 5 MySin = 255, 179, 71 NavajoWhite = 255, 222, 173 Navy = 0, 0, 128 NavyBlue = 2, 71, 254 NeonCarrot = 255, 153, 51 NeonPink = 255, 92, 205 Nepal = 145, 163, 176 Nero = 20, 20, 20 NewMidnightBlue = 0, 0, 156 Niagara = 58, 176, 158 NightRider = 59, 47, 47 Nobel = 152, 152, 152 Norway = 169, 186, 157 Nugget = 183, 135, 39 OceanGreen = 95, 167, 120 Ochre = 202, 115, 9 OldCopper = 111, 78, 55 OldGold = 207, 181, 59 OldLace = 253, 245, 230 OldLavender = 121, 104, 120 OldRose = 195, 33, 72 Olive = 128, 128, 0 OliveDrab = 107, 142, 35 OliveGreen = 181, 179, 92 Olivetone = 110, 110, 48 Olivine = 154, 185, 115 Onahau = 196, 216, 226 Opal = 168, 195, 188 Orange = 255, 165, 0 OrangePeel = 251, 153, 2 OrangeRed = 255, 69, 0 Orchid = 218, 112, 214 OuterSpace = 45, 56, 58 OutrageousOrange = 254, 90, 29 Oxley = 95, 167, 119 PacificBlue = 0, 136, 220 Padua = 128, 193, 151 PalatinatePurple = 112, 41, 99 PaleBrown = 160, 120, 90 PaleChestnut = 221, 173, 175 PaleCornflowerBlue = 188, 212, 230 PaleGoldenrod = 238, 232, 170 PaleGreen = 152, 251, 152 PaleMagenta = 249, 132, 239 PalePink = 250, 218, 221 PaleSlate = 201, 192, 187 PaleTaupe = 188, 152, 126 PaleTurquoise = 175, 238, 238 PaleVioletRed = 219, 112, 147 PalmLeaf = 53, 66, 48 Panache = 233, 255, 219 PapayaWhip = 255, 239, 213 ParisDaisy = 255, 244, 79 Parsley = 48, 96, 48 PastelGreen = 119, 221, 119 PattensBlue = 219, 233, 244 Peach = 255, 203, 164 PeachOrange = 255, 204, 153 PeachPuff = 255, 218, 185 PeachYellow = 250, 223, 173 Pear = 209, 226, 49 PearlLusta = 234, 224, 200 Pelorous = 42, 143, 189 Perano = 172, 172, 230 Periwinkle = 197, 203, 225 PersianBlue = 34, 67, 182 PersianGreen = 0, 166, 147 PersianIndigo = 51, 0, 102 PersianPink = 247, 127, 190 PersianRed = 192, 54, 44 PersianRose = 233, 54, 167 Persimmon = 236, 88, 0 Peru = 205, 133, 63 Pesto = 128, 117, 50 PictonBlue = 102, 153, 204 PigmentGreen = 0, 173, 67 PigPink = 255, 218, 233 PineGreen = 1, 121, 111 PineTree = 42, 47, 35 Pink = 255, 192, 203 PinkFlare = 191, 175, 178 PinkLace = 240, 211, 220 PinkSwan = 179, 179, 179 Plum = 221, 160, 221 Pohutukawa = 102, 12, 33 PoloBlue = 119, 158, 203 Pompadour = 129, 20, 83 Portage = 146, 161, 207 PotPourri = 241, 221, 207 PottersClay = 132, 86, 60 PowderBlue = 176, 224, 230 Prim = 228, 196, 207 PrussianBlue = 0, 58, 108 PsychedelicPurple = 223, 0, 255 Puce = 204, 136, 153 Pueblo = 108, 46, 31 PuertoRico = 67, 179, 174 Pumpkin = 255, 99, 28 Purple = 128, 0, 128 PurpleMountainsMajesty = 150, 123, 182 PurpleTaupe = 93, 57, 84 QuarterSpanishWhite = 230, 224, 212 Quartz = 220, 208, 255 Quincy = 106, 84, 69 RacingGreen = 26, 36, 33 RadicalRed = 255, 32, 82 Rajah = 251, 171, 96 RawUmber = 123, 63, 0 RazzleDazzleRose = 254, 78, 218 Razzmatazz = 215, 10, 83 Red = 255, 0, 0 RedBerry = 132, 22, 23 RedDamask = 203, 109, 81 RedOxide = 99, 15, 15 RedRobin = 128, 64, 64 RichBlue = 84, 90, 167 Riptide = 141, 217, 204 RobinsEggBlue = 0, 204, 204 RobRoy = 225, 169, 95 RockSpray = 171, 56, 31 RomanCoffee = 131, 105, 83 RoseBud = 246, 164, 148 RoseBudCherry = 135, 50, 96 RoseTaupe = 144, 93, 93 RosyBrown = 188, 143, 143 Rouge = 176, 48, 96 RoyalBlue = 65, 105, 225 RoyalHeath = 168, 81, 110 RoyalPurple = 102, 51, 152 Ruby = 215, 24, 104 Russet = 128, 70, 27 Rust = 192, 64, 0 RusticRed = 72, 6, 7 Saddle = 99, 81, 71 SaddleBrown = 139, 69, 19 SafetyOrange = 255, 102, 0 Saffron = 244, 196, 48 Sage = 143, 151, 121 Sail = 161, 202, 241 Salem = 0, 133, 67 Salmon = 250, 128, 114 SandyBeach = 253, 213, 177 SandyBrown = 244, 164, 96 Sangria = 134, 1, 17 SanguineBrown = 115, 54, 53 SanMarino = 80, 114, 167 SanteFe = 175, 110, 77 Sapphire = 6, 42, 120 Saratoga = 84, 90, 44 Scampi = 102, 102, 153 Scarlet = 255, 36, 0 ScarletGum = 67, 28, 83 SchoolBusYellow = 255, 216, 0 Schooner = 139, 134, 128 ScreaminGreen = 102, 255, 102 Scrub = 59, 60, 54 SeaBuckthorn = 249, 146, 69 SeaGreen = 46, 139, 87 Seagull = 140, 190, 214 SealBrown = 61, 12, 2 Seance = 96, 47, 107 SeaPink = 215, 131, 127 SeaShell = 255, 245, 238 Selago = 250, 230, 250 SelectiveYellow = 242, 180, 0 SemiSweetChocolate = 107, 68, 35 Sepia = 150, 90, 62 Serenade = 255, 233, 209 Shadow = 133, 109, 77 Shakespeare = 114, 160, 193 Shalimar = 252, 255, 164 Shamrock = 68, 215, 168 ShamrockGreen = 0, 153, 102 SherpaBlue = 0, 75, 73 SherwoodGreen = 27, 77, 62 Shilo = 222, 165, 164 ShipCove = 119, 139, 165 Shocking = 241, 156, 187 ShockingPink = 255, 29, 206 ShuttleGrey = 84, 98, 111 Sidecar = 238, 224, 177 Sienna = 160, 82, 45 Silk = 190, 164, 147 Silver = 192, 192, 192 SilverChalice = 175, 177, 174 SilverTree = 102, 201, 146 SkyBlue = 135, 206, 235 SlateBlue = 106, 90, 205 SlateGray = 112, 128, 144 SlateGrey = 112, 128, 144 Smalt = 0, 48, 143 SmaltBlue = 74, 100, 108 Snow = 255, 250, 250 SoftAmber = 209, 190, 168 Solitude = 235, 236, 240 Sorbus = 233, 105, 44 Spectra = 53, 101, 77 SpicyMix = 136, 101, 78 Spray = 126, 212, 230 SpringBud = 150, 255, 0 SpringGreen = 0, 255, 127 SpringSun = 236, 235, 189 SpunPearl = 170, 169, 173 Stack = 130, 142, 132 SteelBlue = 70, 130, 180 Stiletto = 137, 63, 69 Strikemaster = 145, 92, 131 StTropaz = 50, 82, 123 Studio = 115, 79, 150 Sulu = 201, 220, 135 SummerSky = 33, 171, 205 Sun = 237, 135, 45 Sundance = 197, 179, 88 Sunflower = 228, 208, 10 Sunglow = 255, 204, 51 SunsetOrange = 253, 82, 64 SurfieGreen = 0, 116, 116 Sushi = 111, 153, 64 SuvaGrey = 140, 140, 140 Swamp = 35, 43, 43 SweetCorn = 253, 219, 109 SweetPink = 243, 153, 152 Tacao = 236, 177, 118 TahitiGold = 235, 97, 35 Tan = 210, 180, 140 Tangaroa = 0, 28, 61 Tangerine = 228, 132, 0 TangerineYellow = 253, 204, 13 Tapestry = 183, 110, 121 Taupe = 72, 60, 50 TaupeGrey = 139, 133, 137 TawnyPort = 102, 66, 77 TaxBreak = 79, 102, 106 TeaGreen = 208, 240, 192 Teak = 176, 141, 87 Teal = 0, 128, 128 TeaRose = 255, 133, 207 Temptress = 60, 20, 33 Tenne = 200, 101, 0 TerraCotta = 226, 114, 91 Thistle = 216, 191, 216 TickleMePink = 245, 111, 161 Tidal = 232, 244, 140 TitanWhite = 214, 202, 221 Toast = 165, 113, 100 Tomato = 255, 99, 71 TorchRed = 255, 3, 62 ToryBlue = 54, 81, 148 Tradewind = 110, 174, 161 TrendyPink = 133, 96, 136 TropicalRainForest = 0, 127, 102 TrueV = 139, 114, 190 TulipTree = 229, 183, 59 Tumbleweed = 222, 170, 136 Turbo = 255, 195, 36 TurkishRose = 152, 119, 123 Turquoise = 64, 224, 208 TurquoiseBlue = 118, 215, 234 Tuscany = 175, 89, 62 TwilightBlue = 253, 255, 245 Twine = 186, 135, 89 TyrianPurple = 102, 2, 60 Ultramarine = 10, 17, 149 UltraPink = 255, 111, 255 Valencia = 222, 82, 70 VanCleef = 84, 61, 55 VanillaIce = 229, 204, 201 VenetianRed = 209, 0, 28 Venus = 138, 127, 128 Vermilion = 251, 79, 20 VeryLightGrey = 207, 207, 207 VidaLoca = 94, 140, 49 Viking = 71, 171, 204 Viola = 180, 131, 149 ViolentViolet = 50, 23, 77 Violet = 238, 130, 238 VioletRed = 255, 57, 136 Viridian = 64, 130, 109 VistaBlue = 159, 226, 191 VividViolet = 127, 62, 152 WaikawaGrey = 83, 104, 149 Wasabi = 150, 165, 60 Watercourse = 0, 106, 78 Wedgewood = 67, 107, 149 WellRead = 147, 61, 65 Wewak = 255, 152, 153 Wheat = 245, 222, 179 Whiskey = 217, 154, 108 WhiskeySour = 217, 144, 88 White = 255, 255, 255 WhiteSmoke = 245, 245, 245 WildRice = 228, 217, 111 WildSand = 229, 228, 226 WildStrawberry = 252, 65, 154 WildWatermelon = 255, 84, 112 WildWillow = 172, 191, 96 Windsor = 76, 40, 130 Wisteria = 191, 148, 228 Wistful = 162, 162, 208 Yellow = 255, 255, 0 YellowGreen = 154, 205, 50 YellowOrange = 255, 174, 66 YourPink = 244, 194, 194 } $Script:ScriptBlockColors = { param($commandName, $parameterName, $wordToComplete, $commandAst, $fakeBoundParameters) $Script:RGBColors.Keys | Where-Object { $_ -like "*$wordToComplete*" } } function Close-OfficeWord { [cmdletBinding()] param([alias('WordDocument')] $Document) try { $Document.Close() } catch { if ($_.Exception.InnerException.Message -eq "Memory stream is not expandable.") {} else { if ($PSBoundParameters.ErrorAction -eq 'Stop') { throw } else { Write-Warning "Close-OfficeWord - Couldn't close document properly. Error: $($_.Exception.Message)" } } } } function ConvertFrom-HTMLtoWord { [cmdletBinding()] param([Parameter(Mandatory)][string] $FilePath, [Parameter(Mandatory)][string] $HTML, [switch] $Show) $Document = New-OfficeWord -FilePath $PSScriptRoot\Documents\TestHTML.docx try { $Converter = [HtmlToOpenXml.HtmlConverter]::new($Document.MainDocumentPart) $Converter.ParseHtml($HTML) } catch { Write-Warning -Message "ConvertFrom-HTMLtoWord - Couldn't convert HTML to Word. Error: $($_.Exception.Message)" } Save-OfficeWord -Document $Document -Show:$Show.IsPresent } function Export-OfficeExcel { [cmdletBinding()] param([string] $FilePath, [alias('Name')][string] $WorksheetName = 'Sheet1', [alias("TargetData")][Parameter(ValueFromPipeline = $true)][Array] $DataTable, [int] $Row = 1, [int] $Column = 1, [switch] $Show, [switch] $AllProperties, [ClosedXML.Excel.XLTransposeOptions] $Transpose, [switch] $ShowRowStripes, [switch] $ShowColumnStripes, [switch] $DisableAutoFilter, [switch] $HideHeaderRow, [switch] $ShowTotalsRow, [switch] $EmphasizeFirstColumn, [switch] $EmphasizeLastColumn, [string] $Theme) Begin { $Data = [System.Collections.Generic.List[Object]]::new() } Process { foreach ($_ in $DataTable) { $Data.Add($_) } } End { New-OfficeExcel -FilePath $FilePath { New-OfficeExcelWorkSheet -Name $WorksheetName { $SplatOfficeExcelTable = @{DataTable = $Data Row = $Row Column = $Column AllProperties = $AllProperties.IsPresent DisableAutoFilter = $DisableAutoFilter.IsPresent EmphasizeFirstColumn = $EmphasizeFirstColumn.IsPresent EmphasizeLastColumn = $EmphasizeLastColumn.IsPresent ShowColumnStripes = $ShowColumnStripes.IsPresent ShowRowStripes = $ShowRowStripes.IsPresent ShowTotalsRow = $ShowTotalsRow.IsPresent HideHeaderRow = $HideHeaderRow.IsPresent Transpose = $Transpose Theme = $Theme } Remove-EmptyValue -Hashtable $SplatOfficeExcelTable New-OfficeExcelTable @SplatOfficeExcelTable } } -Show:$Show.IsPresent -Save } } Register-ArgumentCompleter -CommandName Export-OfficeExcel -ParameterName Theme -ScriptBlock $Script:ScriptBlockThemes function Get-OfficeExcel { [cmdletBinding()] param([Parameter(Mandatory)][string] $FilePath, [switch] $Template, [nullable[bool]] $RecalculateAllFormulas, [ClosedXML.Excel.XLEventTracking] $EventTracking) if ($FilePath -and (Test-Path -LiteralPath $FilePath)) { if ($RecalculateAllFormulas -or $EventTracking) { $LoadOptions = [ClosedXML.Excel.LoadOptions]::new() if ($null -ne $RecalculateAllFormulas) { $LoadOptions.RecalculateAllFormulas = $RecalculateAllFormulas } if ($EventTracking) { $LoadOptions.EventTracking = $EventTracking } $WorkBook = [ClosedXML.Excel.XLWorkbook]::new($FilePath, $LoadOptions) } else { if ($FilePath) { $WorkBook = [ClosedXML.Excel.XLWorkbook]::new($FilePath) } else { $WorkBook = [ClosedXML.Excel.XLWorkbook]::new() } } $WorkBook | Add-Member -MemberType NoteProperty -Name 'FilePath' -Value $FilePath -Force $WorkBook } } function Get-OfficeExcelValue { [cmdletBinding()] param($Worksheet, [int] $Row, [int] $Column) if ($Script:OfficeTrackerExcel) { $Worksheet = $Script:OfficeTrackerExcel['WorkSheet'] } elseif (-not $Worksheet) { return } $Worksheet.Cell($Row, $Column) } function Get-OfficeExcelWorkSheet { [cmdletBinding(DefaultParameterSetName = 'All')] param([parameter(Position = 0, ParameterSetName = 'Name')] [parameter(Position = 0, ParameterSetName = 'Index')] [parameter(Position = 0, ParameterSetName = 'All')] [scriptblock] $ExcelContent, [parameter(ParameterSetName = 'Name')] [parameter(ParameterSetName = 'Index')] [parameter(ParameterSetName = 'All')] [alias('ExcelDocument')][ClosedXML.Excel.XLWorkbook]$Excel, [parameter(ParameterSetName = 'Name')][alias('Name')][string] $WorksheetName, [parameter(ParameterSetName = 'Index')][nullable[int]] $Index, [parameter(ParameterSetName = 'All')][switch] $All, [parameter(ParameterSetName = 'Name')] [parameter(ParameterSetName = 'Index')] [parameter(ParameterSetName = 'All')] [switch] $NameOnly) $Worksheet = $null if ($Script:OfficeTrackerExcel -and -not $Excel) { $Excel = $Script:OfficeTrackerExcel['WorkBook'] } try { if ($WorksheetName) { $Worksheet = $Excel.Worksheets.Worksheet($WorksheetName) } elseif ($null -ne $Index) { $Worksheet = $Excel.Worksheets.Worksheet($Index) } else { $Worksheet = $Excel.Worksheets } } catch { if ($PSBoundParameters.ErrorAction -eq 'Stop') { throw } else { Write-Warning -Message "Get-OfficeExcelWorkSheet - Error: $($_.Exception.Message)" } } if ($Worksheet) { if ($ExcelContent) { $Script:OfficeTrackerExcel['WorkSheet'] = $Worksheet $ExecutedContent = & $ExcelContent $ExecutedContent $Script:OfficeTrackerExcel['WorkSheet'] = $null } else { if ($NameOnly) { $Worksheet.Name } else { $Worksheet } } } else { if ($Index) { Write-Warning -Message "Get-OfficeExcelWorkSheet - WorkSheet with index $Index doesnt exits. Skipping." } elseif ($WorksheetName) { Write-Warning -Message "Get-OfficeExcelWorkSheet - WorkSheet with name $WorksheetName doesnt exits. Skipping." } else { Write-Warning -Message "Get-OfficeExcelWorkSheet - Mmm?" } } } function Get-OfficeWord { [cmdletBinding()] param([Parameter(Mandatory)][string] $FilePath, [switch] $ReadOnly, [switch] $AutoSave) if ($FilePath -and (Test-Path -LiteralPath $FilePath)) { $Settings = [DocumentFormat.OpenXml.Packaging.OpenSettings]::new() $Settings.AutoSave = $AutoSave.IsPresent [byte[]] $ByteArray = [System.IO.File]::ReadAllBytes($FilePath) $MemoryStream = [System.IO.MemoryStream]::new($ByteArray) try { $WordDocument = [DocumentFormat.OpenXml.Packaging.WordprocessingDocument]::Open($MemoryStream, -not $ReadOnly.IsPresent, $Settings) $WordDocument | Add-Member -MemberType NoteProperty -Name 'FilePath' -Value $FilePath -Force $WordDocument } catch { if ($PSBoundParameters.ErrorAction -eq 'Stop') { throw } else { Write-Warning "Get-OfficeWord - File $FilePath couldn't be open. Error: $($_.Exception.Message)" } } } else { if ($PSBoundParameters.ErrorAction -eq 'Stop') { throw } else { Write-Warning "Get-OfficeWord - File $FilePath doesn't exists. Try again." } } } function New-OfficeExcel { [cmdletBinding()] param([scriptblock] $ExcelContent, [Parameter(Mandatory)][string] $FilePath, [switch] $Template, [nullable[bool]] $RecalculateAllFormulas, [ClosedXML.Excel.XLEventTracking] $EventTracking, [switch] $Show, [switch] $Save, [validateSet('Reuse', 'Overwrite', 'Stop')][string] $WhenExists = 'Reuse') if ($ExcelContent) { $Script:OfficeTrackerExcel = [ordered] @{} } if (Test-Path -LiteralPath $FilePath) { if ($WhenExists -eq 'Stop') { Write-Warning -Message "New-OfficeExcel - File $FilePath already exists. Terminating." $Script:OfficeTrackerExcel = $null return } elseif ($WhenExists -eq 'Overwrite') { $WorkBook = [ClosedXML.Excel.XLWorkbook]::new() $WorkBook | Add-Member -MemberType NoteProperty -Name 'OpenType' -Value 'New' -Force } elseif ($WhenExists -eq 'ReUse') { Write-Warning -Message "New-OfficeExcel - File $FilePath already exists. Loading up." try { $WorkBook = [ClosedXML.Excel.XLWorkbook]::new($FilePath) } catch { $Script:OfficeTrackerExcel = $null if ($PSBoundParameters.ErrorAction -eq 'Stop') { throw } else { Write-Warning -Message "New-OfficeExcel - File $FilePath returned error: $($_.Exception.Message)" return } } $WorkBook | Add-Member -MemberType NoteProperty -Name 'OpenType' -Value 'Existing' -Force } } else { $WorkBook = [ClosedXML.Excel.XLWorkbook]::new() $WorkBook | Add-Member -MemberType NoteProperty -Name 'OpenType' -Value 'New' -Force } $WorkBook | Add-Member -MemberType NoteProperty -Name 'FilePath' -Value $FilePath -Force if ($ExcelContent) { $Script:OfficeTrackerExcel['WorkBook'] = $WorkBook $Script:OfficeTrackerExcel['OpenType'] = 'Existing' $ExecutedContent = & $ExcelContent $ExecutedContent } if ($ExcelContent) { if ($Save) { Save-OfficeExcel -Show:$Show.IsPresent-FilePath $FilePath -Excel $WorkBook } $Script:OfficeTrackerExcel = $null } else { $WorkBook } } function New-OfficeExcelTable { [cmdletBinding()] param([Array] $DataTable, [Object] $Worksheet, [alias('Row')][int] $StartRow = 1, [alias('Column')][int] $StartCell = 1, [switch] $ReturnObject, [ClosedXML.Excel.XLTransposeOptions] $Transpose, [switch] $AllProperties, [switch] $SkipHeader, [switch] $ShowRowStripes, [switch] $ShowColumnStripes, [switch] $DisableAutoFilter, [switch] $HideHeaderRow, [switch] $ShowTotalsRow, [switch] $EmphasizeFirstColumn, [switch] $EmphasizeLastColumn, [string] $Theme) if ($Script:OfficeTrackerExcel -and -not $Worksheet) { $WorkSheet = $Script:OfficeTrackerExcel['WorkSheet'] } $Cell = $StartCell - 1 if ($DataTable[0] -is [System.Collections.IDictionary]) { $Properties = 'Name', 'Value' } else { $Properties = Select-Properties -Objects $DataTable -AllProperties:$AllProperties -Property $IncludeProperty -ExcludeProperty $ExcludeProperty } if (-not $SkipHeader) { foreach ($Property in $Properties) { $Cell++ New-OfficeExcelValue -Row $StartRow -Value $Property -Column $Cell -Worksheet $Worksheet } } if ($DataTable[0] -is [System.Collections.IDictionary]) { foreach ($Data in $DataTable) { foreach ($Key in $Data.Keys) { $Row++ New-OfficeExcelValue -Row ($Row + $StartRow) -Value $Key -Column ($StartCell) -Worksheet $Worksheet New-OfficeExcelValue -Row ($Row + $StartRow) -Value $Data[$Key] -Column ($StartCell + 1) -Worksheet $Worksheet } } $LastCell = $Worksheet.Row($StartRow + $Row).Cell($Cell) } elseif ($Properties -eq '*') { foreach ($Data in $DataTable) { $Row++ New-OfficeExcelValue -Row ($Row + $StartRow) -Value $Data -Column ($StartCell) -Worksheet $Worksheet } $LastCell = $Worksheet.Row($StartRow + $Row).Cell($StartCell) } else { for ($Row = 1; $Row -le $DataTable.Count; $Row++) { $Cell = $StartCell - 1 foreach ($Property in $Properties) { $Cell++ New-OfficeExcelValue -Row ($Row + $StartRow) -Value $DataTable[$Row - 1].$Property -Column $Cell -Worksheet $Worksheet } } $LastCell = $Worksheet.Row($StartRow - 1 + $Row).Cell($Cell) } $FirstCell = $Worksheet.Row($StartRow).Cell($StartCell) $Range = $Worksheet.Range($FirstCell.Address, $LastCell.Address) $TableOutput = $Range.CreateTable() $SplatOptions = @{Table = $TableOutput Transpose = $Transpose ShowRowStripes = $ShowRowStripes.IsPresent ShowColumnStripes = $ShowColumnStripes.IsPresent DisableAutoFilter = $DisableAutoFilter.IsPresent HideHeaderRow = $HideHeaderRow.IsPresent ShowTotalsRow = $ShowTotalsRow.IsPresent EmphasizeFirstColumn = $EmphasizeFirstColumn.IsPresent EmphasizeLastColumn = $EmphasizeLastColumn.IsPresent Theme = $Theme } Remove-EmptyValue -Hashtable $SplatOptions New-OfficeExcelTableOptions @SplatOptions } function New-OfficeExcelTableOptions { [cmdletBinding()] param($Table, [ClosedXML.Excel.XLTransposeOptions] $Transpose, [switch] $ShowRowStripes, [switch] $ShowColumnStripes, [switch] $DisableAutoFilter, [switch] $HideHeaderRow, [switch] $ShowTotalsRow, [switch] $EmphasizeFirstColumn, [switch] $EmphasizeLastColumn, [string] $Theme) if ($Table) { if ($null -ne $Transpose) { $Table.Transpose($Transpose) } if ($AutoFilter) { $Table.InitializeAutoFilter() } if ($ShowColumnStripes) { $Table.ShowColumnStripes = $true } if ($ShowRowStripes) { $Table.ShowRowStripes = $true } if ($DisableAutoFilter) { $Table.ShowAutoFilter = $false } if ($ShowTotalsRow) { $Table.ShowsTotalRow = $true } if ($null -ne $Theme) { $Table.Theme = $Theme } if ($EmphasizeFirstColumn) { $Table.EmphasizeFirstColumn = $true } if ($EmphasizeLastColumn) { $Table.EmphasizeLastColumn = $true } if ($HideHeaderRow) { $Table.ShowHeaderRow = $false } if ($ReturnObject) { $Table } } } function New-OfficeExcelValue { [cmdletBinding()] param($Worksheet, [Object] $Value, [int] $Row, [int] $Column, [string] $DateFormat, [string] $NumberFormat, [int] $FormatID) $KnownTypes = 'bool|byte|char|datetime|decimal|double|float|int|long|sbyte|short|string|timespan|uint|ulong|URI|ushort' if ($Script:OfficeTrackerExcel) { $Worksheet = $Script:OfficeTrackerExcel['WorkSheet'] } elseif (-not $Worksheet) { return } try { if ($null -eq $Value) { $Worksheet.Cell($Row, $Column).Value = '' } elseif ($Value.GetType().Name -match $KnownTypes) { $Worksheet.Cell($Row, $Column).Value = $Value } else { $Worksheet.Cell($Row, $Column).Value = [string] $Value } } catch { if ($PSBoundParameters.ErrorAction -eq 'Stop') { throw } else { Write-Warning "New-OfficeExcelValue - Error: $($_.Exception.Message)" } } } function New-OfficeExcelWorkSheet { [cmdletBinding()] param([parameter(Position = 0)][scriptblock] $ExcelContent, [alias('ExcelDocument')][ClosedXML.Excel.XLWorkbook]$Excel, [parameter(Mandatory)][alias('Name')][string] $WorksheetName, [ValidateSet("Replace", "Skip", "Rename")][string] $Option = 'Skip', [switch] $Suppress, [string] $TabColor) $Worksheet = $null if ($null -ne $Excel) {} elseif ($Script:OfficeTrackerExcel -and -not $Excel) { $Excel = $Script:OfficeTrackerExcel['WorkBook'] } else { return } if ($Excel.Worksheets.Contains($WorksheetName)) { Write-Warning -Message "New-OfficeExcelWorkSheet - WorkSheet with name $WorksheetName already exists. Using..." $Worksheet = $Excel.Worksheets.Worksheet($WorksheetName) } else { $Worksheet = $Excel.Worksheets.Add($WorksheetName) } if ($Worksheet) { if ($TabColor) { Set-OfficeExcelWorkSheetStyle -TabColor $TabColor -Worksheet $Worksheet } if ($ExcelContent) { $Script:OfficeTrackerExcel['WorkSheet'] = $Worksheet $ExecutedContent = & $ExcelContent $ExecutedContent $Script:OfficeTrackerExcel['WorkSheet'] = $null } else { if (-not $Suppress) { if ($NameOnly) { $Worksheet.Name } else { $Worksheet } } } } } Register-ArgumentCompleter -CommandName New-OfficeExcelWorkSheet -ParameterName TabColor -ScriptBlock $Script:ScriptBlockColors function New-OfficePowerPoint { [cmdletBinding()] param([string] $FilePath, [DocumentFormat.OpenXml.PresentationDocumentType] $Type = [DocumentFormat.OpenXml.PresentationDocumentType]::Presentation) $PowerPoint = [DocumentFormat.OpenXml.Packaging.PresentationDocument]::Create($FilePath, $Type, $true) $null = $PowerPoint.AddPresentationPart() $PowerPoint.PresentationPart.Presentation = [DocumentFormat.OpenXml.Presentation.Presentation]::new() $PowerPoint | Add-Member -Name 'FilePath' -Value $FilePath -Force -MemberType NoteProperty $PowerPoint } function New-OfficeWord { [cmdletBinding()] param([string] $FilePath, [switch] $AutoSave, [int] $Retry = 2) $Saved = $false $Count = 0 while ($Count -le $Retry -and $Saved -eq $false) { $Count++ try { $WordDocument = [OfficeImo.Word.WordDocument]::Create($FilePath, $AutoSave.IsPresent) $Saved = $true } catch { if ($PSBoundParameters.ErrorAction -eq 'Stop') { throw } else { Write-Warning "New-OfficeWord - Couldn't create new Word Document at $FilePath. Error: $($_.Exception.Message)" } } if (-not $Saved) { if ($Retry -ge $Count) { $FilePath = [io.path]::GetTempFileName().Replace('.tmp', '.docx') Write-Warning -Message "New-OfficeWord - Couldn't save using provided file name, retrying with $FilePath" } else { Write-Warning -Message "New-OfficeWord - Couldn't save using provided file name. Run out of retries ($Count / $Retry)." return } } } $WordDocument | Add-Member -Name 'FilePath' -Value $FilePath -Force -MemberType NoteProperty $WordDocument } function New-OfficeWordTable { [cmdletBinding()] param([Array] $DataTable, [DocumentFormat.OpenXml.Wordprocessing.TableLayoutValues] $TableLayout, [switch] $SkipHeader) return if ($DataTable[0] -is [System.Collections.IDictionary]) { $Properties = 'Name', 'Value' } else { $Properties = Select-Properties -Objects $DataTable -AllProperties:$AllProperties -Property $IncludeProperty -ExcludeProperty $ExcludeProperty } $Table = [DocumentFormat.OpenXml.Wordprocessing.TableGrid]::new() $TableProperties = [DocumentFormat.OpenXml.Wordprocessing.TableProperties]::new() if (-not $SkipHeader) { $TableRow = [DocumentFormat.OpenXml.Wordprocessing.TableRow]::new() foreach ($Property in $Properties) { $TableCell = [DocumentFormat.OpenXml.Wordprocessing.TableCell]::new() $Paragraph = [DocumentFormat.OpenXml.Wordprocessing.Paragraph]::new() $TextProperty = New-OfficeWordText -Paragraph $Paragraph -Text $Property -ReturnObject $TableCellProperty = [DocumentFormat.OpenXml.Wordprocessing.TableCellProperties]::new() $TableCellWidth = [DocumentFormat.OpenXml.Wordprocessing.TableCellWidth]::new() $TableCellWidth.Width = 2400 $TableCellProperty.TableCellWidth = $TableCellWidth $TableCellWidth.Type = [DocumentFormat.OpenXml.Wordprocessing.TableWidthUnitValues]::Dxa $TableCell.Append($TextProperty) $TableRow.Append($TableCell) } $Table.Append($TableRow) } $null = $Document.MainDocumentPart.Document.Body.Append($Table) } function New-OfficeWordTableBorder { [cmdletBinding()] param() $TableBorders = [DocumentFormat.OpenXml.Wordprocessing.TableBorders]::new() $TopBorder = [DocumentFormat.OpenXml.Wordprocessing.TopBorder] @{Val = [DocumentFormat.OpenXml.Wordprocessing.BorderValues]::Dashed Size = [DocumentFormat.OpenXml.UInt32Value]::new(24) } $BottomBorder = [DocumentFormat.OpenXml.Wordprocessing.BottomBorder] @{Val = [DocumentFormat.OpenXml.Wordprocessing.BorderValues]::Dashed Size = [DocumentFormat.OpenXml.UInt32Value]::new(24) } $LeftBorder = [DocumentFormat.OpenXml.Wordprocessing.LeftBorder] @{Val = [DocumentFormat.OpenXml.Wordprocessing.BorderValues]::Dashed Size = [DocumentFormat.OpenXml.UInt32Value]::new(24) } $RightBorder = [DocumentFormat.OpenXml.Wordprocessing.RightBorder] @{Val = [DocumentFormat.OpenXml.Wordprocessing.BorderValues]::Dashed Size = [DocumentFormat.OpenXml.UInt32Value]::new(24) } $InsideHorizontalBorder = [DocumentFormat.OpenXml.Wordprocessing.InsideHorizontalBorder] @{Val = [DocumentFormat.OpenXml.Wordprocessing.BorderValues]::Dashed Size = [DocumentFormat.OpenXml.UInt32Value]::new(24) } $InsideVerticalBorder = [DocumentFormat.OpenXml.Wordprocessing.InsideVerticalBorder] @{Val = [DocumentFormat.OpenXml.Wordprocessing.BorderValues]::Dashed Size = [DocumentFormat.OpenXml.UInt32Value]::new(24) Color = "red" } $TableBorders.BottomBorder = $BottomBorder $TableBorders.LeftBorder = $LeftBorder $TableBorders.RightBorder = $RightBorder $TableBorders.TopBorder = $TopBorder $TableBorders.InsideHorizontalBorder = $InsideHorizontalBorder $TableBorders.InsideVerticalBorder = $InsideVerticalBorder , $TableBorders } function New-OfficeWordTableLayout { [cmdletBinding()] param() } function New-OfficeWordTableLook { [cmdletBinding()] param() $TableLook = [DocumentFormat.OpenXml.Wordprocessing.TableLook] @{Val = "04A0" FirstRow = $true LastRow = $false FirstColumn = $true LastColumn = $false NoHorizontalBand = $false NoVerticalBand = $true } , $TableLook } function New-OfficeWordTableStyle { [cmdletBinding()] param() $TableStyle = [DocumentFormat.OpenXml.Wordprocessing.TableStyle]::new() $TableStyle.Val = "LightShading-Accent1" , $TableStyle } function New-OfficeWordText { [cmdletBinding()] param([DocumentFormat.OpenXml.Packaging.WordprocessingDocument] $Document, [DocumentFormat.OpenXml.Wordprocessing.Paragraph] $Paragraph, [string[]]$Text, [DocumentFormat.OpenXml.SpaceProcessingModeValues] $Space = [DocumentFormat.OpenXml.SpaceProcessingModeValues]::Preserve, [nullable[bool][]] $Bold, [nullable[bool][]] $Italic, [nullable[bool][]] $Underline, [string[]] $Color, [DocumentFormat.OpenXml.Wordprocessing.JustificationValues] $Alignment, [switch] $ReturnObject) for ($T = 0; $T -le $Text.Count; $T++) { $WordText = [DocumentFormat.OpenXml.Wordprocessing.Text] @{Text = $Text[$T] Space = $Space } if ($Space -and $Space.Count -ge $T -and $Space[$T]) { $WordText.Space = $Space[$T] } $Run = [DocumentFormat.OpenXml.Wordprocessing.Run]::new() $RunProperties = [DocumentFormat.OpenXml.Wordprocessing.RunProperties]::new() if ($Bold -and $Bold.Count -ge $T -and $Bold[$T]) { $RunProperties.Bold = [DocumentFormat.OpenXml.Wordprocessing.Bold]::new() } if ($Italic -and $Italic.Count -ge $T -and $Italic[$T]) { $RunProperties.Italic = [DocumentFormat.OpenXml.Wordprocessing.Italic]::new() } if ($Underline -and $Underline.Count -ge $T -and $Underline[$T]) { $RunProperties.Underline = [DocumentFormat.OpenXml.Wordprocessing.Underline]::new() } if ($Color -and $Color.Count -ge $T -and $Color[$T]) { $ColorToSet = (ConvertFrom-Color -Color $Color[$T]) if ($ColorToSet) { $RunProperties.Color = [DocumentFormat.OpenXml.Wordprocessing.Color]::new() $RunProperties.Color.Val = $ColorToSet } } if ($Alignment) { $ParagraphProperties = [DocumentFormat.OpenXml.Wordprocessing.ParagraphProperties]::new() $ParagraphProperties.Justification = [DocumentFormat.OpenXml.Wordprocessing.Justification] @{Val = $Alignment } } $null = $Run.AppendChild($RunProperties) $null = $Run.AppendChild($WordText) if (-not $Paragraph) { $Paragraph = [DocumentFormat.OpenXml.Wordprocessing.Paragraph]::new() if ($ParagraphProperties) { $null = $Paragraph.Append($ParagraphProperties) } $null = $Paragraph.Append($Run) if ($Document) { $null = $Document.MainDocumentPart.Document.Body.AppendChild($Paragraph) } } else { $null = $Paragraph.Append($Run) } } if ($ReturnObject) { , $Paragraph } } function Remove-OfficeWordFooter { [cmdletBinding()] param([DocumentFormat.OpenXml.Packaging.WordprocessingDocument] $Document) try { [OfficeIMO.Word.Footers]::RemoveFooters($Document) } catch { if ($PSBoundParameters.ErrorAction -eq 'Stop') { throw } else { Write-Warning -Message "Remove-OfficeWordFooter - Couldn't remove footer. Error: $($_.Exception.Message)" } } } function Remove-OfficeWordHeader { [cmdletBinding()] param([DocumentFormat.OpenXml.Packaging.WordprocessingDocument] $Document) try { [OfficeIMO.Word.Headers]::RemoveHeaders($Document) } catch { if ($PSBoundParameters.ErrorAction -eq 'Stop') { throw } else { Write-Warning -Message "Remove-OfficeWordHeader - Couldn't remove footer. Error: $($_.Exception.Message)" } } } function Save-OfficeExcel { [cmdletBinding()] param([ClosedXML.Excel.XLWorkbook] $Excel, [string] $FilePath, [switch] $Show, [int] $RetryCount = 1, [Parameter(DontShow)] $CurrentRetryCount) if ($Excel) { if (-not $FilePath) { $FilePath = $Excel.FilePath } if ($Excel.Worksheets.Count -gt 0) { try { if (-not $FilePath) { if ($Excel.OpenType -eq 'Existing') { $Excel.Save() } else { if ($Excel.OpenType -eq 'New') { $Excel.SaveAs($Excel.FilePath) } } } else { $Excel.SaveAs($FilePath) } $CurrentRetryCount = 0 } catch { if ($RetryCount -eq $CurrentRetryCount) { Write-Warning "Save-ExcelDocument - Couldnt save Excel to $FilePath. Retry count limit reached. Terminating.." return } $CurrentRetryCount++ $ErrorMessage = $_.Exception.Message if ($ErrorMessage -like "*The process cannot access the file*because it is being used by another process.*" -or $ErrorMessage -like "*Error saving file*") { $FilePath = Get-FileName -Temporary -Extension 'xlsx' Write-Warning "Save-OfficeExcel - Couldn't save file as it was in use or otherwise. Trying different name $FilePath" Save-OfficeExcel -Excel $Excel -Show:$Show -FilePath $FilePath -RetryCount $RetryCount -CurrentRetryCount $CurrentRetryCount return } else { Write-Warning "Save-OfficeExcel - Error: $ErrorMessage" } } if ($Show) { try { Invoke-Item -Path $FilePath } catch { Write-Warning "Save-OfficeExcel - Couldn't open file $FilePath as requested." } } } else { Write-Warning -Message "Save-OfficeExcel - Can't save $FilePath because there are no worksheets." } } else { Write-Warning -Message "Save-OfficeExcel - Excel Workbook not provided. Skipping." } } function Save-OfficePowerPoint { [cmdletBinding()] param($PowerPoint, [switch] $Show) $FilePath = $PowerPoint.FilePath $PowerPoint.Close() if ($Show) { Invoke-Item -LiteralPath $FilePath } } function Save-OfficeWord { [cmdletBinding()] param([alias('WordDocument')][DocumentFormat.OpenXml.Packaging.WordprocessingDocument] $Document, [switch] $Show, [string] $FilePath, [int] $Retry = 2) if (-not $Document) { Write-Warning "Save-OfficeWord - Couldn't save Word Document. Document is null." return } $Saved = $false $Count = 0 if (-not $Document.FilePath -and -not $FilePath) { if ($PSBoundParameters.ErrorAction -eq 'Stop') { throw } else { Write-Warning "Save-OfficeWord - Couldn't save Word Document. No file path provided." return } } while ($Count -le $Retry -and $Saved -eq $false) { $Count++ if ($FilePath) { try { $NewDocument = $Document.SaveAs($FilePath) $Saved = $true } catch { if ($PSBoundParameters.ErrorAction -eq 'Stop') { throw } else { Write-Warning "Save-OfficeWord - Couldn't save $FilePath. Error: $($_.Exception.Message)" } } } else { $FilePath = $Document.FilePath if (-not $Document.AutoSave) { try { $Document.Save() $Saved = $true } catch { if ($PSBoundParameters.ErrorAction -eq 'Stop') { throw } else { Write-Warning "Save-OfficeWord - Couldn't save $FilePath. Error: $($_.Exception.Message)" } } } } if (-not $Saved) { if ($Retry -ge $Count) { $FilePath = [io.path]::GetTempFileName().Replace('.tmp', '.docx') Write-Warning -Message "Save-OfficeWord - Couldn't save using provided file name, retrying with $FilePath" } else { Write-Warning -Message "Save-OfficeWord - Couldn't save using provided file name. Run out of retries ($Count / $Retry)." } } } try { $Document.Close() } catch { if ($_.Exception.InnerException.Message -eq "Memory stream is not expandable.") {} else { if ($PSBoundParameters.ErrorAction -eq 'Stop') { throw } else { Write-Warning "Save-OfficeWord - Couldn't close document for $FilePath. Error: $($_.Exception.Message)" } } } if ($NewDocument) { try { $NewDocument.Close() } catch { if ($PSBoundParameters.ErrorAction -eq 'Stop') { throw } else { Write-Warning "Save-OfficeWord - Couldn't close document for $FilePath. Error: $($_.Exception.Message)" } } } if ($Show) { try { Invoke-Item -LiteralPath $FilePath -ErrorAction Stop } catch { if ($PSBoundParameters.ErrorAction -eq 'Stop') { throw } else { Write-Warning "Save-OfficeWord - Couldn't open $FilePath. Error: $($_.Exception.Message)" } } } } function Set-OfficeExcelCellStyle { [cmdletBinding()] param($Worksheet, [int] $Row, [int] $Column, [string] $Format, [int] $FormatID, [nullable[bool]] $Bold, [ClosedXML.Excel.XLFontCharSet] $FontCharSet, [alias('Color')][string] $FontColor, [string] $BackGroundColor, [ClosedXML.Excel.XLFillPatternValues] $PatternType, [ClosedXML.Excel.XLFontFamilyNumberingValues] $FontFamilyNumbering, [string] $FontName, [double] $FontSize, [nullable[bool]] $Italic , [nullable[bool]] $Shadow, [nullable[bool]] $Strikethrough , [ClosedXML.Excel.XLFontUnderlineValues] $Underline, [ClosedXML.Excel.XLFontVerticalTextAlignmentValues] $VerticalAlignment) if ($Script:OfficeTrackerExcel) { $Worksheet = $Script:OfficeTrackerExcel['WorkSheet'] } elseif (-not $Worksheet) { return } if ($Format) { $Worksheet.Cell($Row, $Column).Style.NumberFormat.Format = $Format } elseif ($FormatID) { $Worksheet.Cell($Row, $Column).Style.NumberFormat.NumberFormatID = $FormatID } if ($FontColor) { $ColorConverted = [ClosedXML.Excel.XLColor]::FromHtml((ConvertFrom-Color -Color $FontColor)) $Worksheet.Cell($Row, $Column).Style.Font.FontColor = $ColorConverted } if ($null -ne $Bold) { $Worksheet.Cell($Row, $Column).Style.Font.Bold = $Bold } if ($null -ne $Italic) { $Worksheet.Cell($Row, $Column).Style.Font.Italic = $Italic } if ($null -ne $Strikethrough) { $Worksheet.Cell($Row, $Column).Style.Font.Strikethrough = $Strikethrough } if ($null -ne $Shadow) { $Worksheet.Cell($Row, $Column).Style.Font.Shadow = $Shadow } if ($FontSize) { $Worksheet.Cell($Row, $Column).Style.Font.FontSize = $FontSize } if ($null -ne $Underline) { $Worksheet.Cell($Row, $Column).Style.Font.Underline = $Underline } if ($null -ne $VerticalAlignment) { $Worksheet.Cell($Row, $Column).Style.Font.VerticalAlignment = $VerticalAlignment } if ($null -ne $FontFamilyNumbering) { $Worksheet.Cell($Row, $Column).Style.Font.FontFamilyNumbering = $FontFamilyNumbering } if ($null -ne $FontCharSet) { $Worksheet.Cell($Row, $Column).Style.Font.FontCharSet = $FontCharSet } if ($BackGroundColor) { $ColorConverted = [ClosedXML.Excel.XLColor]::FromHtml((ConvertFrom-Color -Color $BackGroundColor)) $Worksheet.Cell($Row, $Column).Style.Fill.BackgroundColor = $ColorConverted } if ($PatternType) { $Worksheet.Cell($Row, $Column).Style.Fill.PatternType = $PatternType } } Register-ArgumentCompleter -CommandName Set-OfficeExcelValueStyle -ParameterName FontColor -ScriptBlock $Script:ScriptBlockColors Register-ArgumentCompleter -CommandName Set-OfficeExcelValueStyle -ParameterName BackGroundColor -ScriptBlock $Script:ScriptBlockColors function Set-OfficeExcelWorkSheetStyle { [cmdletBinding(DefaultParameterSetName = 'Name')] param([parameter(ParameterSetName = 'Name')] [parameter(ParameterSetName = 'Index')] [parameter(ParameterSetName = 'Native')] [alias('ExcelDocument')][ClosedXML.Excel.XLWorkbook]$Excel, [parameter(ParameterSetName = 'Name')] [parameter(ParameterSetName = 'Index')] [parameter(ParameterSetName = 'Native')] [string] $TabColor, [parameter(ParameterSetName = 'Native')] $Worksheet, [parameter(ParameterSetName = 'Name')][alias('Name')][string] $WorksheetName, [parameter(ParameterSetName = 'Index')][nullable[int]] $Index) if ($Script:OfficeTrackerExcel -and -not $Excel) { $Excel = $Script:OfficeTrackerExcel['WorkBook'] } if ($Worksheet) {} else { try { if ($WorksheetName) { $Worksheet = $Excel.Worksheets.Worksheet($WorksheetName) } elseif ($null -ne $Index) { $Worksheet = $Excel.Worksheets.Worksheet($Index) } } catch { if ($PSBoundParameters.ErrorAction -eq 'Stop') { throw } else { Write-Warning -Message "Set-OfficeExcelWorkSheet - Error: $($_.Exception.Message)" } } } if ($Worksheet) { if ($TabColor) { $ColorConverted = [ClosedXML.Excel.XLColor]::FromHtml((ConvertFrom-Color -Color $TabColor)) $null = $Worksheet.SetTabColor($ColorConverted) } } } Register-ArgumentCompleter -CommandName Set-OfficeExcelWorkSheetStyle -ParameterName TabColor -ScriptBlock $Script:ScriptBlockColors Export-ModuleMember -Function @('Close-OfficeWord', 'ConvertFrom-HTMLtoWord', 'Export-OfficeExcel', 'Get-OfficeExcel', 'Get-OfficeExcelValue', 'Get-OfficeExcelWorkSheet', 'Get-OfficeWord', 'New-OfficeExcel', 'New-OfficeExcelTable', 'New-OfficeExcelTableOptions', 'New-OfficeExcelValue', 'New-OfficeExcelWorkSheet', 'New-OfficePowerPoint', 'New-OfficeWord', 'New-OfficeWordTable', 'New-OfficeWordTableBorder', 'New-OfficeWordTableLayout', 'New-OfficeWordTableLook', 'New-OfficeWordTableStyle', 'New-OfficeWordText', 'Remove-OfficeWordFooter', 'Remove-OfficeWordHeader', 'Save-OfficeExcel', 'Save-OfficePowerPoint', 'Save-OfficeWord', 'Set-OfficeExcelCellStyle', 'Set-OfficeExcelWorkSheetStyle') -Alias @() # SIG # Begin signature block # MIIdWQYJKoZIhvcNAQcCoIIdSjCCHUYCAQExCzAJBgUrDgMCGgUAMGkGCisGAQQB # gjcCAQSgWzBZMDQGCisGAQQBgjcCAR4wJgIDAQAABBAfzDtgWUsITrck0sYpfvNR # AgEAAgEAAgEAAgEAAgEAMCEwCQYFKw4DAhoFAAQUXni/Q9jn9b5QDCeHPi5t+ceZ # jWCgghhnMIIDtzCCAp+gAwIBAgIQDOfg5RfYRv6P5WD8G/AwOTANBgkqhkiG9w0B # AQUFADBlMQswCQYDVQQGEwJVUzEVMBMGA1UEChMMRGlnaUNlcnQgSW5jMRkwFwYD # VQQLExB3d3cuZGlnaWNlcnQuY29tMSQwIgYDVQQDExtEaWdpQ2VydCBBc3N1cmVk # IElEIFJvb3QgQ0EwHhcNMDYxMTEwMDAwMDAwWhcNMzExMTEwMDAwMDAwWjBlMQsw # CQYDVQQGEwJVUzEVMBMGA1UEChMMRGlnaUNlcnQgSW5jMRkwFwYDVQQLExB3d3cu # ZGlnaWNlcnQuY29tMSQwIgYDVQQDExtEaWdpQ2VydCBBc3N1cmVkIElEIFJvb3Qg # Q0EwggEiMA0GCSqGSIb3DQEBAQUAA4IBDwAwggEKAoIBAQCtDhXO5EOAXLGH87dg # +XESpa7cJpSIqvTO9SA5KFhgDPiA2qkVlTJhPLWxKISKityfCgyDF3qPkKyK53lT # XDGEKvYPmDI2dsze3Tyoou9q+yHyUmHfnyDXH+Kx2f4YZNISW1/5WBg1vEfNoTb5 # a3/UsDg+wRvDjDPZ2C8Y/igPs6eD1sNuRMBhNZYW/lmci3Zt1/GiSw0r/wty2p5g # 0I6QNcZ4VYcgoc/lbQrISXwxmDNsIumH0DJaoroTghHtORedmTpyoeb6pNnVFzF1 # roV9Iq4/AUaG9ih5yLHa5FcXxH4cDrC0kqZWs72yl+2qp/C3xag/lRbQ/6GW6whf # GHdPAgMBAAGjYzBhMA4GA1UdDwEB/wQEAwIBhjAPBgNVHRMBAf8EBTADAQH/MB0G # A1UdDgQWBBRF66Kv9JLLgjEtUYunpyGd823IDzAfBgNVHSMEGDAWgBRF66Kv9JLL # gjEtUYunpyGd823IDzANBgkqhkiG9w0BAQUFAAOCAQEAog683+Lt8ONyc3pklL/3 # cmbYMuRCdWKuh+vy1dneVrOfzM4UKLkNl2BcEkxY5NM9g0lFWJc1aRqoR+pWxnmr # EthngYTffwk8lOa4JiwgvT2zKIn3X/8i4peEH+ll74fg38FnSbNd67IJKusm7Xi+ # fT8r87cmNW1fiQG2SVufAQWbqz0lwcy2f8Lxb4bG+mRo64EtlOtCt/qMHt1i8b5Q # Z7dsvfPxH2sMNgcWfzd8qVttevESRmCD1ycEvkvOl77DZypoEd+A5wwzZr8TDRRu # 838fYxAe+o0bJW1sj6W3YQGx0qMmoRBxna3iw/nDmVG3KwcIzi7mULKn+gpFL6Lw # 8jCCBP4wggPmoAMCAQICEA1CSuC+Ooj/YEAhzhQA8N0wDQYJKoZIhvcNAQELBQAw # cjELMAkGA1UEBhMCVVMxFTATBgNVBAoTDERpZ2lDZXJ0IEluYzEZMBcGA1UECxMQ # d3d3LmRpZ2ljZXJ0LmNvbTExMC8GA1UEAxMoRGlnaUNlcnQgU0hBMiBBc3N1cmVk # IElEIFRpbWVzdGFtcGluZyBDQTAeFw0yMTAxMDEwMDAwMDBaFw0zMTAxMDYwMDAw # MDBaMEgxCzAJBgNVBAYTAlVTMRcwFQYDVQQKEw5EaWdpQ2VydCwgSW5jLjEgMB4G # A1UEAxMXRGlnaUNlcnQgVGltZXN0YW1wIDIwMjEwggEiMA0GCSqGSIb3DQEBAQUA # A4IBDwAwggEKAoIBAQDC5mGEZ8WK9Q0IpEXKY2tR1zoRQr0KdXVNlLQMULUmEP4d # yG+RawyW5xpcSO9E5b+bYc0VkWJauP9nC5xj/TZqgfop+N0rcIXeAhjzeG28ffnH # bQk9vmp2h+mKvfiEXR52yeTGdnY6U9HR01o2j8aj4S8bOrdh1nPsTm0zinxdRS1L # sVDmQTo3VobckyON91Al6GTm3dOPL1e1hyDrDo4s1SPa9E14RuMDgzEpSlwMMYpK # jIjF9zBa+RSvFV9sQ0kJ/SYjU/aNY+gaq1uxHTDCm2mCtNv8VlS8H6GHq756Wwog # L0sJyZWnjbL61mOLTqVyHO6fegFz+BnW/g1JhL0BAgMBAAGjggG4MIIBtDAOBgNV # HQ8BAf8EBAMCB4AwDAYDVR0TAQH/BAIwADAWBgNVHSUBAf8EDDAKBggrBgEFBQcD # CDBBBgNVHSAEOjA4MDYGCWCGSAGG/WwHATApMCcGCCsGAQUFBwIBFhtodHRwOi8v # d3d3LmRpZ2ljZXJ0LmNvbS9DUFMwHwYDVR0jBBgwFoAU9LbhIB3+Ka7S5GGlsqIl # ssgXNW4wHQYDVR0OBBYEFDZEho6kurBmvrwoLR1ENt3janq8MHEGA1UdHwRqMGgw # MqAwoC6GLGh0dHA6Ly9jcmwzLmRpZ2ljZXJ0LmNvbS9zaGEyLWFzc3VyZWQtdHMu # Y3JsMDKgMKAuhixodHRwOi8vY3JsNC5kaWdpY2VydC5jb20vc2hhMi1hc3N1cmVk # LXRzLmNybDCBhQYIKwYBBQUHAQEEeTB3MCQGCCsGAQUFBzABhhhodHRwOi8vb2Nz # cC5kaWdpY2VydC5jb20wTwYIKwYBBQUHMAKGQ2h0dHA6Ly9jYWNlcnRzLmRpZ2lj # ZXJ0LmNvbS9EaWdpQ2VydFNIQTJBc3N1cmVkSURUaW1lc3RhbXBpbmdDQS5jcnQw # DQYJKoZIhvcNAQELBQADggEBAEgc3LXpmiO85xrnIA6OZ0b9QnJRdAojR6OrktIl # xHBZvhSg5SeBpU0UFRkHefDRBMOG2Tu9/kQCZk3taaQP9rhwz2Lo9VFKeHk2eie3 # 8+dSn5On7UOee+e03UEiifuHokYDTvz0/rdkd2NfI1Jpg4L6GlPtkMyNoRdzDfTz # ZTlwS/Oc1np72gy8PTLQG8v1Yfx1CAB2vIEO+MDhXM/EEXLnG2RJ2CKadRVC9S0y # OIHa9GCiurRS+1zgYSQlT7LfySmoc0NR2r1j1h9bm/cuG08THfdKDXF+l7f0P4Tr # weOjSaH6zqe/Vs+6WXZhiV9+p7SOZ3j5NpjhyyjaW4emii8wggUwMIIEGKADAgEC # AhAECRgbX9W7ZnVTQ7VvlVAIMA0GCSqGSIb3DQEBCwUAMGUxCzAJBgNVBAYTAlVT # MRUwEwYDVQQKEwxEaWdpQ2VydCBJbmMxGTAXBgNVBAsTEHd3dy5kaWdpY2VydC5j # b20xJDAiBgNVBAMTG0RpZ2lDZXJ0IEFzc3VyZWQgSUQgUm9vdCBDQTAeFw0xMzEw # MjIxMjAwMDBaFw0yODEwMjIxMjAwMDBaMHIxCzAJBgNVBAYTAlVTMRUwEwYDVQQK # EwxEaWdpQ2VydCBJbmMxGTAXBgNVBAsTEHd3dy5kaWdpY2VydC5jb20xMTAvBgNV # BAMTKERpZ2lDZXJ0IFNIQTIgQXNzdXJlZCBJRCBDb2RlIFNpZ25pbmcgQ0EwggEi # MA0GCSqGSIb3DQEBAQUAA4IBDwAwggEKAoIBAQD407Mcfw4Rr2d3B9MLMUkZz9D7 # RZmxOttE9X/lqJ3bMtdx6nadBS63j/qSQ8Cl+YnUNxnXtqrwnIal2CWsDnkoOn7p # 0WfTxvspJ8fTeyOU5JEjlpB3gvmhhCNmElQzUHSxKCa7JGnCwlLyFGeKiUXULaGj # 6YgsIJWuHEqHCN8M9eJNYBi+qsSyrnAxZjNxPqxwoqvOf+l8y5Kh5TsxHM/q8grk # V7tKtel05iv+bMt+dDk2DZDv5LVOpKnqagqrhPOsZ061xPeM0SAlI+sIZD5SlsHy # DxL0xY4PwaLoLFH3c7y9hbFig3NBggfkOItqcyDQD2RzPJ6fpjOp/RnfJZPRAgMB # AAGjggHNMIIByTASBgNVHRMBAf8ECDAGAQH/AgEAMA4GA1UdDwEB/wQEAwIBhjAT # BgNVHSUEDDAKBggrBgEFBQcDAzB5BggrBgEFBQcBAQRtMGswJAYIKwYBBQUHMAGG # GGh0dHA6Ly9vY3NwLmRpZ2ljZXJ0LmNvbTBDBggrBgEFBQcwAoY3aHR0cDovL2Nh # Y2VydHMuZGlnaWNlcnQuY29tL0RpZ2lDZXJ0QXNzdXJlZElEUm9vdENBLmNydDCB # gQYDVR0fBHoweDA6oDigNoY0aHR0cDovL2NybDQuZGlnaWNlcnQuY29tL0RpZ2lD # ZXJ0QXNzdXJlZElEUm9vdENBLmNybDA6oDigNoY0aHR0cDovL2NybDMuZGlnaWNl # cnQuY29tL0RpZ2lDZXJ0QXNzdXJlZElEUm9vdENBLmNybDBPBgNVHSAESDBGMDgG # CmCGSAGG/WwAAgQwKjAoBggrBgEFBQcCARYcaHR0cHM6Ly93d3cuZGlnaWNlcnQu # Y29tL0NQUzAKBghghkgBhv1sAzAdBgNVHQ4EFgQUWsS5eyoKo6XqcQPAYPkt9mV1 # DlgwHwYDVR0jBBgwFoAUReuir/SSy4IxLVGLp6chnfNtyA8wDQYJKoZIhvcNAQEL # BQADggEBAD7sDVoks/Mi0RXILHwlKXaoHV0cLToaxO8wYdd+C2D9wz0PxK+L/e8q # 3yBVN7Dh9tGSdQ9RtG6ljlriXiSBThCk7j9xjmMOE0ut119EefM2FAaK95xGTlz/ # kLEbBw6RFfu6r7VRwo0kriTGxycqoSkoGjpxKAI8LpGjwCUR4pwUR6F6aGivm6dc # IFzZcbEMj7uo+MUSaJ/PQMtARKUT8OZkDCUIQjKyNookAv4vcn4c10lFluhZHen6 # dGRrsutmQ9qzsIzV6Q3d9gEgzpkxYz0IGhizgZtPxpMQBvwHgfqL2vmCSfdibqFT # +hKUGIUukpHqaGxEMrJmoecYpJpkUe8wggUxMIIEGaADAgECAhAKoSXW1jIbfkHk # Bdo2l8IVMA0GCSqGSIb3DQEBCwUAMGUxCzAJBgNVBAYTAlVTMRUwEwYDVQQKEwxE # aWdpQ2VydCBJbmMxGTAXBgNVBAsTEHd3dy5kaWdpY2VydC5jb20xJDAiBgNVBAMT # G0RpZ2lDZXJ0IEFzc3VyZWQgSUQgUm9vdCBDQTAeFw0xNjAxMDcxMjAwMDBaFw0z # MTAxMDcxMjAwMDBaMHIxCzAJBgNVBAYTAlVTMRUwEwYDVQQKEwxEaWdpQ2VydCBJ # bmMxGTAXBgNVBAsTEHd3dy5kaWdpY2VydC5jb20xMTAvBgNVBAMTKERpZ2lDZXJ0 # IFNIQTIgQXNzdXJlZCBJRCBUaW1lc3RhbXBpbmcgQ0EwggEiMA0GCSqGSIb3DQEB # AQUAA4IBDwAwggEKAoIBAQC90DLuS82Pf92puoKZxTlUKFe2I0rEDgdFM1EQfdD5 # fU1ofue2oPSNs4jkl79jIZCYvxO8V9PD4X4I1moUADj3Lh477sym9jJZ/l9lP+Cb # 6+NGRwYaVX4LJ37AovWg4N4iPw7/fpX786O6Ij4YrBHk8JkDbTuFfAnT7l3ImgtU # 46gJcWvgzyIQD3XPcXJOCq3fQDpct1HhoXkUxk0kIzBdvOw8YGqsLwfM/fDqR9mI # UF79Zm5WYScpiYRR5oLnRlD9lCosp+R1PrqYD4R/nzEU1q3V8mTLex4F0IQZchfx # FwbvPc3WTe8GQv2iUypPhR3EHTyvz9qsEPXdrKzpVv+TAgMBAAGjggHOMIIByjAd # BgNVHQ4EFgQU9LbhIB3+Ka7S5GGlsqIlssgXNW4wHwYDVR0jBBgwFoAUReuir/SS # y4IxLVGLp6chnfNtyA8wEgYDVR0TAQH/BAgwBgEB/wIBADAOBgNVHQ8BAf8EBAMC # AYYwEwYDVR0lBAwwCgYIKwYBBQUHAwgweQYIKwYBBQUHAQEEbTBrMCQGCCsGAQUF # BzABhhhodHRwOi8vb2NzcC5kaWdpY2VydC5jb20wQwYIKwYBBQUHMAKGN2h0dHA6 # Ly9jYWNlcnRzLmRpZ2ljZXJ0LmNvbS9EaWdpQ2VydEFzc3VyZWRJRFJvb3RDQS5j # cnQwgYEGA1UdHwR6MHgwOqA4oDaGNGh0dHA6Ly9jcmw0LmRpZ2ljZXJ0LmNvbS9E # aWdpQ2VydEFzc3VyZWRJRFJvb3RDQS5jcmwwOqA4oDaGNGh0dHA6Ly9jcmwzLmRp # Z2ljZXJ0LmNvbS9EaWdpQ2VydEFzc3VyZWRJRFJvb3RDQS5jcmwwUAYDVR0gBEkw # RzA4BgpghkgBhv1sAAIEMCowKAYIKwYBBQUHAgEWHGh0dHBzOi8vd3d3LmRpZ2lj # ZXJ0LmNvbS9DUFMwCwYJYIZIAYb9bAcBMA0GCSqGSIb3DQEBCwUAA4IBAQBxlRLp # UYdWac3v3dp8qmN6s3jPBjdAhO9LhL/KzwMC/cWnww4gQiyvd/MrHwwhWiq3BTQd # aq6Z+CeiZr8JqmDfdqQ6kw/4stHYfBli6F6CJR7Euhx7LCHi1lssFDVDBGiy23UC # 4HLHmNY8ZOUfSBAYX4k4YU1iRiSHY4yRUiyvKYnleB/WCxSlgNcSR3CzddWThZN+ # tpJn+1Nhiaj1a5bA9FhpDXzIAbG5KHW3mWOFIoxhynmUfln8jA/jb7UBJrZspe6H # USHkWGCbugwtK22ixH67xCUrRwIIfEmuE7bhfEJCKMYYVs9BNLZmXbZ0e/VWMyIv # IjayS6JKldj1po5SMIIFPTCCBCWgAwIBAgIQBNXcH0jqydhSALrNmpsqpzANBgkq # hkiG9w0BAQsFADByMQswCQYDVQQGEwJVUzEVMBMGA1UEChMMRGlnaUNlcnQgSW5j # MRkwFwYDVQQLExB3d3cuZGlnaWNlcnQuY29tMTEwLwYDVQQDEyhEaWdpQ2VydCBT # SEEyIEFzc3VyZWQgSUQgQ29kZSBTaWduaW5nIENBMB4XDTIwMDYyNjAwMDAwMFoX # DTIzMDcwNzEyMDAwMFowejELMAkGA1UEBhMCUEwxEjAQBgNVBAgMCcWabMSFc2tp # ZTERMA8GA1UEBxMIS2F0b3dpY2UxITAfBgNVBAoMGFByemVteXPFgmF3IEvFgnlz # IEVWT1RFQzEhMB8GA1UEAwwYUHJ6ZW15c8WCYXcgS8WCeXMgRVZPVEVDMIIBIjAN # BgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAv7KB3iyBrhkLUbbFe9qxhKKPBYqD # Bqlnr3AtpZplkiVjpi9dMZCchSeT5ODsShPuZCIxJp5I86uf8ibo3vi2S9F9AlfF # jVye3dTz/9TmCuGH8JQt13ozf9niHecwKrstDVhVprgxi5v0XxY51c7zgMA2g1Ub # +3tii0vi/OpmKXdL2keNqJ2neQ5cYly/GsI8CREUEq9SZijbdA8VrRF3SoDdsWGf # 3tZZzO6nWn3TLYKQ5/bw5U445u/V80QSoykszHRivTj+H4s8ABiforhi0i76beA6 # Ea41zcH4zJuAp48B4UhjgRDNuq8IzLWK4dlvqrqCBHKqsnrF6BmBrv+BXQIDAQAB # o4IBxTCCAcEwHwYDVR0jBBgwFoAUWsS5eyoKo6XqcQPAYPkt9mV1DlgwHQYDVR0O # BBYEFBixNSfoHFAgJk4JkDQLFLRNlJRmMA4GA1UdDwEB/wQEAwIHgDATBgNVHSUE # DDAKBggrBgEFBQcDAzB3BgNVHR8EcDBuMDWgM6Axhi9odHRwOi8vY3JsMy5kaWdp # Y2VydC5jb20vc2hhMi1hc3N1cmVkLWNzLWcxLmNybDA1oDOgMYYvaHR0cDovL2Ny # bDQuZGlnaWNlcnQuY29tL3NoYTItYXNzdXJlZC1jcy1nMS5jcmwwTAYDVR0gBEUw # QzA3BglghkgBhv1sAwEwKjAoBggrBgEFBQcCARYcaHR0cHM6Ly93d3cuZGlnaWNl # cnQuY29tL0NQUzAIBgZngQwBBAEwgYQGCCsGAQUFBwEBBHgwdjAkBggrBgEFBQcw # AYYYaHR0cDovL29jc3AuZGlnaWNlcnQuY29tME4GCCsGAQUFBzAChkJodHRwOi8v # Y2FjZXJ0cy5kaWdpY2VydC5jb20vRGlnaUNlcnRTSEEyQXNzdXJlZElEQ29kZVNp # Z25pbmdDQS5jcnQwDAYDVR0TAQH/BAIwADANBgkqhkiG9w0BAQsFAAOCAQEAmr1s # z4lsLARi4wG1eg0B8fVJFowtect7SnJUrp6XRnUG0/GI1wXiLIeow1UPiI6uDMsR # XPHUF/+xjJw8SfIbwava2eXu7UoZKNh6dfgshcJmo0QNAJ5PIyy02/3fXjbUREHI # NrTCvPVbPmV6kx4Kpd7KJrCo7ED18H/XTqWJHXa8va3MYLrbJetXpaEPpb6zk+l8 # Rj9yG4jBVRhenUBUUj3CLaWDSBpOA/+sx8/XB9W9opYfYGb+1TmbCkhUg7TB3gD6 # o6ESJre+fcnZnPVAPESmstwsT17caZ0bn7zETKlNHbc1q+Em9kyBjaQRcEQoQQNp # ezQug9ufqExx6lHYDjGCBFwwggRYAgEBMIGGMHIxCzAJBgNVBAYTAlVTMRUwEwYD # VQQKEwxEaWdpQ2VydCBJbmMxGTAXBgNVBAsTEHd3dy5kaWdpY2VydC5jb20xMTAv # BgNVBAMTKERpZ2lDZXJ0IFNIQTIgQXNzdXJlZCBJRCBDb2RlIFNpZ25pbmcgQ0EC # EATV3B9I6snYUgC6zZqbKqcwCQYFKw4DAhoFAKB4MBgGCisGAQQBgjcCAQwxCjAI # oAKAAKECgAAwGQYJKoZIhvcNAQkDMQwGCisGAQQBgjcCAQQwHAYKKwYBBAGCNwIB # CzEOMAwGCisGAQQBgjcCARUwIwYJKoZIhvcNAQkEMRYEFEZkW213N17eZplhW9aS # 4Ft8u+68MA0GCSqGSIb3DQEBAQUABIIBAH2Hbcgsdz9hdshmQJqublBX1hzC1YR4 # bpaVy/AmQbcMguzGHK0blSt/cH2Cjszbu9nLTgd2fr+59Ezqwa7ByldSSRshEX3f # ce1ES6AUHi8ymK/vzmMmEIlDWjG9jmwj6oRylPTB0sivzXWHdUfBTOwbXUDgGS69 # X7tJhmMspLKZZtYH8q/KTwyG0hnGKZogRBrFZJA1gJG0ufMTpzYNa38Q+bw2kVp6 # qJPEUOZ01qtYvttd/yB7JzDuSWlf92EqMUlmTeDQKyWRVVHY1CKgJ5TS0F7cCJu6 # g+MUqfFn4q7ITswYYyFDWdzUvJ80JvLaklwCWmPeg8u0Kd4405JUVo6hggIwMIIC # LAYJKoZIhvcNAQkGMYICHTCCAhkCAQEwgYYwcjELMAkGA1UEBhMCVVMxFTATBgNV # BAoTDERpZ2lDZXJ0IEluYzEZMBcGA1UECxMQd3d3LmRpZ2ljZXJ0LmNvbTExMC8G # A1UEAxMoRGlnaUNlcnQgU0hBMiBBc3N1cmVkIElEIFRpbWVzdGFtcGluZyBDQQIQ # DUJK4L46iP9gQCHOFADw3TANBglghkgBZQMEAgEFAKBpMBgGCSqGSIb3DQEJAzEL # BgkqhkiG9w0BBwEwHAYJKoZIhvcNAQkFMQ8XDTIyMDExMTIxMjExM1owLwYJKoZI # hvcNAQkEMSIEIPFD5saF0RrHd2QjtOa2vIrd8ByL1tyBC7c3GAWhfTsLMA0GCSqG # SIb3DQEBAQUABIIBADD6UtIS5ClhBsJWxXI0hX4ecH7KT+GHcct73JDOSEiY+izQ # Czp1VFlPdgFU32msIzMcPC7jBK4A0T3dr9cgewwr5IBT+omC2xRoEskAc2sPxD6q # gWQK06uxcEL5iPpAeWgYZ9lEVxeXrMoQAxiO4FrA/ka/ENIFNtslCn1LOdE4dUDY # FrPBZ2d1YsENgXprPAV5aifrYGU0s821AQ1xXzdVW9tA3HKexAa35dnrdYALyteI # IUiim1dwIDgR4v0j/KwnxIS2GjXAjZUKoVdTArTed6iRZznrMbnaMVNf21L3AjId # hvlNB7h5RYMr9EMMZJnkHxmnA/0D8s/u9/1MZqg= # SIG # End signature block |