.netwpfcomcom-interoprtd

Excel RTD server in a WPF .NET 8 application

Excel RTD is a COM based technology and all examples I found have the following characteristics:

I have a WPF .NET 8 64bit application and have been required to implement a RTD server into it, but I am not sure it is possible.

I have followed the examples implementing a class (code is below). Being an out of process COM Server and as excel needs to connect to a running instance and not start a new instance of the server, I also used the ROT registration.

The closest I got is to excel to actually see the registration, but It tries to start a new instance of the server (launching a new process from the registered exe).

And in any case, after the instance has started, excel freezes for a while (20, 30s?) maybe trying to connect and finally gives an error and shows #N/A in the cell.

Here my questions:

  1. Does anyone know if what I am trying to do is possible?
  2. If yes, is there any example or whatever resource I can refer to?
  3. If not, what is and alternative method to achieve similar results in the context of my scenario (WPF, 64 bits, .NET 8)
[Guid(ClsId)]
[ProgId(ProgId)]
[ComVisible(true)]
public class RTDServer : IRtdServer
{
    const string ClsId = "575A6DCF-5EDE-419C-9AFF-AFD0745D711C";
    const string ProgId = "Catamount.Pounce.RtdServer";

    ~RTDServer()
    {
        UnregisterFromROT();
    }

    public static void Start()
    {
        Register();
        RTDServer rtdServer = new RTDServer();
        rtdServer.RegisterInROT();

        AppDomain.CurrentDomain.ProcessExit += (s, e) => UnregisterFromROT();
        AppDomain.CurrentDomain.DomainUnload += (s, e) => UnregisterFromROT();

    }

    static IRunningObjectTable? _rot;
    static IMoniker? _moniker;
    static int _rotCookie;

    public void RegisterInROT()
    {
        GetRunningObjectTable(0, out IRunningObjectTable rot);
        _rot = rot;

        CreateItemMoniker("!", ProgId, out IMoniker moniker);
        _moniker = moniker;

        _rotCookie = _rot.Register(0, Marshal.GetIUnknownForObject(this), _moniker);
    }

    public static void UnregisterFromROT()
    {
        if (_rot != null && _rotCookie != 0)
        {
            _rot.Revoke(_rotCookie);
            _rot = null;
            _moniker = null;
            _rotCookie = 0;
        }
    }

    [DllImport("ole32.dll")]
    static extern int GetRunningObjectTable(int reserved, out IRunningObjectTable pprot);

    [DllImport("ole32.dll")]
    static extern int CreateItemMoniker(string lpszDelim, string lpszItem, out IMoniker ppmk);

    public static void Register()
    {
        try
        {
            string exePath = Process.GetCurrentProcess().MainModule?.FileName ?? throw new InvalidOperationException("Unable to determine the executable path.");

            using (RegistryKey? key = Registry.CurrentUser.OpenSubKey($@"Software\Classes\CLSID\{{{ClsId}}}")) if (key != null) return;

            using (RegistryKey clsidKey = Registry.CurrentUser.CreateSubKey($@"Software\Classes\CLSID\{{{ClsId}}}"))
            {
                clsidKey.SetValue(null, ProgId);
                using (RegistryKey localServer32Key = clsidKey.CreateSubKey("LocalServer32"))
                {
                    localServer32Key.SetValue(null, exePath);
                }
            }

            using (RegistryKey progIdKey = Registry.CurrentUser.CreateSubKey($@"Software\Classes\{ProgId}"))
            {
                progIdKey.SetValue(null, ProgId);
                using (RegistryKey clsidSubKey = progIdKey.CreateSubKey("CLSID"))
                {
                    clsidSubKey.SetValue(null, $"{{{ClsId}}}");
                }
            }
        }
        catch (Exception ex)
        {
            Debug.WriteLine($"Error registering COM server: {ex.Message}");
            throw;
        }
    }

    IRTDUpdateEvent? _callback;
    Dictionary<int, string> _topics = new Dictionary<int, string>();

    public int ServerStart(IRTDUpdateEvent callback)
    {
        _callback = callback;
        return 1;
    }

    public void ServerTerminate()
    {
        _topics.Clear();
    }

    public object ConnectData(int topicId, ref Array strings, ref bool newValues)
    {
        _topics[topicId] = strings.GetValue(0)?.ToString() ?? string.Empty;
        return "Initial Data";
    }

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

    public Array RefreshData(ref int topicCount)
    {
        object[,] data = new object[2, _topics.Count];
        int index = 0;
        foreach (var topic in _topics)
        {
            data[0, index] = topic.Key;
            data[1, index] = "Updated Data";
            index++;
        }
        topicCount = _topics.Count;
        return data;
    }

    public int Heartbeat()
    {
        return 1;
    }
}

Further info (found after solution)

For those interested, I have found these:

https://asp-blogs.azurewebsites.net/kennykerr/excel-rtd-server-in-c

https://asp-blogs.azurewebsites.net/kennykerr/ExcelRTDServerinCS

https://asp-blogs.azurewebsites.net/kennykerr/Rtd3

https://asp-blogs.azurewebsites.net/kennykerr/Rtd4

https://asp-blogs.azurewebsites.net/kennykerr/Rtd5

https://asp-blogs.azurewebsites.net/kennykerr/Rtd6

https://asp-blogs.azurewebsites.net/kennykerr/Rtd7

https://asp-blogs.azurewebsites.net/kennykerr/Rtd8

https://asp-blogs.azurewebsites.net/kennykerr/Rtd9

I highlight here the excellent solution by Simon Mourier, which not only implements a working Rtd server, but also shows how to serve it from a running process instead of the typical in-process DLL https://github.com/smourier/ExcelOutOfProcessRdtServer

Solution

  • The running object table is not what Excel needs to connect. It needs a "regular" COM object registered as such in the registry to be able to communicate with it, and yes, this COM object can be served by an independent .exe.

    There's nothing particularly complex, you can use Microsoft's official out-of-process .NET core sample available here https://github.com/dotnet/samples/tree/main/core/extensions/OutOfProcCOM

    but I've written a small sample (with .NET8's WPF) that demonstrates all this for Excel specifically here https://github.com/smourier/ExcelOutOfProcessRdtServer

    ExcelOutOfProcessRdtServer