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

.net

2018-10-03 mitosuya 0 Comment

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

データを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