.net

【.net】Excel出力高速化まとめ

.netでExcelを出力する際に、
処理が遅い場合の見直しポイントを備忘録として残します。

  1. データを2次元配列にして一括で渡す
  2. プロパテイの見直し

大事なことは、Excelへのアクセス回数を最小限にすることに尽きます。
1セルずつRangeやCellにアクセスして、
値設定 ⇒ 参照の解放 としていると、
1万セル程度でも相当遅くなってしまいます。
あとはExcelのプロパティ設定ですね、
当記事では、よく言われる下記の3つのプロパティを検証します。

  • ScreenUpdating(描画更新有無)
  • EnableEvents(イベントの抑制有無)
  • Calculation(自動計算の自動/手動)

検証条件は↓

  • サードパーティ製のExcel出力ライブラリは使用しない
  • 100行 * 100列出力
  • 101行目は各列ごとのSumを設定しておく(Calculation検証のため)
  • Worksheet_Changeイベントを作成し、各列の合計値を102行目に出力(EnableEvents検証のため)
  • マシンスペック(高し)

サンプルソース

※遅延バインディングしているため、参照の設定は不要です。

今回、アクセス方法や、プロパティの設定による速度差がどの程度でるのか検証します。
まず、VisualStudio(c#)でテストフォームを作成します。

検証用フォーム

次に処理です。
各ボタンクリックイベントは省略。
メイン関数のみ載せます。


private void Export(bool isBulk, bool screenUpdating, bool enableEvents, int calculation, TextBox txtResult)
{
    Stopwatch sw = new Stopwatch();
    sw.Start();

    dynamic xlApp = null;
    dynamic xlWbooks = null;
    dynamic xlWbook = null;
    dynamic xlSheets = null;
    dynamic xlSheet = null;
    dynamic xlRange = null;
    dynamic xlCell = null;
    try
    {
        Type objectClassType = Type.GetTypeFromProgID("Excel.Application");
        xlApp = Activator.CreateInstance(objectClassType);
        xlApp.ScreenUpdating = screenUpdating;
        xlApp.EnableEvents = enableEvents;

        xlWbooks = xlApp.Workbooks;
        xlWbook = xlWbooks.Open(@"C:\file\Book1.xlsm");
        xlApp.Calculation = calculation;

        xlSheets = xlWbook.Worksheets;
        xlSheet = xlSheets.Item("Sheet1");

        if (isBulk) //一括の場合
        {
            object[,] values = new object[100, 100]; 
            for (int iRow = 0; iRow < 100; iRow++)
            {
                for (int iCol = 0; iCol < 100; iCol++)
                {
                    values[iRow, iCol] = (iRow + 1).ToString() + (iCol + 1).ToString();
                }
            }

            try
            {
                xlRange = xlSheet.Range("A1:CV100");
                xlRange.Value = values;
            }
            finally
            {
                Marshal.ReleaseComObject(xlRange);
            }
        }
        else //1セルずつの場合
        {
            for (int iRow = 1; iRow <= 100; iRow++)
            {
                for (int iCol = 1; iCol <= 100; iCol++)
                {
                    try
                    {
                        xlCell = xlSheet.Cells(iRow, iCol);
                        xlCell.Value = (iRow).ToString() + (iCol).ToString();
                    }
                    finally
                    {
                        Marshal.ReleaseComObject(xlCell);
                    }
                }
            }
        }
        
        xlApp.Calculation = CALCULATION_DEFAULT;
        xlWbook.Save();
    }
    finally
    {
        if (xlWbook != null)
        {
            xlWbook.Saved = true;
        }
        xlApp.EnableEvents = true;
        xlApp.ScreenUpdating = true;

        //COMオブジェクトの開放
        Marshal.ReleaseComObject(xlSheet);
        Marshal.ReleaseComObject(xlSheets);
        Marshal.ReleaseComObject(xlWbook);
        Marshal.ReleaseComObject(xlWbooks);
        //Excelアプリケーション終了
        xlApp.Quit();
        Marshal.ReleaseComObject(xlApp);

        //計測結果表示
        sw.Stop();
        TimeSpan ts = sw.Elapsed;
        txtResult.Text = $"{ts.TotalSeconds:0.00}";
    }
}

計測結果

実際の計測結果です。
単位は秒です。

項目 1セルずつ出力 一括出力
オプション無 27.83 1.04
ScreenUpdatingのみ設定 25.48 1.20
EnableEventsのみ設定 19.98 1.00
Calculationのみ設定 23.77 1.03
すべて設定 18.66 1.10

まずアクセス方法の違い
1セルずつ出力か、一括出力かで圧倒的に差が出ますね。

あとはプロパティにより異なりますが
この検証条件だとScreenUpdatingよりも、EnableEventsの方が効果大ですね。
イベントの設定がない場合だと、圧倒的にScreenUpdatingの方が体感できます。

一括出力のほうのプロパティ毎の差は誤差みたいなものでしょう。


まとめ

  • 2次元配列を作成し、一括設定
  • プロパティは設定した方が良い、がケースにより効果増減あり

おしまい。

コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です