Kenny Kerr

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.

So it turns out that there are in fact quite a few developers who care about writing RTD servers. I’ve received a few emails asking for more information so I may post a few more entries on the subject. Last time I simply showed what the RTD server interfaces look like in C++. Here’s the equivalent in C#:

[Guid("A43788C1-D91B-11D3-8F39-00C04F3651B8")]
interface IRTDUpdateEvent
{
    void UpdateNotify();

    int HeartbeatInterval { get; set; }

    void Disconnect();
}

[Guid("EC0E6191-DB51-11D3-8F3E-00C04F3651B8")]
interface IRtdServer
{
    int ServerStart(IRTDUpdateEvent callback);

    object ConnectData(int topicId,
                       ref Array strings,
                       ref bool newValues);

    Array RefreshData(ref int topicCount);

    void DisconnectData(int topicId);

    int Heartbeat();

    void ServerTerminate();
}

Of course what these interfaces look like and how they are actually called by Excel are two very different things thanks to the horrors wonders of IDispatch.

I’m including C# in the discussion as its very tempting for .NET developers to implement an RTD server in managed code. Unfortunately it’s easy to forget that you still need to follow the rules outlined by the COM specification. If you don’t, it will come back to haunt you later on.

Excel provides a function called RTD (real-time data) that lets you specify a COM server via its ProgId so that you can push data into Excel. Recently I had to implement an RTD server for a client and was frustrated at how hard it was to find the COM interface definitions to implement. It doesn’t appear to be documented anywhere. They’re clearly geared toward VB6 or VBA or VBScript or whatever. Anyway, I sucked out the necessary COM interfaces from the type library embedded inside the Excel executable. Here’s what an RTD server written in C++ needs to implement:

struct __declspec(novtable) __declspec(uuid("A43788C1-D91B-11D3-8F39-00C04F3651B8"))
IRTDUpdateEvent : IDispatch
{
    virtual HRESULT __stdcall UpdateNotify() = 0;

    virtual HRESULT __stdcall get_HeartbeatInterval(/*[out]*/ long* value) = 0;

    virtual HRESULT __stdcall put_HeartbeatInterval(long value) = 0;

    virtual HRESULT __stdcall Disconnect() = 0;
};

struct __declspec(novtable) __declspec(uuid("EC0E6191-DB51-11D3-8F3E-00C04F3651B8"))
IRtdServer : IDispatch
{
    virtual HRESULT __stdcall ServerStart(/*[in]*/ IRTDUpdateEvent* callback,
                                          /*[out]*/ long* result) = 0;

    virtual HRESULT __stdcall ConnectData(long topicId,
                                          /*[in]*/ SAFEARRAY** strings,
                                          /*[in,out]*/ VARIANT_BOOL* newValues,
                                          /*[out]*/ VARIANT* values) = 0;

    virtual HRESULT __stdcall RefreshData(/*[in,out]*/ long* topicCount,
                                          /*[out]*/ SAFEARRAY** data) = 0;

    virtual HRESULT __stdcall DisconnectData(long topicId) = 0;

    virtual HRESULT __stdcall Heartbeat(/*[out]*/ long* result) = 0;

    virtual HRESULT __stdcall ServerTerminate() = 0;
};

I’m posting this mostly so that I can quickly find it in future but perhaps it will come in handy for others. I could write some more about how the heck you implement these crazy interfaces and other tricks I’ve picked up but I suspect there’s a very small number of developers who care.  :)

Now that the Windows 7 keynote is over we can finally start exploring some of the improvements. This is going to be a truly awesome upgrade. As Paul puts it, Windows 7 is Windows Vista done right.

If you’re not sure where to start then I’d suggest starting right at the bottom. Listen to Mark Russinovich introduce many of the improvements made to the Windows kernel. This is really going to benefit those of you with more than 64 processing cores.  :)

Incidentally, if you’re wondering why Mark referred to the Concurrency Runtime as “concert” the reason is that the APIs for the scheduler are included in the concrt.h header file. For more on that check out what Rick has to say about Visual C++ 10.

I’m starting to think it’s about time I moved this blog. I’ve appreciated not having to worry about hosting and taking advantage of Microsoft’s hosting bandwidth, but it has been frustrating at times not having as much control as I wanted. The weblogs.asp.net server in particular seems to have branched from the blogs.msdn.com server some time back. Originally they were running the same code if I remember correctly.

My family loves books. Here are a few of the better books I've read this year.   

I’ve been a bit distracted over the last few weeks as our third son Samuel John was born earlier this month. I hope to catch up on email soon.

The MSDN Library is one of those things I install locally right after installing Visual Studio. It’s an invaluable resource to have at your fingertips. There are however occasions when I don’t have it installed or I’m using the web for some extra search power. Inevitably I end up with something like this: