Excel RTD Servers: Minimal C# Implementation
Continuing the discussion of Excel RTD servers, here is about the simplest RTD server I could come up with. I used C# for this example to allow us to focus on semantics. Next time I’ll show a minimal C++ implementation.
[
Guid("9AA100A8-E50E-4047-9C60-E4732391063E"),
ProgId("Kerr.Sample.RtdServer"),
]
public class RtdServer : IRtdServer
{
As you can see, the RtdServer class is attributed with a CLSID and ProgId that can be used by COM clients to locate the COM server. By default (if you don’t employ registration-free COM), COM clients will locate the COM server by looking up either the ProgId (to find the CLSID) or the CLSID directly in the registry. You can register your assembly using the RegAsm tool that ships with the .NET Framework as follows:
%SystemRoot%\Microsoft.Net\Framework\v2.0.50727\RegAsm.exe RtdServer.dll /codebase
Remember also to mark your assembly as being visible to COM as follows:
[assembly: ComVisible(true)]
Next we need to define a minimal set of member variables in order for the RTD server to function:
private IRTDUpdateEvent m_callback;
private Timer m_timer;
private int m_topicId;
m_callback is needed to hold onto a reference to the callback interface provided by Excel. This interface is primarily used to let Excel know that new data is available. m_timer is a Windows.Forms timer used to periodically notify Excel via the callback interface. This timer is obviously optional and you are free to implement update notifications any way you want. I did however use this technique for a reason which I’ll outline in a moment. Finally, m_topicId is used to identify the topic that Excel is “subscribing” to. Again, this is just one approach which as you’ll see in a moment is very naïve.
public int ServerStart(IRTDUpdateEvent callback)
{
m_callback = callback;
m_timer = new Timer();
m_timer.Tick += new EventHandler(TimerEventHandler);
m_timer.Interval = 2000;
return 1;
}
ServerStart is the first method called by Excel and is where we prepare the RTD server. In particular we set the callback member variable and prepare the timer. Notice that the timer is not yet enabled. Returning 1 indicates that everything is fine.
public void ServerTerminate()
{
if (null != m_timer)
{
m_timer.Dispose();
m_timer = null;
}
}
ServerTerminate is called when Excel is ready to unload the RTD server. Here we simply release the timer.
public object ConnectData(int topicId,
ref Array strings,
ref bool newValues)
{
m_topicId = topicId;
m_timer.Start();
return GetTime();
}
ConnectData is called for each “topic” that Excel wishes to “subscribe” to. It is called once for every unique subscription. As should be obvious, this implementation assumes there will only be a single topic. In a future post I’ll talk about handling multiple topics. ConnectData also starts the timer and returns an initial value that Excel can display.
public void DisconnectData(int topicId)
{
m_timer.Stop();
}
DisconnectData is called to tell the RTD server that Excel is no longer interested in data for the particular topic. In this case, we simply stop the timer to prevent the RTD server from notifying Excel of any further updates.
private void TimerEventHandler(object sender,
EventArgs args)
{
m_timer.Stop();
m_callback.UpdateNotify();
}
TimerEventHandler is the private method that is called when the timer Tick event is raised. It stops the timer and uses the callback interface to let Excel know that updates are available. Stopping the timer is important since we don’t want to call UpdateNotify repeatedly.
public Array RefreshData(ref int topicCount)
{
object[,] data = new object[2, 1];
data[0, 0] = m_topicId;
data[1, 0] = GetTime();
topicCount = 1;
m_timer.Start();
return data;
}
RefreshData is called when Excel is ready to retrieve any updated data for the topics that it has previously subscribed to via ConnectData. The implementation looks a bit strange. That’s mainly because Excel is expecting the data as a COM SAFEARRAY. Although it isn’t pretty, The CLR’s COM infrastructure does a commendable job of marshalling the data for you. All you need to do is populate the two-dimensional array with the topic Ids and values and set the topicCount parameter to the number of topics that are included in the update. Finally, the timer is restarted before returning the data.
public int Heartbeat()
{
return 1;
}
Heartbeat is called by Excel if it hasn’t received any updates recently in an attempt to determine whether your RTD server is still OK. Returning 1 indicates that everything is fine.
private string GetTime()
{
return DateTime.Now.ToString("hh:mm:ss:ff");
}
}
GetTime is a private method used to get a formatted time string that represents the data to display in Excel. As you can imagine, this RTD server simply updates the time in the cell roughly every two seconds.
Clearly this RTD server implementation leaves a lot to be desired but it does demonstrate enough functionality to give you an idea of how RTD servers work. To give it a try you can use the following function from within Excel:
=RTD("Kerr.Sample.RtdServer", , "topic")
The only thing left that’s worth mentioning about this implementation is the use of the Windows.Forms.Timer class and why this even works. If you look at the way the RegAsm tool (and internally the RegistrationServices class) registers the types within the assembly you may notice that it is registered with a threading model of “Both” which indicates that the COM class is able, from a threading perspective, to load into the apartment of the caller (no proxy). In the case of Excel, the apartment happens to be a single-threaded apartment which as part of its contract provides a message pump and the message pump is all the timer needs to function. Internally it creates a hidden window to handle the WM_TIMER messages and then raise the Tick event. So it happens to work because Excel creates the RTD server in a single threaded apartment and the RTD server is happy to run directly in that apartment. This is incidentally also how ActiveX controls work.
About the only remaining thing that might trip you up is the way that Excel calls the RTD server interface. In particular it uses IDispatch to invoke the methods rather than calling them directly. In order for this to work, a type library needs to be used. I’ll discuss this further when I look at the C++ implementation but for now you just need to reference the “Microsoft Excel 11.0 Object Library”, or later, for the definitions of those interfaces.
That’s all for today. I’m really trying to keep these entries short, but there’s clearly a lot more to discuss. If you find this helpful please let me know.