PowerShell循环逐行写入Excel仅最后一行生效问题排查

需求：清理含冗余字符的混乱邮件列表并写入Excel以作后续处理。遍历文本文件完成清理后，使用Out-File逐行写入功能正常，但改用Excel COM对象循环写入时仅最后一行被保存。

正常代码（功能正常） #

$untrimmedEmails = 'S:\PS_Scripts\Data\trimEmails.txt'
foreach ($line in Get-Content $untrimmedEmails) {
    $row = 1
    $newLine = $line.Split(':')[1]
    Out-File -FilePath .\Notes\trimResults.txt -InputObject $newLine.trimEnd('>') -Encoding ASCII -Append
    $row++
}

异常代码（仅最后一行保存） #

$untrimmedEmails = 'S:\PS_Scripts\Data\trimEmails.txt'
& {
    $excel = New-Object -ComObject Excel.Application
    $wb = $excel.Workbooks.Open('S:\PS_Scripts\Data\Leavers.xlsx')
    $ws = $wb.WorkSheets.Add()
    foreach ($line in Get-Content $untrimmedEmails) {
        $row = 1
        $newLine = $line.Split(':')[1]
        $ws.cells.item($row, 1).Value2 = $newLine.TrimEnd('>')
        $row++
    }
    $wb.SaveAs($CSVImportFile, 6)
    $wb.Close()
    $excel.Quit()
}
[GC]::Collect()

故障原因 #

核心问题是**$row = 1被放在了foreach循环内部**：

• 每次循环迭代时，$row都会被重置为1，导致所有处理后的邮件地址都被写入Excel的第1行，后续行的内容直接覆盖前一行。
• 循环结束后，只有最后一次迭代的内容保留在第1行，所以最终仅显示最后一行数据。

修复后的代码 #

把$row = 1移到循环外部，确保每次循环时$row能持续递增：

$untrimmedEmails = 'S:\PS_Scripts\Data\trimEmails.txt'
# 提前定义保存路径变量
$CSVImportFile = 'S:\PS_Scripts\Data\ProcessedLeavers.csv'

& {
    $excel = New-Object -ComObject Excel.Application
    $excel.Visible = $false # 后台运行Excel，避免弹窗干扰
    $wb = $excel.Workbooks.Open('S:\PS_Scripts\Data\Leavers.xlsx')
    $ws = $wb.WorkSheets.Add()
    $row = 1 # 将行号初始化移到循环外
    foreach ($line in Get-Content $untrimmedEmails) {
        $newLine = $line.Split(':')[1]
        $ws.cells.item($row, 1).Value2 = $newLine.TrimEnd('>')
        $row++
    }
    $wb.SaveAs($CSVImportFile, 6) # 6对应CSV格式
    $wb.Close()
    $excel.Quit()
}
# 强制回收COM对象，避免Excel进程残留
[GC]::Collect()
[GC]::WaitForPendingFinalizers()

额外注意事项：

• 必须提前定义$CSVImportFile变量，否则SaveAs操作会报错。
• 添加$excel.Visible = $false可让Excel在后台运行，避免不必要的界面弹窗。
• 回收COM对象时增加[GC]::WaitForPendingFinalizers()，确保Excel进程彻底关闭。

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