我有一個從 excel 讀取并讓用戶選擇一列的腳本。問題是該串列不可讀，我想向用戶展示選擇帶有 Out-Gridview 的 UI 版本的選項還有一件事，我需要答案是一個數字 這是腳本：
$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 #>
}
}
它看起來像這樣：
uj5u.com熱心網友回復：
要讓用戶使用 選擇工具版本Out-GridView，您需要構建一個物件陣列，如下所示：
$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 #>
}
請不要忘記在代碼完成后將使用過的 COM 物件從記憶體中洗掉，否則它們會一直存在。
$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()
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/448424.html