Search code examples
c++excelmarshallingvariantsafearray

How can I marshall between XLOPER and VARIANT?

I'm working on an Excel plugin (XLL), which communicates with COM objects. So, I have to marshall between XLOPER and VARIANT. I've got most of this working, but arrays are definitely a pain. I need to support 1- and 2D arrays.

I imagine someone has already had to deal with this before. What's the best way to simplify dealing with VARIANT, SAFEARRAY, and XLOPER (and XLOPER12)?

Solution

  • I had to hand-roll my own marshalling code for this. There were no freely available libs to handle this. XLW is meant for wrapping your entire plugin - this was not an option for me.

    In the end, it just took a lot of time, looking through the documentation for xloper and variant, and figuring out how to map them to each other.

    For the curious:

    arrays in xloper(12):

    • .xltype is xltypeMulti
    • .val.array is a pointer to an array of xlopers.

    arrays in variant:

    • .vt is VT_ARRAY | VT_VARIANT
    • .parray is a SafeArray of variants

    other tips with this marshalling:

    • xltypeRef and xltypeSRef: use xlCoerce to look up actual cell values.
    • integer values run the risk of overflow. boost::numeric_cast helps with that.
    • string marshalling is annoying. xloper uses char, xloper12 uses wchar, variant uses bstr.
    • Remember to free the buffers you allocate for your new xloper strings. Use xlbitDLLFree and xlAutoFree(12) accordingly.
    • Template programming is useful for the xloper/xloper12 issue. Boost helps. _bstr_t also helps.