65.9K
CodeProject 正在变化。 阅读更多。
Home

创建 C# DCOM 简单服务器，使用 Excel RTD 函数访问 Excel 表格的实时数据

junyaoxu
starIconstarIconstarIconstarIconstarIcon

5.00/5 (2投票s)

2022年10月2日

CPOL

3分钟阅读

viewsIcon

12307

downloadIcon

447

使用 Yahoo 金融 API 分步实现 DCOM 实时股票行情服务器

引言

为了将实时数据提取到 Excel 电子表格中，VBA 存在一些缺点：例如连接到 Web 服务；将 JSON 字符串转换为集合、字典和变体；自动刷新设置等。

使用带有 C# 的 DCOM 并利用 Excel RTD 函数可提供以下好处

  1. 使 .NET 环境中的广泛编程特性和功能可用于处理超出 VBA 限制的数据。
  2. 允许 Excel 使用其自身的计算引擎来处理数据呈现和刷新。
  3. 将 VBA 代码与 Web 访问分离。

创建 DCOM StockService 的分步指南

  1. 启动 Visual Studio，创建 C# 类库 (.NET Framework) 项目，将默认项目名称更改为 StockService 或您喜欢的任何名称。在项目“属性/生成”选项卡中，选中“为 COM 互操作注册”。您需要以管理员身份运行 Visual Studio 才能编译项目，因为它会注册 COM 对象。
  2. 将默认类 *class1.cs* 更改为 *QuoteServer.cs*（或任何名称）。
  3. 添加 COM 引用 Microsoft.Excel16.0 对象库（或桌面上的任何其他可用版本）。
  4. 在类名 QuoteServer 后添加 IRtServer，自动生成以下代码
    using Microsoft.Office.Interop.Excel;

namespace StockService
{
  Guid("93CF58DC-9381-4DA6-82D0-D7958C80045B"), // you need to generate 
                                                // your own GUID
  ProgId("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 Excel
     private Dictionary<int, Topic> myTopic;    // data element to store the 
                                                // information excel passed in s
     private DataSource myData;                 // data class we will create 
                                                // to store stock 
     public QuoteServer()
     {
       myTopics = new Dictionary<int, Topic>(); //hold inputs from excel side
       myData = new DataSource();               //hold quote records 
       myData.DataUpdated += MyDataUpdated;     //subscribe event from datasource
     }
  6. 按如下方式创建 Topic 类
     internal class Topic
    {
        public int TopicId { get; set; }         //the value passed from Excel
        public string Symbol { get; set; }       //the value passed from Excel
        public string TopicType { get; set; }    //the value passed from Excel
    }
  7. 按如下方式创建 DataSource 类并构造
     internal class DataSource {
        private static Dictionary<string, Quote> myQuotes = 
                new Dictionary<string, Quote>();       // to store stock infomation
        public event EventHandler<object> DataUpdated; // event handler to notify 
                                                       // QuoteServer
        private Timer myTimer;
        public DataSource()
        {   myTimer = new Timer();                     // time to auto refresh
            myTimer.Interval = 30 * 1000;              // initial value set 
                                                       // to 30 seconds
            myTimer.Elapsed += MyTimerElapsed;         // set Timer process
            myTimer.Start();                           // start timer
        }}
  8. 创建 Quote 类，如下所示
     internal class Quote
    {       
        [DataMap("Price")]                             //Will discuss 
                                                       //[DataMapAttribute later
        public 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.....
    }

    在此类中，属性名称需要与 Yahoo 金融 API 返回的 JSON 节点名称匹配。您不需要定义该 API 上的所有属性。我使用 NewtonSoft.Json 库将 JSON 字符串反序列化为 Quote 类。

  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. 然后，让我们设置 QuoteServer 的六个方法，这些方法实现 Excel IRtdServer 接口。
    //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 end
  string 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;
}
//3
 public 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 count
    return 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 Excel 
       rtdCallback.UpdateNotify(); //here, notify Excel we have data updated 
  }
  11. 在 DataSource 类上。我们实现一个 YFConnect 类，通过 Yahoo Finance API 获取股票报价
     internal class YFConnect
 {
  const string YahooUrl = 
        "https://query1.finance.yahoo.com/v7/finance/quote?symbols=";
  internal async System.Threading.Tasks.Task<List<Quote>> 
           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<JProperty>()
         .Where(p => p.Name == "result")
         .First()
         .Value;
       var results  = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Quote>>
                      (result.ToString());
          return results;
     }
      catch (Exception ex)
     {
      Console.WriteLine(ex.ToString());
    }
    }
  }
  return null;
 }
}

    在这里，我使用 Newtonsoft.Json 库从 Yahoo Finance API 反序列化 JSON string。该库包含在附加的 zip 文件中。 如果存在版本问题，您可能需要 Nuget。

  12. 在 DataSource 类中，我们设置了两种情况来调用这个类。 一种情况是在 Excel 通过 ConnectData 方法初始化查询时。 另一种是在 Timer Elapsed 事件中，DataSource 以 timer 对象定义的间隔通过 Yahoo API 获取数据。 然后 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<List<Quote>>(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<T>(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 Variant
    If v = Empty Then Exit Function
    Dim arr() As String
    arr = Split(v, "|")
    If i > UBound(arr) Then Exit Function
    ParseText = arr(i)
End Function

关注点

微软已经很久没有更新 VBA 了。 对于 C/C++ 或 C# 世界的程序员来说，VBA 编程并不有趣。 借助 RTD 功能，其他语言的许多潜力和功能可以在 Excel 和 VBA 中实现。

历史

  • 2022 年 10 月 2 日：初始版本
  • 2022 年 10 月 4 日：更新了源代码 zip 文件
© . All rights reserved.