从UDF中获取excel单元格地址

本文关键字：单元格 地址 excel 获取 UDF | 更新日期: 2023-09-27 18:19:12

我已经为Excel创建了一个自动化插件，作为c#类库实现，其中包含一个UDF包装器。(我不使用VSTO)UDF看起来像这样:

string foo(string data){
  //Do some work on the data passed
  string result;
  return(result);
}

是否有一种方法可以隐式地获得输入此公式的单元格的地址，而无需传递任何额外的参数?一种方法是在加载外接程序后立即将事件侦听器挂接到工作簿，并在单元格的值发生变化时捕获事件;但我正在寻找一个替代方案。

谢谢,

从UDF中获取excel单元格地址

你可以试试global . thisaddin . application。调用者，它返回一个Excel。包含单元格的范围。也许像这样可以得到Excel。应用

public class InteropHelper
{
    public static void GetReferences(ref Microsoft.Office.Interop.Excel.Application _Application, ref Microsoft.Office.Interop.Excel.Workbook _Workbook)
    {
        EnumChildCallback cb;
        // First, get Excel's main window handle.
        int hwnd = (int)Process.GetCurrentProcess().MainWindowHandle;
        // We need to enumerate the child windows to find one that
        // supports accessibility. To do this, instantiate the
        // delegate and wrap the callback method in it, then call
        // EnumChildWindows, passing the delegate as the 2nd arg.
        if (hwnd != 0)
        {
            int hwndChild = 0;
            cb = new EnumChildCallback(EnumChildProc);
            EnumChildWindows(hwnd, cb, ref hwndChild);
            // If we found an accessible child window, call
            // AccessibleObjectFromWindow, passing the constant
            // OBJID_NATIVEOM (defined in winuser.h) and
            // IID_IDispatch - we want an IDispatch pointer
            // into the native object model.
            if (hwndChild != 0)
            {
                const uint OBJID_NATIVEOM = 0xFFFFFFF0;
                Guid IID_IDispatch = new Guid(
                     "{00020400-0000-0000-C000-000000000046}");
                Microsoft.Office.Interop.Excel.Window ptr = null;
                int hr = AccessibleObjectFromWindow(
                      hwndChild, OBJID_NATIVEOM, IID_IDispatch.ToByteArray(), ref ptr);
                if (hr >= 0)
                {
                    // If we successfully got a native OM
                    // IDispatch pointer, we can QI this for
                    // an Excel Application (using the implicit
                    // cast operator supplied in the PIA).
                    _Application = ptr.Application;
                    _Workbook = _Application.ActiveWorkbook;
                }
            }
        }
    }
    [DllImport("Oleacc.dll")]
    public static extern int AccessibleObjectFromWindow(
          int hwnd, uint dwObjectID, byte[] riid,
          ref Microsoft.Office.Interop.Excel.Window ptr);
    public delegate bool EnumChildCallback(int hwnd, ref int lParam);
    [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 bool EnumChildProc(int hwndChild, ref int lParam)
    {
        StringBuilder buf = new StringBuilder(128);
        GetClassName(hwndChild, buf, 128);
        if (buf.ToString() == "EXCEL7")
        {
            lParam = hwndChild;
            return false;
        }
        return true;
    }
}

感谢brijesh为我指出了正确的方向。我使用以下命令来获取单元格地址:

using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
Excel.Application excelApp = (Excel.Application)Marshal.GetActiveObject("Excel.Application");
Excel.Range target = (Excel.Range)excelApp.get_Caller(System.Type.Missing);
string cellAddress = target.get_Address(Missing.Value, Missing.Value,
           Excel.XlReferenceStyle.xlA1, Missing.Value, Missing.Value);
相关文章：