[C#]Excel VBA で jQuery のようにアクセスできるC#ライブラリを作る

投稿日: 2012/09/13 作成者: masuda

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 を使っている感じに、なるかもしれない。とかとか。

カテゴリー: C#, Excel VBA パーマリンク

[C#]Excel VBA で jQuery のようにアクセスできるC#ライブラリを作る への3件のフィードバック

  1. ピンバック: 9月13日の注目記事 | Javable.Jp

  2. 通りすがり のコメント:
    2013/09/27 2:17 am

    １年以上前の記事にコメントもなんですが。。。
    「■Excel VBA から使う COM 作成のコツ」の１行目、「Excel VBA の「integer」は、C# の Int64/short にあたります。16ビットの数値なんですね。」

    Int64/shortではなく、Int16/shortですね＾＾

    • masuda のコメント:
      2013/10/03 11:39 am

      さいです。「Int16/short」ですね。
      Int64だと「なんて先進的なVBAッ!!!」ってことに。

コメントは停止中です。