日記というよりは過去を振り返るときのための単なる備忘録この広告は、90日以上更新していないブログに表示しています。Data)どちらも、ほぼ似たようなワークシート関数で、使い方もほぼ同じ、結果もほぼ同じである。"TOPIC1"....)Excelでリアルタイムデータを取得できるようになると、刻々と変わる値から動的に計算させたり、状態に応じて表示色を変えたりできるので、多数の計測機器から出力される計測ファイルや、ネット上で刻々と変化する無数の値からリアルタイムに分析・俯瞰するのに使えるようになる。今回は、このちうちのRTDとして機能するRTDサーバーをC#で作成してみることにする。DDEの特性DDEはWindows3.1の16ビット全盛時代によく使われていたレガシーな技術で、データ交換にウィンドウメッセージを用いているところに特徴がある。1このDDE関数で指定する「APPLICATION」はトップレベルウィンドウの「ウィンドウクラス名」そのものである。ExcelのDDE関数は起動している別アプリのウィンドウとの間でメッセージを交換することでリアルタイムにデータを取得している。Library)としてまとめられている。C++で実装する分にはddeml.hをインクルードすれば現在でも問題なく実装できるのであるが、C#(WindowsFormやWPF)では難しい。DotNETではウィンドウクラスを独自に設定することが想定されておらず、DDEMLに相当するウィンドウメッセージのハンドリングも標準では全くサポートされていないためである。2002からサポートされており、ダイナミックデータの取得方法としてウィンドウメッセージではなく、COMオートメーションサーバーを用いているところに特徴がある。要するに、データの送り元としては、別アプリとしてウィンドウを用意する必要はなく、COMのDDLないしEXEを作成すれば良い、ということである。EXE、またはプロキシ/スタブをもつDLLの場合は適切に設定すればリモートPC上のCOMを扱える(DCOM)ようになる、という利点もある。3そのため、ExcelのRTD関数の第2引数ではコンピュータ名が指定できるようになっている。(ローカルPCのCOMを指定するのであれば、ここは省略して良い。ITEMの2種類のデータしか持つことが出来なかったが、RTDの場合は1つ以上、最大で253個のパラメータをもつことができる。単純なCOMサーバーを作るのであればDotNETで容易につくることができる。いうことで、今回はC#で簡単なRTDサーバーを作ってみる。欠点としてはCOMであるため、基本的にレジストリへの登録が必須となる点である。(DDEの場合は現在存在するトップレベルウィンドウのウィンドウクラス名を検索するだけなので、レジストリ等への事前登録が必要ない。インターフェイスRTDはCOMなので当然インターフェイスとして定義されており、RTDサーバーは、このインターフェイスを実装することで実現する。これらのインターフェイスはExcelのタイプライブラリをインポートすることで利用可能になるが、インターフェイスの型やGUID等々は明確になっているので、わざわざExcelからタイプライブラリをインポートせずとも、同じ定義を自分で用意しても何ら問題ない。(そうすればExcelのないビルド環境でもビルドできるようになる。)また、RTDに必要なインターフェイスは2つだけである。IRtdServerインターフェイスIRtdServerが実装すべきRTDサーバーのインターフェイスである。IRTDUpdateEventインターフェイスIRTDUpdateEventは、データが更新されたことをExcelに通知するためのコールバック用のインターフェイスである。サーバー開始時にExcelからIRTDUpdateEventが実装されたCOMオブジェクトが渡されてくるので、これに対してコールバックを行う。(DISPIDなどは別のソースコードから検索してつけてみた。RTDまわりのことは、この人の記事だけで、ほとんど網羅できる。ServerTerminate();}VB6的なAutomation型らしさの色濃いSafeArray配列を多用したり、ref引数を使ったりとしていて、なかなかめんどくさそうな型定義になっている。以下、IRTDServerの実装方法を示す。プロジェクトの作成まずC#のクラスライブラリプロジェクトを作成する。ここでは「SeraRTDServerExample」というプロジェクトを作成するものとする。これは、ソリューションエクスプローラのプロジェクト名を右クリックしてプロパティを開き、アプリケーションタブのアセンブリ情報から「アセンブリをCOM参照可にする」をチェック状態にしておく。これでビルドされたDLLはCOMとして登録可能になる。IRTDServer実装コードIRTDServerは以下のクラスとして実装してみた。引数としてファイル名とフォーマットを渡すと...日付またはファイルサイズのいずれかを動的に返すもしくはフォルダ名を指定してファイル一覧を文字列として返すだけの単純なものとした。Desktopでビルド可能としている。ソース一式はgist:51b509592e0b20fc77612673d490f247にも置いている。ファイル名に対する更新日付を取得する。ファイル名に対するファイルサイズを取得する。サーバーから新しいトピックを追加します。関数を含む新しい数式を入力したときに呼び出されます。name="topicId">必ず指定します。トピックを識別する一意の値を指定します。よって割り当てられます。name="strings">必ず指定します。次元配列を指定します。name="newValues">必ず指定します。入力値がFALSEの場合はRTDセルを含むブックをロードしたなどでセル上に既存の値があることを示します。TRUE(非0)の場合はRTDセルを新規に入力したことを示します。返却値は、新規の値を使用するべきかを示し、FALSEの場合は既存のセルの値を変更しないことを示します。トピックは1個以上、最大253個までありえる。セルのコピー等を行った場合は本メソッドが重ねて呼び出されることはない。アプリケーションに、トピックが使用されなくなったことを通知します。name="topicId">必ず指定します。トピックに割り当てる一意の値を指定します。よって割り当てられます。サーバーがアクティブかどうかを判別します。<returns>ゼロまたは負の値は失敗を示し、正の値は、サーバーがアクティブであることを示します。新しいデータを取得するときに呼び出されます。name="topicCount">必ず指定します。値を、取得された配列内の要素数に変更する必要があります。サーバーがインスタンス化された直後に呼び出されます。name="callback">必ず指定します。オブジェクトを指定します。オブジェクトを指定します。正の値は起動が成功したことを示します。サーバーへの接続を終了します。name="member">関数名等。}}ビルド方法プロジェクトをビルドして生成されたdllは、COMとして登録する必要がある。(アセンブリ登録ツール)を使って登録する。regasm.exeは、DotNETフレームワークのフォルダ5にある。Tool」を使うとパスが通っている。『codebaseを指定する場合は署名をつけろ』とか警告がでるが、とりあえず登録はできる。オプションとしては以下のようなものがある。レジストリに書き込む内容をファイルとして出力する場合6今回はタイプライブラリの登録は必要ない。既存のインターフェイスIRTDServerに対する実装しか行っていないためである。(新しいインターフェイスは何も定義されておらず、tlbexpツールでtlbファイルをエクスポートしても中身の実体は何もないものとなる。)実行例たとえば、以下のようなシートを作成してみた。screencapture_excel_rtdこのRTDではディレクトリへのパスを指定して"list"フォーマットを指定すると、ファイルリストがタブ区切り文字列で返ってくるので、まず、これを配列に分解するための関数を作る。Functionとする。B2:B10のセルを選択した状態で以下の配列式を入力する。"")CTRL+SHIFT+ENTERで配列式を確定する。8これでリアルタイムでファイル一覧がB2:B10の範囲で表示されるようになる。次に、C2:C10のセルを選択した状態で以下の配列式を入力する。"",RTD("serartdsvr",,B2:B10,"size"),"")CTRL+SHIFT+ENTERで式を確定するとC2:C10の範囲でB2:C10のファイル名に対するファイルサイズがリアルタイムで表示されるようになる。自作のToArray関数にDebug.Printするような診断メソッドを仕込むと、更新のたびに呼び出されていることが分かると思う。ちなみに、ファイルの数が増えたら配列セルの範囲を超えた分は無視される。自動で拡張されたりしないので配列セルを手直ししなければならない。Windowsを入れてExcelの実行中のデバッグプリントをキャプチャすると、RTDのメソッドの呼ばれる順序が分かったりして面白い。new=パスの形式が無効です。thread=2(MTA)ちなみに、このログをみて気がついたが、topicId=3のfnameが空のセルに対してRTDを問い合わせていた。C列でIF関数をつかっているのが原因だろうと思う。ワークシート上のIFは「関数」なので、関数の呼び出し前に、関数の引数である、真・偽のいずれの場合の計算式も事前に評価せざるを得ない為だろう。以下、メソッドごとに説明する。SeraphyRTDServerクラスのヘッダ部SeraphyRTDServerクラスが、IRtdServerを実装している、本サンプルの本体クラスである。COMオブジェクトになるため、CLSIDとProgIDとして[Guid("5B6AA03F-F280-4B06-AFA3-519C9215D6BD")][ProgId("SeraRtdSvr")]のように指定している。CLSIDは全世界で一意になるべきものなので、本サンプルを真似して別のクラスを作る場合には、Guidは、それぞれ生成し直す必要がある。ProgIdはCOMオブジェクトの一般的な形式に従うと「ベンダ名.プログラム名.バージョン番号」のようなドット区切りで長い名前になるのが普通だが、今回は、あえて短くしている。これはRTD関数で指定する名前なので、ProgIDが長いと数式がめんどくさくなるからである。(短すぎてシステムの予約語や一般語と衝突すると、いろいろ問題がでるので注意のこと。開始処理ExcelからRTDサーバーの利用が開始されると、最初に呼び出される。複数ブックに同一RTDサーバへの接続がある場合は同じインスタンスが使われ、同じインスタンスでServerStartが二度呼び出されることはない。ここでExcel側からIRTDUpdateEventインターフェイスを実装したコールバック用オブジェクトが渡される。RTDサーバーは、このコールバック用オブジェクトを保存しておいて、監視している値のいずれかが変更された場合には、IRTDUpdateEvent#UpdateNotify()メソッドを呼び出してExcelに変更を通知する必要がある。変更有無チェックをUIスレッドで常時実行するとExcelがカクカクして操作性が悪化するため、監視処理はUIスレッドとは別のスレッドで行う必要がある。mSec単位で保持しているTopicIdの値に変更があるかチェックし、もし変更があればUpdateNotifyを呼び出すようにしている。Apartments)であり、ServerStartが呼ばれたときのスレッドと同じスレッドから呼び出されなければならない、ということである。C++ではCoMarshalInterThreadInterfaceInStream関数などを用いてスレッドをまたいだ場合のマーシャリングを行うが、C#の場合では、SynchronizationContextを使うことになるようである。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スレッド内で重い処理は行うべきではない。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で一括更新する、という実装もありえるだろう。Topicの削除ExcelがRTDセルを消して、同一の引数をもつRTDセルが1つもなくなった場合に呼び出される。ブックを閉じて監視する必要がなくなった場合にも通知される。以後、このTopicIdは未使用になる。Topicの状態の一括更新Excel側からTopicの状態を最新化するために呼び出される。これは一般的にはIRTDUpdateEvent#UpdateNotifyによってExcelに変更通知を行ったあと、Excelから呼び出されるものである。TopicIdごとの値を格納した二次元配列と、その配列の個数を返す。すべてのTopicを返す必要はなく、更新の必要のあるTopicIdと、そのデータだけ返せば良い。生存確認Excel側からRTDサーバーの生存確認のために定期的に呼び出される。正の値を返すことで生きていることを応答する。インプロセスのCOMサーバーの場合には、DLLが死んだときはExcelプロセスも死んだ時なので意味はないが、リモートPC上のDCOMや、EXEのCOMサーバーの場合には意味があると思われる。いつでも1を返しておけば良い。ちなみに0を返したりすると、RTDサーバーの再起動の確認ダイアログのようにExcelからお問い合わせが表示される。終了処理すべてのブックから、このRTDサーバーの持つ全てのTopicIDが削除されるか、すべてのブックが閉じられて、TopicIDが1つも必要としなくなったときに呼び出される。本サンプルでは無限ループタスクに対してキャンセル指示を出してタスクの終了を行っている。ガベージ処理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を表現できる。}のようにしてエラー値を返却してみたところ、Excel側ではRTDからエラー値を受け取ると、セルの値を更新せず無視するようである。(#N/Aにもならない。そもそも更新されない。)とりあえずC#からVT_ERRORを返す仕組みはわかったので、この線でRTDまわりをGoogle検索したところ、興味深い投稿を見つけた。https://groups.google.com/forum/#!msg/exceldna/Z6mmxJ4LSbM/ryiWJDjhu90Jどうやら、特定の値を返せば、Excelのセル上でエラーを表現できるらしい。「#N/A」と表示されるうまくいった。まとめ今回試作してみた感触では、C#でExcelのRTDサーバーを作るのは、とくに難しいところもなかった。C++よりも容易に作れそうな感じである。Excelでリアルタイムデータを引くことの意義は、それなりの有益なデータソースがあってこそ、と思うのだが、最近はラズパイやアルデイーノといったDIY感覚でネットワーク接続できるセンサー類が手に入りやすくなってきたので、むしろ、そっち方面での技術力なり発想力が求められているかもしれない。参考文献/URLkennykerrさんのところのブログRTDに関する詳細が揃っている。Interfacesマイクロソフト公式MSの記事、VB6を使うけれどRTDの作り方の基本が分かる。アプリケーション間のデータ交換としてDDEを使うことはなくなったが、Explorerがアプリを起動する際に、すでにアプリが起動している場合は新たにEXEを立ち上げるのではなく既存のEXEに対してドキュメントを開くように依頼する、というようなケースで現在でも使われている。↩どうしてもDotNETとDDEの両方を使いたいのであれば、C++/CLIを使えばどうとでもなると思う。サードパーティのライブラリとしてはNDdeというのもあるようだ。↩リモートでCOMを扱うDCOMに対して、リモートでDDEを扱うNetDDEという仕様もあったが、NetDDEはVista以降は廃止されている。IRTDUpdateEventインターフェイスは、ComImportとして型(vtbl)を合わせているだけであり、Excel側も、IRTDServerにはIDispatchは使っていないか、ちゃんと名前で索引しているようでDISPIDの値はどうでもいいらしいのだが、生成されたSeraphyRTDServerに対してIDispatch#GetIDsOfNamesで名前からDISPIDを取り出したときに、本来の定義されたDISPIDと異なる値が返ってくるのは気持ち悪いので一応、正しいものを返せるようにしておく。↩たとえば、"C:\Windows\Microsoft.NET\Framework\v4.0.30319"のような場所にある。↩ただしタイプライブラリは除く。必要なら、タイプライプラリ用のレジストリエントリは出力されないので手動で作成する必要がある。↩RTDサーバから直接、配列を返したかったのだが、方法がみつからなかった。確定すると式の見た目は{}で囲まれ、配列式であることを示す。配列式にした範囲では一括して編集・削除しなければならない。配列式の範囲が分からない場合はCTRL+Gで「ジャンプ」ダイアログを開き、「セル選択」ボタンから「アクティブセルの配列」を選ぶと、配列式の入ったセル範囲をまるごと選択してくれる。(1)はてなブログをはじめよう!seraphyさんは、はてなブログを使っています。ブログを報告するスターをつけました引用をストックしましたストック一覧を見る閉じる引用するにはまずログインしてくださいログイン閉じる引用をストックできませんでした。再度お試しください閉じる限定公開記事のため引用できません。