C＃Excel如何使用已经打开的工作簿

Question

这个问题是从我最后一个问题的学习中提出的：上一个问题

我需要能够将文本发送到已打开的选定工作表。

我认为这很接近，但它没有起作用：

        string wb = cmb_BookName.Text.ToString();
        string ws = cmb_SheetName.Text.ToString();

        if (chkContainer.Checked)
        {
            Excel.Application oexcel = new Excel.Application();
            Excel.Workbook wkbk = (Excel.Workbook)oexcel.Workbooks[wb];
            Excel.Worksheet wksk = (Excel.Worksheet)wkbk.Sheets[ws];
            Range cellRange = wksk.Range["D48:D48"];
            cellRange.Value = cboContainer.Text;
        }

该代码没有错误构建，但是当运行时，该代码会停在线上试图获取工作簿，请参阅图像。

所以基本上我的问题仍然是如何我是否可以使用已经打开的Excel工作簿？

似乎我发现的大多数文章都在打开一个Excel文件，然后使用它。就我而言，我需要使用已经打开的Excel工作簿。

Answer 1

There are some helpful answers (some of them, not the accepted answer) to this question here:

但这是我的摘要答案。

尝试一下：

首先
您必须访问当前正在运行的应用程序（如果已经有多个Excel运行的实例，这可能每次都无法正常工作）

using Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;

// I use a global application object
Application excelApp;

// Gets the first running instance of the Excel.Application
// If that fails, the error is caught, and a new application is created
try
{
   excelApp = (Application)Marshal.GetActiveObject("Excel.Application");
}
catch (COMException ex)
{
   excelApp = new Application
   {
      Visible = true
   };
}

second
通过路径获取所需的工作簿（必须是Windows样式，即“ C：\ Desktop \ Myworkbook.xlsx”）

Workbook myWorkbook = null;

foreach (Workbook xlWorkbook in excelApp.Workbooks)
{
   if (xlWorkbook.FullName == "<path to workbook>")
   {
      myWorkbook = xlWorkbook;
   }
} 

Answer 2

它看起来像元帅。getActiveObject在基于.net Core的.NET版本中消失了。
但是我在另一个论坛上找到了一个简单的解决方案。

using System.Runtime.InteropServices;
using System.Runtime.Versioning;
using System.Security;

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

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

        try
        {
            CLSIDFromProgIDEx(progID, out clsid);
        }
        catch (Exception)
        {
            CLSIDFromProgID(progID, out clsid);
        }

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

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

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

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

可以像这样使用：

using xl = Microsoft.Office.Interop.Excel;
...
    try
    {
        var excelApp = (xl.Application)MarshalForCore.GetActiveObject("Excel.Application");
        if (excelApp != null)
        {
            var wb = excelApp.ActiveWorkbook;

            xl.Worksheet? sh = null;
            foreach (xl.Worksheet _ in wb.Worksheets) 
              if (_.Name == "MyTable") 
              { 
                sh = _; 
                break; 
              };

            if (sh == null)
            {
                sh = (xl.Worksheet)wb.Worksheets.Add();
                sh.Name = "MyTable";
            }
            sh.Cells[1, 1] = "Hello World";
        }
    }
    catch
    {
        // Excel is not running.
    }
}