Building a Real Time Data for Excel: Architecture, part 4

This is part 4 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.

Putting together an RTD server is not particularly difficult… once you know how to do it. Hopefully this guide will shorten your learning experience

In part 3: How RTD servers work we saw what happens when a user types and RTD formula in a cell. Here I shall try and map that to the dataflow it generates amongst the class instances in Geodesix.

The blue items are instances of an object, the black labels are method calls. If this is your first reading, print the diagram, it’ll be easier to follow:

Commentary

From now on, when I refer to a tuple “A.B.C”, I am directing you to project/namespace A, class(module) B, Method C. The bullet numbers correspond to the 18 #N# codes in the source code.

1. The user enters the formula =RTD(“Geodesix.RTD”,,”geocode”,”status”,”Tokyo”) in cell A1.
   Excel searches for a registered RTD server called Geodesix.RTD.
   It calls the Connect method with the topics “geocode”, “status” and “Tokyo”.
2. The addin interface marshals the call to managed code and calls Geodesix.RTDGeodesix.Topic_Connect.
3. Topic_Connect determines that this a Geocode call and dispatches it to Geodesic.Cache.InitiateGeocode.
   If InitiateGeocode finds the query “Tokyo” in the cache it returns and we’re done
   (Excel will call RefreshData later to get the value).
   If not, it creates a geocode query and passes it to Geodesics.Geodesic.Find
4. Find adds the query to an internal queue, starts the worker task if necessary, and returns (to Excel).
   Note that these first 4 steps are quick, so that the user interface remains snappy.
5. Asynchronously, the worker task takes the next query off the queue.
   It raises the Started event to notify listeners that it is beginning a query.
   It continues doing this as long as there are entries in the queue.
6. The worker passes each query to Geodesics.Geodesic.Resolve.
7. Resolve determines the query type (Geocode or Travel/Directions).
   It sends a REST HTTP request to Google and receives a JSON answer in return.
   It calls GetLocations to parse the JSON object into a list of Locations.
   The worker raises the Completed event to notify listeners that it has finished a query.
8. Resolve calls Geodesix.Cache.GeodesicComplete on the worker thread.
9. GeodesicComplete uses the previously saved Excel SynchronizationContext to queue
   a call to GeodesicCompleted (notice the “d” at the end).
10. On Excel’s UI thread
    GeodesicCompleted sees that the query is a Geocode and passes the query to
    GeodesicCompletedGeocode, who updates the cache.
11. GeodesicCompleted calls Excel’s UpdateTopics on Excel’s UI thread,
    to provoke Excel into issuing a RefreshData
12. Some time later, when it has nothing else to do, Excel calls RefreshData.
13. The addin interface marshals the call to Geodesix.RTDGeodesix.RefreshData.
14. RefreshData Calls Geodesix.Cache.Geocode to obtain the query and passes
    the result back. RefreshData returns the A1 cell value to Excel.
15. Separately, sometime later, the user enters =Geocode(“status”,”Tokyo”) in cell B1.
    Excel calls the Geocode UDF.
16. The interface marshals the call to managed code and calls the UDF Geodesix.ExcelAddinModule.Geocode.
17. This Geocode, looks to see if “Tokyo” is in the cache.
    If it is, it simply returns the “status” result; this is the end of all things to
    do with RTD and the vallue of B1 is fixed forever.
18. If “Tokyo” isn’t in the cache, Geocode returns =RTD(“Geodesix.RTD”,,”geocode”,”status”,”Tokyo”),
    which tells Excel that this cell needs to be updated by RTD, and we return to step 1.

The moral of the story is that in the end, =Geocode() formulas will eventually return the relevant value #17# (once the RTD has got it) and there will be no more RTD updates on the spreadsheet. This is not how a typical RTD server works; the UDF wrapper would normally always return the =RTD() #18# function so that updates are continuously received.

Note. Because of this, the Disconnect interface is never used.

Sorry if that was a bit windy, the rest is relatively plain sailing.

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
• Building a Real Time Data for Excel: Excel, multithreading and callbacks, part 5