Excel-DNA中同一Symbol对应单个Observer的机制及相关实现疑问

Hey there! Let's break down what's happening with your Excel-DNA setup and answer your questions one by one, based on how Excel-DNA's async observation mechanism works.

1. Does each unique Symbol map to exactly one Observer in the Excel workbook? #

Absolutely! This is all down to how ExcelAsyncUtil.Observe works. The second parameter you pass (symbol here) acts as a cache key. When you call GetLtp("X") in the first cell, Excel-DNA creates your LtpObserver instance and stores it against the key "X". When you call the same formula with "X" in another cell, Excel-DNA checks the cache, finds the existing LtpObserver, and reuses it instead of creating a new one. That's why you don't see a new constructor call or Subscribe() trigger for the second cell.

2. Is maintaining an Observer list and updating each one in OnNext() a reasonable approach? #

Yes, this is actually the standard way to handle multiple subscribers to a single observable in Excel-DNA—you're correctly keeping track of all the Excel cells (represented by IExcelObserver instances) that depend on the data for a given symbol. But there are a few tweaks you should make to make this more robust:

• Thread safety: Your _observerList is being modified in Subscribe() (called from Excel's main thread) and _timer_Elapsed (called from a background timer thread), plus the unsubscribe action (also Excel's thread). You need to add a lock around access to this list to avoid race conditions. For example:

  private readonly object _listLock = new object();
  // When adding an observer
  lock(_listLock) _observerList.Add(observer);
  // When removing an observer
  lock(_listLock) _observerList.Remove(observer);
  // When iterating to trigger OnNext
  lock(_listLock) foreach (var obs in _observerList.ToList()) obs.OnNext(_now);
  

  Using ToList() when iterating ensures you don't get an InvalidOperationException if the list changes mid-loop.
• Timer lifecycle: Right now, your timer starts when the first subscriber connects but never stops if all subscribers are removed. You should check if _observerList is empty after unsubscribing, and stop/dispose the timer to save resources:

  return new ActionDisposable(() => {
      Trace.TraceInformation("Un Subscribed for LTP Price :" + _symbol);
      lock(_listLock) {
          _observerList.Remove(observer);
          if (_observerList.Count == 0) {
              _timer.Stop();
              _timer.Dispose(); // Clean up the timer properly
          }
      }
  });
  

• Redundant Data.LtpObserver assignment: Assigning Data.LtpObserver = _observerList in both the constructor and timer event seems unnecessary—you can just use _observerList directly in your timer_tick method.

3. How does the internal mechanism work when a single cell uses 2 or more formulas (e.g., =GetLtp("X")+GetLtp("X"))? #

In this case, Excel treats each GetLtp("X") call as a separate dependency, but since they share the same cache key, they'll both subscribe to the same LtpObserver instance. That means two IExcelObserver entries will be added to your _observerList. When your timer ticks and calls OnNext() on all observers, both dependencies will get updated, and Excel will recalculate the cell value correctly.

Behind the scenes, Excel-DNA manages each cell's subscription independently—even within the same cell, each function call gets its own IExcelObserver. But since the cache key is the same, they reuse the observable. When the cell is deleted or modified, both subscriptions will trigger their unsubscribe actions, removing both observers from the list.

Quick note on your Dispose behavior #

When you delete the first cell, only that cell's unsubscribe action runs—removing one observer from the list, but since there's still another observer (from the second cell), the list isn't empty, so your timer keeps running. Only when both cells are deleted do all unsubscribe actions run, emptying the list (and with the timer cleanup we mentioned, stopping the timer too). The "Dispose" you're seeing here refers to the unsubscribe action for each individual subscription, not the LtpObserver itself—Excel-DNA will only clean up the LtpObserver instance when there are no more subscribers and it's removed from the cache.

内容的提问来源于stack exchange，提问作者cofee2code