Excel RTD Servers: Multiple Topics in C#

The last few entries in this series got a bit long but I didn’t feel like spinning off a mini-series just to focus on COM fundamentals. I skimmed over a lot but hopefully it was enough to give you an idea of what’s involved. Hopefully from now on there should be enough groundwork in place to keep things short.

Having got a minimal single-topic RTD server working in C# and C++ it makes sense to take a look at what it takes to support multiple topics. Fortunately it’s quite straightforward. For starters we’ll update the minimal C# implementation to support multiple topics. But first let’s recap how topics are fed into the RTD server.

The Excel RTD function is prototyped as follows:

=RTD(progId, server, topic1, [topic2], ...)

Unfortunately this is a bit misleading. The topics here really should be called parameters. So an RTD function starts with a ProgId, an optional server name, and one or more parameters. Each unique list of parameters forms an individual topic for which Excel will call the RTD server’s ConnectData method.

Imagine you’ve populated four cells in Excel with the following functions:

A1 =RTD("ProgId", ,  "one")
A2 =RTD("ProgId", ,  "one")
A3 =RTD("ProgId", ,  "one", "two")
A4 =RTD("ProgId", ,  "two", "one")

Excel will call the RTD server’s ConnectData method once for A1 and A2 as they have the same list of parameters. It will then call ConnectData for both A3 and A4 as they have a different list of parameters. As far as the RTD server is concerned Excel is interested in three unique topics with each topic identified by a unique number provided by Excel. That number is how the RTD server communicates values back for individual topics when Excel calls the RTD server’s RefreshData method.

With that let’s update the C# RTD server to support multiple topics. The initial version just printed out the time as follows:

DateTime.Now.ToString("hh:mm:ss:ff")

Instead of hard coding the format string lets take the first parameter of the topic and use that as the format string so that the user can display the time in whatever format he or she desires.

Start by replacing the m_topicId member variable with a dictionary of topic Ids and format strings:

private Dictionary<int, string> m_topics;

You should create the dictionary in the ServerStart method and then add topics to it in the ConnectData method:

string format = strings.GetValue(0).ToString();
m_topics[topicId] = format;

The DisconnectData method also needs a bit of work. Previously we simply stopped the timer. Now that wouldn’t make sense as there may still be other topics that are in use. Instead we need to simply remove the specific topic from the dictionary.

Finally, the RefreshData method now has to enumerate the topics in the dictionary and build the multi-dimensional array. Remember that the first dimension is for the list of topic Ids and the second is for the values corresponding to those topics.

You can test it in Excel using a variety of RTD functions with different format strings. Here’s an example:

=RTD("Kerr.Sample.RtdServer", , "hh:mm:ss")

Below is the complete source code for the updated RTD server:

[
    Guid("B6AF4673-200B-413c-8536-1F778AC14DE1"),
    ProgId("Kerr.Sample.RtdServer"),
    ComVisible(true)
]
public class RtdServer : IRtdServer
{
    private IRTDUpdateEvent m_callback;
    private Timer m_timer;
    private Dictionary<int, string> m_topics;

    public int ServerStart(IRTDUpdateEvent callback)
    {
        m_callback = callback;

        m_timer = new Timer();
        m_timer.Tick += new EventHandler(TimerEventHandler);
        m_timer.Interval = 2000;

        m_topics = new Dictionary<int, string>();

        return 1;
    }

    public void ServerTerminate()
    {
        if (null != m_timer)
        {
            m_timer.Dispose();
            m_timer = null;
        }
    }

    public object ConnectData(int topicId,
                              ref Array strings,
                              ref bool newValues)
    {
        if (1 != strings.Length)
        {
            return "Exactly one parameter is required (e.g. 'hh:mm:ss').";
        }

        string format = strings.GetValue(0).ToString();

        m_topics[topicId] = format;
        m_timer.Start();
        return GetTime(format);
    }

    public void DisconnectData(int topicId)
    {
        m_topics.Remove(topicId);
    }

    public Array RefreshData(ref int topicCount)
    {
        object[,] data = new object[2, m_topics.Count];

        int index = 0;

        foreach (int topicId in m_topics.Keys)
        {
            data[0, index] = topicId;
            data[1, index] = GetTime(m_topics[topicId]);

            ++index;
        }

        topicCount = m_topics.Count;

        m_timer.Start();
        return data;
    }

    public int Heartbeat()
    {
        return 1;
    }

    private void TimerEventHandler(object sender,
                                   EventArgs args)
    {
        m_timer.Stop();
        m_callback.UpdateNotify();
    }

    private static string GetTime(string format)
    {
        return DateTime.Now.ToString(format, CultureInfo.CurrentCulture);
    }
}

If you’re looking for one of my previous articles here is a complete list of them for you to browse through.

Produce the highest quality screenshots with the least amount of effort! Use Window Clippings.