创建C# DCOM简单服务器以使用Excel RTD函数访问Excel电子表格的实时数据
目录
介绍
逐步创建DCOM股票服务
兴趣点
- 下载源代码 - 278.9 KB
介绍
为了将实时数据提取到Excel电子表格中，VBA有一些缺点：例如连接到Web服务;将JSON字符串转换为集合、字典和变体;自动刷新设置等
将DCOM与C#结合使用并利用Excel RTD函数具有以下优点：
- 使.NET环境中的广泛编程特性和功能可用于处理超出VBA限制的数据。
- 允许Excel自己的计算引擎来处理数据呈现和刷新。
- 从Web访问中分离VBA代码。
逐步创建DCOM股票服务
- 启动Visual Studio，创建C#类库（.NET Framework）项目，将默认项目名称更改为StockService或您喜欢的任何名称。在“项目属性/生成”选项卡中，选中“注册COM互操作”。您以管理员身份运行Visual Studio以编译项目，因为它注册了COM对象。
- 将默认类 class1.cs 更改为 QuoteServer.cs（或任何名称）。
- 添加COM Microsoft.Excel16.0引用对象库（或桌面上提供的任何其他版本）。
- 类名QuoteServer添加IRtServer后，自动生成以下代码：
using Microsoft.Office.Interop.Excel;namespace StockService
{Guid("93CF58DC-9381-4DA6-82D0-D7958C80045B"), // you need to generate // your own GUIDProgId("Stock.QuoteServer"), // and add these three lines // before class definition ComVisible(true)
]class QuoteServer:IRtdServer{public int ServerStart(IRTDUpdateEvent CallbackObject){}public dynamic ConnectData(int TopicID, ref Array Strings, ref bool GetNewValues){}public Array RefreshData(ref int TopicCount){}public void DisconnectData(int TopicID){}public int Heartbeat(){}public void ServerTerminate(){}}
}剩下的工作是实现这六种方法。
5、在该QuoteServer:IRtServer行下，首先将数据元素放入类中，并按如下方式设置类构造函数：
class QuoteServer:IRtdServer
{private IRTDUpdateEvent rtdCallback; // to hold object passed from Excelprivate Dictionary myTopic; // data element to store the// information excel passed in sprivate DataSource myData; // data class we will create// to store stockpublic QuoteServer(){myTopics = new Dictionary(); //hold inputs from excel sidemyData = new DataSource(); //hold quote recordsmyData.DataUpdated += MyDataUpdated; //subscribe event from datasource} 6、按如下方式创建类Topic：
internal class Topic{public int TopicId { get; set; } //the value passed from Excelpublic string Symbol { get; set; } //the value passed from Excelpublic string TopicType { get; set; } //the value passed from Excel}7、创建类DataSource并构造如下：
internal class DataSource {private static Dictionary myQuotes =new Dictionary(); // to store stock infomationpublic event EventHandler 8、按如下方式创建类Quote：
internal class Quote{[DataMap("Price")] //Will discuss//[DataMapAttribute laterpublic double regularMarketPrice { get; set; } //property name need to//match Yahoo finance API[DataMap("Change")]public double regularMarketChange { get; set; }[DataMap("Trade Time")]public double regularMarketTime { get; set; }[DataMap("ChangePercent")]public double regularMarketChangePercent { get; set; }// other members omitted here.....}在此类中，属性名称需要与从雅虎财经API返回的JSON节点名称匹配。无需在该API上定义所有属性。我使用NewtonSoft.JsonQuote库将JSON字符串反序列化为类。
9、创建DataMapAttribute自定义属性类以方便处理Quote类数据成员。以下是完整的实现。
internal class DataMapAttribute: System.Attribute {public DataMapAttribute(string name){Name = name;}public string Name { get; set; }public DataMapAttribute(){}public static DataMapAttribute GetAttriubute(PropertyInfo fi){var atr = fi.GetCustomAttributes(typeof(DataMapAttribute), false);if (atr.Length > 0)return atr[0] as DataMapAttribute;return null;} }10、然后，让我们设置实现Excel IRtdServer接口的QuoteServer的六种方法。
//1.
public int ServerStart(IRTDUpdateEvent CallbackObject){rtdCallback = CallbackObject;return 1;}
//2.
public dynamic ConnectData(int TopicID, ref Array Strings, ref bool GetNewValues)
{if (Strings.Length<2)return "Two Parameters required"; // return value to Excel front endstring symbol = Strings.GetValue(0).ToString();string topicType = Strings.GetValue(1).ToString();var topic = new Topic() { TopicId = TopicID, Symbol = symbol, TopicType = topicType };myTopics[TopicID] = topic;var ret = myData.GetQuoteData(symbol, topicType);GetNewValue=true;return ret;
}
//3public Array RefreshData(ref int TopicCount) // call by Excel. // return data 2 rows by multiple column array{object[,] data = new object[2, this.myTopics.Count];int index = 0;foreach (var item in myTopics){data[0, index] = item.Key;data[1, index] = myData.GetQuoteData(item.Value.Symbol, item.Value.TopicType);index++;}TopicCount = myTopics.Count; //update Excel side topic countreturn data;}
//4
public void DisconnectData(int TopicID) // call by Excel happened // when formular changed{var symbol = myTopics[TopicID].Symbol;myTopics.Remove(TopicID);}
//5
public int Heartbeat() // call by Excel every 15 seconds // to keep server // and connection alive
{return 1; // just return 1
}
//6
public void ServerTerminate() // call by Excel when Excel application// exit
{myTopics.Clear(); // clear up local data goes here
}我们在QouteServer类中添加了额外的方法来实现RTDCallback.UpdateNotify方法：
private void MyDataUpdated(object sender, object arg){if (rtdCallback != null) //rtdCallback is passed from ExcelrtdCallback.UpdateNotify(); //here, notify Excel we have data updated}11、在DataSource类上。我们实现了一个YFConnect类来通过雅虎财经API提取股票报价：
internal class YFConnect{const string YahooUrl = "https://query1.finance.yahoo.com/v7/finance/quote?symbols=";internal async System.Threading.Tasks.Task> GetQuoteAsync(string symbol){string url = $"{YahooUrl}{symbol}";using (var client = new HttpClient()){using (HttpResponseMessage response = await client.GetAsync(url)) {try{response.EnsureSuccessStatusCode();string responseBody = await response.Content.ReadAsStringAsync();JObject data = JObject.Parse(responseBody);var result = data.Descendants().OfType().Where(p => p.Name == "result").First().Value;var results = Newtonsoft.Json.JsonConvert.DeserializeObject>(result.ToString());return results;}catch (Exception ex){Console.WriteLine(ex.ToString());}}}return null;}
} 在这里，我使用Newtonsoft.Json库从雅虎财经API反序列化JSON string。该库包含在附加的zip文件中。如果存在版本问题，则可能需要Nuget。
12、在DataSource类上，我们设置了两种情况来调用这个类。一种是Excel通过ConnectData方法初始化查询的情况。另一个是在计时器经过事件，并且DataSource通过Yahoo API以timer对象定义的间隔拉取数据。然后DataSource将引发DataUpdated事件，如下所示：
private void MyTimerElapsed(object sender, ElapsedEventArgs e)
{string symbols = string.Empty;foreach (var quote in myQuotes) //get all symbols from local data {symbols += quote.Key + ","; }var connect = new YFConnect();var data = Task.Run>(async () => await connect.GetQuoteAsync(symbols));var list = data.Result;foreach (var item in list){ myQuotes[item.Symbol] = item; // use new data to update old data}if(this.DataUpdated!=null){DataUpdated(this, "DateUpdated"); // notify data change. // This event is subscribed // by QuoteServer class.}} 13、设置Excel电子表格以提取数据。Excel使用RTD函数作为单元格公式来订阅StockServer。语法如下：
=RTD("Stock.QuoteServer",,"IBM","Price")=RTD("Stock.QuoteServer",,"IBM","Change")在其他计算机上运行服务器时，需要提供该计算机名称作为第二个参数：
=RTD("Stock.QuoteServer","OtherMachineName","IBM","Price") ....为了更高效的过程，我还使用公式器在一次调用中实现订阅所有数据元素，如下所示：
=RTD("Stock.QuoteServer",,"IBM","All")此公式器将获取报价的所有数据元素累积到string，并将其放在单元格中。每个字段由分隔符分隔。在后端，它实现如下：
static internal class utitlity
{public static object ToValue(this T t, string topicType){if (t == null)return null;string allValue = string.Empty;PropertyInfo[] props = typeof(T).GetProperties();foreach (PropertyInfo prop in props){var v1 = DataMapAttribute.GetAttriubute(prop);if ((v1 != null && v1.Name.ToUpper() == topicType.ToUpper()) || prop.Name.ToUpper() == topicType.ToUpper())return prop.GetValue(t);if (v1 != null && topicType.ToUpper() == "ALL"){allValue += prop.GetValue(t) + "|";}}return allValue;}} 在Excel电子表格中，您需要编写一个VBA函数来解析string，如下所示：
Function ParseText(v As String, i As Integer) As VariantIf v = Empty Then Exit FunctionDim arr() As Stringarr = Split(v, "|")If i > UBound(arr) Then Exit FunctionParseText = arr(i)
End Function兴趣点
微软很长一段时间没有更新VBA。对于C/C++或C#领域的程序员来说，VBA编程并不好玩。使用RTD功能，可以在Excel和VBA中实现其他语言的许多潜力和功能。
https://www.codeproject.com/Articles/5343545/Create-Csharp-DCOM-Simple-Server-to-Use-Excel-RTD
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！