Read Excel sheet from PowerShell - Display issue-CodePudding

I have a script that can read from an excel all the columns. The user need to choose the right column so I am displaying the table but it look very bad (Also tried with | Format-Table -AutoSize -Wrap that display it 2 rows. without the -AutoSize there is only the start with ...)

In addition how can I search according to background color and string? I am using this for color:

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

And this for string:

$val2 = $worksheet.Cells.Item($row, $columnNumber).value2
if ($val2 -match $searchValue)  {
    # output an object with both values from columns A and B
    [PsCustomObject]@{Patch = $worksheet.Cells.Item($row, 1).Value2}

I want to get a result only if I have a specific color and a string inside

# get the number of rows in the sheet
$rowMax = $worksheet.UsedRange.Rows.Count
# get the number of columns in the sheet
$colMax = $worksheet.UsedRange.Columns.Count

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

Each result should be in one row

CodePudding user response：

With the third code, you get a (ordered) Hashtable of the Excel column Names combined with their indices:

# get the number of rows in the sheet
$rowMax = $worksheet.UsedRange.Rows.Count
# get the number of columns in the sheet
$colMax = $worksheet.UsedRange.Columns.Count

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

If you now want to present this HashTable of column names / Excel column indices as a menu to the user, you could do this:

# draw the menu
$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
}
Write-Host  # add an empty line

# quick and dirty..
do {
    $answer = Read-Host "Please enter the number of the update you need to install. 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}$')

Next examine what the user typed in and perform the appropriate action

Either using a switch like this:

switch ($answer) {
    'Q'  { break } # user wants to quit
    '1'  { <# run 'Patch 7.5.31.29' #> }
    '2'  { <# run 'Bulk Tool 7.5.40.197-7.5.40.300' #> }
    '3'  { <# run 'Side VIP 8.6.22' #> }
    # and so on
}

Or more generic like:

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 #>
    }
}

This is what it will look like on screen:

 1: Patch 7.5.31.29
 2: Bulk Tool 7.5.40.197-7.5.40.300
 3: Side VIP 8.6.22

Please enter the number of the update you need to install. Press Q to exit: 2
You chose to perform 'Bulk Tool 7.5.40.197-7.5.40.300'