WindowsでExcelをいじくり倒すコードを書く最適解を探す旅路
複数エクセルの大量データをプログラムでいじくる。
要件
- WindowsでExcelはじめとする様々な操作を行いたい。
- Excelで数百-数千行の配列をいくつも扱いたい
- windowsで、exeとしてまとまった形で実行したい
- GUIがほしい
- 実行中にその動作を監視したいが、プロセスがとられて動かなくなるのはもちろん嫌だ。
要するにExcelなどのコントロールを行いながら、
「ぱっとみ普通のアプリケーションにしたい」
という要件を叶えるためにgoogleさんにお世話になりまくったが、どこにも最適解だと思われるものは載っておらず、大変だったのでメモっておく。
僕のやり方が本当に最適解か、というのはわからないけど、こういったテキストで助かる人がいればと思い、せめて。
解法1 VBA
VBAとはExcelに付属のマクロで、Visual Basic for Applicationの略である。
単にある領域をコピーで持ってきたりとか、ある領域を削除したりとか、ならこれでいいと思われる。
もしこれで何とかなるのなら、わざわざ実行ファイルを作ったり、GUIのあるアプリケーションを作ったりする必要などない。
- 配列操作が駄目
しかし、このVBA、致命的に配列操作が駄目である。
collectionというものを使えば多少マシにはなるものの、たとえばrubyで書くと以下のような操作に膨大な記述を必要とする。
result = {[1],[2],[3],[4]}.select{|t| t[0] > 2}
そう、二次配列から中身の一次配列を取り出して、新しい二次配列としてつめるだけ。
これはデータの精査には必須なのだが、これで心が折れた。
VBA的最適なやり方はおそらく、そもそもExcelの機能を用いて演算することであると思う。
すなわち「find」とかで行い、配列の機能とかを使わない、ということだと思う。
しかし何回もselectしたい場合はどうすればいいのだろうか。
心がぼきっと折れてしまう。
- 連想配列がない
そもそも基本の言語機能として、なんと連想配列がない。
マジか、と声を出したのはいうまでもない。
「Scripting.Dictionary」を使えば、使える。
また、これを使わなくても、もしくはMac環境などで、使えなくても、
http://www.ozgrid.com/forum/showthread.php?t=159428
でMacのユーザー用にcollectionを用いたdictionaryの実装がある。
これをclass1としてモジュールとしていれ、連想配列は使える。
Dim hash As Class1
Set hash = new Class1
hash.add key, value
しかし、大変なことに変わりはなかった。
解法2 Ruby
ある日思い至る。rubyを使えばいいじゃない。と。
そしてrubyをexeにまとめればいいじゃない。と。
しかし、結論から言って、Rubyは駄目だった。
その理由は、エクセルがきちんと扱えないことである。
rubyでは、win32oleというRubyの機能を用いて、Excelへのアクセスを行う。
しかし大量の配列、連想配列(といっても1000程度)を扱い、何度も開いたり閉じたりすると、RPCサーバーが応答しなくなる。
RPCサーバーとは、 Remote Procedure Call の略。
調べたところポートが占有されて使用できなくなる不具合のようだった。
エラー番号で調べていくと、なぜかwindowsXPで解決パッチが配布されている問題に行き当たった。
そもそもRubyのヴァージョンが1.8.7という、ほぼほぼ化石であったのが原因だと予想された。
"exe化"して簡単に使いたかったので、exerbを使おうと思っていたが、その最新の実装が、ruby1.8.7までの対応だった。
もっと簡単に出来ないのだろうか、そして、終に。
解法3 JScript + ActiveXObject
やっとここにたどり着いた。
これが、ひとつの最適解だ。
JScriptはJavascriptにActiveXの操作オブジェクトを加えたものである。
僕も本当に驚いたのだが、「ほどんどなんでも」出来る。
いつぞやは馬鹿にしていて本当にすまない気持ちでいっぱいである。
JScriptのおかげで、windowsとも親友になれそうな気がする。
僕の視野は狭かったと実感できた。
配列？連想配列？クロージャ？なんでもOK。
だってjavascriptですから。
Excelを開いた後、中身のActiveXObjectの走査が、Enumeratorを作成してやらないといけない( for - in が使えない)くらいが面倒だが、ラッパー関数を作成すればいいだけだった。
死ぬほど楽であった。
死ぬほど簡単であった。
死ぬほど楽しかった。
しかもActiveXObjectを用いたエクセル操作、まったく落ちたりしない。
しかも早い。
作成の仕方は他のページへ検索してほしいが、
hoge.js として保存して、powershellで、
cscript hoge.js
とやるだけである。
なにこれ楽しい。
解法4 HTA
GUIがほしい。
JScriptで使えるGUIツールが。
そしてHTAである。
htaとは、htmlのファイルの拡張子を、htaにするだけである。
するとあら不思議、普通のアプリケーションのように起動、動作する。
詳しくはこちらの方のhtaのページを見てほしい。
まさにこれは
「なんじゃあこりゃああああ」
状態である。
しかもhta(html)の中で普通にJScriptを呼び出して、ActiveXObjectでファイル操作もお手の物である。
こんなに簡単でいいのか。
解法5 JScript + WSH + HTA
HTAにも弱点がある。
それは単一プロセスであることである。
非常に長いパッチを流す際、同期処理で行うと画面がフリーズしてしまう。
せっかくGUIを得たのだから、非同期処理で、バックグラウンドで流し、結果を監視したい。
これには、WSHのrunメソッドで対応可能だ。
JScriptで WSH の shell を呼び出し、そのrunメソッドで、処理を走らせると、非同期になる。
ちなみにJScriptはexeに出来るツールがある。
HTAは色々調べたけれど、簡単にはいかないようだ。
最適解
JScript(exe) -> runでhta を呼び出し -> その中で非同期処理をrunで呼び出し -> ファイルに結果書き出し -> htaからファイルを監視
これでめでたく、どんな処理も非同期で、
ぱっと見アプリケーションで、
Excelがコントロールできる、
アプリを作成することが出来ました。
具体的なコードについて書きました。