Moonmile Solutions Blog

SQL Server Management Studio の「データのエクスポート」を使うと、SQL Server から手軽にバックアップが取れる。バックアップというよりも、どこかに移動したいときに使うわけだが、Access 形式で取るのが一番手軽だと思う。

問題

顧客の SQL Server なデータを、開発用の SQL Server に吸い上げて、検証環境を作ることはよくやる。確実な方法は、SQL Server のバックアップをコピーするのがベストなのだけど、残念ながらバージョンが違うとこれがうまくいかない。バージョンが違うのだから、バージョンを揃えた環境を整えるのが筋なんだけど、古めのデータベース（SQL Server 7 とか）とか微妙な差異があって、いろいろなバージョンを揃えておくのも以外と面倒くさい。できれば、開発環境の SQL Server をそのまま使っておきたい。

SSMS の「データのインポート」がそのまま動けばいいのだが、Access 形式にエクスポートしたときにカラムの型が違ってしまっていて、インポート時にエラーになる。それに PK の扱いもややこしくて、ちまちま設定をしながらインポートをしないといけない。ひとつふたつのテーブルならば、手作業でもいいのだけど、

• 大量のテーブル（20以上ある）をインポートするのに、いちいち手作業は大変
• 何度もインポートするので手作業は大変

解決方法

ということで、緩い感じで、Access から SQL Server へインポートできないものか？と考えていた。で、結局 C# でスクリプト的なものを書くことにする。

顧客 SQL Server → Access → 開発 SQL Server

という流れなので、SQL Server 同士の型は一緒になっている。最初の「顧客 SQL Server → Access 」は問題がないので、後の「Access → 開発 SQL Server」をスクリプト化する。

public class DBBackup
{
    public string AccessMdbPath { get; set; }
    public string SqlServerConnectionString { get; set; }

    public bool Restore( string tableName )
    {
        // Accessから読み込み
        OleDbConnection cn = new OleDbConnection();
        cn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + AccessMdbPath; // MDB名など
        var dt = new DataTable();
        var da = new OleDbDataAdapter($"select * from {tableName}", cn);
        da.Fill(dt);
        // SQL Severへ書き出し
        var cnn = new SqlConnection(SqlServerConnectionString);
        // 指定テーブル内を消去
        var cmd = new SqlCommand($"TRUNCATE TABLE {tableName}", cnn);
        cmd.Connection.Open();
        cmd.ExecuteNonQuery();
        cmd.Connection.Close();
        // バルクコピーで挿入
        var bc = new SqlBulkCopy(cnn);
        cnn.Open();
        bc.DestinationTableName = tableName;
        bc.WriteToServer(dt);
        cnn.Close();

        return true;
    }

}

こんな感じに

• OleDb で Access MDB ファイルから読み込む
• SqlBulkCopy を使って、SQL Server にバルクコピーする

バルクコピーが一番早いし、こうじゃないと実行時に時間が掛かってしまう。

使い方

テーブル名は Access でも SQL Server でも一緒なので、DBBackup::Restore を使ってがしがしと戻していく。テーブル名は自動で取得できるけど、今回の場合は、移行不要なテーブルもあるので、必要なものだけピックアップしている。

private void clickLoadAccess(object sender, RoutedEventArgs e)
{
    var dbb = new DBBackup()
    {
        AccessMdbPath = @"営業管理_20190304.mdb",
        SqlServerConnectionString = "Data Source=.;Initial Catalog=営業管理2;Integrated Security=True;",
    };

    var tables = new List()
    {
        "タイトルファイル2",
        "タイトルファイル属性",
        "プログラム一覧",
        "パスワード",
        "ユーザマスタ",
...
    };

    foreach ( var name in tables )
    {
        dbb.Restore( name );
        System.Diagnostics.Debug.WriteLine("テーブル: " + name);
    }
    MessageBox.Show("保存しました");
}

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 に挙げられるようにする予定。