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.
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.
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.
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.
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.