Excel互操作加载XLL和DLL [英] Excel interop loading XLLs and DLLs
问题描述
我假设，如果你手动启动excel该加载项正确加载。
如果是这样，您可以通过调用进程p = Process.Start（excel.exe）获取加载的BB-Addins的Excel Interop对象）; 然后使用 AccessibleObjectFromWindow 方法获取Interop对象。
可以找到一个关于如何做到这一点的例子此处。
快速演练
我决定显示我的步骤通过加载 c> c> Excel.Interop.Application 实例以加载 bloomberg add ins。也许有人看这个，发现它有用，或者知道我错过了什么，以获得更优雅的解决方案。
我假设你有Bloomberg终端安装在您的客户端，您可以从excel中请求参考和历史数据（BDP和BDH）。否则这可能不适合你...
最简单的方法不起作用
var excel = new Microsoft.Office.Interop.Excel.Application（）;
excel.Workbooks.Open（filePath）;
excel.Run（RefreshAllStaticData）;
Run（）将抛出 COMException ：
无法运行宏RefreshAllStaticData。该工作簿中的宏可能不可用，或者所有宏都可能被禁用。
手动加载AddIns不起作用
var excel = new Microsoft.Office.Interop.Excel.Application（）;
var addIn = ex.AddIns.Add（filename）;
addIn.Installed = true;
Console.WriteLine（addIn.IsOpen）;
false
但是，如果在Windows任务栏上启动Excel，那么AddIns会很好地加载...所以我尝试启动excel，启动可执行文件excel.exe。
使用Process.Start（excel.exe）工作!!
使用 System.Diagnostics.Process 类加载bloomberg AddIns。 （我可以看到 Bloomberg-RibbonTab 。）
现在我需要的是那个excel的Interop-Object处理。我可以使用上述示例获得它
我的实现
//从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（异常）
{
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;返回假; }
返回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;
}
}
现在我可以使用以下代码行来获取一个 Excel.Interop.Application 实例加载了bloomberg插件！
var excel = ExcelInteropService.GetExcelInterop（）;
excel.Workbooks.Open（filename）;
excel.Run（RefreshAllStaticData）;
//工作像一个魅力
我希望这能使我浪费时间有了这个，如果有人有一个更优雅的解决方案，并希望分享它，这将是非常感谢。
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互操作加载XLL和DLL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
