Powershell convert table colm index to letters for excel


#==============================================================================
# Convert columns Interger values to Letters for excel column range selections
#==============================================================================
# 1 based values, if your array is not zero based subtract 1 from index first
function ExcelColumnIndexToName {
param(
[Parameter(Mandatory = $true)] [int] $index
)

[string]$range = [string]::Empty;
if ($Index -lt 0 ) { return $range; }

[int]$a = 26;
$mfr = [Math]::Floor([Math]::Log(($Index) * ($a – 1) / $a + 1, $a))
[int]$x = [int]$mfr;
$Index -= [int]([Math]::Pow($a, $x) – 1) * $a / ($a – 1);
for ([int] $i = $x + 1; $Index + $i -gt 0; $i–) {
$range = ([char](65 + $Index % $a)).ToString() + $range;
$Index /= $a;
}
return $range;
}

 

So in another method that loops through the columns I could do the following

 

foreach ($tmpDataColm in $table.Columns) {

# This is the index integer value of the column in the columns array of the datatable

$colIndex = $table.Columns.IndexOf($tmpDataColm);

Write-Host($colIndex);

$colLetter = ExcelColumnIndexToName($colIndex);

#$excelCol = ExcelColumnIndexToName($table.Columns.IndexOf($tmpDataColm));

Write-Host(“Table Column index value: $colIndex to Excel letter: ” + $colLetter );

 

}

So the output in the console looks like this

You can use this to make range selections without knowing how many columns the sql is creating and etc…