Excel VBA で jQuery のようにアクセスできるライブラリを作れるか? | Moonmile Solutions Blog
http://www.moonmile.net/blog/archives/3217
なところの続き。と言うか、あのときは Excel VBA 内に閉じてないと使いづらいのでは?と思っていたのですが、Excel VBA から COM ライブラリを呼び出す、という方法でもいいことに気づきました。そこで、C# の相互運用(COMアクセス)を利用して、Excel を jQuery 風に使うライブラリ作成の続きおば。
■用法
こんなコードを書いて実行をすると、
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | Sub test() ' 初期化 Dim obj As New ExQuery.Query obj.SetApplication Excel.Application obj.Cell( "A1" ).Text = "最初" obj.Cell( "A2:B10" ).Text = "埋める" ' 背景色を赤に設定 obj.Cell( "A2" ).CSS( "background-color" ) = RGB(255, 0, 0) ' 文字色と背景色を変える Dim v As Long v = RGB(0, 0, 255) With obj.Cell( "A3" ) '.CSS("color") = RGB(255, 255, 255) .CSS( "color" ) = "#FF00000" .CSS( "background-color" ) = RGB(0, 0, 255) End With End Sub |
以下のように Excel のシートを操作できます。
先行きは表を簡単に作るとか、検索を楽にするとかを実装したいですね。
■C#のソースコード
面倒なので全文を晒しておきます。プロジェクトを作る時に、
- Visual Studio 2010 は管理者モードで起動する
- Microsoft Excel を参照設定する。
- プロジェクトのプロパティで「COM 相互運用機能の登録」をチェックする
を忘れずに。管理者モードのほうは、COM をレジストリに登録するの必要です。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 | using System; using System.Collections.Generic; using System.Linq; using System.Text; using Excel = Microsoft.Office.Interop.Excel; using System.Runtime.InteropServices; namespace Moonmile.ExQuery { [ClassInterfaceAttribute(ClassInterfaceType.AutoDual)] [ComVisible( true )] public class Query { private Excel.Application _app; private Excel.Workbook _book; private Excel.Worksheet _sheet; private Excel.Range _sel; /// /// Initalize Excel.Application object /// public Excel.Application Application { get { return _app; } set { _app = value; _book = _app.ActiveWorkbook; _sheet = _app.ActiveSheet; _sel = _app.Selection; } } public void SetApplication(Excel.Application app) { this .Application = app; } public Excel.Workbook Book { get { return _book; } set { _book = value; _sheet = _app.ActiveSheet; _sel = _app.Selection; } } public Excel.Worksheet Sheet { get { return _sheet; } set { _sheet = value; _sel = _app.Selection; } } public Excel.Range Selection { get { return _sel; } set { _sel = value; } } /// /// default constructor /// public Query() { } public ExRange Cell( object row1, object col1 = null , object row2 = null , object col2 = null ) { if (row1 == null ) return new ExRange(); var s = row1 as string ; if (s != null ) return Cell(row1.ToString()); if (row2 == null ) return Cell( int .Parse(row1.ToString()), int .Parse(col1.ToString())); return Cell( int .Parse(row1.ToString()), int .Parse(col1.ToString()), int .Parse(row2.ToString()), int .Parse(col2.ToString())); } /// /// pattern: /// Cell("A1") /// Cell("A1:B10") /// Cell("#id") /// /// /// protected ExRange Cell( string s) { Excel.Range rg; if (s.StartsWith( "#" )) rg = _sheet.get_Range(s.Substring(1)); else if (s.StartsWith( "." )) rg = _sheet.get_Range(s.Substring(1)); else rg = _sheet.get_Range(s); return new ExRange(rg); } /// /// pattern: /// Cell(1,2) /// /// /// /// protected ExRange Cell( int row, int col) { if (_sheet == null ) return new ExRange(); var rg = _sheet.Cells[row, col]; return new ExRange(rg); } /// /// pattern: /// Cell(1,2,3,4) /// /// /// /// /// /// protected ExRange Cell( int row1, int col1, int row2, int col2) { if (_sheet == null ) return new ExRange(); var rg = _sheet.Range[ _sheet.Cells[row1, col1], _sheet.Cells[row2, col2]]; return new ExRange(rg); } } [ClassInterfaceAttribute(ClassInterfaceType.AutoDual)] [ComVisible( true )] public class ExRange { private Excel.Range _range ; private CSS _css; protected internal ExRange() { } public ExRange(Excel.Range rg) { _range = rg; _css = new CSS(_range); } public string Text { get { return _range.Value; } set { _range.Value = value; } } public CSS css { get { return _css; } } } [ClassInterfaceAttribute(ClassInterfaceType.AutoDual)] [ComVisible( true )] public class CSS { private Excel.Range _range; protected internal CSS() { } protected internal CSS(Excel.Range rg) { _range = rg; } public string this [ string key] { get { switch (key.ToLower()) { case "color" : return _range.Font.Color; case "background-color" : return _range.Interior.Color; } return "0" ; } set { if (value.StartsWith( "#" )) { int r = Convert.ToInt32(value.Substring(1, 2), 16); int g = Convert.ToInt32(value.Substring(1, 4), 16); int b = Convert.ToInt32(value.Substring(1, 6), 16); value = (r + g * 0x100 + b * 0x10000).ToString(); } switch (key.ToLower()) { case "color" : _range.Font.Color = value; break ; case "background-color" : _range.Interior.Color = value; break ; } } } } } |
■Excel VBA から使う COM 作成のコツ
いくつか引っ掛かるところを書き下しておきます。
Excel VBA の「integer」は、C# の Int64/short Int16/short にあたります。16ビットの数値なんですね。なので、COM のメソッドの引数は、short にしておく必要があります。そうしないと、Excel VBA から COM に値を引き渡すときにエラーになります。
Cell メソッドの引数で object を使っていますが、VBA の Variant は、object でしか取れないようです。
最初は、Cell メソッドを多重定義して公開していたのですが、VBA 側で、Cell/Cell_2/Cell_3 と名前を変えられてしまうので、デフォルト値を付けて同じ名前で実行できるように変えました。
Excel VBA のインテリセンスを利用するために [ClassInterfaceAttribute(ClassInterfaceType.AutoDual)] という属性をつけます。実は AutoDual を使うと、COM 側のインターフェースが変わる(メソッドの順番が変わるなど)たびに、VB 側のビルドが必要になるのですが、今回は Excel VBA を対象にするのでこのまま使っています。Excel VBA の場合はインタープリタ的に COM を読み込むので、タイプライブラリを起動時に読み込んでくれるためです。このあたりは、以下のサイトを参考にしてください。
.NETコンポーネントをVB6から使用するための方法
http://www.sev.or.jp/ijupiter/world/dc_interrop/dotnet_com_interrop.html
Visual Basic 6.0 から Visual Basic .NET または Visual Basic 2005 アセンブリを呼び出す方法
http://support.microsoft.com/default.aspx?scid=kb;ja;817248
ClassInterfaceType 列挙体 (System.Runtime.InteropServices)
http://msdn.microsoft.com/ja-jp/library/system.runtime.interopservices.classinterfacetype(v=vs.110).aspx
■今後の予定?
css メソッドをちまちまと実装して、Excel VBA の複雑な UI を楽にアクセスできるようになると良いかも。
あと、C# で書いたので内部的に LINQ が使えますよね。Where/Select メソッド等を適当に公開してやれば、Excel VBA で LINQ を使っている感じに、なるかもしれない。とかとか。
ピンバック: 9月13日の注目記事 | Javable.Jp
1年以上前の記事にコメントもなんですが。。。
「■Excel VBA から使う COM 作成のコツ」の1行目、「Excel VBA の「integer」は、C# の Int64/short にあたります。16ビットの数値なんですね。」
Int64/shortではなく、Int16/shortですね^^
さいです。「Int16/short」ですね。
Int64だと「なんて先進的なVBAッ!!!」ってことに。