Building a Real Time Data for Excel: How RTD servers work, part 3
This is part 3 of the tutorial that describes the techniques necessary to build an Excel RTD server. If you have just come to this page, I suggest you read all parts in order, here is Building a Real Time Data for Excel, part 1.
There are plenty of articles on Excel RTD programming, but I’ve yet to see one that correctly addresses the issues of multi-tasking. All the examples rely on a toy timer-driven application, which is not how real-world applications work.
The simplest way to explain it is to describe the process that happens once a user
enters an RTD formula in a spreadsheet. This is what goes on behind the scenes:
- The user enters =RTD(“GeodesiX.RTD”,,”status”,”Tokyo”) in a cell
- Excel searches its addins for a registered RTD server called “GeodesiX.RTD”
- Excel calls the RTD server on the Connect interface, with Topic1=status and Topic2=Tokyo
- The RTD server does whatever it needs to (asynchronous task etc.) to find an answer to that pair of topics and returns immediately (if it doesn’t the UI hangs)
- Time goes by
- Excel calls the RTD server on the RefreshData interface. Let’s assume the server doesn’t have an answer yet.
- Time goes by
- The server receives an answer (from wherever) for the topics. It performs a callback to Excel on the thread that Excel did the Connect, to the UpdateTopic interface.
- Time goes by
- Excel calls the RTD server on the RefreshData interface. The server has the data now and replies with a list of topics for which it has data.
- Goto step 5
The important thing to note is that this is neither a push nor a pull interface, it’s a sort of cooperative notification scheme, in which Excel is the master, who decides when to talk to the server. Now, you’ll probably say “Ah, but in step 8 it’s the serve who speaks first”. You’re right, but the callback has to be executed asynchronously, by scheduling it on Excel’s UI message thread, which Excel will consume only when it’s good and ready.
This probably appears contrived and you’re already thinking that it must be easier just to callback Excel from your asynchronous thread. I’ve been there and done that. It works, for a while, and then all of a sudden you fire up the Task Manager and there are 41 orphaned copies of Excel in the tasklist. Or maybe one of your active Excel sessions will crash. Or a host of other strange things. Believe me, you have to do it this way, and the code is there for you to copy/paste, so why fight it?
Building a Real Time Data for Excel tutorial:
- Building a Real Time Data for Excel, part 1
- Building a Real Time Data for Excel: Avoiding VSTO, part 2
- Building a Real Time Data for Excel: How RTD servers work, part 3
- Building a Real Time Data for Excel: Architecture, part 4