The xll::handle class let's you embed C++ objects into Excel in a type-safe way that supports simple inheritance. Some Excel add-in librarys have "object handlers" that require thousands of lines of code. The file xll/handle.h is only 150 lines of code.

Base Class

Given the class

class base {
    int data_;
public:
    base(int data) 
        : data_(data) 
    { }
    virtual ~base() // for RTTI
    { }
    int value(void) const
    {
        return data_;
    }
};

the "constructor" is implemented as

static AddInX xai_base(
    _T("?xll_base"), XLL_HANDLEX XLL_SHORTX XLL_UNCALCEDX,
    _T("BASE"), _T("Int")
);
HANDLEX WINAPI
xll_base(short b)
{
#pragma XLLEXPORT
    handle<base> h;
    
    try {
        h = new base(b);
    }
    catch (const std::exception& ex) {
        ex.what();

        return 0;
    }

    return h.get();
}

This hooks up the C++ function xll_base to the Excel function BASE. The function returns a handle and takes a short integer as it's argument. Note that XLL_UNCALCEDX needs to be specified in the C signature.

The call to a member function of the object is implemented as

static AddInX xai_base_value(
    _T("?xll_base_value"), XLL_LONGX XLL_HANDLEX,
    _T("BASE.VALUE"), _T("Handle")
);
LONG WINAPI
xll_base_value(HANDLEX h)
{
#pragma XLLEXPORT
    int value;

    try {
        handle<base> b_(h);
        ensure (b_);

        value = b_->value();
    }
    catch (const std::exception& ex) {
        XLL_ERROR(ex.what());

        return 0;
    }

    return value;
}

This creates the add-in BASE.VALUE which takes the handle returned by BASE and returns the integer you supplied to the constructor. You should think of xll::handle as a smart pointer, because it is. It is implemented using the new C++2011 smart pointer std::unique_ptr. The number you see returned by BASE is actually the bits of the C++ pointer to the object, but don't be scared by naked pointers. The constructor for xll::handle checks to see if this pointer is in the bucket of handles for this type. If you give it a pointer of a different type it returns the null pointer.

The class also does automatic garbage collection. If the constructor finds an old handle in the cell it is being called from, it will call delete on the old object.

Derived Class

The destructor for base does not have to be virtual, but if you intend to derive from it this is just standard C++ practice.

class derived : public base {
    int data_;
public:
    derived(int bdata, int ddata)
        : base(bdata), data_(ddata)
    { }
    ~derived()
    { }
    int value2(void) const
    {
        return data_;
    }
};

This extends base by adding a new data member. The constructor for DERIVED is

static AddInX xai_derived(
    _T("?xll_derived"), XLL_HANDLEX XLL_SHORTX XLL_SHORTX XLL_UNCALCEDX,
    _T("DERIVED"), _T("Int, Int2")
);
HANDLEX WINAPI
xll_derived(short b, short d)
{
#pragma XLLEXPORT
    HANDLEX h;
    try {
        // put pointer in the base bucket
        handle<base> h_(new derived(b, d));
        ensure (h_);

        h = h_.get();
    }
    catch (const std::exception& ex) {
        XLL_ERROR(ex.what());

        return 0;
    }

    return h;
}

Note that we are putting the pointer into the base handle bucket. Now you can call BASE.VALUE on the derived class handle and get the the base class data. Code reuse is a  good thing!

The member function derived::value2 is implemented as

static AddInX xai_derived_value(
    _T("?xll_derived_value"), XLL_LONGX XLL_HANDLEX,
    _T("DERIVED.VALUE2"), _T("Handle")
);
LONG WINAPI
xll_derived_value(HANDLEX h)
{
#pragma XLLEXPORT
    int value2;
    
    try {
        // use RTTI
        derived *pd = dynamic_cast<derived*>(handle<base>(h).ptr());
        ensure (pd);
        value2 = pd->value2();
    }
    catch (const std::exception& ex) {
        XLL_ERROR(ex.what());

        return 0;
    }

    return value2;
}

This looks up the pointer in the base handle bucket and calls dynamic_cast. If the handle really is a derived pointer then the cast succeeds and you get the pointer to the derived class. If not, you get the null pointer.

Using Handles

If you construct a handle, you can call member functions as many times as you desire without the object constructor being called. If one of the inputs to the constructor changes, the old object gets deleted, a new one constructed, and all the member functions get recalculated (assuming automatic recalc mode) because they get new handles.

When you open a spreadsheet with handles you need to manually perform a recalculation Ctrl-Alt-F9 to warm up the handles the first time around. After that, all recalculation follows the Zen of Excel.

Wait, what? You are still complaining about how ugly the naked pointers look? Fine. Roll your own encoder/decoder that converts the raw handle to MyPrettyPinkPony-aBcWxYz123-[2012-01-14:15:12(GMT)] and back. You now have the tools to do that easily. Knock yourself out.

Handle Lifecycle

When a handle is being created the previous cell contents may be an old pointer that needs to be deleted. The handle class looks up the the cell value in the handle bucket and deletes it if it finds it. This does automatic garbage collection but it is possible for it to leak in two ways: (1) if you delete a cell the handle is not deleted (2) if cell containing the handle is selected then the previous contents are not available as a candidate for deletion.

A work-around that guarantees all handles will be garbage collected is to unload the xll, reload it, and hit Ctrl-Alt-F9 to warm up the handles.

Last edited Feb 14, 2012 at 9:42 PM by keithalewis, version 4

Comments

No comments yet.