Excel: Creating an RTD Real time Data Server with C#


Json

Excel Realtime Data (RTD) Servers

The goal of an RTD server is to provide updates to values in Excel worksheet cells. While “realtime” is something of a misnomer, updates are often scheduled to occur as often as every second, keeping the worksheet on a constantly up-to-date basis.

RTD servers can be tricky to implement. The technique is not intrinsically difficult, but there are many details that must be handled correctly. First and foremost, RTD servers are COM servers, and so must meet all the necessary requirements for COM interoperabilty. As mentioned in a previous installment, the “bitness” of the server must match the instance of Excel, that is, 32- and 64-bit versions of Excel demand 32- and 64-bit COM servers, respectively. Furthermore, COM servers must be correctly registered on the Windows system on which they will be running. Incorrect registration is probably the most common cause of failure when attempting to deploy an RTD server.

If you have not done so, it is probably wise to create a COM library for Excel before attempting to create an RTD server. This way you will be familiar with the requisite details of COM.

As we shall see, an RTD server must also implement the IRtdServer interface, without which the server cannot manage the intricate communication between the server and Excel.

How an RTD Server Works

An Excel developer uses an existing RTD server by including the RTD ( ) function in a formula. The first argument is the COM “ProgId” of the server, a two-part name like “MyServer.MyClass” (The second argument is for remote servers is simply omitted if the server is deployed on the local machine.) Because MyServer.MyClass has been registered, Excel can lookup the information necessary to instantiate a MyClass object in the system registry. As a part of the initialization, Excel will provide the RTD server with a callback reference. The RTD server can then use this callback reference to inform Excel when there is new data to be loaded. This can be done at a regularly recurring interval using timers, if appropriate. Once Excel has been notified that data is ready for update, it is up to Excel to ask the RTD server for the update. Of course, this takes a finite amount of time to complete, and many developers have swamped Excel with too many updates in too short a time interval.

SimpleRtdServer

The sample project includes two servers in a single dll. One, the SimpeRtdServer, returns the system time to Excel. Like using a sledgehammer to swat a fly, but it has the advantage of providing clear visual feedback that the updating is occurring correctly.

The primary difference between SimpleRtdServer and the second example, StockQuote, is in the use of topics. The third ( and potentially fourth, fifth and nth) arguments to the Excel RTD function collectively refer a “topic”. It is important to appreciate that even though 5 topic strings ight be provided as arguments, as far as the RTD server is concerned, there is only one topic. Five string values might be used in the server’s code to select particular data values or to modify function behavior, but as far as the COM communication is concerned, the five strings are part of one topic; that topic is identified by an integer topicId.

For the SimpleRtdServer, we assume that there is only one topic and the server returns only a single value, a formatted time string. In the StockQuote server, we will increase the complexity just a tad and use topic to specify the ticker symbol of the desired stock.

Setting Up the RTD Server Project in the Visual Studio

We will assume that you are familiar with the requirements of COM and can create a Visual Studio project for a COM library.

To create an RTD server, we must reference Microsoft.Office.Interop.Excel, which includes, among other things, the definition of the required interface, IRtdServer. Of course, to make life easier, we will also want to include the namespace in our “using” list.

References

using System;

using System.Collections.Generic; // Dictionary for topics

using System.Linq;

using System.Text;

using System.Timers;

using System.Runtime.InteropServices; // Where guid, progID attributes live

using Microsoft.Office.Interop.Excel; // defines IRtdServer

Note that we have also added System.Collections.Generic, which provides the Dictionary class useful for managing topics and System.Timers for, well, I think you can figure that one out.

Here is the code for the SimpleRtdServer class:

[

Guid(“f7ff4a68-5147-4204-a0f8-1a19580eb6c2”),

ProgId(“RtdServer.SimpleRtdServer”),

]

public class SimpleRtdServer : IRtdServer

{

private IRTDUpdateEvent _callback;

private Timer _timer;

private int _topicId;

public SimpleRtdServer()

{

//Console.OpenStandardInput();

//Console.OpenStandardOutput();

}

public int ServerStart(IRTDUpdateEvent callback)

{

Console.WriteLine(“SimpleRtdServer Start”);

_callback = callback;

_timer = new Timer();

_timer.Elapsed += new ElapsedEventHandler(TimerEventHandler);

_timer.Interval = 5000; // in this example we choose an interval of five seconds so we can easily observe the ubpdates in Excel

return 1;

}

public void ServerTerminate()

{

if (null != _timer)

{

_timer.Dispose();

_timer = null;

}

}

public int Heartbeat()

{

return 1;

}

public object ConnectData(int topicId,

ref Array strings,

ref bool newValues)

{

_topicId = topicId;

_timer.Start();

return GetTime();

}

public void DisconnectData(int topicId)

{

_timer.Stop();

}

private void TimerEventHandler(object sender,

EventArgs args)

{

// UpdateNotify is called to inform Excel that new data are available

// the timer is turned off so that if Excel is busy, the TimerEventHandler is not called repeatedly

_timer.Stop();

_callback.UpdateNotify();

}

public Array RefreshData(ref int topicCount)

{

object[,] data = new object[2, 1];

data[0, 0] = _topicId;

data[1, 0] = GetTime();

topicCount = 1;

_timer.Start();

return data;

}

private string GetTime()

{

return “RTD Server GetTime method: “ + DateTime.Now.ToString(“hh:mm:ss”);

}

} //SimpleRtdServer

The Major Points

Of course, the class must implement IRtdServer.

public class SimpleRtdServer : IRtdServer

When SimpleRtdServer is started by Excel, Excel provides a callback reference that we save in a field of the class. We also use this opportunity to instantiate a timer and set the desired interval.

public int ServerStart(IRTDUpdateEvent callback)

{

Console.WriteLine(“SimpleRtdServer Start”);

_callback = callback;

_timer = new Timer();

_timer.Elapsed += new ElapsedEventHandler(TimerEventHandler);

_timer.Interval = 5000; // in this example we choose an interval of five seconds so we can easily observe the ubpdates in Excel

return 1;

After the SimpleRtdServer class has started up, Excel will invoke ConnectData. Excel provides a topicId number, which we must use in further communications with Excel. In this example, there should only ever be one topicId, so we can just stash it in a field.

public object ConnectData(int topicId,

ref Array strings,

ref bool newValues)

{

_topicId = topicId;

_timer.Start();

return GetTime();

}

In the timer event handler, we call the UpdateNotify method of the Excel callback reference. We are letting Excel know that there is new data available in the server for Excel to load.

private void TimerEventHandler(object sender,

EventArgs args)

{

// UpdateNotify is called to inform Excel that new data are available

// the timer is turned off so that if Excel is busy, the TimerEventHandler is not called repeatedly

_timer.Stop();

_callback.UpdateNotify();

}

When Excel has time, it will call the RefreshData method with a topicId as an integer. Note that the return value is an array of type object. In this array, the first element is a topicId number, and the second is the new data value associated with this topicId. In the SimpleRtdServer example, there is only ever one topic, so the array is hard-coded to return the formatted time string and the only topicId..

public Array RefreshData(ref int topicCount)

{

object[,] data = new object[2, 1];

data[0, 0] = _topicId;

data[1, 0] = GetTime();

topicCount = 1;

_timer.Start();

return data;

}

If the Excel developer removes the RTD function from some cell, the DisconnectData method will be invoked. In this example, we use this opportunity to turn off the timer. In general, however, this is poor technique, since in general there may be other topics that will still require servicing via the timer. Here, we are trying to keep the code as simple as practical.

Part of the job of the developer is to clean up after himself or herself, so we have a ServerTerminate method.

public void ServerTerminate()

{

if (null != _timer)

{

_timer.Dispose();

_timer = null;

}

}

Even if the Excel developer removes all the RTD function calls from every cell, the RTD server will not be terminated until Excel actually closes.

The simple HeartBeat method exists solely to let Excel determine if the server is running.

public int Heartbeat()

{

return 1;

}

Using the RTD Server in Excel

Once the RTD server is compiled, it can be easily invoked from Excel using the RTD ( ) function.

RtdSimpleServerFormula

Note that the second argument, the remote server name, is omitted. Also note that while our simple server made no use of topics, at least one topic string is required by the RTD ( ) function.

Coming Up…

Once you have successfully implemented a running RTD server, you have jumped the most difficult hurdles. Only one more hurdle remains in the creation of a practical and useful RTD server, the management of multiple topics. We will see this in the next installment.

The source code for this project can be downloaded here. However, this project is specific for the 32–bit version of Excel 2010. If you are developing for a different environment, you will have to make the corresponding changes.

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google

Você está comentando utilizando sua conta Google. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s