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

前回のサンプルプログラムの性能を確認します。テストプログラム側を修正し、100ファイルを出力する際の処理時間を計測します。

List 3: Test GUI

private void button1_Click(object sender, EventArgs e)
{

    DefaultTraceListener dtl = (DefaultTraceListener)Debug.Listeners["Default"];
    dtl.LogFileName = @"c:\Sample.log";


    Util.Ora2Excel x = new Util.Ora2Excel();

    x.OpenDb(txtUser.Text, txtPassword.Text, txtConnString.Text);
    x.InitExcel();

    DateTime lapTime;
    DateTime startTime = DateTime.Now;

    for (int i = 0; i < 100; i++)
    {
        lapTime = DateTime.Now;
        x.OpenBook(txtTemplate.Text);
        Debug.WriteLine("[file open] " + (DateTime.Now - lapTime).ToString());

        lapTime = DateTime.Now;
        x.Extract(txtSql.Text, txtSheet.Text);
        Debug.WriteLine("[extract] " + (DateTime.Now - lapTime).ToString());

        lapTime = DateTime.Now;
        x.SaveBook(@"d:\result_emp" + i.ToString() + ".xlsx");
        Debug.WriteLine("[save] " + (DateTime.Now - lapTime).ToString());

        lapTime = DateTime.Now;
        x.CloseBook();
        Debug.WriteLine("[close] " + (DateTime.Now - lapTime).ToString());
    }

    Debug.WriteLine("[total] " + (DateTime.Now - startTime).ToString());

    x.QuitExcel();
    x.CloseDb();
}

上記の処理結果ログを集計し、1ファイルあたりの平均処理時間を算出した結果は以下の通りです。最も長い処理イベントはデータをエクセルシートに展開する部分であることがわかります。

Figure 5: Events

エクセルシートに展開する部分についてより詳細を確認するため、DLL内の処理を以下のように修正し、再度計測を行います。

List 4:

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

    int idxRow;
    DateTime lapTime;

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

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

    idxRow = xApplication.ActiveCell.Row;
            
    while (rst.Read())                          // output loop
    {
        lapTime = DateTime.Now;
        xSheet.Rows[idxRow].Copy();
        xSheet.Rows[idxRow+1].Insert(Shift: XL_DOWN);
        Debug.WriteLine("[insert row] " + (DateTime.Now - lapTime).ToString());

        lapTime = DateTime.Now;
        for (int idxCol = 0; idxCol < rst.FieldCount; idxCol++)
            xSheet.Cells[idxRow,idxCol+1].value = rst.GetValue(idxCol);
        Debug.WriteLine("[set cell val] " + (DateTime.Now - lapTime).ToString());

        idxRow++;
    }
    rst.Close();
}

結果は以下の通りです。セルへのデータ設定と行のインサート処理の占める割合が大きいことがわかります。

Figure 6: Events in DLL

今回のケースでは、10行程度の出力で、1ファイルあたり約1.5秒を要することが確認できます。
次回はデータをエクセルに出力する部分のソースを変更し、性能の改善を図ります。

---

[Summary]

The sample program shown in List 3 outputs 100 excel files (The original source is in the last post). This source writes down the lap time to log file.

Figure 5 shows that the longest event is extracting the data onto Excel worksheet.  Extracting takes about 1.2 second per a file.
To see more detailed log, see List 4 and Figure 6. You see that the cell and row manipulations take long time.

In the next post, these manipulations will be improved.