seraphyの日記

日記というよりは過去を振り返るときのための単なる備忘録

ExcelのRTDサーバをC#で作る方法

Excelでリアルタイムデータを取得するRTDサーバをC#で作る方法

概要

Excelには動的にリアルタイムデータをセルに取得・表示するための仕組みが2つある

どちらも、ほぼ似たようなワークシート関数で、使い方もほぼ同じ、結果もほぼ同じである。

=DDE("APPLICATION", "TOPIC", "ITEM")
=RTD("PROGRAM_ID", "SERVER", "TOPIC1"....)

Excelでリアルタイムデータを取得できるようになると、刻々と変わる値から動的に計算させたり、状態に応じて表示色を変えたりできるので、多数の計測機器から出力される計測ファイルや、ネット上で刻々と変化する無数の値からリアルタイムに分析・俯瞰するのに使えるようになる。

今回は、このちうちのRTDとして機能するRTDサーバーをC#で作成してみることにする。

DDEの特性

DDEはWindows3.1の16ビット全盛時代によく使われていたレガシーな技術で、データ交換にウィンドウメッセージを用いているところに特徴がある。1

このDDE関数で指定する「APPLICATION」はトップレベルウィンドウの「ウィンドウクラス名」そのものである。 つまり、ExcelのDDE関数は起動している別アプリのウィンドウとの間でメッセージを交換することでリアルタイムにデータを取得している。

数あるウィンドウメッセージのうち、DDEまわりを制御するライブラリはDDEML(Dynamic Data Exchange Management Library)としてまとめられている。

C++で実装する分にはddeml.hをインクルードすれば現在でも問題なく実装できるのであるが、C#(WindowsFormやWPF)では難しい。

DotNETではウィンドウクラスを独自に設定することが想定されておらず、DDEMLに相当するウィンドウメッセージのハンドリングも標準では全くサポートされていないためである。2

RTDの特性

RTD関数はExcel 2002からサポートされており、ダイナミックデータの取得方法としてウィンドウメッセージではなく、COMオートメーションサーバーを用いているところに特徴がある。

要するに、データの送り元としては、別アプリとしてウィンドウを用意する必要はなく、COMのDDLないしEXEを作成すれば良い、ということである。

EXE、またはプロキシ/スタブをもつDLLの場合は適切に設定すればリモートPC上のCOMを扱える(DCOM)ようになる、という利点もある。3

そのため、ExcelのRTD関数の第2引数ではコンピュータ名が指定できるようになっている。(ローカルPCのCOMを指定するのであれば、ここは省略して良い。)

また、DDEではTOPIC, ITEMの2種類のデータしか持つことが出来なかったが、RTDの場合は1つ以上、最大で253個のパラメータをもつことができる。

単純なCOMサーバーを作るのであればDotNETで容易につくることができる。

ということで、今回はC#で簡単なRTDサーバーを作ってみる。

※ 欠点としてはCOMであるため、基本的にレジストリへの登録が必須となる点である。(DDEの場合は現在存在するトップレベルウィンドウのウィンドウクラス名を検索するだけなので、レジストリ等への事前登録が必要ない。)

IRtdServer, IRTDUpdateEvent インターフェイス

RTDはCOMなので当然インターフェイスとして定義されており、RTDサーバーは、このインターフェイスを実装することで実現する。

これらのインターフェイスExcelのタイプライブラリをインポートすることで利用可能になるが、インターフェイスの型やGUID等々は明確になっているので、わざわざExcelからタイプライブラリをインポートせずとも、同じ定義を自分で用意しても何ら問題ない。(そうすればExcelのないビルド環境でもビルドできるようになる。)

Excelがビルド環境にあることを前提とする場合は、単にマシンに入っているExcelバージョンに合わせた「Microsoft Excel xx.x オブジェクトライブラリ」を参照設定すればいい。(→ 「Excel 用の RealTimeData サーバーを作成する方法」 )

また、RTDに必要なインターフェイスは2つだけである。

IRtdServerインターフェイス

IRtdServerが実装すべきRTDサーバーのインターフェイスである。

IRTDUpdateEventインターフェイス

IRTDUpdateEventは、データが更新されたことをExcelに通知するためのコールバック用のインターフェイスである。

サーバー開始時にExcelからIRTDUpdateEventが実装されたCOMオブジェクトが渡されてくるので、これに対してコールバックを行う。

定義例

以下は https://weblogs.asp.net/kennykerr/Rtd7 などから借用した。(DISPIDなどは別のソースコードから検索してつけてみた。)4

↑↑↑ RTDまわりのことは、この人の記事だけで、ほとんど網羅できる。

// Excelを参照せず、IRTDServer, IRTDUpdateEventのインターフェイスの定義を行う.
// Excel RTD Servers: C# without the Excel Assembly Reference
// https://weblogs.asp.net/kennykerr/Rtd7

/// <summary>
/// RTDのイベントのコールバック用インターフェイス.
/// </summary>
[ComImport]
[TypeLibType(TypeLibTypeFlags.FDual | TypeLibTypeFlags.FDispatchable)]
[Guid("A43788C1-D91B-11D3-8F39-00C04F3651B8")]
public interface IRTDUpdateEvent
{
    [DispId(10)]
    void UpdateNotify();

    [DispId(11)]
    int HeartbeatInterval
    {
        get;
        set;
    }

    [DispId(12)]
    void Disconnect();
}

/// <summary>
/// Excelから呼び出されるRTDインターフェイス
/// </summary>
[ComImport]
[TypeLibType(TypeLibTypeFlags.FDual | TypeLibTypeFlags.FDispatchable)]
[Guid("EC0E6191-DB51-11D3-8F3E-00C04F3651B8")]
public interface IRtdServer
{
    [DispId(10)]
    int ServerStart(IRTDUpdateEvent callback);

    [DispId(11)]
    object ConnectData(int topicId,
                        [MarshalAs(UnmanagedType.SafeArray, SafeArraySubType = VarEnum.VT_VARIANT)] ref Array strings,
                        ref bool newValues);

    [DispId(12)]
    [return: MarshalAs(UnmanagedType.SafeArray, SafeArraySubType = VarEnum.VT_VARIANT)]
    Array RefreshData(ref int topicCount);

    [DispId(13)]
    void DisconnectData(int topicId);

    [DispId(14)]
    int Heartbeat();

    [DispId(15)]
    void ServerTerminate();
}

VB6的なAutomation型らしさの色濃いSafeArray配列を多用したり、ref引数を使ったりとしていて、なかなかめんどくさそうな型定義になっている。

IRTDServerの実装

以下、IRTDServerの実装方法を示す。

プロジェクトの作成

まずC#のクラスライブラリプロジェクトを作成する。

ここでは「SeraRTDServerExample」というプロジェクトを作成するものとする。

これは、ソリューションエクスプローラのプロジェクト名を右クリックしてプロパティを開き、アプリケーションタブのアセンブリ情報から「アセンブリをCOM参照可にする」をチェック状態にしておく。

これでビルドされたDLLはCOMとして登録可能になる。

IRTDServer実装コード

IRTDServerは以下のクラスとして実装してみた。

今回はRTDサーバを作ることが目的なので、

  • 引数としてファイル名とフォーマットを渡すと...
    • 日付またはファイルサイズのいずれかを動的に返す
    • もしくはフォルダ名を指定してファイル一覧を文字列として返す

だけの単純なものとした。

Visual Studio 2017 Express for Desktopでビルド可能としている。

ソース一式はgist:51b509592e0b20fc77612673d490f247にも置いている。

/// <summary>
/// シンプルなRTDサーバーの実装例
/// </summary>
[Guid("5B6AA03F-F280-4B06-AFA3-519C9215D6BD")]
[ProgId("SeraRtdSvr")]
[TypeLibType(TypeLibTypeFlags.FDual | TypeLibTypeFlags.FDispatchable)]
[ComVisible(true)]
public class SeraphyRTDServer : IRtdServer
{
    /// <summary>
    /// TopicIdごとの値を保持し更新の有無を判定する値ホルダー
    /// </summary>
    class ValueHolder
    {
        /// <summary>
        /// 値のリゾルバ
        /// </summary>
        private Func<object> resolver;

        /// <summary>
        /// 現在の値
        /// </summary>
        public object Current { get; set; }

        /// <summary>
        /// 更新の有無
        /// </summary>
        public bool Modified { set; get; }

        /// <summary>
        /// コンストラクタ
        /// </summary>
        /// <param name="resolver"></param>
        internal ValueHolder(Func<object> resolver)
        {
            this.resolver = resolver;
        }

        /// <summary>
        /// 更新する.
        /// 変更がある場合は更新の有無が設定される.
        /// </summary>
        public void Refresh()
        {
            object value;
            try
            {
                value = resolver();
            }
            catch (Exception ex)
            {
                Debug.Print("▼exception {0}", ex.Message);
                // https://groups.google.com/forum/#!topic/exceldna/Z6mmxJ4LSbM
                // ErrDiv0  = -2146826281 
                // ErrNA = -2146826246
                // ErrName = -2146826259
                // ErrNull = -2146826288
                // ErrNum = -2146826252
                // ErrRef = -2146826265
                // ErrValue = -2146826273
                value = new ErrorWrapper(-2146826246); // N/A
            }
            if (!Object.Equals(value, Current))
            {
                Modified = true; // ModifiedはOffにはしない
                Debug.Print("◇ modified old={0}, new={1}", Current, value);
            }
            Current = value;
        }
    }

    /// <summary>
    /// タスクを終了させるためのキャンセラレーショントークンソース
    /// </summary>
    private CancellationTokenSource cancelTokenSource;

    /// <summary>
    /// 定期的にRTDにイベントを通知するための無限ループタスク
    /// </summary>
    private Task task;

    /// <summary>
    /// RTDイベントコールバック
    /// </summary>
    private IRTDUpdateEvent callback;

    /// <summary>
    /// RTDイベントコールバックを使う場合の同期コンテキスト
    /// </summary>
    private SynchronizationContext synchronizationContext;

    /// <summary>
    /// TopicIdごとのValueHolderを保持するマップ.
    /// </summary>
    private Dictionary<int, ValueHolder> TopicMap = new Dictionary<int, ValueHolder>();

    /// <summary>
    /// ファイル名に対する更新日付を取得する。例外の場合は例外メッセージを返す.
    /// </summary>
    /// <param name="name">ファイル名</param>
    /// <returns>更新日付</returns>
    private object GetLastWriteTime(string name)
    {
        return System.IO.File.GetLastWriteTime(name);
    }

    /// <summary>
    /// ファイル名に対するファイルサイズを取得する。例外の場合は例外メッセージを返す.
    /// </summary>
    /// <param name="name">ファイル名</param>
    /// <returns>更新日付</returns>
    private object GetFileSize(string name)
    {
        return new System.IO.FileInfo(name).Length;
    }

    /// <summary>
    /// ファイルまたはディレクトリに対するタブ区切りファイル一覧を返す.
    /// </summary>
    /// <remarks>
    /// 本当は配列を返したいのだが、方法がなさそうなので受け取り側で分割する.
    /// </remarks>
    /// <param name="name">ファイル名</param>
    /// <returns>更新日付</returns>
    private object GetFileList(string name)
    {
        var attr = File.GetAttributes(name);
        if (attr.HasFlag(FileAttributes.Directory))
        {
            var dir = new DirectoryInfo(name);
            return String.Join("\t", dir.GetFiles().Select(f => f.FullName).ToArray());
        }
        return name;
    }

    /// <summary>
    /// リアルタイム データ サーバーから新しいトピックを追加します。
    /// ConnectData メソッドは、リアルタイム データ関数を含むファイルが開かれたとき、
    /// またはユーザーが RTD 関数を含む新しい数式を入力したときに呼び出されます。
    /// </summary>
    /// <param name="topicId">必ず指定します。整数型 (Integer) の値を指定します。
    /// トピックを識別する一意の値を指定します。この値は、Excel によって割り当てられます。</param>
    /// <param name="strings">必ず指定します。オブジェクト型 (Object) の値を指定します。
    /// トピックを識別する文字列の 1 次元配列を指定します。(RTD関数の引数)</param>
    /// <param name="newValues">必ず指定します。ブール型 (Boolean) の値を指定します。
    /// 入力値がFALSEの場合はRTDセルを含むブックをロードしたなどでセル上に既存の値があることを示します。
    /// TRUE(非0)の場合はRTDセルを新規に入力したことを示します。
    /// 返却値は、新規の値を使用するべきかを示し、FALSEの場合は既存のセルの値を変更しないことを示します。</param>
    /// <returns></returns>
    public object ConnectData(
        int topicId,
        [MarshalAs(UnmanagedType.SafeArray, SafeArraySubType = VarEnum.VT_VARIANT)] ref Array strings,
        ref bool newValues)
    {
        PrintMethodInfo();
        try
        {
            // RTD関数の引数(トピック)の取得
            // トピックは1個以上、最大253個までありえる。
            // (なお、同一のパラメータは同一のtopicidが割り当てられるので
            // セルのコピー等を行った場合は本メソッドが重ねて呼び出されることはない。)
            string fname = strings.GetValue(0).ToString();
            string fmt = "time";
            if (strings.Length >= 2) // 2個目以降は省略可
            {
                fmt = strings.GetValue(1).ToString();
            }
            Debug.Print("○ topicId={0} fname={1}, format={2}", topicId, fname, fmt);

            Func<object> func;
            switch (fmt)
            {
                case "time":
                default:
                    func = () => this.GetLastWriteTime(fname);
                    break;

                case "size":
                    func = () => this.GetFileSize(fname);
                    break;

                case "list":
                    func = () => this.GetFileList(fname);
                    break;
            }

            // 割り当てられたTOPICIDと関連づけて保存する
            var valueHolder = new ValueHolder(func);
            TopicMap[topicId] = valueHolder;

            // セルを更新することを示す
            newValues = true;

            // データを取得し返却する.
            valueHolder.Refresh();
            valueHolder.Modified = false;
            return valueHolder.Current;
        }
        catch (Exception ex)
        {
            return ex.ToString();
        }
    }

    /// <summary>
    /// RTD (リアルタイム データ) サーバー アプリケーションに、トピックが使用されなくなったことを通知します。
    /// </summary>
    /// <param name="topicId">必ず指定します。整数型 (Integer) の値を指定します。
    /// トピックに割り当てる一意の値を指定します。この値は、Excel によって割り当てられます。</param>
    public void DisconnectData(int topicId)
    {
        PrintMethodInfo();
        TopicMap.Remove(topicId);
    }

    /// <summary>
    /// リアルタイム データ サーバーがアクティブかどうかを判別します。
    /// </summary>
    /// <returns>ゼロまたは負の値は失敗を示し、正の値は、サーバーがアクティブであることを示します。</returns>
    public int Heartbeat()
    {
        // ハートビート通知
        PrintMethodInfo();
        return 1;
    }

    /// <summary>
    /// このメソッドは、Excel が新しいデータを取得するときに呼び出されます。
    /// </summary>
    /// <param name="topicCount">必ず指定します。整数型 (Integer) の値を指定します。
    /// RTD サーバーは、TopicCount の値を、取得された配列内の要素数に変更する必要があります。</param>
    /// <returns>新しいデータが格納されたバリアント型 (Variant) の配列。</returns>
    [return: MarshalAs(UnmanagedType.SafeArray, SafeArraySubType = VarEnum.VT_VARIANT)]
    public Array RefreshData(ref int topicCount)
    {
        PrintMethodInfo();

        // 更新されているアイテムの抽出
        var modifiedItems = TopicMap.Where(entry => entry.Value.Modified).ToList();

        int count = modifiedItems.Count; // アイテムの個数
        object[,] data = new object[2, count]; // TOPICID, 値のペアからなる2次元配列

        int index = 0;
        foreach (var entry in modifiedItems)
        {
            int topicId = entry.Key;
            object value = entry.Value.Current;
            Debug.Print("* topidId={0}, value={1}", topicId, value);

            data[0, index] = topicId; // topicId
            data[1, index] = value; // 現在値

            entry.Value.Modified = false; // 返却したアイテムはModifiedをクリアする
            ++index;
        }

        topicCount = count;
        return data;
    }

    /// <summary>
    /// ServerStart メソッドは、
    /// リアルタイム データ サーバーがインスタンス化された直後に呼び出されます。
    /// </summary>
    /// <param name="callback">必ず指定します。
    /// IRTDUpdateEvent オブジェクトを指定します。
    /// コールバック オブジェクトを指定します。</param>
    /// <returns>負の値またはゼロはサーバーの起動に失敗したことを示し、
    /// 正の値は起動が成功したことを示します。</returns>
    public int ServerStart(IRTDUpdateEvent callback)
    {
        PrintMethodInfo();
        if ((task == null || task.IsCompleted) && callback != null)
        {
            this.callback = callback;

            // RTDイベントはSTAのため
            // 別スレッドから呼び出すためはマーシャリングする必要がある.
            synchronizationContext = SynchronizationContext.Current ??
                    new WindowsFormsSynchronizationContext();
            Debug.Print("synchronizationContext={0}", synchronizationContext);

            // タイマー的な無限ループタスクを開始する.
            cancelTokenSource = new CancellationTokenSource();
            task = new Task(WatchLoop,
                    cancelTokenSource.Token,
                    TaskCreationOptions.LongRunning);
            task.Start();
            return 1;
        }
        return 0;
    }

    /// <summary>
    /// キャンセルが指示されるまで現在保持しているTopicIdの状態を1秒ごとに検査し、
    /// 更新があればRTDイベントコールバックでExcelに更新を通知する無限ループタスク
    /// </summary>
    private void WatchLoop()
    {
        // キャンセルされるまで無限ループする
        while (!cancelTokenSource.Token.IsCancellationRequested)
        {
            if (!cancelTokenSource.Token.WaitHandle.WaitOne(1000)) // 1000mSec待ち
            {
                // タイムアウトした場合 = キャンセルされていない場合
                PrintMethodInfo("Timeout");

                if (UpdateAllTopic() > 0)
                {
                    // Excelに更新を通知する.
                    synchronizationContext.Post(x => {
                        try
                        {
                            PrintMethodInfo("UpdateNotify");
                            callback?.UpdateNotify();
                        }
                        catch (Exception ex)
                        {
                            // コールバックに失敗した場合は
                            // Excelとの関係が異常になっている
                            Debug.Print("★Callback failed. " + ex.Message);
                            cancelTokenSource.Cancel();
                        }
                    }, null);
                }
            }
        }
        Debug.Print("★★end★★");
    }

    /// <summary>
    /// 保持している、すべてのTopicの更新を行う
    /// </summary>
    /// <returns>更新があった個数を返す</returns>
    private int UpdateAllTopic()
    {
        int modifiedCount = 0;
        foreach (var valueHolder in TopicMap.Values)
        {
            valueHolder.Refresh();
            modifiedCount += valueHolder.Modified ? 1 : 0;
        }
        return modifiedCount;
    }

    /// <summary>
    /// リアルタイム データ サーバーへの接続を終了します。
    /// </summary>
    public void ServerTerminate()
    {
        PrintMethodInfo();
        Dispose();
    }

    public SeraphyRTDServer()
    {
        PrintMethodInfo();
    }

    ~SeraphyRTDServer()
    {
        PrintMethodInfo();
        Dispose(); // ServerTerminate前にCOMがリリースされた場合の対応
    }

    /// <summary>
    /// 後片付け。タスク等を終了させます.
    /// </summary>
    private void Dispose()
    {
        cancelTokenSource?.Cancel();
        task?.Wait();

        cancelTokenSource = null;
        task = null;
        synchronizationContext = null;
    }

    /// <summary>
    /// 診断メッセージ表示
    /// </summary>
    /// <param name="member">関数名等。
    ///  省略された場合は呼び元のメソッド名が自動的に適用される</param>
    private void PrintMethodInfo(
    [System.Runtime.CompilerServices.CallerMemberName] string member = "")
    {
        Debug.Print("☆{0}: @{1:X} thread={3}({2})", member, GetHashCode(),
            Thread.CurrentThread.GetApartmentState(),
            Thread.CurrentThread.ManagedThreadId);
    }
}

ビルド方法

プロジェクトをビルドして生成されたdllは、COMとして登録する必要がある。

管理者権限でコマンドプロンプトを開き、

regasm /codebase SeraRtdSvr.dll

のように、Regasm.exe (アセンブリ登録ツール)を使って登録する。

regasm.exeは、DotNETフレームワークのフォルダ5にある。あるいは、Visual Studioをインストールするとスタートメニューに追加される「Developer Command Line Tool」を使うとパスが通っている。

『codebaseを指定する場合は署名をつけろ』とか警告がでるが、とりあえず登録はできる。

オプションとしては以下のようなものがある。

  • /unregister ... 登録解除する場合
  • /codebase ... DLLの場所(パス)を指定して登録する場合
  • /tlb ... COMオートメーションのタイプライブラリの登録を行う場合
  • regfile ... レジストリに書き込む内容をファイルとして出力する場合6

今回はタイプライブラリの登録は必要ない。既存のインターフェイスIRTDServerに対する実装しか行っていないためである。 (新しいインターフェイスは何も定義されておらず、tlbexpツールでtlbファイルをエクスポートしても中身の実体は何もないものとなる。)

実行例

たとえば、以下のようなシートを作成してみた。

f:id:seraphy:20180208191941p:plain
screencapture_excel_rtd

このRTDではディレクトリへのパスを指定して"list"フォーマットを指定すると、ファイルリストがタブ区切り文字列で返ってくるので、まず、これを配列に分解するための関数を作る。7

標準モジュールを追加して

Public Function ToArray(ByVal str As String) As String()
    ToArray = Split(str, Chr(9))
End Function

とする。

つぎに、B1にフォルダへのパスを入力し、 B2:B10のセルを選択した状態で以下の配列式を入力する。

=IFERROR(TRANSPOSE(toarray(RTD("serartdsvr",,$B$1,"list"))), "")

CTRL+SHIFT+ENTERで配列式を確定する。8

これでリアルタイムでファイル一覧がB2:B10の範囲で表示されるようになる。

次に、C2:C10のセルを選択した状態で以下の配列式を入力する。

=IF(B2:B10 <> "",RTD("serartdsvr",,B2:B10,"size"),"")

CTRL+SHIFT+ENTERで式を確定するとC2:C10の範囲でB2:C10のファイル名に対するファイルサイズがリアルタイムで表示されるようになる。

自作のToArray関数にDebug.Printするような診断メソッドを仕込むと、更新のたびに呼び出されていることが分かると思う。

ちなみに、ファイルの数が増えたら配列セルの範囲を超えた分は無視される。自動で拡張されたりしないので配列セルを手直ししなければならない。めんどくさい。

トレースログの確認

SystemInternalsのDebug View fow Windowsを入れてExcelの実行中のデバッグプリントをキャプチャすると、RTDのメソッドの呼ばれる順序が分かったりして面白い。

[12020] ☆.ctor: @378734A thread=1(STA) 
[12020] ☆ServerStart: @378734A thread=1(STA) 
[12020] synchronizationContext=System.Windows.Forms.WindowsFormsSynchronizationContext 
[12020] ☆ConnectData: @378734A thread=1(STA) 
[12020] ○ topicId=0 fname=c:\temp\testdata\sample2.txt, format=size 
[12020] ◇ modified old=, new=4 
[12020] ☆ConnectData: @378734A thread=1(STA) 
[12020] ○ topicId=1 fname=c:\temp\testdata\sample1.txt, format=size 
[12020] ◇ modified old=, new=14 
[12020] ☆ConnectData: @378734A thread=1(STA) 
[12020] ○ topicId=2 fname=c:\temp\testdata, format=list 
[12020] ◇ modified old=, new=c:\temp\testdata\sample1.txt c:\temp\testdata\sample2.txt 
[12020] ☆ConnectData: @378734A thread=1(STA) 
[12020] ○ topicId=3 fname=, format=size 
[12020] ◇ modified old=, new=パスの形式が無効です。 
[12020] ☆Timeout: @378734A thread=4(MTA) 
[12020] ☆Timeout: @378734A thread=4(MTA) 
[12020] ☆Timeout: @378734A thread=4(MTA) 
[12020] ☆Timeout: @378734A thread=4(MTA) 
[12020] ◇ modified old=14, new=5 
[12020] ☆UpdateNotify: @378734A thread=1(STA) 
[12020] ☆RefreshData: @378734A thread=1(STA) 
[12020] * topidId=1, value=5 
[12020] ☆Timeout: @378734A thread=4(MTA) 
[12020] ☆Timeout: @378734A thread=4(MTA) 
[12020] ☆Timeout: @378734A thread=4(MTA) 
[12020] ☆Timeout: @378734A thread=4(MTA) 
[12020] ☆Timeout: @378734A thread=4(MTA) 
[12020] ☆ServerTerminate: @378734A thread=1(STA) 
[12020] ★★end★★ 
[12020] ☆Finalize: @378734A thread=2(MTA) 

ちなみに、このログをみて気がついたが、topicId=3fnameが空のセルに対してRTDを問い合わせていた。C列でIF関数をつかっているのが原因だろうと思う。ワークシート上のIFは「関数」なので、関数の呼び出し前に、関数の引数である、真・偽のいずれの場合の計算式も事前に評価せざるを得ない為だろう。

ソースの説明

以下、メソッドごとに説明する。

SeraphyRTDServerクラスのヘッダ部

SeraphyRTDServerクラスが、IRtdServerを実装している、本サンプルの本体クラスである。

COMオブジェクトになるため、CLSIDとProgIDとして

[Guid("5B6AA03F-F280-4B06-AFA3-519C9215D6BD")]
[ProgId("SeraRtdSvr")]

のように指定している。

CLSIDは全世界で一意になるべきものなので、本サンプルを真似して別のクラスを作る場合には、Guidは、それぞれ生成し直す必要がある。

ProgIdはCOMオブジェクトの一般的な形式に従うと「ベンダ名.プログラム名.バージョン番号」のようなドット区切りで長い名前になるのが普通だが、今回は、あえて短くしている。

これはRTD関数で指定する名前なので、ProgIDが長いと数式がめんどくさくなるからである。(短すぎてシステムの予約語や一般語と衝突すると、いろいろ問題がでるので注意のこと。)

ServerStartメソッド: 開始処理

ExcelからRTDサーバーの利用が開始されると、最初に呼び出される。

複数ブックに同一RTDサーバへの接続がある場合は同じインスタンスが使われ、同じインスタンスでServerStartが二度呼び出されることはない。

ここでExcel側からIRTDUpdateEventインターフェイスを実装したコールバック用オブジェクトが渡される。

RTDサーバーは、このコールバック用オブジェクトを保存しておいて、監視している値のいずれかが変更された場合には、IRTDUpdateEvent#UpdateNotify()メソッドを呼び出してExcelに変更を通知する必要がある。

ただし、変更有無チェックをUIスレッドで常時実行するとExcelがカクカクして操作性が悪化するため、監視処理はUIスレッドとは別のスレッドで行う必要がある。

このサンプルではRTDサーバの開始時に無限ループタスクを立ち上げて、1000 mSec単位で保持しているTopicIdの値に変更があるかチェックし、もし変更があればUpdateNotifyを呼び出すようにしている。

コールバックで注意しなければならないのは、このIRTDUpdateEventオブジェクトはSTA(Single-Threaded Apartments)であり、ServerStartが呼ばれたときのスレッドと同じスレッドから呼び出されなければならない、ということである。

C++ではCoMarshalInterThreadInterfaceInStream関数などを用いてスレッドをまたいだ場合のマーシャリングを行うが、C#の場合では、SynchronizationContextを使うことになるようである。

    SynchronizationContext synchronizationContext = SynchronizationContext.Current; // 現在の同期コンテキストを保存

    // ... 別スレッドで処理開始 ...

    // 保存した同期コンテキストで実行
    synchronizationContext.Post(x => {
        callback?.UpdateNotify();
    }, null);

ただし、実際にはExcelからRTDサーバーが呼び出された時点ではSynchronizationContext#Currentは初期化されておらず、かわりに明示的にWindowsFormのSystem.Windows.Forms.WindowsFormsSynchronizationContextを生成して使うことになる。(参照設定で、System.Windows.Formsが必要となる。)

これはWindowsFormのUIスレッドにマーシャリングすることを意味しているが、RTDのServerStartメソッドもUIスレッドから呼び出されているので、結果的に同じになるので大丈夫、ということのようである。

(たしかに、トレースログに出ているThreadIdを見ても、ServerStartメソッドのIDと、UpdateNotify時のIDが同一になっている。)

なお、UI用タイマーであるSystem.Windows.Forms.Timerを使うことで、UIスレッドからタイマー処理を実行できるため、明示的にSynchronizationContextを使わずとも良く、実装も単純になるが、前述のとおり、UIスレッド内で重い処理は行うべきではない。

ConnectDataメソッド: Topicの追加

RTD関数に渡された引数の内容がTopicIdとともに渡されてくる。

TopicIdは、Excelが割り当てた番号である。

引数ごとに1つのIDが割り当てられるので、たとえば複数のセルに同一の引数をもつRTD式がある場合でも、TopicIdは1つだけである。(なので呼び出しも1回である)

関数に渡される引数は、1つ以上、最大253個までの配列として渡されてくる。

分かりにくいのはnewValues引数で、これはIn/Out引数である。

引数として渡されてくるとき、この値がTrueである場合、新規にRTDセルが入力されたことを表している。これに対してFalseである場合はRTDセルをもつブックが開かれた場合など、すでにRTDセルが存在しており更新するために呼び出されたことを意味している。

また、このnewValues引数は戻り値としても使われ、Trueを返した場合は、このメソッドが返す値でセルの内容を更新することを意味する。Falseの場合は更新しない。

(現在のセルの内容が何であるかは分からないので通常は常にTrueを返せばよいとおもわれるが、たとえば、すぐには最新情報が得られないケースなど、TopicId登録時はとりあえずFalseを返して、あとでRefreshDataで一括更新する、という実装もありえるだろう。)

DisconnectDataメソッド: Topicの削除

ExcelがRTDセルを消して、同一の引数をもつRTDセルが1つもなくなった場合に呼び出される。

もしくは、ブックを閉じて監視する必要がなくなった場合にも通知される。

以後、このTopicIdは未使用になる。

RefreshDataメソッド: Topicの状態の一括更新

Excel側からTopicの状態を最新化するために呼び出される。

これは一般的にはIRTDUpdateEvent#UpdateNotifyによってExcelに変更通知を行ったあと、Excelから呼び出されるものである。

TopicIdごとの値を格納した二次元配列と、その配列の個数を返す。

なお、すべてのTopicを返す必要はなく、更新の必要のあるTopicIdと、そのデータだけ返せば良い。

Heartbeatメソッド: 生存確認

Excel側からRTDサーバーの生存確認のために定期的に呼び出される。

正の値を返すことで生きていることを応答する。

インプロセスのCOMサーバーの場合には、DLLが死んだときはExcelプロセスも死んだ時なので意味はないが、リモートPC上のDCOMや、EXEのCOMサーバーの場合には意味があると思われる。

とりあえず、いつでも1を返しておけば良い。

ちなみに0を返したりすると、

f:id:seraphy:20180210095400p:plain
RTDサーバーの再起動の確認ダイアログ

のようにExcelからお問い合わせが表示される。

ServerTerminateメソッド: 終了処理

すべてのブックから、このRTDサーバーの持つ全てのTopicIDが削除されるか、すべてのブックが閉じられて、TopicIDが1つも必要としなくなったときに呼び出される。

本サンプルでは無限ループタスクに対してキャンセル指示を出してタスクの終了を行っている。

Finalize: ガベージ処理

COMとしてはServerTerminateメソッドを呼ばずにReleaseすることもありえるので、念のため、ServerTerminateメソッドと同じDispose処理を入れている。

その他

IRTDUpdateEvent.Disconnectについて

UpdateNotifyで更新を通知するかわりに、Disconnectを通知すると、Excel側からServerTerminateが呼び出されてRTDとの接続が終了する。

この場合、シート上のRTDセルは、RTDが接続できなかった場合と同様、エラー「#N/A」になる。

これ以上、リアルタイムデータを取得しつづけることができない、なんらかの事由がある場合に使ったりするのだろう。

例外処理について

DotNETのオブジェクトをCOMとして公開した場合、例外をスローすると受け取り側のCOMクライアントにはHRESULTのエラーコードに変換されて渡される。

https://blogs.msdn.microsoft.com/yizhang/2010/12/17/interpreting-hresults-returned-from-netclr-0x8013xxxx/

たとえば、ApplicationException例外をスローすると、COMの受け取り側のHRESULTはCOR_E_APPLICATION(0x80131600)になる。

ただし、実験では、RTDサーバー内で例外が発生してExcelとの連携が旨くゆかなくなった場合には、Excelも(しばらくしてから)異常終了することが多かったので、エラー処理はきちんとやっておいたほうが良いようだ。

ErrorWrapperの返却について

COMのVariantではVT_ERRORとしてSCODE(HRESULT)を送ることができるが、DotNETではSystem.Runtime.InteropServices.ErrorWrapperを使うことでVT_ERRORを表現できる。

Default Marshaling for Objects | Microsoft Docs

そこで、エラーになった場合に

    catch (Exception ex)
    {
        value = new ErrorWrapper(ex);
    }

のようにしてエラー値を返却してみたところ、Excel側ではRTDからエラー値を受け取ると、セルの値を更新せず無視するようである。(#N/Aにもならない。そもそも更新されない。)

とりあえずC#からVT_ERRORを返す仕組みはわかったので、この線でRTDまわりをGoogle検索したところ、興味深い投稿を見つけた。

https://groups.google.com/forum/#!msg/exceldna/Z6mmxJ4LSbM/ryiWJDjhu90J

どうやら、特定の値を返せば、Excelのセル上でエラーを表現できるらしい。

エラー種別 エラー値
ErrNA -2146826246
ErrName -2146826259
ErrNull -2146826288
ErrNum -2146826252
ErrRef -2146826265
ErrValue -2146826273
    value = new ErrorWrapper(-2146826246); // 「#N/A」と表示される

うまくいった。

まとめ

今回試作してみた感触では、C#ExcelのRTDサーバーを作るのは、とくに難しいところもなかった。 C++よりも容易に作れそうな感じである。

しかし、Excelでリアルタイムデータを引くことの意義は、それなりの有益なデータソースがあってこそ、と思うのだが、最近はラズパイやアルデイーノといったDIY感覚でネットワーク接続できるセンサー類が手に入りやすくなってきたので、むしろ、そっち方面での技術力なり発想力が求められているかもしれない。

参考文献/URL

kennykerrさんのところのブログ

RTDに関する詳細が揃っている。超オススメ

マイクロソフト公式

MSの記事、VB6を使うけれどRTDの作り方の基本が分かる。

以上、メモ終了。


  1. アプリケーション間のデータ交換としてDDEを使うことはなくなったが、Explorerがアプリを起動する際に、すでにアプリが起動している場合は新たにEXEを立ち上げるのではなく既存のEXEに対してドキュメントを開くように依頼する、というようなケースで現在でも使われている。

  2. どうしてもDotNETとDDEの両方を使いたいのであれば、C++/CLIを使えばどうとでもなると思う。あるいは、サードパーティのライブラリとしてはNDdeというのもあるようだ。

  3. リモートでCOMを扱うDCOMに対して、リモートでDDEを扱うNetDDEという仕様もあったが、NetDDEはVista以降は廃止されている。

  4. 今回はIRTDServer, IRTDUpdateEventインターフェイスは、ComImportとして型(vtbl)を合わせているだけであり、Excel側も、IRTDServerにはIDispatchは使っていないか、ちゃんと名前で索引しているようでDISPIDの値はどうでもいいらしいのだが、生成されたSeraphyRTDServerに対してIDispatch#GetIDsOfNamesで名前からDISPIDを取り出したときに、本来の定義されたDISPIDと異なる値が返ってくるのは気持ち悪いので一応、正しいものを返せるようにしておく。

  5. たとえば、"C:\Windows\Microsoft.NET\Framework\v4.0.30319"のような場所にある。

  6. ただしタイプライブラリは除く。もし必要なら、タイプライプラリ用のレジストリエントリは出力されないので手動で作成する必要がある。

  7. RTDサーバから直接、配列を返したかったのだが、方法がみつからなかった。

  8. 確定すると式の見た目は{}で囲まれ、配列式であることを示す。なお、配列式にした範囲では一括して編集・削除しなければならない。配列式の範囲が分からない場合はCTRL+Gで「ジャンプ」ダイアログを開き、「セル選択」ボタンから「アクティブセルの配列」を選ぶと、配列式の入ったセル範囲をまるごと選択してくれる。