How to Create a Real-Time Excel Automation Add-In in C# Using Rtdserver

How do I create a real-time Excel automation add-in in C# using RtdServer?

(As an alternative to the approach described below you should consider using Excel-DNA. Excel-DNA allows you to build a registration-free RTD server. COM registration requires administrative privileges which may lead to installation headaches. That being said, the code below works fine.)

To create a real-time Excel automation add-in in C# using RtdServer:

1) Create a C# class library project in Visual Studio and enter the following:

using System;
using System.Threading;
using System.Collections.Generic;
using System.Runtime.InteropServices;
using Microsoft.Office.Interop.Excel;

namespace StackOverflow
{
    public class Countdown
    {
        public int CurrentValue { get; set; }
    }

    [Guid("EBD9B4A9-3E17-45F0-A1C9-E134043923D3")]
    [ProgId("StackOverflow.RtdServer.ProgId")]
    public class RtdServer : IRtdServer
    {
        private readonly Dictionary<int, Countdown> _topics = new Dictionary<int, Countdown>();
        private Timer _timer;

        public int ServerStart(IRTDUpdateEvent rtdUpdateEvent)
        {
            _timer = new Timer(delegate { rtdUpdateEvent.UpdateNotify(); }, null, TimeSpan.Zero, TimeSpan.FromSeconds(5));
            return 1;
        }

        public object ConnectData(int topicId, ref Array strings, ref bool getNewValues)
        {
            var start = Convert.ToInt32(strings.GetValue(0).ToString());
            getNewValues = true;

            _topics[topicId] = new Countdown { CurrentValue = start };

            return start;
        }

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

            foreach (var entry in _topics)
            {
                --entry.Value.CurrentValue;
                data[0, index] = entry.Key;
                data[1, index] = entry.Value.CurrentValue;
                ++index;
            }

            topicCount = _topics.Count;

            return data;
        }

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

        public int Heartbeat() { return 1; }

        public void ServerTerminate() { _timer.Dispose(); }

        [ComRegisterFunctionAttribute]
        public static void RegisterFunction(Type t)
        {
            Microsoft.Win32.Registry.ClassesRoot.CreateSubKey(@"CLSID\{" + t.GUID.ToString().ToUpper() + @"}\Programmable");
            var key = Microsoft.Win32.Registry.ClassesRoot.OpenSubKey(@"CLSID\{" + t.GUID.ToString().ToUpper() + @"}\InprocServer32", true);
            if (key != null)
                key.SetValue("", System.Environment.SystemDirectory + @"\mscoree.dll", Microsoft.Win32.RegistryValueKind.String);
        }

        [ComUnregisterFunctionAttribute]
        public static void UnregisterFunction(Type t)
        {
            Microsoft.Win32.Registry.ClassesRoot.DeleteSubKey(@"CLSID\{" + t.GUID.ToString().ToUpper() + @"}\Programmable");
        }
    }
}

2) Right click on the project and Add > New Item... > Installer Class. Switch to code view and enter the following:

using System.Collections;
using System.ComponentModel;
using System.Diagnostics;
using System.Runtime.InteropServices;

namespace StackOverflow
{
    [RunInstaller(true)]
    public partial class RtdServerInstaller : System.Configuration.Install.Installer
    {
        public RtdServerInstaller()
        {
            InitializeComponent();
        }

        [System.Security.Permissions.SecurityPermission(System.Security.Permissions.SecurityAction.Demand)]
        public override void Commit(IDictionary savedState)
        {
            base.Commit(savedState);

            var registrationServices = new RegistrationServices();
            if (registrationServices.RegisterAssembly(GetType().Assembly, AssemblyRegistrationFlags.SetCodeBase))
                Trace.TraceInformation("Types registered successfully");
            else
                Trace.TraceError("Unable to register types");
        }

        [System.Security.Permissions.SecurityPermission(System.Security.Permissions.SecurityAction.Demand)]
        public override void Install(IDictionary stateSaver)
        {
            base.Install(stateSaver);

            var registrationServices = new RegistrationServices();
            if (registrationServices.RegisterAssembly(GetType().Assembly, AssemblyRegistrationFlags.SetCodeBase))
                Trace.TraceInformation("Types registered successfully");
            else
                Trace.TraceError("Unable to register types");
        }

        public override void Uninstall(IDictionary savedState)
        {
            var registrationServices = new RegistrationServices();
            if (registrationServices.UnregisterAssembly(GetType().Assembly))
                Trace.TraceInformation("Types unregistered successfully");
            else
                Trace.TraceError("Unable to unregister types");

            base.Uninstall(savedState);
        }
    }
}

3) Right click on the project Properties and check off the following: Application > Assembly Information... > Make assembly COM-Visible and Build > Register for COM Interop

3.1) Right click on the project Add Reference... > .NET tab > Microsoft.Office.Interop.Excel

4) Build Solution (F6)

5) Run Excel. Go to Excel Options > Add-Ins > Manage Excel Add-Ins > Automation and select "StackOverflow.RtdServer"

6) Enter "=RTD("StackOverflow.RtdServer.ProgId",,200)" into a cell.

7) Cross your fingers and hope that it works!

How do I create an Excel automation add-in in C# that wraps an RTD function?

Indeed, you are not getting hold of the right Application object. One solution is to implement the IDTExtensibility2 interface in your add-in. This interface has an OnConnection method that Excel will call when loading your add-in. In this method you are passed the Application object which you can keep in a local variable for later use.

Excel Automation Addin function returing excel range

Since you don't show any source code it is hard to tell what's wrong - for returning Excel Range use object[,] as the return type of your UDF.

How to register UDF/RTD within VSTO project

This can be accomplished using an installer class that runs RegAsm.exe during installation. See:
How to register a .NET CCW with regasm from a Visual Studio 2008 Setup project

Hope this helps,
Frank

Implement an Excel UDF that can call web methods asynchronously

RTD would work. You have to implement the IRtdServer interface. When your add-in is started, Excel gives you a function pointer. For each cell you will be given an "excel id" and a list of arguments. Send your async web request. When the response arrives, you call the notfiy function that Excel gave you. When Excel is ready it will call your GetData method to actually get the data.

See How do I create a real-time Excel automation add-in in C# using RtdServer? for an example of how to do this in C#.

Once you understand how RTD works, Excel-DNA does a nice job of abstracting away the COM plumbing...highly recommended.

---

Related Topics