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();
            }
        }
    }
}

SATソルバのお試し（後編）

前回「SATソルバのお試し（前編）」の続きとして、SATソルバで何か例題を解いてみる。

検索すると、数独を例題としているサイトがあったので、それに倣ってみる。

実は、このサイトではruby-minisatを使って解いているので、ソースコードそのまんまでも動く。
でも、それでは学習にならないので、解説だけは参考にしながら独自に実装。
後述するが、このコードは汚いので、追試する人は先ほど紹介した参考サイトを見たほうが良いだろう。

#
# * 2 | * 4
# 1 * | * *
# ---+---
# * * | 3 *
# * * | * *
#

require "minisat"
solver = MiniSat::Solver.new

arr =
  (0...4).map do
    (0...4).map do
      (0...4).map { solver.new_var }
    end
  end

arr.each do |row|
  row.each do |square|
    solver << square
  end
end

for k in 0...4 do
  for j in 0...4 do
    solver << [-arr[0][j][k], -arr[1][j][k]]
    solver << [-arr[0][j][k], -arr[2][j][k]]
    solver << [-arr[0][j][k], -arr[3][j][k]]
    solver << [-arr[1][j][k], -arr[2][j][k]]
    solver << [-arr[1][j][k], -arr[3][j][k]]
    solver << [-arr[2][j][k], -arr[3][j][k]]
  end
end

for k in 0...4 do
  for i in 0...4 do
    solver << [-arr[i][0][k], -arr[i][1][k]]
    solver << [-arr[i][0][k], -arr[i][2][k]]
    solver << [-arr[i][0][k], -arr[i][3][k]]
    solver << [-arr[i][1][k], -arr[i][2][k]]
    solver << [-arr[i][1][k], -arr[i][3][k]]
    solver << [-arr[i][2][k], -arr[i][3][k]]
  end
end

solver << arr[0][1][1]
solver << arr[0][3][3]
solver << arr[1][0][0]
solver << arr[2][2][2]

solver.solve

ans =
  arr.map do |row|
    row.map do |square|
      (1..4).find do |i|
        solver[square[i - 1]]
      end
    end
  end

ans.each do |row|
  puts row.join(" ")
end

実行した結果は、以下の通り。

$ ruby sudoku.rb
3 2 1 4
1 4 2 3
4 1 3 2
2 3 4 1
$

どうやら正しい結果が出てるっぽい。
しかし、参考サイトとソースコードを比べると、僕のはとても汚い。

まず、同じ行に同じ数字が登場しない、というルールを表すのに、

for k in 0...4 do
  for j in 0...4 do
    solver << [-arr[0][j][k], -arr[1][j][k]]
    solver << [-arr[0][j][k], -arr[2][j][k]]
    solver << [-arr[0][j][k], -arr[3][j][k]]
    solver << [-arr[1][j][k], -arr[2][j][k]]
    solver << [-arr[1][j][k], -arr[3][j][k]]
    solver << [-arr[2][j][k], -arr[3][j][k]]
  end
end

とやってしまっている所が良くない。
ループを回すのに直値を使ってしまっているし、1行の中の4マスから任意に2マスを選択するところは、全組み合わせを手書きしてしまっている。

この部分は、参考サイトでは、

# 各行に同じ数字が 2 回以上現れない
field.each do |row|
  row.transpose.each do |vars|
    vars.combination(2) do |x, y|
      solver << [-x, -y]
    end
  end
end

となっており美しい。

僕が知らなかった、transposeやcombinationといったメソッドを利用している。
transposeは、配列の行と列を転置するメソッドの様だ。
それから、combinationはその名の通り組み合わせを返してくれるらしい。
こういうメソッドを知っていると、配列のサイズが変更になったときにも対応できる美しいコードが書ける。
いや、知らなくても、こういうメソッド無いかな？って調べれば良かったんだ。
もし、調べてみても見つからなかったら、自分でメソッドを書いても良かったはずだ。
反省。
その他、zipやらflattenなどの、配列を上手く操作するメソッドを多様されていて、とても勉強になった。

さて、このように便利なSATソルバだが、半導体のフロアレイアウトの自動決定などに使われている、ということを最近知った。会員制の記事で恐縮だが、興味のある方は一読をおススメする。

それにしても、機械に自動的に問題を解かせる、ってのは男のロマンだな。久しぶりに、手を動かしながらワクワク感を覚えた。

SATソルバのお試し（前編）

以前から使ってみたかったSATソルバを使ってみたので、その記録。

SATソルバのひとつminisatというのが、非常に高速と評判なので、これを使ってみよう。
SATソルバへは、連言標準形の形式で命題論理式を与える必要がある。
業界標準？では、DIMACS CNFという記法が一般的らしいけど、命題論理が全て数値で記述されており、とても読みにくい。
式が大きくなると書くだけで大変になるのは明白である。
そこで、命題論理式を書く部分を多少なりとも楽にするために、今回はRubyからminisatを利用できるruby-minisatを使ってみる。

以下、Ubuntu-14.04上で実行した。

$ sudo gem install ruby-minisat
Building native extensions.  This could take a while...
ERROR:  Error installing ruby-minisat:
        ERROR: Failed to build gem native extension.

        /usr/bin/ruby1.9.1 extconf.rb
/usr/lib/ruby/1.9.1/rubygems/custom_require.rb:36:in `require': cannot load such file -- mkmf (LoadError)
        from /usr/lib/ruby/1.9.1/rubygems/custom_require.rb:36:in `require'
        from extconf.rb:1:in `<main>'

Gem files will remain installed in /var/lib/gems/1.9.1/gems/ruby-minisat-2.2.0 for inspection.
Results logged to /var/lib/gems/1.9.1/gems/ruby-minisat-2.2.0/ext/minisat/gem_make.out
$

いきなりエラー。

そこで、このページを参考にして、ruby-devとg++をインストール。

$ sudo apt-get install ruby-dev
（略）
$sudo apt-get install g++
（略）
$

その後、以下の通りして、ruby-minisatのインストール完了。

$ sudo gem install ruby-minisatBuilding native extensions.  This could take a while...
Successfully installed ruby-minisat-2.2.0
1 gem installed
Installing ri documentation for ruby-minisat-2.2.0...
Installing RDoc documentation for ruby-minisat-2.2.0...
$

まずは、ruby-minisatのWebサイトにある、例題を解かせてみる。

例題は、以下の通りの簡単なもの。

# solve (a or b) and (not a or b) and (a or not b)

require "minisat"
solver = MiniSat::Solver.new

a = solver.new_var
b = solver.new_var

solver << [a, b] << [-a, b] << [a, -b]
p solver.solve  #=> true (satisfiable)

p solver[a]  #=> true
p solver[b]  #=> true

冒頭のコメント行にもある通り、
(a or b)、(not a or b)、(a or not b)の全てを満たす、aとbの組み合わせは存在するか？
という問題。
ここでのaとかbは、TrueかFalseの2値を取る。
この問題では、aとbが両方ともTrueの時に、命題全体が成立する。

これをsample.rbとして保存して実行。
結果は、以下の通りとなった。

$ ruby sample.rb
true
true
true
$ 

命題論理を与えるときに、

solver << [a, b] << [-a, b] << [a, -b]

なんて書けちゃうところが、大変楽チン。

ruby-minisatを作って下さった方に感謝。

というわけで、とりあえず、最初の一歩を踏み出した。
後編では、もうちょっとパズルっぽい問題を解かせてみたいと思う。