PowerShell操作Excel残留进程 无需批量杀进程的关闭方案咨询

核心原因 #

Excel COM对象采用引用计数机制，只要脚本中还有任意层级的子对象（工作表、单元格、范围等）未被释放，引用计数不归零，Excel进程就不会退出。你之前的写法只调用了Quit()，没有处理子对象引用，所以会残留。

代码问题&修正 #

你原代码存在2个核心问题：

• 笔误：$Worksheets = $Workbooks.worksheets 中$Workbooks为未定义变量（你之前定义的是单数$Workbook），会导致异常引用
• 未显式释放所有层级的Excel COM子对象

完整修正后代码 #

$WorkDir = "D:\Test\QR_ES\RG_Temp"
$BGDir = "D:\Test\QR_ES\3_BG"
$File = "D:\Test\QR_ES\4_Adr_Excel\KD_eMail.xlsx"
$SentDir = "D:\Test\QR_ES\RG_Temp\Sent\Dunning"

chdir $WorkDir

$firstPageList = Get-ChildItem "$WorkDir\1*.pdf" -File -Name

ForEach ($firstPage in $firstPageList)
{
    $secondPage = "$BGDir\BG_RG.pdf"
    $output = "Dunn-$firstPage"
    invoke-command {pdftk $firstPage background $secondPage output $output}
}

del 1*.pdf

gci $WorkDir\Dunn-*.pdf | rename-item -newname {$_.Name.Substring(5)} -Force

# 初始化Excel对象
$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $false
$Workbook = $Excel.workbooks.open($file)

$DunnList = Get-ChildItem "$WorkDir\1*.pdf" -File -Name

ForEach ($Dunn in $DunnList)
{
    # 修正笔误，改为单数$Workbook
    $Worksheet = $Workbook.Worksheets.Item("KD_eMail")
    $Range = $Worksheet.Range("A1").EntireColumn
    $DunnSearch = $Dunn.Substring(0,5)
    $SearchString = $DunnSearch
    $Search = $Range.find($SearchString)
    $Recipient = $Worksheet.Cells.Item($Search.Row, $Search.Column + 1)

    $Msg = "<span style='font-family:Calibri;font-size:12pt;'>Test</span>"

    $Outlook = New-Object -ComObject Outlook.Application
    $namespace = $Outlook.GetNameSpace("MAPI")
    $namespace.Logon($null, $null, $false, $true)
    $EmailFrom = ('test@test.com')
    $account = $outlook.Session.Accounts.Item($EmailFrom)
    $Mail = $Outlook.CreateItem(0)
    $Mail.HTMLBody = $Msg
    $Mail.Subject = "OP - $SearchString"

    $Mail.To = $Recipient

    function Invoke-SetProperty {
        param(
            [__ComObject] $Object,
            [String] $Property,
            $Value        
        )
        [Void] $Object.GetType().InvokeMember($Property,"SetProperty",$NULL,$Object,$Value)
    }
    Invoke-SetProperty -Object $mail -Property "SendUsingAccount" -Value $account

    $Mail.Attachments.Add("$WorkDir\$Dunn")
    $Mail.Save()
    $Mail.close(1)
    $Mail.Send()

    # 释放本次循环用到的Excel子对象
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Recipient) | Out-Null
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Search) | Out-Null
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Range) | Out-Null
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Worksheet) | Out-Null

    # 释放Outlook相关对象，避免Outlook残留
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($account) | Out-Null
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($namespace) | Out-Null
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Mail) | Out-Null
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Outlook) | Out-Null
    
    # 变量置空
    $Recipient = $Search = $Range = $Worksheet = $account = $namespace = $Mail = $Outlook = $null
}

$workbook.close($false)
# 释放工作簿对象
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Workbook) | Out-Null
$Workbook = $null

$Excel.Quit()
# 释放Excel根对象
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel) | Out-Null
$Excel = $null

# 强制垃圾回收清理残留引用
[GC]::Collect()
[GC]::WaitForPendingFinalizers()

chdir $WorkDir
del 1*.pdf

关键说明 #

• 该方案仅释放你脚本中创建的Excel实例的所有引用，完全不会影响你正在使用的其他Excel文件，不需要杀进程
• 不要在脚本运行过程中手动打断执行，否则释放逻辑没运行还是会出现残留
• 额外补充了Outlook COM对象的释放逻辑，避免后台残留Outlook进程

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