c# - 如何在 net.core 3.1 中连接打开的 excel 应用程序？

Question

在 Net.Framework 中，我可以使用下一个函数来操作打开的 excel 应用程序实例:

using Excel = Microsoft.Office.Interop.Excel;
public static Excel.Workbook GetOpenedExcelWorkbook(string workBookName)
{
    Excel.Application xlsApp = null;
    try { xlsApp = (Excel.Application)Marshal.GetActiveObject("Excel.Application"); }
    catch { return null; }

    foreach (Excel.Workbook wb in xlsApp.Workbooks)
    {
        if (wb.Name == workBookName)
            return wb;
    }
    return null;
}

这很方便，因为用户只需单击 excel 工作表中的按钮并调用所有必要的程序。而且我可以用C#编写操作excel的代码，比VBA更有用、更强大。
VBA 中的下一个代码用于在单击 excel 中的按钮时调用 C# 控制台应用程序。它在事件的 excel 文件附近找到适当的 exe 文件，并在 VBA 中通过带有必要参数的 Shell 命令调用它。

Sub Button1_Click()
    Call ExeCaller("ConsoleApp.exe", "Button1_action_arg")
End Sub


Sub ExeCaller(ExeName As String, Optional cmd As String = "")
    Dim arg As String
    If Not Dir(ExeName) = "" Then
        arg = ExeName & " " & ActiveWorkbook.Name & IIf(cmd = "", "", " " & cmd)
    Else
        Dim pathToWB As String: pathToWB = ThisWorkbook.Path
        Dim pathToExe_Release As String: pathToExe_Release = pathToWB + "\bin\Release\" + ExeName
        Dim pathToExe_Debug As String:   pathToExe_Debug = pathToWB + "\bin\Debug\" + ExeName

        Dim dr As Double, dd As Double
        If Not Dir(pathToExe_Release) = "" Then dr = FileDateTime(pathToExe_Release)
        If Not Dir(pathToExe_Debug) = "" Then dd = FileDateTime(pathToExe_Debug)
        Dim dmax As Double: dmax = IIf(dr > dd, dr, dd)

        If (dmax = 0) Then
            If Not Dir(ExeName) = "" Then
                pathToExe = ExeName
            Else
                MsgBox "File " + ExeName + "not found!", vbCritical, Error
                Exit Sub
            End If
        ElseIf dmax = dr Then
            pathToExe = pathToExe_Release
        ElseIf dmax = dd Then
            pathToExe = pathToExe_Debug
        Else

            MsgBox "File " + ExeName + "not found!", vbCritical, Error
            Exit Sub
        End If

        arg = pathToExe & " " & ActiveWorkbook.Name & IIf(cmd = "", "", " " & cmd)
    End If

    Shell arg, vbNormalFocus
End Sub

使其成为可能的关键功能是 Marshal.GetActiveObject(string ProgID)在 C# 中，可在 Net.Framework 中使用。

现在我尝试迁移到 Net.Core 3.1。在Net.Core中可以通过这种方式使用Excel对象模型: https://github.com/dotnet/samples/tree/master/core/extensions/ExcelDemo
这样我就可以创建一个新的 Excel.Application 实例，并通过它创建新的或打开现有的 Excel 工作簿，并使用它来操作所有功能。但是我无法连接到打开 excel 工作簿，因为 Net.Core 中没有 Marshal.GetActiveObject(string ProgID) 函数。 Preview Net.Core 5 也没有。并且没有类似的功能。

我尝试使用 Net.Core 中可用的 Marshal.GetObjectForIUnknown(IntPtr pUnk) 来做到这一点。据我所知 Excel.Application 是一个 COM 对象，因此它具有 IUnknown 接口(interface)。我试图通过可以按名称找到的打开 excel 应用程序的过程来获取此指针。我使用了这个天真的代码:

public static Excel.Application GetOpenedExcelApplication()
{
    Process[] aP = Process.GetProcesses();
    foreach (Process p in aP)
    {
        string pn = p.ProcessName.ToLower();

        if (Regex.IsMatch(pn, "excel*"))
        {
            object o = Marshal.GetObjectForIUnknown(p.Handle);
            Excel.Application app = (Excel.Application)o;
            return app;
        }
    }

    return null;
}

Marshall.GetObjectForIUnknown() 调用线路上发生 System.ExecutionEngineException。

Net.Core 中有没有办法访问打开的 Excel 工作簿？

Answer 1

我们可以查看 .NET Framework 4.X 中 Marshal 类的源代码，并将 GetActiveObject 方法实现复制到 .NET Core 和 .NET 5 和 .NET 6 的项目中。它可以工作。

public static class ExMarshal
{
    internal const String OLEAUT32 = "oleaut32.dll";
    internal const String OLE32 = "ole32.dll";

    [System.Security.SecurityCritical]  // auto-generated_required
    public static Object GetActiveObject(String progID)
    {
        Object obj = null;
        Guid clsid;

        // Call CLSIDFromProgIDEx first then fall back on CLSIDFromProgID if
        // CLSIDFromProgIDEx doesn't exist.
        try
        {
            CLSIDFromProgIDEx(progID, out clsid);
        }
        //            catch
        catch (Exception)
        {
            CLSIDFromProgID(progID, out clsid);
        }

        GetActiveObject(ref clsid, IntPtr.Zero, out obj);
        return obj;
    }

    //[DllImport(Microsoft.Win32.Win32Native.OLE32, PreserveSig = false)]
    [DllImport(OLE32, PreserveSig = false)]
    [ResourceExposure(ResourceScope.None)]
    [SuppressUnmanagedCodeSecurity]
    [System.Security.SecurityCritical]  // auto-generated
    private static extern void CLSIDFromProgIDEx([MarshalAs(UnmanagedType.LPWStr)] String progId, out Guid clsid);

    //[DllImport(Microsoft.Win32.Win32Native.OLE32, PreserveSig = false)]
    [DllImport(OLE32, PreserveSig = false)]
    [ResourceExposure(ResourceScope.None)]
    [SuppressUnmanagedCodeSecurity]
    [System.Security.SecurityCritical]  // auto-generated
    private static extern void CLSIDFromProgID([MarshalAs(UnmanagedType.LPWStr)] String progId, out Guid clsid);

    //[DllImport(Microsoft.Win32.Win32Native.OLEAUT32, PreserveSig = false)]
    [DllImport(OLEAUT32, PreserveSig = false)]
    [ResourceExposure(ResourceScope.None)]
    [SuppressUnmanagedCodeSecurity]
    [System.Security.SecurityCritical]  // auto-generated
    private static extern void GetActiveObject(ref Guid rclsid, IntPtr reserved, [MarshalAs(UnmanagedType.Interface)] out Object ppunk);
}