'Read Excel sheet from PowerShell - Display issue

Thanks you Theo. Now its look better but I prefer to display each one in the same row and not as below:

enter image description here

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

enter image description here



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source