ClosedXMLをお試し

以前に仕事で、C#のプログラムからExcelのファイルを扱ったことがある。あの時は、まだExcelの新しい形式(xslx：OpenXML)がでてくる前で、C#側からExcelのCOMオブジェクトを呼び出して、Excelファイルを操作していた。
しかし、これが厄介な代物で、Excelのバージョン間でCOMオブジェクトの互換性が無く、開発環境上でどれかのバージョンでC#プログラムをビルドすると、実行環境で別のバージョンのExcelがインストールされていると使えなかった。
で、それじゃまずい、ってことで、リフレクションを使いながら何とか切り抜けた。結構大変だった。

さて、昔話はさておき、最近のExcelでは保存形式がOpenXMLになっており、仕様がオープンのため、Excelを扱う様々なライブラリが登場しているらしい。

で、その中でも、「ClosedXML」というのが使いやすい、ってことなので、試しに使ってみた。

以下に示すプログラムでは示していないが、Foo,Bar,Bazというクラスがあって、Foo->Bar->Bazという関連を形成している。今回は、この情報をExcelファイルに書き出してみたい。

usingusing System;
using System.Collections;
using System.Collections.ObjectModel;
using System.IO;
using System.Linq;
using System.Reflection;
using ClosedXML.Excel;

namespace ClosedXmlSample
{
    class Program
    {
        static void Main(string[] args)
        {
            // 不要ファイルを削除
            File.Delete("a.xlsx");


            // オブジェクトを作成
            var baz1 = new Baz() { Name = "BazObj1", SampleValue = 2.4 };
            var baz2 = new Baz() { Name = "BazObj2", SampleValue = 2.9 };
            var baz3 = new Baz() { Name = "BazObj3", SampleValue = 5.1 };

            var bar1 = new Bar() { Name = "BarObj1", Baz = baz1 };
            var bar2 = new Bar() { Name = "BarObj2", Baz = baz2 };
            var bar3 = new Bar() { Name = "BarObj3", Baz = baz3 };
            var bar4 = new Bar() { Name = "BarObj4", Baz = baz1 };
            var bar5 = new Bar() { Name = "BarObj5", Baz = baz1 };
            var bar6 = new Bar() { Name = "BarObj6", Baz = baz2 };

            var foo1 = new Foo() { Name = "FooObj1", Bar = bar1 };
            var foo2 = new Foo() { Name = "FooObj2", Bar = bar2 };
            var foo3 = new Foo() { Name = "FooObj3", Bar = bar3 };
            var foo4 = new Foo() { Name = "FooObj4", Bar = bar6 };
            var foo5 = new Foo() { Name = "FooObj5", Bar = bar6 };
            var foo6 = new Foo() { Name = "FooObj6", Bar = bar6 };

            var bazs = new ObservableCollection() { baz1, baz2, baz3, };
            var bars = new ObservableCollection() { bar1, bar2, bar3, bar4, bar5, bar6 };
            var foos = new ObservableCollection() { foo1, foo2, foo3, foo4, foo5, foo6 };


            // Linqを使って複数のコレクションの要素を結合したものを用意する
            var query = from bar in bars
                        join baz in bazs on bar.BazId equals baz.ID into gj
                        from subBaz in gj.DefaultIfEmpty()
                        select new
                        {
                            bar.ID,
                            bar.Name,
                            bar.BazId,
                            Baz_Name = (subBaz == null ? String.Empty : subBaz.Name),
                            Baz_SampleValue = (subBaz == null ? 0.0 : subBaz.SampleValue),
                        };


            // ワークブックを作成
            var wb = new XLWorkbook();

            // 4つのシートを作成
            MakeSheet(wb, foos, "FooObjs");
            MakeSheet(wb, bars, "BarObjs");
            MakeSheet(wb, bazs, "BazObjs");
            MakeSheet(wb, query.ToList(), "Query");

            // ファイルに保存
            wb.SaveAs("a.xlsx");
        }

        /// シートを作成する
        static void MakeSheet(XLWorkbook book, IList collection, string name)
        {
            var sheet = book.Worksheets.Add(name);

            sheet.Protect("123")
                .SetFormatCells()
                .SetInsertColumns()
                .SetDeleteColumns()
                .SetDeleteRows();

            PropertyInfo[] properties = collection[0].GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);

            sheet.Cell("A1").SetActive();
            MakeHeader(sheet, properties, collection[0], string.Empty, false);
            MoveToNextRowFirstColumn(sheet);

            foreach (var v in collection)
            {
                MakeValue(sheet, properties, v, false);
                MoveToNextRowFirstColumn(sheet);
            }

            sheet.Columns().AdjustToContents();
        }

        /// 次の行の先頭列に移動する
        static void MoveToNextRowFirstColumn(IXLWorksheet sheet)
        {
            sheet.ActiveCell.CellBelow(1).CellLeft(sheet.ActiveCell.Address.ColumnNumber - 1).SetActive();
        }

        /// ヘッダ行を作成する
        static void MakeHeader(IXLWorksheet sheet, PropertyInfo[] properties, object obj, string prefix, bool isExternal)
        {
            foreach (var property in properties)
            {
                if ((property.GetValue(obj) is ValueType || property.GetValue(obj) is string) == false)
                {
                    PropertyInfo[] childProperties = property.PropertyType.GetProperties(BindingFlags.Public | BindingFlags.Instance);

                    MakeHeader(sheet, childProperties, property.GetValue(obj), prefix + property.PropertyType.Name + ".", true);
                    continue;
                }

                if ((property.GetValue(obj) is Guid) && (String.Compare(property.Name, "ID", true) != 0))
                {
                    continue;
                }

                sheet.ActiveCell.Value = prefix + property.Name;
                if (isExternal)
                {
                    sheet.ActiveCell.Style
                        .Protection.SetLocked(true)
                        .Fill.SetBackgroundColor(XLColor.LightGray);
                }
                sheet.ActiveCell.CellRight(1).SetActive();
            }
        }

        /// ヘッダ以外の値の表示領域を一行分作成する
        static void MakeValue(IXLWorksheet sheet, PropertyInfo[] properties, object obj, bool isExternal)
        {
            foreach (var property in properties)
            {
                if ((property.GetValue(obj) is ValueType || property.GetValue(obj) is string) == false)
                {
                    PropertyInfo[] childProperties = property.PropertyType.GetProperties(BindingFlags.Public | BindingFlags.Instance);

                    MakeValue(sheet, childProperties, property.GetValue(obj), true);
                    continue;
                }

                if ((property.GetValue(obj) is Guid) && (String.Compare(property.Name, "ID", true) != 0))
                {
                    continue;
                }

                sheet.ActiveCell.Value = property.GetValue(obj);
                if (isExternal)
                {
                    sheet.ActiveCell.Style
                        .Protection.SetLocked(true)
                        .Fill.SetBackgroundColor(XLColor.LightGray);
                }
                sheet.ActiveCell.CellRight(1).SetActive();
            }
        }
    }
}