1,Excel-DNA 第一种方法,简单
2,自定义添加,VIsual studio 需要管理员权限启动。
写Server代码
1) 新建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) 右键项目->选择属性->新添加项目->选择安装类(Installer Class).F7切换到代码编辑器加入如下代码:
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)... >勾选最下面的COM可以参照
进入编译(build)选项卡->选择Release模式->最下面输出的地方勾选COM登录 ( Register for COM Interop)
4) 添加Nuget包: Microsoft.Office.Interop.Excel
5) Release 模式编译测序
6) 打卡Excel,选择文件->选项 > Add-Ins > Excel Add-Ins > Automation and select "StackOverflow.RtdServer"
7) 在Cell里面输入 "=RTD("StackOverflow.RtdServer.ProgId",,200)" .
每天成就一小步,积累下来就是一大步。
转发本文请注明出处,谢谢您的阅读与分享!