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

psoutput

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

 

Powershell importing data from SQL into Excel COM OBJECT interop and EMAILING

I needed to pull data from a ASE Database (T-SQL), filter it and then dump it into a excel workbook.  Finally wrap all that up and attach it to a Email via SMTP…

See below for inline code or jump over to my github repo
https://github.com/wlafrance/Powershell/blob/master/QueryExcelEmail.ps

Continue reading “Powershell importing data from SQL into Excel COM OBJECT interop and EMAILING”