Excel的互操作加载XLLs和DLL [英] Excel interop loading XLLs and DLLs

问题描述

我与彭博API（使用简单的调用像= BDP（MS公平，问））脱颖而出。我也有打开一个Excel文件（通过互操作），使用彭博API C＃应用程序。在这里我已阅读 的，当你加载的加载项不加载通过互操作脱颖而出。我甚至用code建议有试过。然而，这似乎只为XLL和XLAM文件的工作，它看起来像彭博还使用了中没有加载DLL文件。有没有什么办法让所有这些加载项中通过互操作加载？

解决方案

我认为，如果你开始手动出类拔萃的加载项加载正确。

如果是这样，你可以通过调用进程p =的Process.Start（EXCEL.EXE）获得加载了BB-加载项的Excel互操作对象; ，然后使用在 AccessibleObjectFromWindow 方法来获取互操作对象。

这是如何做到这一点的例子可以在这里找到 。

---

快速演练

我决定展示我经历了与加载彭博添加插件来获得 Excel.Interop.Application 实例的步骤。也许有人着眼于这一点，并发现它是有用的，或者知道哪里我错过了什么，以获得更完美的解决方案。

  

我假设你已经安装了客户端上的彭博终端，并且可以要求引用和历史数据（BDP和BDH）从Excel中。否则，这可能是不适合你...

最简单的方法不起作用

  VAR的Excel =新Microsoft.Office.Interop.Excel.Application（）;
excel.Workbooks.Open（文件路径）;
excel.Run（RefreshAllStaticData）;
 

运行（）将抛出一个收到COMException ：

  

无法运行宏RefreshAllStaticData。宏可能无法使用此工作簿或所有宏可能会被禁用。

手工加载加载项不起作用

  VAR的Excel =新Microsoft.Office.Interop.Excel.Application（）;
VAR插件= ex.AddIns.Add（文件名）;
addIn.Installed = TRUE;
Console.WriteLine（addIn.IsOpen）;
 

  

假

但是，如果启动Excel地单击Windows任务栏上，加载项将被很好地加载中...所以，我想通过启动可执行文件EXCEL.EXE启动Excel。

使用的Process.Start（EXCEL.EXE）的作品！

使用的System.Diagnostics.Process 类加载彭博加载项启动Excel中。 （我可以看到彭博-RibbonTab ）

现在我需要的是Excel进程的互操作对象。我可以用上面的例子中得到它

我实现它

  //从http://blogs.officezealot.com/whitechapel/archive/2005/04/10/4514.aspx
公共类ExcelInteropService
{
    私人常量字符串EXCEL_CLASS_NAME =EXCEL7;    私人常量UINT DW_OBJECTID = 0xFFFFFFF0;    私有静态的Guid = rrid新的GUID（{00020400-0000-0000-C000-000000000046}）;    公共委托布尔EnumChildCallback（HWND INT，楼盘INT lParam的）;    函数[DllImport（Oleacc.dll）]
    公共静态外部INT AccessibleObjectFromWindow（INT HWND，UINT dwObjectID，字节[] RIID，楼盘Microsoft.Office.Interop.Excel.Window PTR）;    函数[DllImport（User32.dll中）]
    公共静态的extern BOOL EnumChildWindows（INT hWndParent，EnumChildCallback lpEnumFunc，楼盘INT lParam的）;    函数[DllImport（User32.dll中）]
    公共静态外部INT GetClassName（INT的hWnd，StringBuilder的lpClassName，INT nMaxCount）;    公共静态Microsoft.Office.Interop.Excel.Application GetExcelInterop（INT？=的ProcessID空）
    {
        VAR P = processId.HasValue？ Process.GetProcessById（processId.Value）：的Process.Start（EXCEL.EXE）;
        尝试
        {
            返回新ExcelInteropService（）SearchExcelInterop（P）。
        }
        赶上（例外）
        {
            Debug.Assert的（P = NULL，P = NULL！！）;
            返回GetExcelInterop（p.Id）;
        }
    }    私人布尔EnumChildFunc（INT hwndChild，楼盘INT的lParam）
    {
        VAR BUF =新的StringBuilder（128）;
        GetClassName（hwndChild，BUF，128）;
        如果（buf.ToString（）== EXCEL_CLASS_NAME）{lParam的= hwndChild;返回false; }
        返回true;
    }    私人Microsoft.Office.Interop.Excel.Application SearchExcelInterop（进程p）
    {
        Microsoft.Office.Interop.Excel.Window PTR = NULL;
        INT HWND = 0;        INT hWndParent =（INT）p.MainWindowHandle;
        如果（hWndParent == 0）抛出新ExcelMainWindowNotFoundException（）;        EnumChildWindows（hWndParent，EnumChildFunc，楼盘HWND）;
        如果（HWND == 0）抛出新ExcelChildWindowNotFoundException（）;        INT HR = AccessibleObjectFromWindow（HWND，DW_OBJECTID，rrid.ToByteArray（），REF PTR）;
        如果（HR℃下）抛出新AccessibleObjectNotFoundException（）;        返回ptr.Application;
    }
}
 

现在我可以用code的下面一行加载了加载项彭博社获得的 Excel.Interop.Application 实例！

  VAR的Excel = ExcelInteropService.GetExcelInterop（）;
excel.Workbooks.Open（文件名）;
excel.Run（RefreshAllStaticData）;
// 奇迹般有效
 

我希望这饶恕别人我这个浪费的时间，如果有人有一个更好的解决方案，并希望分享它，这将是更AP preciated。

I have excel with the Bloomberg API ( which uses simple calls like =BDP("MS equity","ask") ). I also have a C# application that opens an excel file (through interop) that uses the Bloomberg API. I have read here that addins are not loaded when you load excel through interop. I have even tried using the code suggested there. However, that only seems to work for XLL and XLAM files and it looks like Bloomberg also uses DLL files which doesn't get loaded. Is there any way to get all of these addins to be loaded through interop?

解决方案

I assume that, if you start excel manually the add-ins are loaded properly.

If so, you can get an Excel Interop Object with loaded BB-Addins by calling Process p = Process.Start("excel.exe"); and then using the AccessibleObjectFromWindow method to get the Interop Object.

An example on how to do that can be found here.

---

Quick walkthrough

I decided to show the steps I went through to get an Excel.Interop.Application instance with loaded bloomberg add ins. Maybe someone looks at this and finds it useful, or knows where I missed something to get a more elegant solution.

I assume you have the bloomberg terminal installed on your client and that you can request reference and historical data (BDP and BDH) out of excel. Otherwise this is probably not for you...

The simplest way doesn't work

var excel = new Microsoft.Office.Interop.Excel.Application();
excel.Workbooks.Open(filePath);
excel.Run("RefreshAllStaticData");

Run() will throw a COMException:

Cannot run the macro 'RefreshAllStaticData'. The macro may not be available in this workbook or all macros may be disabled.

Loading AddIns by hand doesn't work

var excel = new Microsoft.Office.Interop.Excel.Application();
var addIn = ex.AddIns.Add(filename);
addIn.Installed = true;
Console.WriteLine(addIn.IsOpen);

false

But if start Excel by klicking on the Windows Taskbar, the AddIns are being loaded nicely... So I tried to start excel by starting the executable file "excel.exe".

Using Process.Start("excel.exe") works!!

Starting excel using the System.Diagnostics.Processclass loads the bloomberg AddIns. (I can see the Bloomberg-RibbonTab.)

Now all I need is the Interop-Object of that excel process. I can get it using the above example

My implementation of it

//From http://blogs.officezealot.com/whitechapel/archive/2005/04/10/4514.aspx
public class ExcelInteropService
{
    private const string EXCEL_CLASS_NAME = "EXCEL7";

    private const uint DW_OBJECTID = 0xFFFFFFF0;

    private static Guid rrid = new Guid("{00020400-0000-0000-C000-000000000046}");

    public delegate bool EnumChildCallback(int hwnd, ref int lParam);

    [DllImport("Oleacc.dll")]
    public static extern int AccessibleObjectFromWindow(int hwnd, uint dwObjectID, byte[] riid, ref Microsoft.Office.Interop.Excel.Window ptr);

    [DllImport("User32.dll")]
    public static extern bool EnumChildWindows(int hWndParent, EnumChildCallback lpEnumFunc, ref int lParam);

    [DllImport("User32.dll")]
    public static extern int GetClassName(int hWnd, StringBuilder lpClassName, int nMaxCount);

    public static Microsoft.Office.Interop.Excel.Application GetExcelInterop(int? processId = null)
    {
        var p = processId.HasValue ? Process.GetProcessById(processId.Value) : Process.Start("excel.exe");
        try
        {
            return new ExcelInteropService().SearchExcelInterop(p);
        }
        catch (Exception)
        {
            Debug.Assert(p != null, "p != null");
            return GetExcelInterop(p.Id);
        }
    }

    private bool EnumChildFunc(int hwndChild, ref int lParam)
    {
        var buf = new StringBuilder(128);
        GetClassName(hwndChild, buf, 128);
        if (buf.ToString() == EXCEL_CLASS_NAME) { lParam = hwndChild; return false; }
        return true;
    }

    private Microsoft.Office.Interop.Excel.Application SearchExcelInterop(Process p)
    {
        Microsoft.Office.Interop.Excel.Window ptr = null;
        int hwnd = 0;

        int hWndParent = (int)p.MainWindowHandle;
        if (hWndParent == 0) throw new ExcelMainWindowNotFoundException();

        EnumChildWindows(hWndParent, EnumChildFunc, ref hwnd);
        if (hwnd == 0) throw new ExcelChildWindowNotFoundException();

        int hr = AccessibleObjectFromWindow(hwnd, DW_OBJECTID, rrid.ToByteArray(), ref ptr);
        if (hr < 0) throw new AccessibleObjectNotFoundException();

        return ptr.Application;
    }
}

Now I can use the following line of code to get an Excel.Interop.Application instance with loaded bloomberg addins!

var excel = ExcelInteropService.GetExcelInterop();
excel.Workbooks.Open(filename);
excel.Run("RefreshAllStaticData");
// works "like a charm"

I hope this spares someone the hours I wasted with this and if someone has a more elegant solution and wants to share it, it would be much appreciated.

这篇关于Excel的互操作加载XLLs和DLL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！