通过 Powershell 关闭时无法停止所有 excel 进程 [英] Can't get all excel processes to stop when closing through Powershell

问题描述

使用此代码，我打开 excel(使用可见 = false，因此用户无法看到它)，写入工作簿，然后在脚本结束后打开 excel(使其可见)或在不保存的情况下完全关闭它.当我保存excel时，保持打开状态，结束脚本，然后稍后手动关闭excel，任务管理器中没有后台进程.但是，当我使用脚本关闭 excel 时，它仍保留在任务管理器中.

With this code, I am opening excel(with visible = false so the user cannot see it), writing to a workbook, and then either opening excel(making it visible) after the script ends or closing it completely without saving. When I save excel, leave it open, end the script, and then manually close excel later, there are no background processes in the task manager. However, when I close excel with the script, it remains in the task manager.

以下是我开始使用 Excel 的方法:

Here is how I start excel:

    $script:excel = new-object -ComObject excel.application # create excel object
    $excel.visible = $false # hide excel window
    $script:workbook = $excel.Workbooks.Add() # add excel file
    $script:ws1 = $workbook.Worksheets.Item(1) # create new sheet

这是我关闭它的方法:

        [gc]::Collect()
        [gc]::WaitForPendingFinalizers()
        if ($script:closeOnX) {
            #only do this if not keeping excel open
            Write-Host "Closing Excel"
            $excel.Quit()
        }
        [System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel)

closeOnX 只是一个标志，因此它实际上只在某些情况下关闭 Excel 应用程序.其余的在每次脚本结束时执行.

closeOnX is just a flag so it only actually closes the excel app on certain occasions. The rest is executed each time the script ends.

当我结束脚本并同时关闭 excel 时，我只想关闭当前的 excel 进程(这就是为什么我不想停止进程)而不关闭用户可能正在工作的其他工作簿在.

When I end the script and close excel at the same time, I want only the current excel process to close (which is why I don't want to stop-process) and not close other workbooks that the user may be working on.

当我结束脚本，保存并打开excel时，我希望用户手动关闭excel时所有进程都消失.(这是有效的)

When I end the script, save and open the excel, I want all the processes to be gone when the user manually closes excel. (This is working)

推荐答案

有关如何发布 (Excel) COM 对象的一般指南，请参阅底部.

For general guidance on how to release (Excel) COM objects, see the bottom section.

$excel.Quit() 足以最终终止 Excel 进程，但是 何时发生取决于垃圾收集器下次运行的时间.

$excel.Quit() is enough to eventually terminate the Excel process, but when that happens depends on when the garbage collector happens to run the next time.

您尝试使用 [System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel) 显式发布 Excel 是不够的，因为变量 $script:workbook 和 $script:ws1 仍然有对 Excel COM 对象的引用，在变量超出范围并且这些引用最终被垃圾收集之前不会被释放.

Your attempt to explicitly release Excel with [System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel) is insufficient, because variables $script:workbook and $script:ws1 still have references to Excel COM objects that will not be released until the variables have gone out of scope and these references are eventually garbage collected.

因此，为了加速释放，你也必须显式释放这些引用，在运行垃圾收集器之前:

Therefore, in order to speed up the release, you must release these references explicitly too, before running the garbage collector:

$script:excel = new-object -ComObject excel.application # create excel object
$script:workbook = $excel.Workbooks.Add() # add a workbook
$script:ws1 = $workbook.Worksheets.Item(1) # reference the 1st sheet

# ...

# You must *always* call .Quit(), otherwise the Excel process lingers
# for the entire OS users session.
$script.excel.Quit()

# Relinquish references to *all* Excel objects.
$script:excel = $script:workbook = $script:ws1 = $null
# Alternative:
# Remove-Variable -Scope Script excel, workbook, ws1

# With all references released, running the garbage collector
# should now release the COM objects and terminate Excel
# at shortly after.
[GC]::Collect()
# Note that calling [GC]::WaitForPendingFinalizers() afterwards
# to wait for *completion* of the *doesn't work here*,
# because the CLR-managed RCWs (Runtime-Callable Wrappers for COM objects)
# do not guarantee deterministic release of the underlying COM objects.

因为手动清除/删除所有相关变量容易出错且麻烦，您可以通过在临时子作用域中创建本地引用 COM 对象的所有变量来自动化该过程，使用 &{ ... }:

Because manually clearing / removing all relevant variables is error-prone and cumbersome, you can automate the process by creating all variables that reference COM objects locally in a temporary child scope, using & { ... }:

& {  # Create a temporary child scope.

  $excel = new-object -ComObject excel.application # create excel object
  $workbook = $excel.Workbooks.Add() # add a workbook
  $ws1 = $workbook.Worksheets.Item(1) # reference the 1st sheet

  # You must *always* call .Quit(), otherwise the Excel process lingers
  # for the entire OS users session.
  $excel.Quit()

} # On exiting this block, $excel, $workbook, and $ws1
  # go out of scope and release the COM objects when the
  # garbage collector runs next.

# Run the garbage collector now.
# The Excel process should terminate shortly after.
[GC]::Collect()

---

释放 (Excel) COM 对象:

• 始终调用 .Quit() - 没有它，幕后创建的 Excel 进程永远不会终止，即使 PowerShell 会话结束也不会终止(当然，当 OS 用户会话作为一个整体结束时，它就会终止).

  ---

  Releasing (Excel) COM Objects:

  • ALWAYS call .Quit() - without it, the Excel process that is created behind the scenes is never terminated, not even when the PowerShell session ends (of course, it is terminated when the OS user session as a whole ends).

    $excel.Quit() 通常是所有需要的(除非全局变量变量用于存储对 Excel 对象的引用)，因为随着引用 COM 对象的脚本/函数变量超出范围，底层 COM 对象最终也会自动释放.

    $excel.Quit() is usually all that is needed (unless global variables variables are used to store references to Excel objects), because with the script / function variables that reference COM objects going out of scope, the underlying COM objects are eventually released automatically too.

    • 但是，它可能需要一个 - 变化的，不可预测的 - Excel 进程实际终止，这取决于超出范围变量的对象何时垃圾收集.
    • However, it may take a - varying, unpredictable - while for the Excel process to actually terminate, depending on when the objects that that the gone-out-of-scope variables are garbage-collected.

    如果您希望尽快释放 COM 对象:

    • 您必须释放对存储在单个变量中的所有 COM 对象的引用:

    • You must release references to all COM objects you've stored in individual variables:

    • 请注意，不需要[System.Runtime.InteropServices.Marshal]::ReleaseComObject() 调用；因为有一个更简单、更强大的替代方案:
    • 或者:清除所有显式引用 COM 对象的变量，通过(参见上面的第一个代码片段):
      • 任一:将它们全部设置为 $null.
      • 或:将他们的名字传递给Remove-Variable
      • Note that there is NO NEED for [System.Runtime.InteropServices.Marshal]::ReleaseComObject() calls; because there is a simpler and more robust alternative:
      • Either: Clear all variables referencing COM objects explicitly, by (see first code snippet above):
        • either: setting them all to $null.
        • or: passing their names to Remove-Variable
        • 使用在子作用域中引用 COM 对象的变量，通过 &{ ... } 块，这意味着引用将在离开子作用域时隐式释放.
        • Use variables that reference COM objects in a child scope, via a & { ... } block, which means that the references will implicitly be released on leaving the child scope.

        这些方法不仅比调用 [System.Runtime.InteropServices.Marshal]::ReleaseComObject() 更简单、更简洁，而且还能防止以后尝试访问已发布的 COM对象.

        These approaches are not only simpler and more concise than calling [System.Runtime.InteropServices.Marshal]::ReleaseComObject(), but also prevent later attempts at accessing already-released COM objects.

        之后，调用 [GC]::Collect() 以强制即时垃圾回收 - 但请注意您的代码被阻止 当垃圾收集器运行时(尽管通常只是短暂的).

        Afterwards, call [GC]::Collect() to force instant garbage collection - but note that your code is blocked while the garbage collector runs (albeit usually only briefly) .

        如果您另外想确保在继续之前已完成释放 COM 对象:

        If you additionally want to make sure that releasing the COM objects has completed before you continue:

        • 注意:可能很少需要这样做，因为 Excel 通常在其 .Quit() 方法被调用时释放资源，例如关闭文件已打开.

        • Note: There's probably rarely a need for this, because Excel usually releases resources when its .Quit() method is called, such as closing files it has open.

        您可以在调用 [GC]::Collect() 之后调用 [GC]::WaitForPendingFinalizers()，但是它可能不起作用:管理对 COM 对象的访问的 RCW(运行时可调用包装器)自己没有保证发布当时的 COM 资源；来自文档(强调):

        You can call [GC]::WaitForPendingFinalizers() after calling [GC]::Collect(), but it is likely not to work: The RCWs (runtime-callable wrappers) that manage access to the COM objects themselves being finalized does not guarantee release of the COM resources at that time; from the docs (emphasis added):

        • "当 COM 对象上的引用计数变为 0 时，COM 对象通常被释放，尽管这取决于 COM 对象的实现并且超出了运行时."

        • "When the reference count on the COM object becomes 0, the COM object is usually freed, although this depends on the COM object's implementation and is beyond the control of the runtime."

        确实，在目前的情况下，Excel 进程不会在 [GC]::WaitForPendingFinalizers() 之前终止调用返回；这只会在之后一秒左右发生.

        Indeed, in the case at hand the Excel process does not terminate before the [GC]::WaitForPendingFinalizers() call returns; that only happens within a second or so afterwards.

        这篇关于通过 Powershell 关闭时无法停止所有 excel 进程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！