excel - 使用 VB.NET 获取正在运行的 Excel 实例

Question

我有以下工作代码取自 this answer :

Option Compare Binary
Option Explicit On
Option Infer On
Option Strict Off

Imports Microsoft.Office.Interop
Imports System.Collections.Generic
Imports System.Runtime.InteropServices

Friend Module Module1
    Private Declare Function GetDesktopWindow Lib "user32" () As IntPtr
    Private Declare Function EnumChildWindows Lib "user32.dll" (ByVal WindowHandle As IntPtr, ByVal Callback As EnumWindowsProc, ByVal lParam As IntPtr) As Boolean
    Private Declare Function GetClassName Lib "user32.dll" Alias "GetClassNameA" (ByVal hWnd As IntPtr, ByVal lpClassName As String, ByVal nMaxCount As Integer) As Integer
    Private Delegate Function EnumWindowsProc(ByVal hwnd As IntPtr, ByVal lParam As Int32) As Boolean
    Private Declare Function AccessibleObjectFromWindow Lib "oleacc" (ByVal Hwnd As IntPtr, ByVal dwId As Int32, ByRef riid As Guid, <MarshalAs(UnmanagedType.IUnknown)> ByRef ppvObject As Object) As Int32
    Private lstWorkBooks As New List(Of String)
    Public Sub Main()
        GetExcelOpenWorkBooks()
    End Sub
    Private Sub GetExcelOpenWorkBooks()
        EnumChildWindows(GetDesktopWindow(), AddressOf GetExcelWindows, CType(0, IntPtr))
        If lstWorkBooks.Count > 0 Then MsgBox(String.Join(Environment.NewLine, lstWorkBooks))
    End Sub
    Public Function GetExcelWindows(ByVal hwnd As IntPtr, ByVal lParam As Int32) As Boolean
        Dim Ret As Integer = 0
        Dim className As String = Space(255)
        Ret = GetClassName(hwnd, className, 255)
        className = className.Substring(0, Ret)
        If className = "EXCEL7" Then
            Dim ExcelApplication As Excel.Application
            Dim ExcelObject As Object = Nothing
            Dim IDispatch As Guid
            AccessibleObjectFromWindow(hwnd, &HFFFFFFF0, IDispatch, ExcelObject)
            If ExcelObject IsNot Nothing Then
                ExcelApplication = ExcelObject.Application
                If ExcelApplication IsNot Nothing Then
                    For Each wrk As Excel.Workbook In ExcelApplication.Workbooks
                        If Not lstWorkBooks.Contains(wrk.Name) Then
                            lstWorkBooks.Add(wrk.Name)
                        End If
                    Next
                End If
            End If
        End If
        Return True
    End Function
End Module

它将用于获取所有打开/运行的 Excel 实例/应用程序的引用。

如果不在线查找，我永远不会猜到该怎么做，因为我不太了解，所以这可能不是最好的方法，是 bug/error prone . 我正在尝试转option strict在 ( 1 ， 2 )所以我更改了 ExcelApplication = ExcelObject.Application 行至 ExcelApplication = CType(ExcelObject, Excel.Application).Application但这样做会引发异常:

System.InvalidCastException Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.Office.Interop.Excel.Application'. This operation failed because the Query Interface call on the COM component for the interface with IID '{000208D5-0000-0000-C000-000000000046}' failed due to the following error: No such interface supported. (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).

我可以在不同的站点中找到多个相似的引用，但没有运气用 trial and error method 修复它.

我的问题是如何 turn on option strict如果有人帮助我获得更好的代码或修复/解释它的任何其他问题，则可以获得奖金。

Answer 1

关于主要目标，访问已打开的现有 Excel 实例的工作簿(创建运行 EXCEL.EXE。这当然包括对 Shell 的请求以打开与 Excel 相关的文件扩展名)。

The following method uses Console.WriteLine() just to evaluate(eventually setting a BreakPoint), the current values of some objects.It's clearly redundant (has to be deleted/commented out beforerelease).

它创建一个本地 List(Of Workbook) ，这是返回给调用者的:
请注意，每次创建 Interop 对象时，都会将其编码并设置为空。
为什么两者都有？调试时检查对象，您会看到。
Process.GetProcessesByName("EXCEL")也是多余的。同样，仅用于评估返回的 Process对象并检查它们的值。
使用 Marshal.GetActiveObject() 访问 Excel 事件实例(如果有)
请注意，这将 不是 创建一个新进程。我们正在访问现有实例。 Visual Studio Version: 15.7.6 - 15.8.3 .Net FrameWork version: 4.7.1 Option Strict: On, Option Explicit: On, Option Infer: On

Public Function FindOpenedWorkBooks() As List(Of Workbook)
    Dim OpenedWorkBooks As New List(Of Workbook)()

    Dim ExcelInstances As Process() = Process.GetProcessesByName("EXCEL")
    If ExcelInstances.Count() = 0 Then
        Return Nothing
    End If

    Dim ExcelInstance As Excel.Application = TryCast(Marshal.GetActiveObject("Excel.Application"), Excel.Application)
    If ExcelInstance Is Nothing Then Return Nothing
    Dim worksheets As Sheets = Nothing
    For Each WB As Workbook In ExcelInstance.Workbooks
        OpenedWorkBooks.Add(WB)
        worksheets = WB.Worksheets
        Console.WriteLine(WB.FullName)
        For Each ws As Worksheet In worksheets
            Console.WriteLine(ws.Name)
            Marshal.ReleaseComObject(ws)
        Next
    Next

    Marshal.ReleaseComObject(worksheets)
    worksheets = Nothing
    Marshal.FinalReleaseComObject(ExcelInstance)
    Marshal.CleanupUnusedObjectsInCurrentContext()
    ExcelInstance = Nothing
    Return OpenedWorkBooks
End Function

返回的 List(Of Workbook)包含事件对象。这些对象尚未编码且可访问。
您可以调用 FindOpenedWorkBooks()像这样的方法:
(有些值，如 WorkSheet.Columns.Count 是毫无值(value)的。这些值用于表明您访问了每个 WorkSheet 返回的每个 Sheets 值，对于找到的所有 WorkBooks) Excel.Range为访问 Cell 的值而创建的对象(此处为第一个列标题): Dim CellRange As Excel.Range = CType(ws.Cells(1, 1), Excel.Range)是一个新的 Interop 对象，因此在对其值进行评估后将其释放。

Private ExcelWorkBooks As List(Of Workbook) = New List(Of Workbook)()

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    ExcelWorkBooks = FindOpenedWorkBooks()

    If ExcelWorkBooks IsNot Nothing Then
        Dim WBNames As New StringBuilder()
        For Each wb As Workbook In ExcelWorkBooks
            WBNames.AppendLine(wb.Name)
            Dim sheets As Sheets = wb.Worksheets
            Console.WriteLine($"Sheets No.: { sheets.Count}")
            For Each ws As Worksheet In sheets
                Console.WriteLine($"WorkSheet Name: {ws.Name}  Columns: {ws.Columns.Count}  Rows: {ws.Rows.Count}")
                Dim CellRange As Excel.Range = CType(ws.Cells(1, 1), Excel.Range)
                Console.WriteLine(CellRange.Value2.ToString)
                Marshal.ReleaseComObject(CellRange)
                Marshal.ReleaseComObject(ws)
            Next

            Marshal.ReleaseComObject(sheets)
        Next
        MessageBox.Show(WBNames.ToString())
    End If
End Sub

必须释放哪些对象？您创建的所有对象。
假设你必须打开一个新的 Excel 文件并且你想访问一个 WorkBook在里面。
( 这将创建一个新进程 )

Dim WorkBook1Path As String = "[Some .xlsx Path]"
Dim ExcelApplication As New Excel.Application()
Dim ExcelWorkbooks As Workbooks = ExcelApplication.Workbooks
Dim MyWorkbook As Workbook = ExcelWorkbooks.Open(WorkBook1Path, False)
Dim worksheets As Sheets = MyWorkbook.Worksheets
Dim MyWorksheet As Worksheet = CType(worksheets("Sheet1"), Worksheet)

'(...)
'Do your processing here
'(...)

Marshal.ReleaseComObject(MyWorksheet)
Marshal.ReleaseComObject(worksheets)
MyWorkbook.Close(False) 'Don't save
Marshal.ReleaseComObject(MyWorkbook)
ExcelWorkbooks.Close()
Marshal.ReleaseComObject(ExcelWorkbooks)
ExcelApplication.Quit()
Marshal.FinalReleaseComObject(ExcelApplication)
Marshal.CleanupUnusedObjectsInCurrentContext()

同样，必须释放所有对象。 WorkBooks必须是 .Close() d 并根据需要保存其内容。 WorkBooks集合必须是 .Close() d。
使用 Excel.Application主要对象 .Quit() 通知操作结束的方法。
.Quit()不会终止您创建的进程 .
Marshal.FinalReleaseComObject(ExcelApplication) 用于完成它的发布。
此时 EXCEL过程将结束。
最后一条指令， Marshal.CleanupUnusedObjectsInCurrentContext()` , 是一种清理预防措施。
可能甚至没有必要，但它不会受到伤害:我们要离开这里。
当然，您可以在应用程序的初始化过程中实例化所有这些对象一次，然后在应用程序关闭时对它们进行编码。
使用 Form 时类，它创建一个 Dispose()可用于此任务的方法。
如果您在自己的类中实现这些过程，请实现 IDisposable interface和 implement the required Dispose() method .
但是，如果您不想或不能处理所有这些对象的实例化/销毁怎么办？
您可能更喜欢在实例化新对象时使用类型推断。所以你设置 Option Explicit和 Option Strict ON , 同时保持 Option Infer On .许多人这样做。
所以你写了这样的东西: Dim MyWorkbook = ExcelWorkbooks.Open([FilePath], False)代替: Dim MyWorkbook As Workbook = ExcelWorkbooks.Open([FilePath], False)有时很清楚已经创建了哪些对象来满足您的请求。
有时绝对不是。
因此，许多人更喜欢实现不同的模式来释放/处置互操作对象。
你可以在这里看到很多方法(主要是 c#，但它是一样的):
How do I properly clean up Excel interop objects?
这种深思熟虑的实现:
Application not quitting after calling quit
此外， TnTinMn 描述的一种特殊方式这里:
Excel COM Object not getting released
测试，找到你的方式:)。
切勿使用 Process.Kill() .除其他外，您不知道要终止什么。
此外，还有一些关于托管/非托管代码中的 COM 编码的有趣读物:
Visual Studio 工程团队:
Marshal.ReleaseComObject Considered Dangerous
Hans Passant 谈 COM 编码和垃圾收集 :
Understanding garbage collection in .NET
MSDN 文档关于 Runtime Callable Wrapper (RCW) 和 COM Callable Wrapper (CCW)
Runtime Callable Wrapper
COM Callable Wrapper