Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
437 views
in Technique[技术] by (71.8m points)

c# - Get the excel cell address from a UDF

I have created an Automation Add-in for Excel,implemented as a c# class library, which contains a UDF wrapper.(I do not use VSTO) The UDF looks like this:

string foo(string data){

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

Is there a way for me to implicitly obtain the address of the cell where this formula was entered, without passing any additional parameters? One way would be to hook an event listener to the workbook,as soon as the add-in loads, and capture the events as the cells' value change; But I am looking for an alternative to that.

Thanks,

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

you can try Globals.ThisAddIn.Application.Caller, it returns an Excel.Range containing the cell. Maybe something like this to get Excel.Application

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;
    }
}

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...