cp-miv.ExcelCoordinate.psm1
|
<# .SYNOPSIS Convert and Excel cell coordinate (i.e. D3) to a hashtable with Row and Column properties (i.e. Column = 4, Row = 3) #> function ConvertFrom-ExcelCellCoordinate { [OutputType([hashtable])] param ( [Parameter(ValueFromPipeline = $true)] [string] $InputObject ) process { [hashtable] $cell = Expand-ExcelCellCoordinate -InputObject $InputObject [int[]] $asciiCodes = Convert-StringToBase26 -InputObject $cell.Column $cell.Column = Convert-Base26ToInt -InputObject $asciiCodes Write-Output $cell } } <# .SYNOPSIS Separate the components of an Excel cell coordinate into Row and Column properties #> function Expand-ExcelCellCoordinate { [OutputType([hashtable])] param ( [Parameter(ValueFromPipeline = $true)] [string] $InputObject ) process { if ($InputObject -notmatch '^(?<Column>[a-z]{1,3})(?<Row>\d{1,7})$') { throw [System.ArgumentException]::new('InputObject should be in format LettersNumbers', 'InputObject') } [hashtable] $cell = @{ Row = [int]$Matches['Row'] Column = $Matches['Column'].ToUpperInvariant() } Write-Output $cell } } <# .SYNOPSIS Convert a String to a reversed int array where each character is represented by it's position in alphabet #> function Convert-StringToBase26 { [OutputType([int[]])] param ( [Parameter(ValueFromPipeline = $true)] [string] $InputObject ) process { if ([string]::IsNullOrEmpty($InputObject)) { Write-Output (, [int[]]::new(0)) return } [int[]] $parts = $InputObject.ToUpperInvariant().ToCharArray() $parts = $parts | ForEach-Object { $_ - 64 }; [Array]::Reverse($parts) Write-Output (, $parts) } } <# .SYNOPSIS Convert an int array representing ranking in alphabet to a decimal representation. #> function Convert-Base26ToInt { [OutputType([int])] param ( [Parameter(ValueFromPipeline = $true)] [int[]] $InputObject ) process { [int] $result = 0; for ($i = 0; $i -lt $InputObject.Length; $i++) { $result += [Math]::Pow(26, $i) * $InputObject[$i] } Write-Output $result } } |