从Excel 2002开始,微软提供了一种叫做Excel RTD(real-time data)的技术,使用该技术可以在Excel中实时查看和更新数据。RTD采用所谓的“推-拉”相结合的技术,使得其在实时获取和更新不断变化的数据(例如股票、汇率、天气)的性能方面,相比较之前的DDE更加稳健和快速。在MSDN的Real-Time Data FAQ上有其性能描述(http://msdn.microsoft.com/en-us/library/aa140060(v=office.10).aspx#odc_xlrtdfaq_whatisrtd),据说在一台配置为 Pentium III 500 MHz 的CPU和 128 MB 内存的电脑上,RTD可以在一秒内对20000个独立的主题(topic)更新三次,对于单独一个主题的更新频率可达1秒200次。
在Excel 中使用RTD非常简单,Excel 提供了一个新的工作表函数 RTD,此函数允许通过调用组件对象模型 (COM) 自动化服务器来实现实时数据检索。RTD 工作表函数使用以下语法:
"=RTD(ProgID, Server, String 1, String 2, ... String n)"
第一个变量 ProgID 表示Real-Time Data 服务器(RTD Server)的编程标识符 (ProgID)。Server 变量指示运行RTD Server的计算机的名称;如果RTD Server在本地运行,则可以将此变量设置为空字符串或将其忽略。其他变量只表示发送到RTD Server的参数;这些参数的每个唯一组合都表示一个“主题”(topic),每个“主题”有一个关联的“主题 ID”(topic id)。这些参数区分大小写。例如,以下内容演示将生成三个不同主题ID的RTD Server调用:
=RTD("ExcelRTD.RTDFunctions",,"AAA", "10")
=RTD("ExcelRTD.RTDFunctions",,"AAA", "5")
=RTD("ExcelRTD.RTDFunctions",,"aaa", "5")
要使用 Excel 的 RTD 函数,必须注册一个实现 IRTDServer 接口的COM组件。实现这个接口的COM组件就是所谓的RTD Server。IRTDServer具有以下成员:
ServerStart(CallbackObject)
CallbackObject 是一个IRTDUpdateEvent类型的参数,它有一个UpdateNotify方法,用于通知Excel有更新的数据可用(push)。这样Excel就会通过调用RefreshData方法来刷新所有的主题(pull)。当 Excel 请求RTD Server的第一个 RTD 主题时调用ServerStart方法,该方法会在成功时返回 1,并在失败时返回负值或 0。这个方法在随后应用其他RTD函数时不会再次被调用。
ConnectData(TopicID, Strings, GetNewValues)
其中,TopcID 唯一标识这个函数在Excel中的一个应用,即使复制多份到不同的单元格,对于Excel来讲,也只是对应一个主题。这个 topicID 由Excel返回,我们需要将其记录下来,以便为其提供更新的数据。Strings 是一个System.Array,用于接收RTD函数传入的参数(String 1...String n),这是一个引用类型的参数。GetNewValues 用于确定是否总是获取最新数据,如果这个参数传入true,则每次保存Excel文档以后,再次重新打开时,看到的不一定是上次保存时的数据,而是最新的实时数据,这也是一个引用类型的参数。
每当一个新的主题(Topic)被应用到Excel,ConnectData都会被调用。在这里,需要保存传入的新的TopicID和查询参数以供之后更新数据使用。为此,需要定义好自己的数据结构。
DisconnectData(TopicID)
与ConnectData一样,TopcID 唯一标识这个函数在Excel中的一个应用。当我们从Excel中移除一个主题(删除所有采用相同参数的RTD函数)之后,DisconnectData将被调用,在这里,可以释放对这个主题的监控,并不再为其获取新数据。
Heartbeat
确定RTD Server是不是依然可用,0和负数代表不可用,1代表可用。Excel会调用此方法确定服务是否断连。
RefreshData(TopicCount)
TopicCount表示要更新的主题数量,这是一个引用类型的参数,用于返回给Excel。我们可以定义一个时钟,用于定时向数据源获取数据,这样,在时钟的Elapsed事件中,获取最新数据,并调用xlRTDUpdate成员的UpdateNotify方法以通知Excel,新的数据准备完毕。这样Excel就会调用RefreshData方法,来对工作簿中的数据进行更新。
ServerTerminate
当Excel不再需要从RTD Server获取实时数据时被调用。在这里,可以执行一些清理,例如清除缓存,关闭时钟等等。至此,一个RTD Server的生命周期就结束了。
演练
创建一个项目ExcelRTD,添加Microsoft.Office.Interop.Excel引用。创建一个类MarketData.cs,这个类继承IRtdServer接口,以实现一个RTD Server。新建另一个类DataCollector.cs用于从数据源获取数据。DataCollector有一个方法public DataTable GetMarketData(string keyField, object keyValue)将会在MarketData.cs中被使用。为了图简便,我就贴出MarketData.cs的全部代码。
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
Code
1
using System;
2
using System.Collections.Generic;
3
using System.Linq;
4
using System.Text;
5![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
6
using System.Collections;
7
using System.Configuration;
8
using System.Timers;
9
using System.Runtime.InteropServices;
10
using Microsoft.Office.Interop.Excel;
11![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
12
namespace ExcelRTD
13![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
{
14
[ComVisible(true),ProgId("ExcelRTD.MarketData")]
15
public class MarketDataServer : IRtdServer
16![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
17
private IRTDUpdateEvent xlRTDUpdate;
18
private Timer tmrTimer;
19
private List<MarketData> marketDatas;
20![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
21
//Lets Excel know that our RTD server is still "alive".
22
public int Heartbeat()
23![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
24
return 1;
25
}
26
27
//Initialize RTD server.
28
public int ServerStart(Microsoft.Office.Interop.Excel.IRTDUpdateEvent CallbackObject)
29![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
30
//Hold a reference to the callback object.
31
xlRTDUpdate = CallbackObject;
32![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
33
//2000 millisecond by default.
34
int intv = 2000;
35![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
36
//'Create the time with a 5000 millisecond interval.
37
tmrTimer = new Timer(intv);
38
tmrTimer.AutoReset = true;
39![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
40
tmrTimer.Elapsed += new ElapsedEventHandler(tmrTimer_Elapsed);
41![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
42
return 1;
43
}
44![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
45
//Terminate RTD server.
46
public void ServerTerminate()
47![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
48
//Clear the RTDUpdateEvent reference.
49
xlRTDUpdate = null;
50![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
51
//Make sure the timer is stopped.
52
if (tmrTimer.Enabled)
53![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
54
tmrTimer.Stop();
55
}
56![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
57
tmrTimer.Elapsed -= new ElapsedEventHandler(tmrTimer_Elapsed);
58
tmrTimer.Dispose();
59
}
60![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
61![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
62
public object ConnectData(int TopicID, ref System.Array Strings, ref bool GetNewValues)
63![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
64
//Retrive new values from RTD server when connected.
65
GetNewValues = true;
66![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
67
//Get field name from Excel.
68
string strFieldName = Strings.GetValue(0).ToString().ToLower();
69
string strKeyValue = Strings.GetValue(1).ToString();
70![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
71
try
72![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
73
if (marketDatas == null)
74![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
75
marketDatas = new List<MarketData>();
76
}
77![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
78
//Get real-time data from data source.
79
DataCollector dc = new DataCollector();
80
System.Data.DataTable dt = null;
81![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
82
MarketData temp = new MarketData();
83![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
84
temp.KeyField = "stockcode";
85
temp.KeyValue = strKeyValue;
86
temp.FieldName = strFieldName;
87![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
88
dt = dc.GetMarketData(temp.KeyField, temp.KeyValue);
89![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
90
if (dt != null && dt.Rows.Count > 0)
91![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
92
temp.FieldValue = dt.Rows[0][temp.FieldName];
93![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
94
marketDatas.Add(temp);
95
}
96![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
97
}
98
catch
99![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
100
return "ERROR IN QUOTE.";
101
}
102![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
103
//Make sure that the timer is started.
104
if (!tmrTimer.Enabled)
105![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
106
tmrTimer.Start();
107
}
108![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
109
for (int i = 0; i < marketDatas.Count; i++)
110![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
111
//Match the topic value
112
if (marketDatas[i].FieldName.Equals(strFieldName, StringComparison.OrdinalIgnoreCase) && marketDatas[i].KeyValue.ToString().Equals(strKeyValue, StringComparison.OrdinalIgnoreCase))
113![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
114
if (marketDatas[i].TopicID == -1)
115![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
116
marketDatas[i].TopicID = TopicID;
117
}
118![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
119
return marketDatas[i].FieldValue;
120
}
121
}
122![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
123
return "Unrecognized value requested";
124
}
125![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
126
public void DisconnectData(int TopicID)
127![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
128
for (int i = marketDatas.Count - 1; i > 0; i--)
129![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
130
if (marketDatas[i].TopicID == TopicID)
131![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
132
marketDatas.RemoveAt(i);
133
}
134
}
135![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
136
if ((marketDatas == null || marketDatas.Count == 0) && tmrTimer.Enabled)
137![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
138
tmrTimer.Stop();
139
}
140
}
141![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
142![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
143
public void tmrTimer_Elapsed(object sender, ElapsedEventArgs e)
144![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
145
try
146![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
147
for (int i = 0; i < marketDatas.Count; i++)
148![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
149
DataCollector dc = new DataCollector();
150
System.Data.DataTable dt = dc.GetMarketData(marketDatas[i].KeyField, marketDatas[i].KeyValue);
151![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
152
if (dt != null && dt.Rows.Count > 0)
153![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
154
marketDatas[i].FieldValue = dt.Rows[0][marketDatas[i].FieldName];
155
}
156
}
157![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
158
//Tell Excel that we have updates.
159
xlRTDUpdate.UpdateNotify();
160
}
161
catch(Exception ex)
162![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
163
string x = ex.ToString();
164
}
165
}
166![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
167
//Pull new values from the real-time data server to Excel.
168
public System.Array RefreshData(ref int TopicCount)
169![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
170
object[,] rets = new object[2, marketDatas.Count];
171
int counter = 0, sum = 0;
172![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
173
foreach (MarketData data in marketDatas)
174![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
175
if (data.TopicID != -1)
176![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
177
rets[0, counter] = data.TopicID;
178
rets[1, counter] = data.FieldValue;
179![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
180
sum++;
181
}
182![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
183
counter++;
184
}
185![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
186
TopicCount = marketDatas.Count;
187![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
188
return rets;
189
}
190![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
191![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
192
private class MarketData
193![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
194
private string fieldName;
195
private object fieldValue;
196
private string keyField;
197
private object keyValue;
198
private int topicID;
199![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
200
public MarketData()
201![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
202
topicID = -1;
203![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
204
keyValue = null;
205
fieldValue = null;
206
}
207![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
208
public string FieldName
209![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
210
get
211![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
212
return fieldName;
213
}
214
set
215![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
216
fieldName = value;
217
}
218
}
219![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
220
public object FieldValue
221![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
222
get
223![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
224
return fieldValue;
225
}
226
set
227![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
228
fieldValue = value;
229
}
230
}
231![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
232
public string KeyField
233![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
234
get
235![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
236
return keyField;
237
}
238
set
239![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
240
keyField = value;
241
}
242
}
243![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
244
public object KeyValue
245![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
246
get
247![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
248
return keyValue;
249
}
250
set
251![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
252
keyValue = value;
253
}
254
}
255![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
256
public int TopicID
257![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
258
get
259![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
260
return topicID;
261
}
262
set
263![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
264
topicID = value;
265
}
266
}
267
}
268
}
269
}
270![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
使用时,需要注册COM。这只是一个简单的示例。
Step by step可以参见以下链接:
http://msdn.microsoft.com/en-us/library/aa140061(office.10).aspx
http://support.microsoft.com/kb/285339/zh-cn
MSDN上的文章虽然是针对Excel 2002,但是2003和2007同样支持。此外,从外部数据源获取Excel的方法有很多种,这只是其中一种而已,它并不能适应所有的情况。