Excel-DNA - FILES=0

ちょっと複雑な処理をVBAで書くとなると、言語やIDEの古さから開発効率が悪くなりがちです。

VS+C#でソース管理もしながら開発したいもんです。

そういった場合、選択肢としてはExcel-DNAとかVSTOとかあるみたいなんですが、こことかこことか見ると、Excel-DNAが良さげな感じがします。

てことで試してみます。環境はVS2015 + Office2013。

Excel-DNAのGetting Startedを参考に簡単なUser Defined Functions (UDF)を作ってみます。

プロジェクト作成

VSでVisualC#->Windows->Class library

名前は”MyExcelFunctions”としました。

(ソース管理はGit)

Excel-DNA追加

Tools->NuGet Package Manager->Package Manager Consoleで

1

PM> Install-Package Excel-DNA

で必要なファイルがプロジェクトに追加されます。

UDF作成

既存のClass1.csを削除して、MyFunctions.csを追加。

こんな風に書きます(コピペコードです)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


using ExcelDna.Integration;

namespace MyExcelFunctions
{
    public static class MyFunctions
    {
        [ExcelFunction(Description = "My first .NET function")]
        public static string SayHello(string name) {
            return "Hello " + name;
        }
    }
}

デバッグ実行するとExcelが起動するんで、適当なセルに

1

=SayHello("World!")

って入れて動作確認します。

関数ウイザードにも出ますね。

配布

Excelアドインの配布にはいろいろ苦労が付きまとうようですが、個人的に使う分にはbuildで作成される「プロジェクト名-AddIn-packed.xll」ファイルをExcelアドインとして登録してあげれば良いみたいです。

Rangeを渡す

UDFにExcelのRangeを”参照”で渡すのにはちょっと工夫がいるようです。

こちらに書いてあるんですが、VBなんでC#で書いてみます。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


[ExcelFunction(Description = "Get range address",IsMacroType =true)]
        public static string GetAddress([ExcelArgument(AllowReference =true)]object arg) {
            var address_builder = new StringBuilder();
            var range = (ExcelReference)arg;
            foreach (var cell in range.InnerReferences) {
                string ref_text = XlCall.Excel(XlCall.xlfReftext, cell, true).ToString();
                address_builder.Append(",");
                address_builder.Append(ref_text.Substring(ref_text.IndexOf("!")+1));
            }
            return address_builder.ToString().Substring(1); //remove first ','
        }

肝はパラメータのAttributeで[ExcelArgument(AllowReference =true)]を指定してやるところでしょうか。

もうちょっと使えるUDF

あいさつとかアドレスとか実用的ではないんで、もうちょっと使える例として、指定範囲で平均値を出すようなUDFを作ってみます。

移動平均の積算範囲を動的に変えるような使用法用を想定しています。

C#なコードはこれ↓

 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


/// <summary>
        /// 積算範囲
        /// </summary>
        private enum XLAverageShift
        {
            xlPrev=0,
            xlCenter = 1,
            xlNext = 2,
        };
        /// <summary>
        /// データ向き
        /// </summary>
        private enum XLDirection
        {
            xlVertical = 0,
            xlHorizontal = 1,
        }

        [ExcelFunction(Description = "Get Average", IsMacroType = true)]
        public static double AverageVRange([ExcelArgument(AllowReference = true, Description = "Data Range", Name = "arg")]object baseCell
                                                , [ExcelArgument(Description = "length of summary", Name = "length")] int length
                                                , [ExcelArgument(Description = "shift to summary 0:prev, 1: center, 2:next", Name = "shift")] int shift = 0
        ) {
            return AverageByRange(baseCell, length, (XLAverageShift)shift, XLDirection.xlVertical);
        }

        [ExcelFunction(Description = "Get Average", IsMacroType = true)]
        public static double AverageHRange([ExcelArgument(AllowReference = true, Description = "Data Range", Name = "arg")]object baseCell
                                        , [ExcelArgument(Description = "length of summary", Name = "length")] int length
                                        , [ExcelArgument(Description = "shift to summary 0:prev, 1: center, 2:next", Name = "shift")] int shift = 0
        ) {
            return AverageByRange(baseCell, length, (XLAverageShift)shift, XLDirection.xlHorizontal);
        }

        private static double AverageByRange(object baseCell, int length, XLAverageShift shift, XLDirection direction) {
            var range = (ExcelReference)baseCell;   //基準となるセル
            // shiftからデータ開始位置のオフセットを取得
            int offset = 0;
            if (shift == XLAverageShift.xlPrev) { //前
                offset = -length + 1;
            } else if (shift == XLAverageShift.xlCenter) {   //真ん中
                offset = -(length / 2);
            } else if (shift == XLAverageShift.xlNext) {   //後ろ
                offset = 0;
            } else {
                throw new System.ArgumentException();   //えらー
            }

            // sum
            double result = 0;  //合計
            double data_length = 0; //データ個数
            for (int i = 0; i < length; i++) {
                var row = direction == XLDirection.xlVertical ? range.RowFirst + i + offset : range.RowFirst;
                var col = direction == XLDirection.xlHorizontal ? range.ColumnFirst + i + offset : range.ColumnFirst;
                if (row >= 0 && col >= 0) { //有効範囲？
                    var cell = new ExcelReference(row, col);
                    var val = cell.GetValue();
                    if (val is double) { //数値？
                        result += (double)val;
                        data_length++;
                    }
                }
            }
            return result / data_length;    //平均値計算
        }

AverageVRangeとAverageHRangeがExcelに公開する関数でデータの積算方向が違います。

AverageByRangeが処理本体。

enumは一応使ってるだけでstaticでもＯＫかと。Excel側でenumが効けばいいんですけどねー。

使用例はこんな感じ。わかりずれー。

この例では10区間分の移動平均をグラフに出しています。C1セルの値を変えると平均区間数が動的に変更できます。

式はこんな感じ

1

=AverageVRange(B5,C$1,C$2)

ちなみにデータは去年の日経平均終値（せめて2万台まで回復しねーかなー）。