Creating an RTD server/function in Excel-DNA is very simple.
An RTD function in Excel is basically a function that continually updates Excel, for example a cell might show the current price of a stock without the Excel user having to refresh/recalc.
I’m going to take the code a sample written by Govert van Drimmelen’s on the Excel-DNA google group and work through it to show what does what. His code demonstrates code for a time service/server. i.e. it will supply the current time to Excel and update it every 500ms.
- Create a class library named RtdFunctions (or whatever you like)
- Using NuGet add the Excel-DNA package to the project
- Add the packages\Excel-DNA.0.30.3\tools\ExcelDna.xll file to your project and rename to match the project but suffixed -AddIn.xll, i.e. mine is RtdFunctions-AddIn.xll set it’s “Copy to Output Directory” property to “Copy if newer”. You could ofcourse handle the copy within the post build step if you prefer.
- Rename the Class1.cs file to TimerServer.cs
We’ve now created a bare bones of this project, it’s time to now implement the TimeServer. Whilst we could implement the IRtdServer interface ourselves. The Excel-Dna assembly includes a thread-safe implementation already named ExcelRtdServer, so let’s use that so we can concentrate on the actual RTD functionality.
So…
- Derive the new TimeServer class from ExcelRtdServer, you’ll need to add the using clause for ExcelDna.Integration.Rtd
- If we want the potential to support multiple topics we’ll need a list of topics, ExcelRtdServer wraps topic id’s in a Topic object so add
private readonly List<Topic> topics = new List<Topic>();
to our TimerServer
- We are going to need to override three methods from the ExcelRtdServer, ServerTerminate, ConnectData and DisconnectData, so go ahead and override those and then we’ll implement the functionality in each
- As the purpose of this TimerServer is to supply the time using a push style notification, we’re going to add a Timer to the class, I’m using the System.Threading.Timer so add the following
private Timer timer; public TimeServer() { timer = new Timer(Callback); } private void Start() { timer.Change(500, 500); } private void Stop() { timer.Change(-1, -1); } private void Callback(object o) { Stop(); foreach (Topic topic in topics) { topic.UpdateValue(GetTime()); } Start(); } private static string GetTime() { return DateTime.Now.ToString("HH:mm:ss.fff"); }As you can see the Callback method, which is triggered on a timer event, will loop through the topics calling UpdateValue with results from GetTime, in other words it will tell each topic to update to the latest time.
- Amend the ServerTerminate method to dispose of the timer, this will be called when Excel terminates any use of the TimrServer, so we can safely clean up here, so ServerTerminate should look like this
protected override void ServerTerminate() { timer.Dispose(); timer = null; } - Next we want to handle the ConnectData implementation. Each time the server is connected to we will cache the topic in our topics like and ensure the timer is started before returning the current time
protected override object ConnectData(Topic topic, IList<string> topicInfo, ref bool newValues) { topics.Add(topic); Start(); return GetTime(); }The ConnectData method is called when a spreadsheet is opened with the RTD functions or, ofcourse, when you add a function call to the server method
- Finally we want to implement the DisconnectData method to remove the cached topic and possible stop the service, so add the following
protected override void DisconnectData(Topic topic) { topics.Remove(topic); if (topics.Count == 0) Stop(); }
So the full code is listed here
public class TimeServer : ExcelRtdServer
{
private readonly List<Topic> topics = new List<Topic>();
private Timer timer;
public TimeServer()
{
timer = new Timer(Callback);
}
private void Start()
{
timer.Change(500, 500);
}
private void Stop()
{
timer.Change(-1, -1);
}
private void Callback(object o)
{
Stop();
foreach (Topic topic in topics)
topic.UpdateValue(GetTime());
Start();
}
private static string GetTime()
{
return DateTime.Now.ToString("HH:mm:ss.fff");
}
protected override void ServerTerminate()
{
timer.Dispose();
timer = null;
}
protected override object ConnectData(Topic topic, IList<string> topicInfo, ref bool newValues)
{
topics.Add(topic);
Start();
return GetTime();
}
protected override void DisconnectData(Topic topic)
{
topics.Remove(topic);
if (topics.Count == 0)
Stop();
}
}
This is all well and good but to use this we need to use the =RTD syntax in Excel and to do this we’ll need to add a Guid to the class and mark the project, via the project properties (build tab), as Register for COM interop and then register the component.
It’s far simpler and less messy to now create a new class, let’s call it TimeFunctions as follows
public static class Timefunctions
{
public static object GetCurrentTime()
{
return XlCall.RTD("RtdFunctions.TimeServer", null, "NOW");
}
}
This is now exposed to Excel and will use XlCall.RTD to call into our RTD server so bypassing any issues with COM etc.