読者です 読者をやめる 読者になる 読者になる

黙々とC#

"In a mad world of VBA, only the mad are sane" 『VBAという名の狂った世界で狂っているというのなら私の気は確かだ』


Excel C# Script入門講座へようこそ!

『全てのVBAコードを生まれる前に消し去りたい。
 すべての宇宙、過去と未来の全てのVBAを、この手で』

f:id:d_ymkw:20160713164718p:plain

このページはExcel VBAを実務で利用されている方で、VBAに心の底から辟易しており、現代的なプログラミング言語(C#6.0)を使って、Excelファイルから分離されたコードで、お手軽にExcel操作を行いたいという方を対象としています。

このページでは、Excelの操作には、VBAに代えてC# Scriptを使います。C# Scriptを実行するにあたっては、C# スクリプト実行エンジンとしてRoslyn for Scripting C#を組み込んだExcel VSTOアドイン "ExcelCSX" を用いています。

このページを通じて、忌まわしきExcel VBAと決別し、より便利で快適なC#スクリプトの魅力を体験して頂ければ幸いです。

  • Excel C# Script入門講座
    • Excel C# Script をはじめる前に
    • セルの操作
    • ワークシートの操作
    • ワークブックの操作
    • プログラムの基礎
    • プログラム制御文
    • Excel C# Script でよく使うメソッド
    • C# Script ポケットリファレンス
続きを読む

オブジェクトとプロパティ、メソッド

本記事は、Excel C# Script入門講座の1記事です。

オブジェクトとプロパティ、メソッド

C# ScriptでExcelを操作するにあたっては、コード上で

  • 操作対象とするモノ
  • 操作内容

の2つを指定します。

前者については、Excelのワークブック(Workbook)やワークシート(Worksheet)、セル(Cell)などが操作対象となります。C# Script(コード)上で、これらの操作対象物を特定するものをオブジェクトと呼びます。*1

Excelにおいては、Excelのプロセス自身を表す Excel.Applicationオブジェクトを最上位として、当該プロセスで開いたWorkbookや、開かれたWorkbookに含まれるWorksheetが階層構造を構成しつつ保持されています。

Excel.Application
└ Excel.Application.Workbooks("****")
   └ Excel.Application.Workbooks("****").Worksheet
       └ Excel.Application.Workbooks("****").Worksheet("*****").Cells(1,1)

なお、ExcelCSXを用いる場合、現在実行中のExcelを示すExcel.Applicationオブジェクトは、変数名Excelでアクセスすることができます。

これらの各オブジェクトには、各種操作を行うためのプロパティメソッドが備えられています。

プロパティは、値を代入することで操作対象のオブジェクトの見た目や振る舞いを変更することができます。なお、プロパティの中には、値を代入することはできず、読み取り専用のプロパティも存在します。

例えば、セルの値を変更したい場合、以下のように、特定のWorksheet上のセルを示すRangeオブジェクトのValueプロパティに値を設定します。

Excel.ActiveWorkbook.Worksheets("Sheet1").Range("A1").Value = "C#";

メソッドは、操作対象のオブジェクトに対して何らかの動作をさせることができます。

例えば、特定のセルを選択(アクティブ)にする場合、以下のように、RangeオブジェクトのSelectメソッドを実行します。

Excel.ActiveWorkbook.Worksheets("Sheet1").Range("A1").Select();

操作したい内容に相当する プロパティメソッドについては種類が多いため、適宜MSDNや巷のWebページで調べる必要があります。また、Excel.Applicationオブジェクトの扱い方はコーディング記法に差こそあれ、VBAから利用する場合でも同様のため、多少の読み替えでVBAのページを参考にすることも可能です。

本連載では以降のページで、

  • 操作対象を示すオブジェクトの名称・指定方法
  • 各オブジェクトに用意された プロパティメソッド の名称・利用方法

について、基本的なものを網羅的に説明します。

*1:より厳密にはExcel.Applicationに関連するクラスのインスタンスや、インターフェースを実装したインスタンスをオブジェクトと呼びます。

C# Scriptの入力と実行

本記事は、Excel C# Script入門講座の1記事です。

C# Scriptの入力と実行

実際にC# Scriptを入力して、実行するまでを簡単に説明します。

C# Scriptを保存するフォルダの指定

f:id:d_ymkw:20160808113855p:plain

まず、Select Folderをクリックして、C# Scriptが保存されている/これから保存するフォルダを指定してください。

f:id:d_ymkw:20160808114006p:plain

選択したフォルダ内に既にC# Scriptファイル(*.csx)が存在する場合には、ドロップダウンボックスにファイル名が表示されます。

C# Scriptの新規作成

ここでは、新規にC# Scriptを入力してみます。

NewScriptボタンをクリックします。

エディタ(この場合、VisualStudioCode)が自動的に立ち上がります。

とりあえず、以下のコードを入力してください。

Excel.ActiveWorkbook.ActiveSheet.Range("A1").Value = "Hello C# Script";

f:id:d_ymkw:20160808114245p:plain

"hello.csx"という名前で保存します。

C# Scriptの編集

この記事では取り上げませんが、既に存在する*.csxを編集したい場合は、ドロップダウンボックスで選択した後、Editボタンをクリックするとファイルを選択した状態でエディタが立ち上がります。*1

C# Scriptの実行

Reloadをクリックした後*2

Script File:のドロップダウンボックスで"hello.csx"を選択します。

Runボタンをクリックすると、スクリプトが実行されます。

f:id:d_ymkw:20160808114511p:plain

Excelを立ち上げてから初回の実行のみ若干実行までに時間がかかります。スクリプト実行中は、Runボタンはグレーアウトされ、スクリプトの実行が終了すると、水色に戻ります。

*1:厳密には、単にOptionsで選択したエディタを、ファイル名を引数で渡して実行するだけです。

*2:Reloadボタンをクリックすると、SelectFolderで選択されたフォルダ内の*.csxを読み込み、ドロップダウンボックスに一覧表示します。

ExcelCSXの導入

本記事は、Excel C# Script入門講座の1記事です。

ExcelCSXの導入

インストールに必要な要件

  • OSがWindows7 以降であること
  • .NET Framework 4.6 がインストールされていること
  • Excel2007 以降 がインストールされていること

インストール手順

githubで公開されているExcelCSX のレポジトリのreleasesページを開きます。

ExcelCSXのパッケージ(zipファイル)をダウンロードします。

ダウンロードしたファイルを展開します。

f:id:d_ymkw:20160808111728p:plain

フォルダの中にある"ExcelCSX.vsto"を実行します。

f:id:d_ymkw:20160808111936p:plain

警告画面が表示されますが、そのままインストールしてください。

f:id:d_ymkw:20160808112010p:plain

正常にインストールされると上記画面が表示されます。

最初にすべきこと

EXcelCSX導入後に、Excelを起動するとアドインタブに以下の機能が追加されています。

f:id:d_ymkw:20160808112310p:plain

(インストールしたにも関わらず表示されない場合は、「リボンのユーザー設定」から「アドイン」タブのチェックをON/OFFすると表示される場合があります。)

「Options」ボタンをクリックし、設定画面を表示します。

f:id:d_ymkw:20160808112550p:plain

"Editor Path" に、C# Scriptの編集に使いたいエディタを指定してください。

(デフォルトではVisual Studio Codeのパスを指定しています。)

オススメのエディタはVisual Studio Codeです。

C# エクステンションを入れると、C# Scriptファイル(*.csx)を開いた際に、ハイライトが有効になるほか、一部の補完機能・入力支援機能が使えるようになります。素のテキストエディタに比べてぐっと書きやすくなります。

"Default Folder Path"は、起動時にデフォルトで読み込まれるフォルダを指定します。C# Scriptを保存するフォルダを指定すると良いです。

オートフィルタ

本記事は、Excel C# Script入門講座の1記事です。

オートフィルタ

Excelのオートフィルタ機能を利用して、データを抽出するには、データの抽出元を示すRangeオブジェクト(*)を用意し、当該RangeオブジェクトのAutoFilterメソッドを使用します。引数により抽出条件を設定できます。

(*) Rangeオブジェクトは、データの抽出範囲全体を示すものでなくても、Excelが自動的にフィルタの対象範囲を判定します。

AutoFilterメソッドの構文

(Rangeオブジェクト).AutoFilter(Field, Criteria1, Operator, Criteria2, VisibleDropDown )

AutoFilterメソッドの戻り値

何かの値が戻ってくるが、使うことはないと思われる。(未確認)

AutoFilterメソッドの引数

引数名 必須 / オプション 内容
Field 省略可能 フィルターの対象となるフィールド番号を指定します。
Criteria1 省略可能 抽出条件となる文字列を指定します。
Operator 省略可能 フィルターの種類を指定します。
Criteria2 省略可能 2番目の抽出条件となる文字列を指定します。
VisibleDropDown 省略可能 オートフィルタのドロップダウン矢印(▼)を表示するか否かを指定します。
引数Field

フィールド番号は、リストの左側から何番目かを指定します。

つまり、最も左側にあるフィールドはフィールド番号 1 になります。

引数Criteria1, Criteria2

通常、抽出条件とする文字列(例えば「りんご」)を指定します。

"=" と指定すると、空白セルが抽出され、"<>" と指定すると空白以外のフィールドが抽出されます。

"A"と指定すると、Aに後方一致する場合、"A"と指定すると、Aに前方一致する場合、"A"と指定すると、Aに部分一致する場合が抽出されます。

また、数値の場合は、不等号(">"や"<", "<=", ">=")を先頭に付けて範囲指定することができます。

この引数を省略すると、抽出条件は All になります。

引数 Operator に xlTop10Items が指定されている場合は、引数 Criteria1 に項目数を指定します (たとえば "10")。

なお、Criteria2 を指定できるか否かは、引数Operatorに何を指定するかで決まります。

引数Operator

XlAutoFilterOperator 列挙型を指定します。

名前 説明
xlAnd 1 Criteria1 とCriteria2 の論理演算子 AND
xlBottom10Items 4 表示される最低値項目 (Criteria1 で指定される項目数)
xlBottom10Percent 6 表示される最低値項目 (Criteria1 で指定される割合)
xlFilterCellColor 8 セルの色
xlFilterDynamic 11 日付の範囲を指定したい場合。Criteria1には XlDynamicFilterCriteria列挙型を指定する。
xlFilterFontColor 9 フォントの色
xlFilterIcon 10 フィルター アイコン
xlFilterValues 7 Criteria1 を配列で指定する場合に指定する。なお、日付を配列で指定したい場合は、Criteria2を指定する。
xlOr 2 Criteria1 またはCriteria2 の論理演算子 OR
xlTop10Items 3 表示される最高値項目 (Criteria1 で指定される項目数)
xlTop10Percent 5 表示される最高値項目 (Criteria1 で指定される割合)
引数VisibleDropDown

trueまたはfalseを指定します。

trueを指定すると、オートフィルタ処理を実行した時にドロップダウン矢印が表示されます。

デフォルト値はtrueです。

全ての引数を省略した場合

フィルタ処理は行わず、オートフィルタのドロップダウン矢印を表示します。

サンプルコード

//Sheet1のA1セルを含む適当な範囲にオートフィルタを設定(ドロップダウン矢印を表示)する。
Excel.Worksheets("Sheet1").Cells(1, 1).AutoFilter();

f:id:d_ymkw:20160805201448p:plain

//フィールド番号5(E列)の値が50以下のデータを抽出
Excel.Worksheets("Sheet1").Cells(1, 1).AutoFilter(Field:5, Criteria1:"<20");
//フィールド番号5(E列)の中から40以上60未満の条件に一致するデータを抽出
var range = Excel.Worksheets("Sheet1").Cells(1, 1);
range.AutoFilter(Field:5, Criteria1:"<60", Operator:1, Criteria2:">=40");

f:id:d_ymkw:20160805201550p:plain

以下の例では、抽出条件を3つ以上指定しています。この場合、引数Operatorに XlFilterValue(7)を指定し、Criteria1に配列を指定します。なお、XlFilterValueはExcel2007以降でのみ利用可能です。

//フィールド番号8(H列)の中から、愛媛県、岡山県、茨城県のいずれかに一致するデータを抽出
var range = Excel.Worksheets("Sheet1").Cells(1, 1);
range.AutoFilter(Field:8, Criteria1:new []{"愛媛県","岡山県","茨城県"}, Operator:7);

f:id:d_ymkw:20160805202155p:plain

ソート

本記事は、Excel C# Script入門講座の1記事です。

ソート

Excelのソート機能を利用するには、ソートしたい範囲を示すRangeオブジェクトを用意し、当該RangeオブジェクトのSortメソッドを使用します。引数により昇順、降順などを指定できます。

※ Excel2007以降では、Sortメソッドに代えて、RangeオブジェクトにSortオブジェクトが用意されています。Sortオブジェクトの方が、オプションが豊富に用意されています。

Sortメソッドの構文

(Rangeオブジェクト).Sort(Key1, Order1, Key2, Type, Order2, Key3, Order3, Header, OrderCustom, MatchCase, Orientation, SortMethod, DataOption1, DataOption2, DataOption3)

Sortメソッドの戻り値

何かが返ってくるが、使うことはないと思われる(未調査)。

Sortメソッドの引数

引数名 必須 / オプション 内容
Key1 省略可能 最初の並べ替えフィールドを指定します。
Order1 省略可能 Key1で指定した値の並び替え順序を指定します。
Key2 省略可能 2番目の並べ替えフィールドを指定します。
Type 省略可能 並べ替え基準にする要素を指定します。
Order2 省略可能 Key2で指定した値の並び替え順序を指定します。
Key3 省略可能 3番目の並べ替えフィールドを指定します。
Order3 省略可能 Key3で指定した値の並び替え順序を指定します。
Header 省略可能 最初の行にヘッダー情報が含まれているかどうかを指定します。
OrderCustom 省略可能 ユーザー設定の並べ替え基準を指定します。
MatchCase 省略可能 大文字小文字の区別を行うか指定します。
Orientation 省略可能 並べ替えを列単位で行うか、行単位で行うかを指定します。
SortMethod 省略可能 並べ替えの方法を指定します。
DataOption1 省略可能 Key1 で指定した範囲でテキストを並べ替える方法を指定します。
DataOption2 省略可能 Key2 で指定した範囲でテキストを並べ替える方法を指定します。
DataOption3 省略可能 Key3 で指定した範囲でテキストを並べ替える方法を指定します。
引数Key1, Key2, Key3

Rangeオブジェクトまたは文字列(範囲名かピボットテーブル名)で指定します。

Key2, Key3については、ピボットテーブルレポートを並び替える場合には使用できません。

引数Order1, Order2, Order3

XlSortOrder 列挙型を指定します。

名前 説明
xlAscending 1 指定したフィールドを昇順で並べ替えます。(デフォルト値)
xlDescending 2 指定したフィールドを降順で並べ替えます。
引数Type

XlSortType 列挙型を指定します。

この引数は、ピボットテーブル レポートを並べ替えるときにだけ使用します。

名前 説明
xlSortValues 1 ピボットテーブル レポートをで並べ替えます。
xlSortLabels 2 ピボットテーブル レポートをラベルで並べ替えます。
引数Header

XlYesNoGuess列挙型を指定します。

名前 説明
xlGuess 0 見出しがあるかどうか、ある場合はその場所を Excel が自動判定します。
xlYes 1 範囲全体が並べ替えられません。
xlNo 2 範囲全体が並べ替えの対象になります。 (デフォルト値)
引数OrderCustom

『ユーザー設定リストの何番目』の基準を用いるかを整数値で指定します。

ユーザー設定の並べ替え基準は、あらかじめユーザー設定リストに登録されているものしか使えません。

引数MatchCase

true の場合、大文字と小文字を区別して並べ替えを行います。false の場合、大文字と小文字を区別しないで並べ替えを行います。

この引数は、ピボットテーブルの並べ替えには使用できません。

引数Orientation

XlSortOrientation 列挙型を指定します。

名前 説明
xlSortColumns 1 |列単位で並べ替えます。
xlSortRows 2 行単位で並べ替えます。(デフォルト値)
引数SortMethod

XlSortMethod 列挙型を指定します。

名前 説明
xlPinYin 1 中国語の発音表記の順で並べ替えます。(デフォルト値)
xlStroke 2 各文字の総画数で並べ替えます。
引数DataOption1, DataOption2, DataOption3

XlSortDataOption 列挙型を指定します。

名前 説明
xlSortNormal 0 数値データとテキスト データを別々に並べ替えます。(デフォルト値)
xlSortTextAsNumbers 1 テキストを数値データとして並べ替えます。

サンプルコード

//Sheet1のセル範囲"A1:B100"を、2列目をキーにして昇順にソートする
Excel.Worksheets("Sheet1").Activate();
var range = Excel.Worksheets("Sheet1").Range(Cells(1, 1), Cells(100, 2));
range.Sort(Key1:Worksheets("Sheet1").Cells(1, 2), Order1:xlAscending);

下記例では、複数のキーを用いてソートする場合を示しています。

//セル範囲"A1:C100"を1列目は降順、2列目は昇順、3列目は降順にソート

var range = Excel.Worksheets("Sheet1").Range(Cells(1, 1), Cells(300, 3));
var col_1 = Excel.Worksheets("Sheet1").Cells(1, 1);
var col_2 = Excel.Worksheets("Sheet1").Cells(1, 2);
var col_3 = Excel.Worksheets("Sheet1").Cells(1, 3);
range.Sort(Key1:col_1, Order1:2, Key2:col_2, Order2:1, Key3:col_3, Order3:2);

下記例では、列Aに並んでいる色をSortメソッドで並べ替えます。

f:id:d_ymkw:20160804191843p:plain

ソートを行うにあたっては、ColorIndex プロパティを使用して、列 A 内のセルの色の値を取得し、その値を使用しています。

 //A列の色の 値(ColorIndex)をC列に表示
 var s = Excel.ActiveWorkbook.ActiveSheet;
 for(var i = 2; i < 11; i++)
 {
     s.Cells(i, 3).Value = s.Cells(i, 1).Interior.ColorIndex;
 }
s.Range("C1").Value = "Index"; //Phase1

//C列を基準にA~C列の範囲を並べ替え
s.Columns("A:C").Sort(Key1:s.Range("C2"), Order1:1, header:1); //Phase2

//C列を削除
s.Columns(3).ClearContents();

f:id:d_ymkw:20160804192515p:plain

連続検索

本記事は、Excel C# Script入門講座の1記事です。

検索

シート内で文字列を検索するは、下記記事で解説したようにRangeオブジェクトのFindメソッドを使用します。

mokumokucsharp.hateblo.jp

連続して検索する(検索条件に一致する箇所を次々に見つけたい)場合には、Findメソッドを用いた後、FindNextメソッドを使用します。

FindNextメソッドは、Findメソッド使用時の検索条件はそのままに前方向に検索を行います。

なお、FindPreviousメソッドを用いると、後ろ方向に検索を行います。

FindNextメソッドの構文

(Rangeオブジェクト).FindNext(After)

FindNextメソッドの引数

引数名 必須 / オプション 内容
After 省略可能 検索を開始する1つ前のセルを指定します。

サンプルコード

以下の例では、セル範囲 A1:A500 で、値に 2 が含まれているセルを検索し、その値を 5 に変更します。

var range = Excel.Worksheets(1).Range("a1:a500");
var hitCell = range.Find(2, LookIn:-4163);
if (hitCell != null)
{
    var firstAddress = hitCell.Address;
    do
    {
         hitCell.Value = 5;
         hitCell = range.FindNext(hitCell);
    }
    while (hitCell != null && hitCell.Address == firstAddress)
}