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.