Excel addins sharing data

| 0 Comments

Or, marvelling at my own, past, cleverness.

I've been doing a lot of work on these Excel addins for my current client and, as I get back into the whole C++ XLL stuff, I start to realise just how cool my child of 2001 really is.

The Excel addins run as a suite; the team I work with has different users within the bank and there's some cross over but some distinct separation. So there's a group of three, maybe four related addins. Early on one of the users wanted something they thought they had at a previous bank, I'm not convinced they did actually have it and that it wasn't just a case of "I'd like this and I know you'll say it can't be done so I'll fib..." thing, but...

Our addins grab data from remote servers, nothing too clever about that, we use CORBA because it's free (as in there are implementations where you don't have to pay money to people at all, ever) and it's decoupled from the OS at either end so the team isn't under pressure to upgrade business systems just because someone else in the bank decides to upgrade operating systems. The C++ Excel XLL addins talk CORBA via Omni ORB to C++ servers on Unix using Omni ORB and Java servers on Unix (or Windows, though they don't use Windows servers in production) running JacORB. The addins allow the users, traders generally, to pull and push trade and risk information into and out of their sheets. Traders seem to like sheets (I tend to prefer duvets).

The interesting/clever bit was when we decided that rather than simply doing the obvious and returning the data from the server directly to the sheet as a range of cells we'd give them the option to add a little indirection. Most of our addin functions allow the user to specify a "name" for the results. If they do so then rather than the addin function returning a range of cells it returns a single cell with the name and version of the result set. So GetYieldCurve() might return either a 2x40 range of data, or a single cell with a user specified name "myCurve", except the name has a version number on the end, so it would actually be "myCurve:1". Each time the data is update the version number increments, which means that the dependant cells see a change and can be triggered off of it. This named data is available to share between addins due to the architecture (we store it in a dll that all addins can access). Given that we have named result objects we need a way to manipulate them, so we have another addin with functions that allows the users to display and manipulate the results objects in various ways. They can display "results properties" which are generally things related to the call they made to generate the results and some internal system info, and they can display or select from the results themselves... Each different data type that we return has a specific "results holder" C++ object that knows how to render the data in various ways to Excel. Some of these holders support a limited form of "select" which allows the user to grab a yield curve and display it somewhere and then select and manipulate just the cells that represent, say, this year's points - I know we could do without select and let them do it in the sheet but...

As ever the devil is in the detail and it's really quite nice to be able to look at how powerful these things are when plugged together and used by users who really know what they want to do with your software.

It's especially cool to be able to go into this code, 5 years on, and rediscover the consistency of the design as I find it's particularly easy to add new functionality and fix subtle problems.

Today's issue was "optional strings or strings part of a range that were over 255 chars being interpreted by the addin as 0 length"; which was an interesting journey.

Excel strings are length prefixed but they only have a single byte prefix. This limits them to 255 bytes. Thus the XLL interface can't pass a string longer than 255 bytes from Excel to an XLL addin. The good news is that this is being addressed in Office 12 which looks like it will contain the first update to the XLL interface since Excel 4!. The also good news is that I managed to identify how to spot the +255 character string situation and handle it more gracefully and then added an alternative method for the users to pass us that much data...

During my search I found this forum which seems to have a healthy set of XLL based discussions and these blogs, 1 & 2. The blogs actually make me wish for an RSS reader to support subscriptions with "selection" so that I could subscribe but only see posts that mentioned XLLs... I also discovered a reasonably recent book (2004) on C++ XLL programming for Excel (Excel Add-in Development in C/C++: Applications in Finance) which looks like it might be worth a look (I'm going to order it and will review it once it arrives).

Leave a comment