Freigeben über

Invoking Native Excel UDFs From Managed Code (Pt2)

  • Artikel

In my previous post, I looked at how to build a VSTO add-in that invokes native XLL UDFs via the Excel COM OM (specifically, Application.Run). In this second part, I'll look at the same managed client calling the same XLL UDFs via the Windows API instead of Excel's COM OM, specifically using LoadLibrary and GetProcAddress.

Continuing with the same VSTO add-in project I used last time, I added a class for the native imports (I looked up pinvoke.net for the signatures). I also declared a delegate which I can use in my managed code to represent the XLL function pointer:

internal static class NativeTypes

{

    [DllImport("kernel32.dll")]

    internal static extern IntPtr LoadLibrary(string lpFileName);

    [DllImport("kernel32.dll", CharSet=CharSet.Ansi,

       ExactSpelling=true, SetLastError=true)]

    internal static extern IntPtr GetProcAddress(

IntPtr hModule, string procName);

    internal delegate IntPtr UdfDelegate();

}

Back in my Ribbon class, I added a couple of fields for the XLL filename and the XLL module handle. The idea is that I only need to get the XLL module handle once, and this is not really part of the perf timing I need to include. That is, although there will be some perf cost to calling LoadLibrary, it is pretty minimal and a realistic solution would only call this once so it should not be included on the per-call timings. Note that LoadLibrary will not load the XLL again if it is already mapped into the process (which it should be by the time I call it, because XLLs are loaded before COM add-ins). So, in the onLoad callback for the Ribbon customization, I first make sure that the XLL is actually loaded, by walking the collection of Excel.AddIns, and then I use LoadLibrary to get its module handle:

private const string xllName = "SimpleXll2007.xll";

private IntPtr xllHandle;

public void Ribbon_Load(Office.IRibbonUI ribbonUI)

{

    this.ribbon = ribbonUI;

    foreach (

Excel.AddIn addIn in Globals.ThisAddIn.Application.AddIns)

    {

        if (addIn.Name == xllName)

        {

            // Cache the module handle for the XLL to target.

            xllHandle = NativeTypes.LoadLibrary(xllName);

        }

    }

}

The next task is to call one of the UDFs. I get the address of the UDF using GetProcAddress. Then, I can use the System.Runtime.InteropServices.Marshal.GetDelegateForFunctionPointer to get a managed delegate mapping to the raw function pointer – using my custom delegate type to store the result. From there, I can invoke the UDF by invoking the delegate. Finally, I can use Marshal.PtrToStringAnsi to convert the returned char* to a managed string:

IntPtr fptr = NativeTypes.GetProcAddress(xllHandle, "GetChars");

NativeTypes.UdfDelegate invoker = (NativeTypes.UdfDelegate)

    Marshal.GetDelegateForFunctionPointer(

    fptr, typeof(NativeTypes.UdfDelegate));

IntPtr ip = invoker();

string s1 = Marshal.PtrToStringAnsi(ip);

For the GetString UDF, life is a little more interesting. This UDF returns an XLOPER. I can get the address of the UDF and invoke it via a delegate in the same way as for the simpler GetChars UDF:

fptr = NativeTypes.GetProcAddress(xllHandle, "GetString");

invoker = (NativeTypes.UdfDelegate)

    Marshal.GetDelegateForFunctionPointer(

    fptr, typeof(NativeTypes.UdfDelegate));

ip = invoker();

…but to convert the returned IntPtr, which represents a pointer to an XLOPER, to a managed type requires a bit more work. Essentially, I need to define a managed equivalent of the XLOPER struct. I've put these in my NativeTypes class. The XLOPER type is a struct which contains a flag to indicate the type of the data, and a set of nested unions to represent the data, defined in xlcall.h in the Excel XLL SDK. Conceptually, this is very similar to the VARIANT type in COM automation. Note: the managed equivalent listed below is not complete, because it does not include the more complex XLOPER union members such as arrays and cell references. The XlVal and XlOper structs together represent a much simplified XLOPER. Also note that to represent a native union, I'm using the FieldOffset attribute with a zero value. In order to use FieldOffset, the struct has to be declared unsafe. This means that the project has to be built to allow unsafe code (Project Properties | Build | Allow unsafe code).

[StructLayout(LayoutKind.Explicit)]

internal unsafe struct XlVal

{

    [FieldOffset(0)]

    public double num;

    [FieldOffset(0)]

    public XlString* str;

    [FieldOffset(0)]

    public ushort xbool;

    [FieldOffset(0)]

    public short w;

    [FieldOffset(0)]

    public XlError err;

}

public struct XlOper

{

    public XlVal val;

    public XlType xlType;

}

To complete the definition, I also defined an enum for the XLOPER type, an enum for the XLOPER error values, and a simple struct to represent the XLOPER string type (which is octal-length-prefixed). All of these are designed to map to their native equivalents defined in xlcall.h:

// XLOPER data types, used for the xlType field of the XlOper struct.

[Flags]

internal enum XlType : ushort

{

    xlTypeNum = 0x0001,

    xlTypeStr = 0x0002,

    xlTypeBool = 0x0004,

    xlTypeRef = 0x0008,

    xlTypeErr = 0x0010,

    xlTypeFlow = 0x0020,

    xlTypeMulti = 0x0040,

    xlTypeMissing = 0x0080,

    xlTypeNil = 0x0100,

    xlTypeSRef = 0x0400,

    xlTypeInt = 0x0800,

    xlBitxLFree = 0x1000,

    xlBitDLLFree = 0x4000,

    xlTypeBigData = xlTypeStr | xlTypeInt

}

// Error codes, used for the val.err field of the XlOper struct.

public enum XlError : ushort

{

    xlErrorNull = 0,

    xlErrorDiv0 = 7,

    xlErrorValue = 15,

    xlErrorRef = 23,

    xlErrorName = 29,

    xlErrorNum = 36,

    xlErrorNA = 42

}

// XLOPER strings are octal-length-prefixed.

internal unsafe struct XlString

{

    public short length;

    public fixed byte data[255];

}

Armed with this managed XlOper type, I can use Marshal.PtrToStructure to convert the IntPtr returned by the UDF delgate to an instance of the managed XlOper type, extract the data member, and then extract the unicode string from that.

NativeTypes.XlOper xlOper = (NativeTypes.XlOper)

    Marshal.PtrToStructure(ip, typeof(NativeTypes.XlOper));

string s2 = String.Empty;

unsafe

{

    s2 = Marshal.PtrToStringUni(

        new IntPtr(xlOper.val.str->data));

}

The complete event handler for the Win API Ribbon button is listed below, including both UDF calls and the perf timing code.

public void OnButtonWinApi(Office.IRibbonControl control)

{

    if (xllHandle == null || xllHandle.ToInt32() == 0)

    {

        return;

    }

    long ticksAccumulated = 0;

    Stopwatch watch;

   

    for (int i = 0; i < repeatCount; i++)

    {

        // Perf-count this method call.

        watch = Stopwatch.StartNew();

        try

        {

            // Call the GetChars UDF, which returns a char*.

            IntPtr fptr =

                NativeTypes.GetProcAddress(

                xllHandle, "GetChars");

            NativeTypes.UdfDelegate invoker =

                (NativeTypes.UdfDelegate)

                Marshal.GetDelegateForFunctionPointer(

                fptr, typeof(NativeTypes.UdfDelegate));

            IntPtr ip = invoker();

            string s1 = Marshal.PtrToStringAnsi(ip);

            // Call GetString (which returns an XLOPER*).

            fptr = NativeTypes.GetProcAddress(

                xllHandle, "GetString");

            invoker = (NativeTypes.UdfDelegate)

                Marshal.GetDelegateForFunctionPointer(

                fptr, typeof(NativeTypes.UdfDelegate));

            ip = invoker();

            NativeTypes.XlOper xlOper = (NativeTypes.XlOper)

                Marshal.PtrToStructure(

                ip, typeof(NativeTypes.XlOper));

            string s2 = String.Empty;

            unsafe

     {

                s2 = Marshal.PtrToStringUni(

                    new IntPtr(xlOper.val.str->data));

            }

            Globals.ThisAddIn.Application.ActiveCell.Value2 =

                String.Format("{0}, {1}", s1, s2);

        }

        catch (Exception ex)

        {

            Debug.WriteLine(ex.ToString());

        }

        // Keep a running total of the elapsed call time.

        ticksAccumulated += watch.ElapsedTicks;

    }

    // Calculate and report the average elapsed time.

    Debug.WriteLine(String.Format(

        "LoadLibrary/GetProcAddress: average = {0} msec",

        (ticksAccumulated * msecPerTick) / repeatCount));

}

Over a large number of invocations (10,000), repeated over 100 runs, calling UDFs via the Windows API averaged out at 0.9851 msec. Recall that the same set of tests using the COM OM averaged out at 1.9625 msec. So, it seems that – in the simple case at least – using the Windows API and avoiding COM interop is almost exactly twice as fast. Of course, these results are skewed slightly because my managed XlOper type is so simplified, and I haven't tested the performance when a UDF takes and/or returns complex XLOPERs made up of cell references or arrays.

Comments