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.

Published Thursday, November 13, 2008 10:13 AM by KennyKerr

Comments

# re: Excel RTD Servers: Minimal C# Implementation

Monday, November 17, 2008 8:19 PM by falcon

I had to write a RTD server in C# a few months ago, not having any background in COM made it a terrible process.  Once my server was running, I noticed that every once in a while, I would get exceptions about spreadsheet being busy (therefore unresponsive).  When I couldn't figure that out, I just dropped the project.

Also, is there a good way to get information OUT of excel...say if one wants to trigger an out of process FIX engine to send out orders?

# re: Excel RTD Servers: Minimal C# Implementation

Tuesday, November 18, 2008 2:50 AM by KennyKerr

falcon: Excel has an Automation API to control it from the outside but you could just write some VBA as a macro to call out to a COM server.

# re: Excel RTD Servers: Minimal C# Implementation

Monday, December 8, 2008 12:53 PM by Patrick Waters

Is this known to work with .net 3.5 / vs 2008 and excel 2007?

i've built what looks almost identical to your sample here, and only ever get N/A in my excel sheet.

could you possibly expand on your development process a bit?  like do you have to reregister each time?  what do you have in AssemblyInfo.cs?

# re: Excel RTD Servers: Minimal C# Implementation

Tuesday, December 9, 2008 3:05 AM by KennyKerr

Patrick Waters: I wrote the C# samples with Visual Studio 2008. If you only have Excel 2007 installed then reference the Excel 12 type library instead. You can find the type library in Excel.exe.

To test you should use a C++ console app that calls CoCreateInstance and query for IRtdServer. That will simulate what Excel does and allow you to get more information about the failure.

I’ll probably write a follow up post about testing RTD servers some time.

Try the complete C# RTD sample found here:

weblogs.asp.net/.../Rtd6.aspx

The one thing that can trip you up is if you use a compiler generated version number for the assembly/file. If you have been using one then change it to be hard coded and change the RTD server’s CLSID then register again with the /codebase flag.

# re: Excel RTD Servers: Minimal C# Implementation

Tuesday, December 16, 2008 12:30 PM by Nuno Tavares

I've written an RTDServer in C#. I Call the updtatenotify when I receive a message from a socket stream. However I can receive a lot of messages for second. The messages that I receive from the socket stream sometimes get lost in the RefreshData. The number of calls I make to updatenotify are bigger than the times that RefreshData is executed! I don't have a clue why! Any thoughts?

# re: Excel RTD Servers: Minimal C# Implementation

Tuesday, December 16, 2008 3:00 PM by KennyKerr

Nuno Tavares: you must only call UpdateNotify on the thread (more specifically the apartment) that you received the interface on. You must also not call it more than once every two seconds or so. Excel will not update the RTD function more frequently than that anyway.

# re: Excel RTD Servers: Minimal C# Implementation

Wednesday, December 17, 2008 5:26 AM by Nuno Tavares

Excel only updates every 1 to 2 seconds. That's the problem. Thank you. RTD is better than DDE Links but why that limitation?

# re: Excel RTD Servers: Minimal C# Implementation

Monday, December 29, 2008 11:39 AM by Nuno Tavares

I've resolved the callback issue, just one last question, why does excel passes the  topicCount variable by ref? And what value do I have to give it? the total number of topics or just the number of topics I want to update?

# re: Excel RTD Servers: Minimal C# Implementation

Tuesday, December 30, 2008 3:28 AM by KennyKerr

Nuno Tavares:

Since the updates occur on Excel’s UI thread it safe to assume that the limit is there to keep Excel responsive. I’ve seen sheets with many thousands of RTD functions. Without such a limit the Excel window would quickly become unusable as the message pump would be blocked. As it is, it is possible to poorly implement RTD servers that starve Excel.

The topic count is the number of topics being updated and thus maps to the size of the second dimension of the returned array.