【ClosedXML】 条件付き書式を使用する

　ClosedXMLで条件付き書式を指定する方法を紹介します。
　条件付き書式にはいくつか種類があります。
　ここでは以下の4種類について説明を行います。
　・指定条件を満たすセルに書式を指定
　・カラースケール
　・アイコンセット
　・データバー

　先ずは「指定条件を満たすセルに書式を指定」する方法です。
　おそらくこの方法が最もよく使われていると思います。

var sheet = book.AddWorksheet("値");

// 適当に表を作成する
var data = new[]
    {
        new object[] { 1, 2, 3, 4, 5},
        new object[] { 6, 7, 8, 9, 10},
        new object[]{"A", "B", "AC", "D"},
    };
sheet.Cell("A1").InsertData(data);

// 条件付き書式を指定する
var range = sheet.RangeUsed();
range.AddConditionalFormat()
    .WhenBetween(1, 5)                       // 条件
    .Fill.SetBackgroundColor(XLColor.Navy)   // 書式
    .Font.SetFontColor(XLColor.Yellow);　    // 書式

// さらに条件を追加する場合
range.AddConditionalFormat()
    .WhenContains("A")                  // Aを含む
    .Font.SetFontColor(XLColor.Red);

　このコードを実行すると以下の様になります。

　条件付き書式を設定する場合は、条件付き書式を設定したいセル範囲からAddConditionalFormatメソッドを使用します。
　条件を満たすセルにのみ書式を指定する場合はAddConditionalFormatメソッドの後にWhenXxxメソッドを使用して条件を指定します。
　WhenXxxメソッドは指定条件に合わせて使い分けてください。
　このサンプルコードで使用しているWhenBetweenメソッドはセルの値が指定範囲内に入っているかどうかを確認します。
　WhenContainsメソッドはセルの値に指定文字列が含まれているかどうかを確認します。
　WhenXxxメソッドの後に書式を指定するためのメソッドを記載します。
　書式の指定は複数あっても問題ありません。
　注意点はExcelの条件付き書式で指定できない書式はClosedXMLでも指定できない点です。
　たとえば、条件付き書式では文字配置を指定することは出来ません。
　書式設定時に.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center)と記載してもエラーにはなりませんが、この書式指定は無視されます。

　次に、カラースケールを指定する方法について説明します。
　カラースケールでは範囲内のセルの値を元にセルの背景色を塗り分けることができます。

var sheet = book.AddWorksheet("カラースケール");

// 適当に表を作成する
var data = new[]
    {
        new object[] { 1, 2, 3, 4, 5},
        new object[] { 6, 7, 8, 9, 10},
    };
sheet.Cell("A1").InsertData(data);

// 条件付き書式を指定する
var range = sheet.RangeUsed();
range.AddConditionalFormat()
    .ColorScale()
    .LowestValue(XLColor.Red)
    .Midpoint(XLCFContentType.Percent, 50, XLColor.Yellow)
    .HighestValue(XLColor.Green);

　このコードを実行すると以下の様になります。

　カラースケールを使用する場合は、AddConditionalFormatメソッドの後にColorScaleメソッドを使用します。
　その後にLowestValueメソッド、Midpointメソッド、HighestValueメソッドを使用して、色分け方法を指定します。
　ここでは、最低値には赤、全データの50%の値を中央値として黄色を使用、最大値には緑を指定しました。
　カラースケールの設定ですがClosedXML ver0.76を使用している場合、作成されたファイルを開くとエラーが起きます。
　どうも構文エラーがあるらしく、その部分を置き換えれば開けますが、カラースケールを指定した部分のデータはなくなります。
　ClosedXML ver0.75ではこのエラーが発生しないので、ver 0.76のバグだと思われます。

　次に、アイコンセットを使用する方法について説明します。

var sheet = book.AddWorksheet("アイコン");

// 適当に表を作成する
var data = new[]
    {
        new object[] { 1, 2, 3, 4, 5},
        new object[] { 6, 7, 8, 9, 10},
    };
sheet.Cell("A1").InsertData(data);

// 条件付き書式を指定する
var range = sheet.RangeUsed();
range.AddConditionalFormat()
    .IconSet(XLIconSetStyle.ThreeSymbols)
    .AddValue(XLCFIconSetOperator.EqualOrGreaterThan, 0, XLCFContentType.Number)
    .AddValue(XLCFIconSetOperator.EqualOrGreaterThan, 4, XLCFContentType.Number)
    .AddValue(XLCFIconSetOperator.EqualOrGreaterThan, 7, XLCFContentType.Number);

　このコードを実行すると以下の様になります。

　アイコンセットを使用する場合はAddConditionalFormatメソッドの後にIconSetメソッドで使用するアイコンセットの種類を指定します。
　その後アイコンを指定する条件設定を行います。
　サンプルコードでは3種類のアイコンを使用していますので、アイコンの条件範囲を3つ指定します。
　AddValueメソッドで条件を指定していきます。
　このコードでは、
・数値が0以上
・数値が4以上
・数値が7以上
の3条件を指定しています。
　これにより、
　0～3 = アイコン1
　4～6 = アイコン2
　7以上=アイコン3
が指定されます。

　最後にデータバーを使用する方法について説明します。

var sheet = book.AddWorksheet("データバー");

// 適当に表を作成する
var data = new[]
    {
        new object[] { 1, 2, 3, 4, 5},
        new object[] { 6, 7, 8, 9, 10},
    };
sheet.Cell("A1").InsertData(data);

// 条件付き書式を指定する
var range = sheet.RangeUsed();
range.AddConditionalFormat()
    .DataBar(XLColor.Navy)
    .LowestValue()
    .HighestValue();

　このコードを実行すると以下の様になります。

　データバーを使用する場合はAddConditionalFormatメソッドの後にDataBarメソッドを使用します。
　DataBarメソッドの引数には棒グラフの色を指定します。
　そのあとにLowestValueメソッドとHighestValueメソッドを呼べばOKです。

【ClosedXML】 値をまとめて入力する

　ClosedXMLでは配列やリスト等のデータをまとめてセルに入力することができます。
　配列等のデータを一気に入力したい場合はInsertDataメソッドを使用します。

using ClosedXML.Excel;
using System;
using System.IO;

namespace ClosedXml18
{
    class Program
    {
        static void Main(string[] args)
        {
            var filePath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "Book.xlsx");

            using(var book = new XLWorkbook(XLEventTracking.Disabled))
            {
                var sheet1 = book.AddWorksheet("シート1");

                // 縦方向に値を入力
                var valueArray = new[] { 1, 2, 3 };
                sheet1.Cell("A1").InsertData(valueArray);
                sheet1.Cell("C1").Value = valueArray;  // InsertData(valueArray)と同じ
                
                // 横方向に値を入力
                sheet1.Cell("A5").InsertData(new[] { valueArray });

                // 表形式のデータを入力する
                var sheet2 = book.AddWorksheet("シート2");
                var tableData = new[]
                {
                    new object[] { "A", "B", "C" },
                    new object[] { 1, 2, 3 },
                    new object[] {"D", 5 }
                };
                sheet2.Cell("A1").InsertData(tableData);

                // 列, 行, セル範囲のセルに代入
                var sheet3 = book.AddWorksheet("シート3");
                sheet3.Row(1).Value = 1;
                //sheet3.Column(1).Value = 2;   // 時間がかかる
                sheet3.Range("A3:C5").Value = 3;

                book.SaveAs(filePath);
            }
        }
    }
}

　InsertDataメソッドの引数はIEnumerableなので配列でもリストでも構いません。
　一次配列を引数に渡した場合は、起点となるセルから下方向に値が入力されます。
　値を横方向に入力したい場合はジャグ配列（配列の配列）を引数に指定します。

　表データを入力したい場合もデータをジャグ配列にして指定します。
　変数の型をobjectにすると異なるデータ型の値でもまとめて入力できます。

　セルのValueプロパティに配列を指定しても同様に値を入力できますが、Valueプロパティに代入した場合は値が１つなのか複数なのかをチェックしてからInsertDataメソッドが呼ばれるので、若干遅くなります。

　最後に、列や行、セル範囲に値を入力する方法です。（ほとんど使いませんが）
　行や列、セル範囲のValueプロパティに値を入力すると該当するセル全てに指定した値が入力されます。

【ClosedXML】 表の読み取り

　今回はExcelファイルに記載されている表からデータを読み取る方法を説明します。
　表部分のセル範囲を取得してセルに記載されている内容を取得しても良いのですが、ClosedXMLには表データを扱う為のクラスが用意されていますので、そちらを使います。
　以下の表からデータを読み取ります。

using ClosedXML.Excel;
using System;
using System.IO;

namespace ClosedXml17
{
    class Program
    {
        static void Main(string[] args)
        {
            var filePath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "Book.xlsx");

            using(var book = new XLWorkbook(filePath, XLEventTracking.Disabled))
            {
                var sheet = book.Worksheet("Sheet1");

                // テーブル作成
                var table = sheet.RangeUsed().AsTable();

                // フィールド名を取得
                Console.WriteLine("フィールド名一覧");
                foreach(var field in table.Fields)
                {
                    Console.WriteLine("Index = {0}, フィールド名 = {1}", field.Index, field.Name);
                }
                Console.WriteLine();

                // 目的のフィールド情報を取得
                Console.WriteLine("目的のフィールド情報を取得する");
                var field0 = table.Field(0);
                var field1 = table.Field("名前");
                Console.WriteLine("field0 = {0}", field0.Name);
                Console.WriteLine("field1 = {0}", field1.Name);
                Console.WriteLine();

                // データを行単位で取得
                Console.WriteLine("データ(行）一覧");
                foreach(var dataRow in table.DataRange.Rows())
                {
                    Console.WriteLine("{0}, {1}, {2}", dataRow.Cell(1).Value, dataRow.Cell(2).Value, dataRow.Cell(3).Value);
                }
                Console.WriteLine();

                // データを列単位で取得
                Console.WriteLine("データ(列）一覧");
                foreach(var dataCol in table.DataRange.Columns())
                {
                    Console.WriteLine("{0}, {1}, {2}", dataCol.Cell(1).Value, dataCol.Cell(2).Value, dataCol.Cell(3).Value);
                }
            }
        }
    }
}

　このコードを実行した結果は以下の通りです。

　表データを扱う場合、セル範囲からテーブルを作成します。
　1シートに表が１つだけならセル範囲はRangeUsedメソッドで取得できます。
　セル範囲に対してAsTableメソッドを行うことでテーブルを作成できます。
　テーブルではフィールドとデータが分けて管理されていますので、フィールド情報のみ取得したりデータのみ取得したりできます。
　フィールド情報はテーブルのFieldsプロパティに入っています。
　FieldメソッドにIndexかフィールド名を指定することで目的のフィールド情報を取得することもできます。

　データはテーブルのDataRangeプロパティに入っています。
　DataRangeはセル範囲(Range)と同じように扱えますので、ここから行や列を列挙することができます。
　サンプルコードでは行単位と列単位で表のデータを読み取る例を挙げました。

　自分でフィールドとデータ部分を分離する必要がないのでテーブルは地味に便利です。
　表からデータを読み取る場合はテーブルを利用することをお奨めします。

【ClosedXML】 セル範囲

　今回はセル範囲(Range)の操作方法を説明します。
　先ずはセル範囲の取得方法です。

// セル範囲取得
var range1 = sheet.Range("B1:D3");
var range2 = sheet.Range(1, 2, 3, 4);
var range3 = sheet.Range("B1", "D3");

var cell1 = sheet.Cell("B1");
var cell2 = sheet.Cell("D3");
var range4 = sheet.Range(cell1, cell2);
var range5 = sheet.Range(cell1.Address, cell2.Address);

　セル範囲を取得するにはRangeメソッドを使用します。
　引数に取得したい範囲を指定します。
　範囲の指定方法は複数用意されていますので、状況に合わせて使用しやすい方法を使えばOKです。
　上記のサンプルコードでは、色々な方法でセル範囲(B1:D3)を取得しています。

　次に、セル範囲内のセルにアクセスしてみます。
　取得したセル範囲はワークシートと同じような感じで扱えます。

// 範囲内のセル
range1.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
range1.Cell("A1").Value = "Range:A1";
range1.Cell("D4").Value = "Range:D4";   // 範囲外だけど入力できちゃう

// 範囲内の列、行
range1.Row(1).Style.Fill.BackgroundColor = XLColor.AliceBlue;
range1.Column(2).Value = "Column:2";

　このコードを実行すると以下の様になります。

　Cellメソッドでセル範囲内のセルにアクセスできます。
　注意点は指定するセルの位置はセル範囲内のセル番地だという点です。
　range1.Cell("A1")はワークシートのセルA1ではなく、range1のセルA1の位置にあるセルになります。
　セル範囲を超える位置を指定してもエラーにはなりません。（Excelのワークシート上限を超える場合は別ですが）
　行や列もセルと同じく、セル範囲内の行・列となります。
　そのためセル範囲の行や列に対してスタイルを指定した場合、セル範囲内の該当するセルのみにスタイルが指定されます。

　最後に、使用されているセル範囲の取得方法を説明します。

// RangeUsedを取得
var range6 = sheet.RangeUsed();
range6.Style.Border.OutsideBorder = XLBorderStyleValues.Medium;

　使用されているセル範囲を取得するにはRangeUsedメソッドを使用します。
　このメソッドは使用されているセルを全て含む最少のセル範囲を返します。

　実際に使用しているセルはB1:D3とE4ですが、RangeUsedで帰ってきたセル範囲はB1:E5となっています。
　RangeUsedメソッドはワークシートに表が１つだけ存在する時に使用すると、表全体のセル範囲を取得できるので便利です。