我有一個可以從 excel 中讀取所有列的腳本。用戶需要選擇正確的列，所以我正在顯示表格，但它看起來很糟糕（也嘗試使用 | Format-Table -AutoSize -Wrap 顯示它 2 行。沒有 -AutoSize 只有開頭... )
另外如何根據背景顏色和字串進行搜索？我用這個顏色：
$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}
這對于字串：
$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}
只有當我有一個特定的顏色和一個字串時，我才想得到一個結果
# 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
每個結果應該在一行中
uj5u.com熱心網友回復：
使用第三個代碼，您將獲得 Excel 列名稱的（有序）哈希表及其索引：
# 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}
}
如果您現在想要將此列名/Excel 列索引的 HashTable 作為選單呈現給用戶，您可以這樣做：
# 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}$')
接下來檢查用戶輸入的內容并執行適當的操作
使用這樣的開關：
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
}
或更通用的，例如：
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 #>
}
}
這是它在螢屏上的樣子：
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'
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/438569.html