The demonstration add-in is contained in project FeedAddin.vcproj in the sample solution, DemoFeed.sln.
This topic contains the following sections.
Overview
The add-in project was created as a standard XLL+ Add-in project, using the XLL+ AppWizard.
For convenience in using the RTD server API and the Demo Feed API, Unicode characters were chosen. For simplicity in coding a popup dialog, the MFC (Microsoft Foundation Classes) libraries were selected.
The add-in contains various styles of function:
-
Worksheet add-in functions that return live data.
-
Worksheet add-in functions that return snap-shots of data.
-
Command macros that publish data to the feed.
-
Command macros that control the behavior of the feed.
An extended user interface is provided for some of the worksheet functions, so that the user is helped to select inputs in the Excel Formula Wizard.
The rest of this topic examines the C++ source code in FeedAddin.vcproj.
Initialization and termination
Open FeedAddin.cpp, which is the main source file for the project.
We start with CFeedAddinApp::OnXllOpenEx(). This contains 4 steps:
-
Start logging.
-
Connect to the data feed.
-
Connect to the RTD server.
-
Set up the user interface.
The RTD server initialization looks like this:
CopyC++// Initialize RTD if (!m_rtdServer.RegisterClient(RTD_CLIENT_NAME, &m_rtdCallback)) { XlMessageBox(_T("Failed to connect to RTD server"), XlMessageBoxTypeExclamation); return FALSE; }
If RegisterClient() fails, then the add-in will not be able to provide live data, and therefore FALSE is returned to indicate that the add-in should not be opened.
A client name is passed as the first argument to RegisterClient(). This should be a name used only by this XLL. The RTD server can support multiple XLLs simultaneously, and therefore each client XLL must have a unique name. You can safely use the file name of the XLL for this purpose.
The second argument is a pointer to an object that implements IXllRtdFeedCallback. This object will handle call-backs from the RTD server.
In CFeedAddinApp::OnXllClose(), the add-in terminates its services.
Tear down the user interface.
Disconnect from the RTD server
Disconnect from the data feed.
The RTD server is terminated as follows:
CopyC++// Terminate connection to RTD feed server
m_rtdServer.UnregisterClient();If RegisterClient() failed earlier, then UnregisterClient() will fail harmlessly.
Callbacks
Next in FeedAddin.cpp are three small callback functions. Each redirects to a method of the application class.
MyXllRtdFeedCallback::OnGetDataState() is called when Excel needs some information about a cell containing live data.
MyXllRtdFeedCallback:: OnTopicRemoved () is called when Excel is no longer using a particular item of live data.
MyFeedConnection::OnGetUpdates() is called by the data feed’s listener thread whenever the data feed has sent some updates for data in which the add-in is interested.
This section contains the following subsections.
OnRtdGetDataState
This call-back is the most important part of the real-time data implementation. It is called by Excel whenever it needs to know about a particular topic.
This call may occur at any of three points:
For a new topic, that has just been requested from an add-in function, via a call to CXllRtdFeedServerProxy::CallRtd().
From a cell in a previously saved workbook which has been reopened.
For a topic that has been updated by the client XLL using CXllRtdFeedServerProxy::UpdateTopic().
The implementation consists of three steps.
Look up the requested item in the cache. (This should be a thread-safe operation.)
If the requested item is not yet on the cache, then add the item, and request the data from the data feed. (This should also be thread-safe.)
Set the value of the parameter *lpvntState to the timestamp of the data.
OnRtdTopicRemoved
This method is called when Excel informs us (via the RTD server) that the specified topic has been removed from the RTD server's list of active topics.
This will occur after the last cell range that uses the topic is removed, closed, or for any other reason ceases to be connected to the specified topic - for instance because an argument to a formula has changed, and therefore the topic has changed.
The call is not guaranteed to come immediately after the topic becomes unused. It may be delayed - Excel controls the timing.
The implementation consists of two steps.
Remove the requested item from the cache. This should be a thread-safe operation.
Inform the data feed that we are no longer interested in the item (Unadvise).
OnFeedGetUpdates
This method is called whenever updates arrive from the data feed. It may be called by the data feed listener thread or by the main thread. Like the other event handler callback functions, all the operations it calls must be thread-safe.
If the feed is currently paused, then the update messages are queued and no further action is taken until the feed is restarted.
If the feed is not currently paused, then 2 steps are required for each update message:
Update the data item in the cache. If it is not present in the cache, discard the message.
Call CXllRtdFeedServerProxy::UpdateTopic() to tell Excel that the topic is changed, and that all cells depending on it should be recalculated.
Feed helper functions
The next section of the source code contains helper functions for using the data feed.
AddFeedItemToCache
This utility method adds a data item to the cache and connects it to the data feed.
Add the item to the cache, without any data.
Fetch the data from the data feed (using a synchronous GET call).
Send an ADVISE message to the feed to register for future updates.
As with all the other core functions of the add-in, all of the operations in this method are thread-safe.
PauseFeed
This method switches on the update queue, so that asynchronous updates no longer reach the cache or Excel. Until the feed is restarted, all updates will be stored in the update queue.
RestartFeed
This method switches the update queue off, so that asynchronous update messages go directly to the cache and to Excel. It also processes all queued update messages that arrived while the feed was paused.
LoadDictionary
LoadDictionary() downloads the data dictionary from the feed, and stores it in a local copy. This copy will be used to validate property identifiers and to enrich the user interface.
Add-in functions
Finally, in FeedAddin.cpp, we reach the code for the add-in functions.
Function |
Type |
Description |
|---|---|---|
Worksheet, real-time data |
Get a named data item as live data |
|
Worksheet |
Get a named data item. Do not receive updates. |
|
Worksheet |
Gets a list of security codes that match the search string |
|
Macro |
Pauses or restarts the data feed |
|
Macro |
Publishes a single value to the data feed |
|
Macro |
Publishes the values in the specified range. If no range is specified, the current selection is used. |
|
Macro |
Sets the time between calls to the data feed, getting updated items |
|
Macro |
Displays a dialog for searching for security codes |
DF_LIVE
DF_LIVE(Security, Property)
Real-time data worksheet function
This function is a simple and typical real-time data function. It is a real-time data function: it returns live data, which is automatically refreshed when the data is updated.
DF_LIVE makes use of the methods in the helper class CFeedHelper, which handle the basic tasks of converting data formats between Excel and the DemoFeed API.
CopyC++CXlOper* DF_LIVE_Impl(CXlOper& xloResult, const CXlStringArg& Security, const CXlStringArg& Property) { CFeedAddinApp* papp = XllGetTypedApp(); // Parse input CStringW strSecurityCode = CFeedHelper::MakeSecurityCode(Security); int nPropertyId = CFeedHelper::ValidateProperty(Property); // Check the cache if (!papp->m_dataCache.GetValue((LPCWSTR)strSecurityCode, nPropertyId, &xloResult, 0)) { // For new items, add to the cache, get the data from the feed, // and also advise future updates. papp->AddFeedItemToCache((LPCWSTR)strSecurityCode, nPropertyId, xloResult); } // Call RTD, to inform Excel that this is live data CXlOper xloRtdResult; std::wstring strTopic = CDataCache::MakeTopic( (LPCWSTR)strSecurityCode, nPropertyId); papp->m_rtdServer.CallRtd(xloRtdResult, strTopic.c_str()); return xloResult.Ret(); }
The function performs four steps.
Validate and convert the inputs, throwing an exception if the property is invalid.
Check the cache for the requested data.
If the data is not in the cache, request it from the feed and add it to the cache. Also send an ADVISE message to the feed, so that our feed listener thread will receive update messages about this data item.
Inform Excel that the calling cell is dependent on external data, by calling CXllRtdFeedServerProxy.CallRtd(), with a topic that defines the data item.
The last step is very important. Even if the data is found in the cache, we must still inform Excel that the data may change in the future; if we don’t use CallRtd(), then Excel will not know this.
Note that all calls to shared components are thread-safe.
Note also that the returned data may be scalar (single-cell) or an array. CFeedHelper::ParsePropertyValue() handles the conversion, making use of the data dictionary to determine the data type and rank of the property.
DF_GET
DF_GET(Security, Property)
Worksheet function
This function is equivalent to DF_LIVE, except that it is not a real-time data function: it simply returns the requested item from the feed – cells will not be refreshed when the data is updated.
CopyC++CXlOper* DF_GET_Impl(CXlOper& xloResult, const CXlStringArg& Security, const CXlStringArg& Property) { CStringW strSecurityCode = CFeedHelper::MakeSecurityCode(Security); int nPropertyId = CFeedHelper::ValidateProperty(Property); std::wstring strValue; DemoFeed::timestamp_type tsValue; int rc = XllGetTypedApp()->m_connection.Get(strSecurityCode, nPropertyId, strValue, tsValue); if (rc == 0) xloResult = CFeedHelper::ParsePropertyValue(strValue, nPropertyId); else xloResult.Format(_T("#ERROR %d"), rc); return xloResult.Ret(); }
DF_GET makes use of the methods in the helper class CFeedHelper, which handle the basic tasks of converting data formats between Excel and the DemoFeed API.
DF_GET performs three steps:
Validate and convert inputs.
Request data from the data feed.
Convert returned data to Excel format.
Note that the returned data may be scalar (single-cell) or an array. CFeedHelper::ParsePropertyValue() handles the conversion, making use of the data dictionary to determine the data type of the property.
DF_FIND
DF_FIND(Search)
Worksheet function
This simple function sends a partial security code to the data feed and returns the list of security codes that match. It is not a real-time data function, and does not receive automatic updates.
DF_TOGGLE_FEED
DF_TOGGLE_FEED()
Macro
This macro function pauses or restarts the data feed. It also updates the text of the Excel menu item to match the current state of the feed.
CopyC++CXlOper* DF_TOGGLE_FEED_Impl(CXlOper& xloResult)
{
CFeedAddinApp* papp = XllGetTypedApp();
if (papp->FeedIsPaused())
papp->RestartFeed();
else
papp->PauseFeed();
papp->m_menu.ReplaceItem(0, papp->FeedIsPaused()
? _T("Restart &Feed") : _T("Pause &Feed"));
return xloResult.Ret();
}Note that the three methods used – FeedIsPaused(), PauseFeed() and RestartFeed() – are all thread-safe operations, to avoid conflicts with the feed listener thread.
DF_SET
DF_SET(Security, Property, Value)
Macro
This macro function publishes a single data value to the feed. It can be called from VBA code, e.g.:
CopyVBAApplication.Run "DF_SET", "MGC", 2, 99.9
It is useful for writing VBA macros that test functionality, performance and latency.
DF_SET_RANGE
DF_SET_RANGE(Range)
Macro
This macro function publishes a range of data values to the feed. It can be called from VBA code, e.g.:
CopyVBAApplication.Run "DF_SET_RANGE", Range("F3:K24")
If Range is omitted, then the current selection is used as the input. A command on the Excel Demo Feed menu, Set data from Selection, invokes this macro.
The macro accepts three forms of data range.
DF_SET_THROTTLE_INTERVAL
DF_SET_THROTTLE_INTERVAL(Period)
Macro
The data feed client uses a pull-push mechanism. It polls the server periodically from a background (listener) thread. Use this macro to set the period, in milliseconds.
DF_FIND_POPUP
DF_FIND_POPUP()
Macro
This macro function pops up the security code search dialog that is discussed below under User Interface extensions. If the user selects a code, it is placed in the active cell.
User interface extensions
DemoFeedUI.cpp contains code that enriches the user interface of the Excel Formula Wizard.
Drop-down lists
In CFeedAddinApp::SetupPropertyValueLists(), the data dictionary is used to build a drop-down list which will appear in the Formula Wizard:
For more information about drop-down lists of values see the User Guide.
Pop-up dialogs
CSecurityCodePopupProvider implements a popup editor for finding security codes, which appears when the user clicks the browse (...) button in the Excel Formula Wizard:
Statically declared CXlWizExUIArgumentPopupCreator objects are used to register the popup editors for each appropriate argument.
(As with much of the Demo Feed sample, this is a minimal implementation, just to give you an idea of what you might do. It is not intended to be a complete, elegant or powerful example of a search tool.)
For more information on pop-up dialogs, see the User Guide.
Components
The add-in application contains several components that handle specific tasks.
CXllRtdFeedServerProxy
The application class, CFeedAddinApp, contains an instance of CXllRtdFeedServerProxy, which is used to send requests and update messages to the RTD server.
CopyC++class CFeedAddinApp : public CXllApp { ... // Connection to RTD CXllRtdFeedServerProxy m_rtdServer; ... }
Whenever a topic of interest is updated, the RTD server is informed during CFeedAddinApp::OnFeedGetUpdates():
CopyC++m_rtdServer.UpdateTopic(pszTopic);
During every add-in function that supplies live data, a call is made to CallRtd():
CopyC++// Call RTD, to inform Excel that this is live data
CXlOper xloRtdResult;
std::wstring strTopic = CDataCache::MakeTopic(strSecurityCode, Property);
papp->m_rtdServer.CallRtd(xloRtdResult, strTopic.c_str());MyXllRtdFeedCallback
This class is derived from the abstract base class IXllRtdFeedCallback. It handles two important call-backs from the RTD server. In both cases, they are routed to the application class:
CopyC++bool MyXllRtdFeedCallback::OnGetDataState(LPCWSTR lpszClient, LPCWSTR lpszTopic, LPVARIANT lpvntState) { return XllGetTypedApp()->OnRtdGetDataState(lpszClient, lpszTopic, lpvntState); } void MyXllRtdFeedCallback::OnTopicRemoved(LPCWSTR lpszClient, LPCWSTR lpszTopic, long lTopicID) { XllGetTypedApp()->OnRtdTopicRemoved(lpszClient, lpszTopic, lTopicID); }
The application class’s implementations of these event handlers are discussed elsewhere.
MyFeedConnection
This class is derived from DemoFeed::FeedConnection, and implements the virtual function, OnGetUpdates(), which is called from a listener thread whenever updates arrive.
CopyC++class MyFeedConnection : public DemoFeed::FeedConnection { public: virtual void OnGetUpdates(const std::vector<DemoFeed::FeedMessage>& updates) { XllGetTypedApp()->OnFeedGetUpdates(updates); } };
The updates are routed to the application class method OnGetFeedUpdates(). This is discussed elsewhere.
An instance of MyFeedConnection is contained in the application class:
CopyC++class CFeedAddinApp : public CXllApp { ... // Connection to data feed MyFeedConnection m_connection; ... }
The instance is used to send requests to the data feed, including the following:
-
Get() and Advise() are called when an add-in function requests a particular data topic.
-
Unadvise() is called Excel informs us (through MyXllRtdFeedCallback::OnTopicRemoved()) that a topic is no longer of interest.
CDataCache
This class is a thread-safe lookup table. The key to each item in the table is a pair: security code + property identifier.
For each item the following fields are held:
| Field | Type | Description |
|---|---|---|
| m_strSecurityCode | string | Security code |
| m_nPropertyId | int | Property identifier |
| m_strValue | string | Value, in string form, as returned by the data feed |
| m_xloValue | CXlOper | Value in Excel form, after being parsed from a string |
| m_bHasValue | bool | True if the value is available, false if it is currently unknown |
| m_timestamp | timestamp | Timestamp of the data value (or 0 if not known) |
Holding the parsed value of the data in m_xloValue is a very good idea for improving performance. A single piece of data may be required many times, since Excel’s recalculation engine can frequently recalculate cells that have not changed, and this way it need only be parsed once.
The following thread-safe methods are available for accessing the cache:
- AddItem
- RemoveItem
- UpdateItem
- GetItem
- GetValue
The last method, GetValue(), is optimized for minimal data copying, and is intended for use from add-in functions.
CMessageQueue
This simple class implements a thread-safe message queue.
If the data feed is paused, then message queue is activated, and the add-in pushes any new update messages into the queue.
If the feed is restarted, then all the queued messages are processed, and any newly arrived update messages are processed normally.
CFeedHelper
This class contains a few static functions that help with data conversion and validation.
CopyC++static int CFeedHelper::ValidateProperty(const wchar_t* pszProperty, const TCHAR* pszArgumentName = _T("Property"));
ValidateProperty() is a useful method, and is worth examining. It does the following for a Property identifier input to an add-in function:
- If the input is an integer, check whether it is a legal numeric property id. If it is not, then throw an exception.
- If the input is a string, look it up in the data dictionary. If it is valid, return the numeric property id. If it is not, then throw an exception.
- Return the numeric property id.
Conveniently, the method only returns a valid property id. If an exception is thrown, then the add-in function will immediately cease, and will return a useful error message to Excel. This is a useful pattern, and allows you to write simple short code in the add-in function, with no extra error handling overhead; e.g.:
CopyC++int nPropertyId = CFeedHelper::ValidateProperty(Property);