ClosedXMLを使って、超高速にExcelからSQL Serverへデータ転送する

投稿日: 2019/02/03 作成者: masuda

SQL Serverのデータをバックアップするとき正式な方法ならば、SQL Serverのバックアップ機能を使ってバックアップ/復元をすればよい。のだが、別な場所のデータベースに移したいとき使うにはいささか面倒くさいことが起こる。相互のバージョンが同じならば、そのあっまバックアップ/復元を繰り返せばよいのだが、ちょっとでもバージョンが違うとエラーになってしまう。仕方がないので、Accessを媒介する方法をよくとるのだが、型が微妙に違っているので（特に文字列の長さが異なってエラーになる）若干の手動の調節が必要になってくる。MySQLならば、ダンプコマンドを使ってえいっとSQLに直してしまうところなのだが、SQL Serverにはそんなものはない。

Excelを媒介にしてみる

適当なバックアップツールを作るのもよいのだが、ここは Excel を使って媒介してみよう、と考えた。SQL Server Management Studioにはデータのエクスポート先に、AccessやExcelを選ぶことができる。当然、Excel からインポートすることもできるのだが、ID の設定でちょっと手動なところがある。できることならば、一発でインポートしたいところだ。それに、Access じゃなく Excel でインポートできれば、ちょっとした修正ならば Excel で修正した後にインポートし直すということができる。いわゆるマスターテーブルの修正を、Excel で書き込んで、SQL Serverに書き戻すことができれば便利だろう、と思たたわけなのだが。

試しに Microsoft.Office.Interop.Excel を使って Cells で参照しながらちまちま読み込んで、SqlBulkCopy を使って一気にインポートすれば高速になるだろう、と思ったのだが…ああ、とてつもなく遅い。どうも COM 経由で Cells を参照しているところがむちゃくちゃ遅くて、1000件位の読み込みでも5,6分はかかってしまうという体たらくだ。たった1万件でも1時間程度かかってしまう見込みなので、これはちょっと実用に耐えない。
ちなみに、Range を使って二次元配列に読み込んでという方法を使うと高速化できるのだが、ちょっとセルをアクセスをすると遅くなるのと、デバッグ実行などで COM な Excel が残ったりして結構面倒なことが多い。
Range を二次元配列で読み取る方法は、後日示しておきたい。手軽な方法ではあるので。

ClosedXML に切り替える

XML形式で保存される xlsx のほうならば、Open XMLが使える。どうやら ClosedXML は内部的にOpen XML SDK を使っているそうなので、これを使ってみる。生の OpenXML を使うよりも断然 ClosedXML のほうが使いやすいという記事があるので、参考にさせてもらう。

【C#】Excelの取り扱いにClosedXMLを使用する – あたも技術ブログ
http://atamo-dev.hatenablog.com/entry/2017/07/23/180026

ClosedXMLを使った、Excel操作の例 ClosedXML
http://closedxml.codeplex.com/ https://gist.github.com/ishisaka/6128639

果たしてどれだけスピードアップするのか？と思って試してみたのだが、体感的には Microsoft.Office.Interop.Excel より ClosedXML のほうが100倍位早い。最初に xlsx ファイルを開くときには結構時間が掛かる（と言っても10秒位）。おそらく XML ファイル全てを読み込みパースするので時間が掛かっているのだろうけど、全体でかかる時間からすれば全然大したことはない。

ClosedXML で読み込んで SqlBulkCopy する

Excel から書き戻すのが目的なので、INSERTにはSqlBulkCopyを使う。SqlBulkCopyに渡すのはDataTableになるので、EFのエンティティクラス（単純な値クラス）からDataTableオブジェクトに変換した関数を使う。

NuGet で ClosedXML を取り込んで、XLWorkbook クラスで WorkBook を読み込む。

using ClosedXML.Excel;

private void clickReadClosedXml(object sender, RoutedEventArgs e)
{
    // 0.テーブル名を指定
    string tableName = "営業業務データ";
    // 1.Excel を開く
    string path = @"営業管理_20190201.xlsx";
    using (var wb = new XLWorkbook(path))
    {
        var sh = wb.Worksheets.FirstOrDefault(t => t.Name == tableName);
        // 2.シートからEFに読み込み
        var rc = new ClosedXmlRangeConverter<営業業務データ>(sh);
        var items = rc.ToList();
        // 3.データベースの「営業業務データ」に書き込み
        var ent = new testdbEntities();
        var dt = items.AsDataTable();
        var cn = ent.Database.Connection as SqlConnection;
        var bc = new SqlBulkCopy(cn);
        bc.DestinationTableName = tableName;
        cn.Open();
        bc.WriteToServer(dt);
        cn.Close();
        MessageBox.Show(&quot;データを保存しました&quot;);
    }
}

SSMS から Excel にエクスポートするとシート毎にテーブルのバックアップが作られる。これを wb.Worksheets.FirstOrDefault で見つける。Excel からセルを読み込んで List に返す自作のコンバーター ClosedXmlRangeConverter を作っている。
List を DataTable に書き直す拡張メソッド AsDataTable を作っておいて SqlBulkCopyのWriteToServer に渡す。

ちなみに、変換元の営業業務データクラスは、EFでデータベースから自動生成させたモデルクラスをそのまま使っている。

public partial class 営業業務データ
{
    public int 業務番号 { get; set; }
    public Nullable<int> 支店コード { get; set; }
    public string 支店名 { get; set; }
    public Nullable<int> 受注番号年度 { get; set; }
    public Nullable<int> 受注番号 { get; set; }
    public Nullable<int> 受注番号枝番 { get; set; }
    public Nullable<int> 未契約番号年度 { get; set; }
    public string 未契約番号 { get; set; }
    public string 社内業務番号 { get; set; }
    public Nullable<int> 営業担当者コード { get; set; }
    public string 営業担当者名 { get; set; }
    public Nullable<int> 発注者コード { get; set; }
    public string 発注者名 { get; set; }
    public string 発注番号 { get; set; }
	...

こんな風なテーブルになっている。

?IXLCellの拡張メソッド

IXLCell 自体は GetValue() で型変換ができるのだが、今回はエンティティクラスの各プロパティの型に合わせるので、リフレクションのPropertyInfoクラスで対象の型に変換できるように拡張メソッドを作っている。

/// CloseXmlのIXLCellの拡張メソッド
public static class ClosedXmlCellExtensions
{
    public static int ToInt(this IXLCell cell)
    {
        if (cell.Value == null) return 0;
        int result = 0;
        return int.TryParse(cell.Value.ToString(), out result) ? result : 0;
    }
    public static int? ToNullableInt(this IXLCell cell)
    {
        if (cell.Value == null) return (int?)null;
        int result = 0;
        return int.TryParse(cell.Value.ToString(), out result) ? result : (int?)null;
    }
    public static double ToDouble(this IXLCell cell)
    {
        if (cell.Value == null) return 0.0;
        double result = 0.0;
        return double.TryParse(cell.Value.ToString(), out result) ? result : 0.0;
    }
    public static double? ToNullableDouble(this IXLCell cell)
    {
        if (cell.Value == null) return (double?)null;
        double result = 0.0;
        return double.TryParse(cell.Value.ToString(), out result) ? result : (double?)null;
    }
    public static decimal ToDecimal(this IXLCell cell)
    {
        if (cell.Value == null) return 0;
        decimal result = 0;
        return decimal.TryParse(cell.Value.ToString(), out result) ? result : 0;
    }
    public static decimal? ToNullableDecimal(this IXLCell cell)
    {
        if (cell.Value == null) return (decimal?)null;
        decimal result = 0;
        return decimal.TryParse(cell.Value.ToString(), out result) ? result : (decimal?)null;
    }
    public static DateTime ToDateTime(this IXLCell cell)
    {
        if (cell.Value == null) return new DateTime();
        DateTime result = new DateTime();
        return DateTime.TryParse(cell.Value.ToString(), out result) ? result : new DateTime();
    }
    public static DateTime? ToNullableDateTime(this IXLCell cell)
    {
        if (cell.Value == null) return (DateTime?)null;
        DateTime result = new DateTime();
        return DateTime.TryParse(cell.Value.ToString(), out result) ? result : (DateTime?)null;
    }
    public static bool ToBoolean(this IXLCell cell)
    {
        if (cell.Value == null) return false;
        bool result = false;
        return bool.TryParse(cell.Value.ToString(), out result) ? result : false;
    }
    public static bool? ToNullableBoolean(this IXLCell cell)
    {
        if (cell.Value == null) return (bool?)null;
        bool result = false;
        return bool.TryParse(cell.Value.ToString(), out result) ? result : (bool?)null;
    }
    public static string ToText(this IXLCell cell)
    {
        if (cell.Value == null) return "";
        return cell.Value.ToString();
    }

    public static object To(this IXLCell cell, System.Reflection.PropertyInfo pi)
    {
        var pt = pi.PropertyType;
        if (pt == typeof(int)) return cell.ToInt();
        if (pt == typeof(int?)) return cell.ToNullableInt();
        if (pt == typeof(double)) return cell.ToDouble();
        if (pt == typeof(double?)) return cell.ToNullableDouble();
        if (pt == typeof(bool)) return cell.ToBoolean();
        if (pt == typeof(bool?)) return cell.ToNullableBoolean();
        if (pt == typeof(DateTime)) return cell.ToDateTime();
        if (pt == typeof(DateTime?)) return cell.ToNullableDateTime();
        if (pt == typeof(string)) return cell.ToText();

        return null;
    }
}

コンバーター ClosedXmlRangeConverter クラス

IXLWorksheet の内容を読み込んで、目的のエンティティクラスのリストを作るためのコンバーターを作成する。シートの1行目にテーブルの列名が入っている想定で作ってある。

/// RangeからEFへのコンバーター
/// Entity Frameworkで利用するエンティティクラスに読み込む
/// 主に ClosedXmlのExcel からデータを読み込み、EF でデータベースに出力するときに使う
public class ClosedXmlRangeConverter<T> where T : class, new()
{
    protected List<System.Reflection.PropertyInfo> _Columns = new List<System.Reflection.PropertyInfo>();
    protected IXLWorksheet _sh;
    /// コンバーターの作成
    public ClosedXmlRangeConverter(IXLWorksheet sh)
    {
        _sh = sh;
        // 最初の行をコンバート先のテーブルと照合する
        var props = typeof(T).GetProperties();
        int col = 1;
        while (sh.Cell(1, col).Value.ToString() != &quot;&quot;)
        {
            var text = sh.Cell(1, col).Value.ToString();
            var prop = props.FirstOrDefault(t => t.Name == text);
            if (prop != null)
            {
                _Columns.Add(prop);
            }
            col++;
        }
    }

    /// 行単位でコンバート
    public T ToItem(int row)
    {
        var item = new T();
        for (int col = 0; col < this._Columns.Count; col++)
        {
            var prop = _Columns[col];
            var o = _sh.Cell(row, col + 1).To(prop);
            prop.SetValue(item, o);
        }
        return item;
    }
    /// 全てのデータをコンバート
    public List<T> ToList()
    {
        var items = new List<T>();
        int r = 2;
        while (_sh.Cell(r, 1).GetValue<string>() != &quot;&quot;)
        {
            var item = this.ToItem(r);
            items.Add(item);
            r++;
        }
        return items;
    }
}

エンティティクラスをDataTableに変換する拡張メソッド

DbSet から DataTable を作成するための AsDataTable() を作る。そのままでは、DataRow の型に Nullable が入らないので、DBNull への切り替えを行っている。

/// 
<summary>
/// DbSetをDataTableに変換
/// </summary>

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)
        {
            if ( prop.PropertyType.IsGenericType == true &amp;&amp;
                    prop.PropertyType.GetGenericTypeDefinition().Name == &quot;Nullable`1&quot;)
            {
                /// Nullable<int>のときは、
                /// DataRowの中身を DBNull と int の時に分けなければいけない。
                var originalType = prop.PropertyType.GetProperty(&quot;Value&quot;).PropertyType;
                var column = new DataColumn();
                column.DataType = originalType;
                column.AllowDBNull = true;
                column.ColumnName = prop.Name;
                dest.Columns.Add(column);
            }
            else
            {
                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 ?? System.DBNull.Value;
            }
            dest.Rows.Add(row);
        }
        return dest;
    }
}

計測はあとで

これで Excel シートから SQL Server への書き戻しが高速に行える。全てのデータを書きこ戻してしまうので、ピンポイントで修正することはできないがマスターテーブルの書き換えとかを Excel 上で行って、SQL Server に戻すことができれば結構便利だと思う。修正自体もお客さんに行って貰うこともできそうだし。

スピードは、COM 経由よりも100倍位早いのだが、これは後で実測してみる。

カテゴリー: 開発 パーマリンク