エクセルファイルのバッチ出力 その3 (Excel file processing, Part 3)

エクセルへのデータ展開を高速化する方法についてはマイクロソフト社のナレッジに説明されています。

Visual C# 2005 または Visual C# .NET を使用してデータを Excel ブックに転送する方法:

http://support.microsoft.com/kb/306023/ja

具体的には、データを各セルに1つ1つ設定するのではなく、データを配列に格納した上で、配列をエクセルのレンジに渡す処理を実装します。この実装によりエクセルとの通信回数が減り、処理時間が短縮されます。

サンプルのソースは以下の通りです。

List 5:

public void Extract(string sql, string SheetName)
{
    const int XL_WHOLE = 1;
    const int XL_DOWN = -4121;
    const string START_TAG = "$start";

    int colCount;
    List<object[]> lst = new List<object[]>();
    DateTime lapTime;

    lapTime = DateTime.Now;
    OleDbDataReader rst = GetRecordset(sql);    // get recordset
    colCount = rst.FieldCount;
    Debug.WriteLine("[query] " + (DateTime.Now - lapTime).ToString());

    lapTime = DateTime.Now;
    while (rst.Read())                          // output loop
    {
        object[] ColValues = new Object[rst.FieldCount];
        for (int idxCol = 0; idxCol < rst.FieldCount; idxCol++)
            ColValues[idxCol] = rst.GetValue(idxCol);
        lst.Add(ColValues);
    }
    Debug.WriteLine("[set row val] " + (DateTime.Now - lapTime).ToString());
    rst.Close();

    // convert list => array
    lapTime = DateTime.Now;
    object[,] Values2Copy = new Object[lst.Count, colCount];
    for (int idxRow = 0; idxRow < lst.Count; idxRow++)
        for (int idxCol = 0; idxCol < colCount; idxCol++)
            Values2Copy[idxRow, idxCol] = lst[idxRow][idxCol];
    Debug.WriteLine("[list to array] " + (DateTime.Now - lapTime).ToString());

    // copy array into excel sheet.
    lapTime = DateTime.Now;
    xSheet = xBook.Worksheets[SheetName];       // activate start position
    Debug.WriteLine("[activate sheet] " + (DateTime.Now - lapTime).ToString());

    lapTime = DateTime.Now;
    xSheet.Cells.Find(What: START_TAG, LookAt: XL_WHOLE).Activate();
    Debug.WriteLine("[find $start] " + (DateTime.Now - lapTime).ToString());

    lapTime = DateTime.Now;
    int idxStart = xApplication.ActiveCell.Row;
    xSheet.Rows[xApplication.ActiveCell.Row].Copy();
    xSheet.Rows[(idxStart + 1).ToString() + ":" + (idxStart + lst.Count-1).ToString()].Insert(Shift: XL_DOWN);
    Debug.WriteLine("[insert row] " + (DateTime.Now - lapTime).ToString());

    lapTime = DateTime.Now;
    Range r = xApplication.ActiveCell;
    r = r.get_Resize(lst.Count, colCount);
    r.set_Value(Missing.Value, Values2Copy);
    Debug.WriteLine("[copy into range] " + (DateTime.Now - lapTime).ToString());
}

実行時にレコードセットの列数と行数を動的に判断させるため、上記のソースでは一度データをListに関連付けたObjectに格納し、データをすべて抽出した後、Listの内容を配列に代入します。

上記サンプルの実行結果（経過時間）は以下の通りです。

Figure 7: Events

データ設定部分のイベント（extract）が大幅に低減（1.24秒→0.04秒）していることが確認できます。全体（1ファイルあたり）の処理時間も1.5秒から0.3秒に短縮されました。帳票出力のバッチとしては遅いといえますが、Excelオートメーションを利用する場合、この程度を目安にする必要があるといえます。
※なお、大量データを1つのシートに保存するようなケースでは、ファイルのオープン/クローズに要する時間の割合が低下するため、処理効率は向上します。

DLL処理部の内訳は以下の通りです。※時間の縮尺が異なります。

Figure 8: Events in DLL

エクセルオブジェクトの操作の操作に関わる部分で処理時間を要していることが確認できます。

---

[Summary]
To speed up Excel operation, please refer to the following Microsoft Knowledge Base.


How to transfer data to an Excel workbook by using Visual C# 2005 or Visual C# .NET
http://support.microsoft.com/kb/306023/en-us


The program transfer two dimension array to a range of multiple cells at one time.  This technique runs faster than passing data cell by cell.
List 5 uses List to store the fetched rows.  This is because the sample program determines the number of rows and columns in runtime.


Figure 7 shows elapsed time per file.  The main part (extract) is reduced from 1.24 sec to 0.04 sec.  So, the total elapsed time is 1.5 sec to 0.3 sec per each.  (Still it is slow, though)


Inside "extract", please see Figure 8.  (*) Note that time scale is different.