Moonmile Solutions Blog

MVVMパターンを考えるとき、ViewはXAMLとかGUIだろうという固定観点があるが、実はそうではない。と思うのだがどうだろうか？いわゆる、ASP.NET MVC の View にWeb APIを割り当てると View ってのは XML や JSON になる。クライアントから見れば XMLやJSON はデータだが、MVCパターンで作られたWebアプリにしてみれば、XMLやJSONをViewに見立てることができる。実際、Web APIを作るとき複雑なXMLを返す場合は、Viewの機能を使ったほうが楽だったりする。

ClosedXMLをViewに見立てる

CloseXMLを使って、Excelに値を書き込もうとするとき Cell(row,column)あるはCell(“A1”)を使うわけだけど、ここの書き込み先をXAMLのバインドのように書けないだろうか？ってのを思いついた。

で、ClosedXML.Report https://github.com/ClosedXML/ClosedXML.Report はテンプレート用のExcelを作っておいて、あらかじめ名前を付けておく。

確かに、こんな風に Excel に名前を付けておいて、そこを目指して書き込むのもいいんんだが、なんらかのマークを書いておかなければいけないのが微妙な感じがする。実は、XAML も {Binding propName} という形で XAML 自身に記述するので似たようなことをやっているのだが。

これ、マークを View のほうに書いているけど、じゃあ ViewModel のほうに書くのはどうか？とおもってみたのがこれ。

テンプレートとなるExcelはふつうに記述しておく。

書き込んだ結果はこっち

Excelへの書き込み位置は、ViewModelに属性として記述する。

public class ViewModel : ObservableObject
{
    private string _Name, _Address;
    private string _ModifiedDate;

    [ExcelBinding(Address: "B2", Property: "Value")]
    public string Name { get => _Name; set => SetProperty(ref _Name, value, nameof(Name)); }
    [ExcelBinding(Address: "B3", Property: "Value")]
    public string Address { get => _Address; set => SetProperty(ref _Address, value, nameof(Address)); }
    // 更新日時
    [ExcelBinding(Address: "A4", Property: "Value")]
    public string ModifiedDate { get => _ModifiedDate; set => SetProperty(ref _ModifiedDate, value, nameof(ModifiedDate)); }
}

ViewModelからClosedXMLを通してExcelに書き込むコードはこんな感じ。

ExcelBind _bind;
ViewModel _vm;
public void LoadExcel()
{
    string tableName = "Personal";
    string path = @"C:\Users\masuda\Documents\サンプルテンプレート.xlsx";
    using (var wb = new XLWorkbook(path))
    {
        var sh = wb.Worksheets.FirstOrDefault(t => t.Name == tableName);
        _bind = new ExcelBind(sh);
        _vm = new ViewModel();
        _bind.DataContext = _vm;

        _vm.Name = "Tomoaki Masuda";
        _vm.Address = "Itabash-ku Tokyo in Japan";
        _vm.ModifiedDate = DateTime.Now.ToString();     // 更新日を表示
        wb.SaveAs(@"C:\Users\masuda\Documents\sample_output.xlsx");
    }
}

当然のことながら、ClosedXML にはバインディング処理はないので、ClosedXMLのXLWorkbookとIXLWorksheetをラップするような橋渡しのExcelBindクラスを作る。ExcelBindクラスにはMVVMパターンのDataContextプロパティを持っているので、これにバインドする。

ViewModel の各種プロパティに設定をすると INotifyPropertyChanged インタフェースを使って、View に見立てられた ClosedXML に値を書き込むという仕組み。ClosedXML は入力インターフェースを持たないので、ClosedXML からの TextChenge などのイベント受信は不要（実は自動計算などがあるから、無いわけではないのだが）というわけだ。
ViewModel クラスは普通の MVVM パターンと同じようにプロパティに値を設定すればよい。

内部実装

ざっと内部的な実装を

目的のセルを設定するExcelBinding属性

“A1″や行列で指定する。将来的にはセルの名前を設定できるのもよいだろう。

[AttributeUsage(AttributeTargets.Property, AllowMultiple = false, Inherited = false)]
public class ExcelBindingAttribute : Attribute
{
    public string SheetName { get; }
    public string Address { get; }
    public int Row { get; }
    public int Column { get; }
    public string PropertyName { get; }
    public ExcelBindingAttribute(string Sheet, string Address, string Property )
    {
        this.SheetName = Sheet;
        this.Address = Address;
        this.PropertyName = Property;
    }
    public ExcelBindingAttribute(string Sheet, int Row, int Column, string Property )
    {
        this.SheetName = Sheet;
        this.Row = Row;
        this.Column = Column;
        this.PropertyName = Property;
    }
    public ExcelBindingAttribute(string Address, string Property = "Value") 
    {
        this.SheetName = "";
        this.Address = Address;
        this.PropertyName = Property;
    }
    public ExcelBindingAttribute(int Row, int Column, string Property = "Value")
    {
        this.SheetName = "";
        this.Row = Row;
        this.Column = Column;
        this.PropertyName = Property;
    }
}

橋渡しをする ExcelBind クラス

内部的にClosedXMLのXLWorkbookかIXLWorksheetを持たせる。DataContextプロパティに設定した ViewModel の PropertyChanged イベントをフックして、ClosedXML 経由で目的のセルの値を変更する。プロパティは、簡易のため Value のみに固定している。

GetDataContext メソッドは、逆向きで Excel シートから ViewModel を作るときに使う。

public class ExcelBind
{
    private XLWorkbook _workbook;
    private IXLWorksheet _worksheet;

    public ExcelBind(XLWorkbook book) { _workbook = book; }
    public ExcelBind(IXLWorksheet sheet) { _worksheet = sheet; }


    private object _DataContext = null;
    public object DataContext
    {
        get => _DataContext;
        set
        {
            if (_DataContext != value)
            {
                _DataContext = value;
                if ( _DataContext is INotifyPropertyChanged )
                    (_DataContext as INotifyPropertyChanged).PropertyChanged += _DataContext_PropertyChanged;
            }
        }
    }
    private void _DataContext_PropertyChanged(object sender, PropertyChangedEventArgs e)
    {
        if (_DataContext == null) return;
        // 1.ViewModelのプロパティ名からリフレクションで値を取得
        var pi = _DataContext.GetType().GetProperty(e.PropertyName);
        if (pi == null) return;
        // 2.属性から設定先のClosedXMLのプロパティを取得
        var attr = Attribute.GetCustomAttribute(pi, typeof(ExcelBindingAttribute)) as ExcelBindingAttribute;
        // 3.設定先にリフレクションで値を代入
        try
        {
            if (string.IsNullOrEmpty(attr.Address))
            {
                // Row,Column 指定の場合
                if (_worksheet != null)
                    _worksheet.Cell(attr.Row, attr.Column).Value = pi.GetValue(_DataContext);
                if (_workbook != null && _workbook.Worksheet(attr.SheetName) != null)
                    _workbook.Worksheet(attr.SheetName).Cell(attr.Row, attr.Column).Value = pi.GetValue(_DataContext);
            }
            else
            {
                // Address 指定の場合
                if (_worksheet != null)
                    _worksheet.Cell(attr.Address).Value = pi.GetValue(_DataContext);
                if (_workbook != null && _workbook.Worksheet(attr.SheetName) != null)
                    _workbook.Worksheet(attr.SheetName).Cell(attr.Address).Value = pi.GetValue(_DataContext);
            }
        }
        catch { } // 例外は無視する
    }

    public void SetDataContext(object context) { _DataContext = context ; }
    public T GetDataContext<T>( T o = null ) where T : class, new() {
        if ( o == null ) o = new T();
        // バインド先のデータを workbook/worksheet から取り出す
        // 1.プロパティ一覧を取得
        var props = o.GetType().GetProperties();
        foreach ( var pi in props )
        {
            // 2.ExcelBindingAttribute属性がついているプロパティを取り出す
            var attr = Attribute.GetCustomAttribute(pi, typeof(ExcelBindingAttribute)) as ExcelBindingAttribute;
            if ( attr != null )
            {
                IXLWorksheet sh = _worksheet;
                if ( sh == null )
                {
                    if (_workbook != null && _workbook.Worksheet(attr.SheetName) != null)
                        sh = _workbook.Worksheet(attr.SheetName);
                }
                if ( sh == null ) { return o; }

                // 4.ViewModel のプロパティに値を設定する
                if (string.IsNullOrEmpty(attr.Address))
                {
                    pi.SetValue(o, sh.Cell(attr.Row, attr.Column).Value);
                }
                else
                {
                    pi.SetValue(o, sh.Cell(attr.Address).Value.ToString());
                }
            }
        }
        return o;
    }
}

逆に Excel シートから ViewModel を構成する

ExcelBindのGetDataContextメソッドを使って、ViewModelを構成する例。これを使うと、Excel シートに記入してもらって、そこから値を取り出すのが楽にできるのでは？と考えたりする。

private void clickGetFromExcel(object sender, RoutedEventArgs e)
{
    string tableName = "Personal";
    string path = @"C:\Users\masuda\Documents\sample_output.xlsx";
    using (var wb = new XLWorkbook(path))
    {
        var sh = wb.Worksheets.FirstOrDefault(t => t.Name == tableName);
        _bind = new ExcelBind(sh);
        // ViewModel を再構成
        var vm = _bind.GetDataContext<ViewModel>();

        System.Diagnostics.Debug.WriteLine(vm.Name);
        System.Diagnostics.Debug.WriteLine(vm.Address);
        System.Diagnostics.Debug.WriteLine(vm.ModifiedDate);

        MessageBox.Show(vm.Name);
    }
}

ViewModelからPropertyChangedを受ければViewになるのか？

直接 ClosedXMLに渡すわけではないが、ExcelBindクラスを媒介して値のやり取りができている。WPFやUWPのようなユーザーインタフェースはないが、ViewModelクラスのプロパティを使ってView（この場合はExcelシート）にアクセスできている。
この部分、構造が簡単な XML ならばデータとして扱うのだが、ExcelのOpenXML形式のように複雑怪奇なXMLの場合、ピンポイントで修正することを考えると「View」として扱うのがベターではないかと思っている。
ViewModelのプロパティは、別途DIなどでXMLやJSONから読み込むようにすれば、消えてしまった InfoPath のようにできるのではないな、と。

このあたりを少し整理して、NuGet に挙げられるようにする予定。

昨日書いたばかりの、これだけど、SqlBulkCopy を使うとどれだけ早くなるのかを再び実験

LINQ の INSERT が遅いときは AutoDetectChangesEnabled を False にする
http://www.moonmile.net/blog/archives/9646

結論

結論から言えば、SqlBulkCopy を使うほうが100倍位早いですね。1万件位だと6秒から0.06秒という誤差？っぽい感じだけど、100万件になるとLINQのINSERTでは難しいので、SqlBulkCopy を直接使えという感じです。

SqlBulkCopy は DataTable を受け取る

SqlBulkCopy Class (System.Data.SqlClient) | Microsoft Docs
https://docs.microsoft.com/ja-jp/dotnet/api/system.data.sqlclient.sqlbulkcopy?view=netframework-4.7.2

SQL Server専用の SqlBulkCopy な訳ですが、引数に DataTable か DataRow の配列を取ります。いわゆるEFじゃない DataSet/DataTable のものを使わないといけないので、EFのDbSetがそのまま渡せません。
逆に言えば、EFのDbSetを渡せるようにDataTableに変換してやれば、LINQとSqlBulkCopyが共存可能になります。

任意のオブジェクトの配列をDataTableに変換する – Qiita
https://qiita.com/keidrumfreak/items/f092b3cacfc2961610b6

この記事を参考にしながら、というかそのまま使って、AsDataTable という拡張メソッドを作ります。

public static class DataTableExtenstions
{
    public static DataTable AsDataTable<T>(this DbSet<T> src) where T : class
    {
        return DataTableExtenstions.AsDataTable( src.Local );
    }
    public static DataTable AsDataTable<T>(this IEnumerable<T> src) where T : class
    {
        var properties = typeof(T).GetProperties();
        var dest = new DataTable();
        // テーブルレイアウトの作成
        foreach (var prop in properties)
        {
            dest.Columns.Add(prop.Name, prop.PropertyType);
        }
        // 値の投げ込み
        foreach (var item in src)
        {
            var row = dest.NewRow();
            foreach (var prop in properties)
            {
                var itemValue = prop.GetValue(item, new object[] { });
                row[prop.Name] = itemValue;
            }
            dest.Rows.Add(row);
        }
        return dest;
    }
}

EFのDbSetは内部でLocalプロパティ（データベースに反映する前のデータを取っている持っておくコレクション）があるので、これを利用して DataTable に変換します。

実験

SqlBulkCopy に渡すデータを List で用意してから DataTable に変換するパターン

private void clickBulk(object sender, RoutedEventArgs e)
{
    var ent = new testdbEntities();
    ent.Database.ExecuteSqlCommand("delete BulkT");
    var start = DateTime.Now;
    var lst = new List<BulkT>();
    for (int i = 0; i < 10000; i++)
    {
        var t = new BulkT()
        {
            GUID = Guid.NewGuid().ToString("N"),
            Created = DateTime.Now,
        };
        lst.Add(t);
    }
    var dt = lst.AsDataTable();
    var cn = ent.Database.Connection as SqlConnection;
    var bc = new SqlBulkCopy(cn);
    bc.DestinationTableName = "BulkT";
    cn.Open();
    bc.WriteToServer(dt);
    cn.Close();

    var tend = DateTime.Now;
    var span = (tend - start).TotalSeconds;
    System.Diagnostics.Debug.WriteLine(span.ToString());
}

SqlBulkCopy に渡すデータをLINQのAddで追加してから DataTable に変換するパターン

private void clickAsDataTable(object sender, RoutedEventArgs e)
{
    var ent = new testdbEntities();
    ent.Configuration.AutoDetectChangesEnabled = false;
    ent.Configuration.ValidateOnSaveEnabled = false;
    ent.Database.ExecuteSqlCommand("delete BulkT");
    var start = DateTime.Now;
    for (int i = 0; i < 10000; i++)
    {
        var t = new BulkT()
        {
            GUID = Guid.NewGuid().ToString("N"),
            Created = DateTime.Now,
        };
        ent.BulkT.Add(t);
    }
    var cn = ent.Database.Connection as SqlConnection;
    var bc = new SqlBulkCopy(cn);
    bc.DestinationTableName = "BulkT";
    var dt = ent.BulkT.AsDataTable();
    cn.Open();
    bc.WriteToServer(dt);
    cn.Close();

    var tend = DateTime.Now;
    var span = (tend - start).TotalSeconds;
    System.Diagnostics.Debug.WriteLine(span.ToString());
}

結果

List を使って DataTable に変換
0.0477976
0.0400377
0.034159

LINQのAddを使って DataTable に変換
1.7919409
1.7825315
1.7763977

AutoDetectChangesEnable = false の場合
6.311641
5.8724151
6.0832671

単純に比較すると、AutoDetectChangesEnable を false にしただけよりも、SqlBulkCopy を使ったほうが6倍位早くなります。さらに、EFのDbSetを使わずに、Listだけを使った場合は、25倍位早くなるってことですね。どうやら、DbSetに対してAdd/Removeしたときに DetectChanges() 等のチェックルーチンが走るらしく、単純な大量 INSERT の場合には SqlBulkCopy を直接使ってしまたほうが早いです。

とあるシステムで1万件程度のテーブルを構成しなおして別の複数のテーブルに移すことをやっていた。いわゆる、正規化していないテーブルをデータ移行の際に正規化しようと思って、複数のテーブルに分けたのだが、たかだか1万件しかないのに非常に遅い。正規化のロジックが遅いのかもしれないけど、1万件程度をデータ挿入するのに30分位掛かってしまうのである。
相手が SQL Server なので SqlBulkCopy を使えば結構なスピードになるはずなのだが、100万件の場合ならばそうかもしれないけど、たかだか1万件の挿入でこんなに遅いのは変。ということで、LINQ の INSERT について調べなおしてみる。

結論

結論から言えば、AutoDetectChangesEnabled と ValidateOnSaveEnabled を OFF(false) にすればよい。

DbContextConfiguration.AutoDetectChangesEnabled Property
https://docs.microsoft.com/en-us/dotnet/api/system.data.entity.infrastructure.dbcontextconfiguration.autodetectchangesenabled?redirectedfrom=MSDN&view=entity-framework-6.2.0#overloads

ent.Configuration.AutoDetectChangesEnabled = false;
ent.Configuration.ValidateOnSaveEnabled = false;

LINQ で INSERT/DELETE/UPDATE をする場合、EF の内部で整合性をチェックしている。System.Data.Entity.DbSet.Add などを呼び出したときに、DetectChanges() でチェックをしているというわけだ。どうやらこれが遅い原因なので、素直に System.Data.Entity.Infrastructure.DbContextConfiguration.AutoDetectChangesEnabled の値を false にして呼び出さないようにすればよい。
デフォルトでは、AutoDetectChangesEnabled が true となっている。

実験

CREATE TABLE [dbo].[BulkT](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[GUID] [varchar](100) NOT NULL,
	[Created] [datetime] NOT NULL
)

この BulkT テーブルに1万件のデータを挿入する。SaveChanges を1回の挿入ごとに行っているが、テーブルが複雑な場合は最後に1回だけだとメモリを食いすぎたりするため、何度かに分ける(100回毎など）必要がある。

private void clickNormal(object sender, RoutedEventArgs e)
{
    var ent = new testdbEntities();
    ent.Database.ExecuteSqlCommand("delete BulkT");
    var start = DateTime.Now;
    for ( int i=0; i<10000; i++ )
    {
        var t = new BulkT()
        {
            GUID = Guid.NewGuid().ToString("N"),
            Created = DateTime.Now,
        };
        ent.BulkT.Add(t);
        ent.SaveChanges();
    }
    var tend = DateTime.Now;
    var span = (tend - start).TotalSeconds;
    System.Diagnostics.Debug.WriteLine(span.ToString());
}

private void clickNoAutoDetect(object sender, RoutedEventArgs e)
{
    var ent = new testdbEntities();
    ent.Database.ExecuteSqlCommand("delete BulkT");
    var start = DateTime.Now;
    ent.Configuration.AutoDetectChangesEnabled = false;
    ent.Configuration.ValidateOnSaveEnabled = false;
    for (int i = 0; i < 10000; i++)
    {
        var t = new BulkT()
        {
            GUID = Guid.NewGuid().ToString("N"),
            Created = DateTime.Now,
        };
        ent.BulkT.Add(t);
        ent.SaveChanges();
    }
    var tend = DateTime.Now;
    var span = (tend - start).TotalSeconds;
    System.Diagnostics.Debug.WriteLine(span.ToString());
}

DetectChanges のチェックあり（AutoDetectChangesEnabled = true）
50.1799167
48.9856614
48.5278795

DetectChanges のチェック無し（AutoDetectChangesEnabled = false)
6.1251368
6.1039912
6.2945836

このように8倍ぐらいの差がでてくる。100件程度ならば特に問題もでないだろうが、1万件以上ある場合は気を付けたおいたほうがよいだろう。データチェックが入らないので、挿入データに気を付ける必要があるが、今回のように空のテーブルに挿入する場合はプログラム内でチェックが済んでいるので特に問題はない。

ちなみに、30分以上掛かっていたデータコンバートは1分以内に終わるようになった。

参考リンク

Entity Framework のパフォーマンス #2 更新処理 | C#.NET vs VB.NET
http://csharpvbcomparer.blogspot.com/2015/04/net-ef-performance-2-updating.html