PowerShell switch语句匹配异常问题求助

我编写了如下PowerShell代码，用于读取指定路径下Excel文件的A4单元格内容存入$cell数组，同时记录文件路径到$filepath数组。但使用switch -exact匹配$cell值时，输出全是no match found，偶尔匹配成功还会分类错误（比如RS被识别为Left）。

原代码 #

### created arrays
$cell = [System.Collections.ArrayList]@()
$filepath = [System.Collections.ArrayList]@()
$index = $cell.IndexOf($_)
### this code pulls values from excel spread sheet as well as that files name and path 
Get-ChildItem C:\UserS\chaos\OneDrive\Documents\working\srs\dynamic*  | ForEach-Object {
    $xl = New-Object -ComObject excel.application
    $xl.Visible = $false
    $woorkbookactive = $xl.Workbooks.Open($_.FullName)
    $woorksheetactive = $woorkbookactive.Worksheets("Sheet1")
    $RANGE = $woorksheetactive.Range("A4")
    $cell.Add($RANGE.Value())
    $filepath.Add($_.FullName)
    $xl.Quit()
}
### the Above code produces these values
Selected Criteria: Enrolment Status: Left                                   
Selected Criteria: Enrolment Status: Active                                     
Selected Criteria: Enrolment Status: Active Permission Type: RESOURCE SCHEME    
###

$cell
Start-Sleep -Seconds 2
switch -exact ($cell)
{
 'Selected Criteria: Enrolment Status: Active  Permission Type: RESOURCE SCHEME'{Write-Host "Found RS";Write-Host $index};#Rename-Item -Path $filepath[$index] -NewName "DynamicRS.xlsx";continue}
 'Selected Criteria: Enrolment Status: Left'{Write-Host "Found Left";Write-Host $index};#Rename-Item -Path $filepath[$index] -NewName "DynamicLeft.xlsx";continue}
 'Selected Criteria: Enrolment Status: Active' {Write-Host "Found Active";Write-Host $index};#Rename-Item -Path $filepath[$index] -NewName "DynamicActive.xlsx";continue} 
default{write-host "no match found"}
}

实际输出 #

no match found
no match found
no match found

预期输出 #

Found RS
Found Left
Found Active 

核心问题 #

1. $index初始化错误：数组为空时就执行$index = $cell.IndexOf($_)，此时$_无有效值，$index始终为-1，无法对应正确的文件索引。
2. 字符串空白干扰：Excel读取的字符串可能包含不可见空白字符（如换行、全角空格），或匹配串的空格数量与实际值不一致，导致精确匹配失败。
3. 数组遍历索引不对应：直接用switch遍历$cell数组时，无法获取当前元素的索引，导致索引与文件路径不匹配。

修正后的代码 #

# 初始化数组
$cell = [System.Collections.ArrayList]@()
$filepath = [System.Collections.ArrayList]@()

# 读取Excel内容
Get-ChildItem C:\UserS\chaos\OneDrive\Documents\working\srs\dynamic* | ForEach-Object {
    $xl = New-Object -ComObject excel.application
    $xl.Visible = $false
    try {
        $workbook = $xl.Workbooks.Open($_.FullName)
        $worksheet = $workbook.Worksheets("Sheet1")
        # 去除首尾空白字符，消除匹配干扰
        $cellValue = $worksheet.Range("A4").Value().Trim()
        $cell.Add($cellValue)
        $filepath.Add($_.FullName)
    }
    finally {
        $xl.Quit()
        # 清理COM对象，避免Excel进程残留
        [System.Runtime.Interopservices.Marshal]::ReleaseComObject($worksheet) | Out-Null
        [System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook) | Out-Null
        [System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl) | Out-Null
        [GC]::Collect()
        [GC]::WaitForPendingFinalizers()
    }
}

# 遍历数组，同步获取索引与值
for ($i = 0; $i -lt $cell.Count; $i++) {
    $currentValue = $cell[$i]
    switch -exact ($currentValue) {
        'Selected Criteria: Enrolment Status: Active Permission Type: RESOURCE SCHEME' {
            Write-Host "Found RS"
            Write-Host $i
            # Rename-Item -Path $filepath[$i] -NewName "DynamicRS.xlsx"
            break
        }
        'Selected Criteria: Enrolment Status: Left' {
            Write-Host "Found Left"
            Write-Host $i
            # Rename-Item -Path $filepath[$i] -NewName "DynamicLeft.xlsx"
            break
        }
        'Selected Criteria: Enrolment Status: Active' {
            Write-Host "Found Active"
            Write-Host $i
            # Rename-Item -Path $filepath[$i] -NewName "DynamicActive.xlsx"
            break
        }
        default {
            Write-Host "no match found for value: '$currentValue'"
        }
    }
}

关键修复点 #

• 去除空白字符：用.Trim()清理单元格值的首尾空白，避免不可见字符导致匹配失败。
• 正确获取索引：使用for循环遍历数组，通过$i直接获取当前元素索引，确保与$filepath数组一一对应。
• 清理COM对象：添加try/finally块释放Excel COM对象，防止后台进程残留。
• 修正匹配字符串：调整匹配串的空格数量，与实际读取的单元格值保持一致。

内容的提问来源于stack exchange，提问作者chaosblade201