How to find a column number in excel sheet with PowerShell-CodePudding

I have a working script that search rows in the specific column but I need to set the number of the column, So before I run the script I am writing =column() in a specific row in that column. I want to know how to get this number according to the columns name. This will give me the option to let the user choose on which column to work.

Open the sheet and search in a specific workbook:

$excel = New-Object -ComObject Excel.Application
$Excel.Visible = $false
$Excel.DisplayAlerts = $False # Disable comfirmation prompts
$workbook  = $excel.Workbooks.Open($ExcelFile)
$worksheet = $workbook.Worksheets.Item("VIP List")

Search for a specific row color: $columnNumber = #

$patches = for ($row = 1; $row -le $rowMax; $row  ) {
    $val = $worksheet.Cells.Item($row, $columnNumber).Interior.ColorIndex # 2 is column B
    if ($val -eq $searchcolorForPatch)  {
        # output an object with both values from columns A and B
        [PsCustomObject]@{Patch = $worksheet.Cells.Item($row, 1).Value2}
    }
}

I want to display all the column names in the workbook with his number and let the user to choose. It is possible?

CodePudding user response：

You can get the names and belonging indices like this:

$colMax = $sheet.UsedRange.Columns.Count

# create a hash with column header names and their indices
$columns = [ordered]@{}
for ($col = 1; $col -le $colMax; $col  ) {
    $name = $sheet.Cells.Item(1, $col).Value()  # assuming the first row has the headers
    $columns[$name] = $col
}

You now have an ordered Hashtable of the column names and their indices

For instance:

$columns['FirstColumn']         # --> 1
$columns['SomeColumnInBetween'] # --> 12