Powershell - Convert list to UI with Out-Gridview-CodePudding

I have a script that read from excel and let the user to choose a column. The issue is that the list is not readable and I want to show the user the option to choose the version with UI with Out-Gridview One more thing, I need that the answer will be a number Here is the script:

$ExcelObject = New-Object -ComObject Excel.Application
$ExcelWorkBook = $ExcelObject.Workbooks.Open($SharePointSiteURL)
$ExcelWorkSheet = $ExcelWorkBook.Sheets.Item("VIP List")
$rowMax = $ExcelWorkSheet.UsedRange.Rows.Count 
$colMax = $ExcelWorkSheet.UsedRange.Columns.Count 
$columns = [ordered]@{}
for ($col = 1; $col -le $colMax; $col  ) {
    $name = $ExcelWorkSheet.Cells.Item(1, $col).Value()  # assuming the first row has the headers
    if ($name -ne $null){
    $columns[$name] = $col} 
}

$columns.GetEnumerator() | ForEach-Object {
    # {0,2} means to write the index number from $_.Value right aligned for two digits
    '{0,2}: {1}' -f $_.Value, $_.Name
}
do {
    $answer = Read-Host "Please enter the number of the column you want to read from" #. Press Q to exit
    # ask this question until the user enters a number or 'Q'
} until ($answer -eq 'Q' -or $answer -match '^\d{1,2}$')

switch ($answer) {
    'Q'  { break } # user wants to quit
    {1..$columns.Count} {
        # get the Name from the chosen value
        $action = $columns.Keys | Where-Object {$columns["$_"] -eq $answer}
        Write-Host "You chose to perform: '$action'" -ForegroundColor Cyan
        <# run $action #>
    }
}

It looks like this:

CodePudding user response：

To let the user select the tool version using Out-GridView, you need to build an array of objects, like below:

$ExcelObject = New-Object -ComObject Excel.Application
$ExcelWorkBook = $ExcelObject.Workbooks.Open($SharePointSiteURL)
$ExcelWorkSheet = $ExcelWorkBook.Sheets.Item("VIP List")
$rowMax = $ExcelWorkSheet.UsedRange.Rows.Count 
$colMax = $ExcelWorkSheet.UsedRange.Columns.Count 

# now, have the loop output objects that will be collected in variable $columns
$columns = for ($col = 1; $col -le $colMax; $col  ) {
    $name = $ExcelWorkSheet.Cells.Item(1, $col).Value()  # assuming the first row has the headers
    # if $name is not empty or whitespace only
    if ($name -match '\S') { 
        [PsCustomObject]@{
            Number  = $col
            Version = $name
        }    
    } 
}

# output to Out-GridView with -PassThru parameter so you can capture the selected item
$answer = ($columns | Out-GridView -Title 'Please select' -PassThru).Number
# if the user did not cancel
if ($answer) {
    # get the Name from the chosen value
    $action = $columns[$answer -1].Version
    Write-Host "You chose to perform: '$action'" -ForegroundColor Cyan
    <# run $action #>
}

Please do not forget to remove the used COM objects from memory when the code is done, otherwise they will linger on..

$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($ExcelWorkSheet)
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($ExcelWorkBook)
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($ExcelObject)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()