c# - 如何使用 RtdServer 在 C# 中创建实时 Excel 自动化插件？

Question

我的任务是使用 RtdServer 以 C# 编写实时 Excel 自动化插件。我非常依赖在 Stack Overflow 中获得的知识。我决定通过编写一个将我所学的所有内容联系在一起的文档来表达我的感谢。肯尼克尔的 Excel RTD Servers: Minimal C# Implementation文章帮助我入门。我找到了 Mike Rosenblum 的评论和 Govert特别有帮助。

Answer 1

(作为下述方法的替代方案，您应该考虑使用 Excel-DNA。Excel-DNA 允许您构建免注册 RTD 服务器。COM 注册需要管理权限，这可能会导致安装问题。话虽这么说，下面的代码工作正常。)

使用 RtdServer 在 C# 中创建实时 Excel 自动化插件:

1) 在Visual Studio中创建一个C#类库项目，输入以下内容:

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) 右键单击​​项目并添加 > 新项目... > 安装程序类。切换到代码 View 并输入以下内容:

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) 右键单击​​项目属性并勾选以下内容:Application > Assembly Information... > Make assembly COM-Visible and Build > Register for COM Interop

3.1) 右键单击​​项目添加引用... > .NET 选项卡 > Microsoft.Office.Interop.Excel

4) 构建解决方案 (F6)

5) 运行 Excel。转到 Excel 选项 > 加载项 > 管理 Excel 加载项 > 自动化并选择“StackOverflow.RtdServer”

6) 在单元格中输入“=RTD("StackOverflow.RtdServer.ProgId",,200)”。

7) 祈祷它能成功!