如何创建一个实时的Excel自动加载使用RtdServer C＃？ [英] How do I create a real-time Excel automation add-in in C# using RtdServer?
问题描述
我在写一个实时任务Excel自动化外接使用RtdServer工作C＃。我非常依赖我在堆栈溢出碰到的知识。我已经写了一个如何记录的联系在了一起一切，我已经学会决定前preSS我的谢意。肯尼 - 科尔的 Excel的RTD服务器：最小的C＃实现文章帮我上手。我发现通过
I was tasked with writing a real-time Excel automation add-in in C# using RtdServer for work. I relied heavily on the knowledge that I came across in Stack Overflow. I have decide to express my thanks by writing up a how to document that ties together all that I have learned. Kenny Kerr's Excel RTD Servers: Minimal C# Implementation article helped me get started. I found comments by Mike Rosenblum and Govert especially helpful.
推荐答案
（作为一种替代下面你所描述的方法应该考虑使用 Excel的DNA 。Excel的DNA，您可以建立一个免注册RTD服务器。COM注册需要管理员权限，这可能导致安装头痛。话虽这么说，下面的code正常工作。）
(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.)
要创建一个实时的Excel自动化使用RtdServer附加在C＃中：
To create a real-time Excel automation add-in in C# using RtdServer:
1）在Visual Studio中的C＃类库项目，并输入以下内容：
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）右键单击该项目，并添加>新项...>安装程序类。切换到code查看，并输入以下内容：
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）右键单击该项目属性，并检查了以下内容：应用程序>大会资料...>进行组装COM可见和建造>注册为COM Interop
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），右键单击该项目添加引用...> .NET标签>的Microsoft.Office.Interop.Excel
3.1) Right click on the project Add Reference... > .NET tab > Microsoft.Office.Interop.Excel
4）构建解决方案（F6）
4) Build Solution (F6)
5）运行Excel。转到Excel选项>插件>管理Excel加载项>自动化并选择StackOverflow.RtdServer
5) Run Excel. Go to Excel Options > Add-Ins > Manage Excel Add-Ins > Automation and select "StackOverflow.RtdServer"
6）输入= RTD（StackOverflow.RtdServer.ProgId,, 200）到细胞中。
6) Enter "=RTD("StackOverflow.RtdServer.ProgId",,200)" into a cell.
7）交叉手指，希望它的作品！
7) Cross your fingers and hope that it works!
这篇关于如何创建一个实时的Excel自动加载使用RtdServer C＃？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
