none
ClosedXMLでエクセルの行削除でフリーズします RRS feed

  • 質問

  • ClosedXMLでエクセルの行削除でフリーズします

    書式と初期セットを施したエクセルを用意し、
    それを複写してデータをセットし、
    最後に余分な行を消去して終わりにしたいのですが、行削除でフリーズします。
    同じ行を指定すると1行は消えます。

     Using XLWorkbook1 = New ClosedXML.Excel.XLWorkbook(StrFilePath)
         Dim Worksheet1 = XLWorkbook1.Worksheet(1)
         Worksheet1.Rows("90:999").Delete() ←ここでフリーズ
         XLWorkbook1.Save()
     End Using

     Worksheet1.Rows("90:90").Delete() ならOK

    何が悪いのでしょうか?
    2017年5月23日 6:40

回答

  • CopyToもパフォーマンスが悪そうなので、手を入れる必要があるようです。
    最新のClosedXMLでは改善されている可能性もありますが・・・

    ClosedXMLのパフォーマンス改善
    http://pg.w-news.jp/?eid=15


    ★良い回答には回答済みマークを付けよう! MVP - .NET  http://d.hatena.ne.jp/trapemiya/

    2017年5月25日 7:24
    モデレータ

すべての返信

  • 単に時間がかかっているだけということはないでしょうか?
    Excelで大量の行を処理しようとした場合、自動計算や画面の描画が有効になっていると、Excelシート上で変化がある度に、いちいち自動計算や画面描写が動き、非常に時間がかかってしまいます。
    以下などを参考にして、無効にすることを試してみて下さい。

    VBA マクロ高速化のために停止すべき3項目
    http://thom.hateblo.jp/entry/2015/08/31/063500

    自動再計算のOn/Off
    http://bbs.wankuma.com/index.cgi?mode=al2&namber=70277&KLOG=120


    ★良い回答には回答済みマークを付けよう! MVP - .NET  http://d.hatena.ne.jp/trapemiya/

    2017年5月23日 7:36
    モデレータ
  • ClosedXMLなのでちょっと違うと思われますが、自動計算などは関係あるかもしれません。

    ClosedXMLにもパフォーマンス観点で多少ノウハウはあった気がします。

    ただ、本当にフリーズなどの不具合が発生している可能性も無きにしも非ずな気がします。

    フリーズすると、そのままずっと戻ってこないでしょうか?

    また、CPU使用率やメモリ使用量などはどんな感じでしょうか?

    2017年5月23日 10:50
  • 単に時間がかかっているだけということはないでしょうか?
    Excelで大量の行を処理しようとした場合、自動計算や画面の描画が有効になっていると、Excelシート上で変化がある度に、いちいち自動計算や画面描写が動き、非常に時間がかかってしまいます。
    以下などを参考にして、無効にすることを試してみて下さい。

    VBA マクロ高速化のために停止すべき3項目
    http://thom.hateblo.jp/entry/2015/08/31/063500

    自動再計算のOn/Off
    http://bbs.wankuma.com/index.cgi?mode=al2&namber=70277&KLOG=120


    ★良い回答には回答済みマークを付けよう! MVP - .NET  http://d.hatena.ne.jp/trapemiya/


    Application.ScreenUpdating = False '描画停止
    Application.EnableEvents = False 'イベント抑制
    Application.Calculation = xlCalculationManual '手動計算

    ApplicationやxlCalculationManualがどこの下にいるのか分かりません。
    2017年5月24日 0:22
  • CPUは34%程度
    メモリは56%程度
    で安定しています。

    開発上で実行すると、下記のメッセージで止まります。
    「続行」すると続行可能です。


    マネージ デバッグ アシスタント 'ContextSwitchDeadlock' が 'C:\Users\user1\Documents\test1\bin\x64\Release\test1.vshost.exe' で問題を検出しました。

    追加情報:CLR は、COM コンテキスト 0xddf27d98 から COM コンテキスト 0xddf27ec0 へ 60 秒で移行できませんでした。
    ターゲット コンテキストおよびアパートメントを所有するスレッドが、ポンプしない待機を行っているか、Windows のメッセージを表示しないで非常に長い実行操作を処理しているかのどちらかです。
    この状態は通常、パフォーマンスを低下させたり、アプリケーションが応答していない状態および増え続けるメモリ使用を導く可能性があります。
    この問題を回避するには、すべての Single Thread Apartment (STA) のスレッドが、CoWaitForMultipleHandles のようなポンプする待機プリミティブを使用するか、長い実行操作中に定期的にメッセージをポンプしなければなりません。
    2017年5月24日 0:27
  • それはClosedXMLを使う方法ではない(Excelを使用する方法)ですので、ClosedXMLでは別の方法または該当する手段はない(関係しない)ということになります。

    とりあえず、ClosedXMLに関しては、イベントトラッキングが重くなる原因の場合があって、オフにすることが推奨されているようです。

    XLWorkbookのコンストラクタに、XLEventTracking.Disabledを渡すようにしてみてください。

    Using XLWorkbook1 = New ClosedXML.Excel.XLWorkbook(StrFilePath)

    Using XLWorkbook1 = New ClosedXML.Excel.XLWorkbook(StrFilePath, ClosedXML.Excel.XLEventTracking.Disabled)な感じ

    2017年5月24日 2:31
  • ApplicationやxlCalculationManualがどこの下にいるのか分かりません。

    XLWorkbook1 .CalculateMode = XLCalculateMode.Manual



    XLWorkbook1 .CalculateMode = XLCalculateMode.Auto

    のようにすると良いようです。
    ただ、エラーメッセージからするとこれが原因ではなさそうな気はしますが、とりあえず試してみて何か変化があるかですね・・・


    ★良い回答には回答済みマークを付けよう! MVP - .NET  http://d.hatena.ne.jp/trapemiya/

    2017年5月24日 2:45
    モデレータ
  • CPUは34%程度
    メモリは56%程度
    で安定しています。

    開発上で実行すると、下記のメッセージで止まります。
    「続行」すると続行可能です。


    マネージ デバッグ アシスタント 'ContextSwitchDeadlock' が 'C:\Users\user1\Documents\test1\bin\x64\Release\test1.vshost.exe' で問題を検出しました。

    追加情報:CLR は、COM コンテキスト 0xddf27d98 から COM コンテキスト 0xddf27ec0 へ 60 秒で移行できませんでした。

    ClosedXML自体はCOM関係ないですので、このエラーについては因果関係が逆だと思います。

    ※ClosedXMLでの処理に時間がかかりすぎたせいで、上記のMDAに引っかかった

    • 編集済み なちゃ 2017年5月24日 3:19
    2017年5月24日 2:57
  • Using XLWorkbook1 = New ClosedXML.Excel.XLWorkbook(StrFilePath, ClosedXML.Excel.XLEventTracking.Disabled)
         Dim Worksheet1 = XLWorkbook1.Worksheet(1)

         XLWorkbook1.CalculateMode = ClosedXML.Excel.XLCalculateMode.Manual
         Worksheet1.Rows("90:999").Delete()
         XLWorkbook1.CalculateMode = ClosedXML.Excel.XLCalculateMode.Auto

         XLWorkbook1.Save()
    End Using

    こういう状態で実行しましたが、フリーズ変わらずです。
    2017年5月24日 3:11
  • Worksheet1.Rows("90:999").Delete()

    この対象数を、問題が発生しない1レコード分から徐々に増やしていくとどうなりますか?

    たとえば10行程度だとどうか、100行程度だとどうか、というのをもう少し細かく試して、実行時間が徐々に伸びていくのか、どこかでいきなり無反応になってしまうのかなど。

    徐々に時間が伸びていく場合は、件数とかかる時間の関係がどうなっているかも。

    2017年5月24日 4:04
  • 1行でも10秒以上かかりました。
    10行だと既にフリーズでした。

    例えば

    For i As Integer = 90 To 999
        Worksheet1.Rows(i.ToString & ":" & i.ToString).Delete() ← ブレイクポイント
    Next

    みたいに書いて、
    ブレイクポイントでF10押す感じでやってみましたが、
    F5押すたびに10数秒かかり、
    ブレイクポイントを外して動かしたら同じフリーズでした。
    レンジで消そうが1行づつ消そうがそこは変わりない感じでした。


    余談ですが、
    今、気付くと、消す行をカウントアップしてしまうと、
    削除された事で行が繰り上がってきて半分ぐらい消えない事になりますね。

    フリーズで終わらせているのでアウトプットを確認していませんが、
    90行目を削除すると、元々91行目だった行が90行目に来るので、
    次に91行目を削除しようとすると、元の91行目の削除が空振りして、元の92行目を削除する。
    回すたびに空振りが増えて半分しか消えない。

    あえてバグの無駄話をしたのは、1行削除すると、行の繰上りが毎回発生しているんだと、改めて実感したという話です。

    いや、でも、
    普通にエクセル開いてマウスでクリックして削除してもここまで遅延しないし、フリーズもしないし、何なのでしょうか。
    2017年5月24日 8:18
  • For Loop を昇順にせず、降順にしても変わりませんか?。

            For i As Integer = 999 To 90 Step -1
                'exec
            Next

    2017年5月24日 8:40
  • For Loop を昇順にせず、降順にしても変わりませんか?。

    Using XLWorkbook1 = New ClosedXML.Excel.XLWorkbook(StrFilePath)

    Using XLWorkbook1 = New ClosedXML.Excel.XLWorkbook(StrFilePath, ClosedXML.Excel.XLEventTracking.Disabled)

    これは、効果がありました。
    やっていないと、全く動きません。

     ◇ ◇ ◇ ◇ ◇

    XLWorkbook1.CalculateMode = ClosedXML.Excel.XLCalculateMode.Manual
    XLWorkbook1.CalculateMode = ClosedXML.Excel.XLCalculateMode.Auto

    これは、全く効果がありませんでした。

     ◇ ◇ ◇ ◇ ◇

    全部まとめて

    Worksheet1.Rows("90:999").Delete()

    どのような設定でも必ずフリーズ。

     ◇ ◇ ◇ ◇ ◇

    10行

    Worksheet1.Rows("90:100").Delete()

    どのような設定でも必ずフリーズ。

     ◇ ◇ ◇ ◇ ◇

    1行

    Worksheet1.Rows("90:90").Delete()

    完了する

     ◇ ◇ ◇ ◇ ◇

    カウントアップ

    For i As Integer = 90 To 999
        Worksheet1.Rows(i.ToString & ":" & i.ToString).Delete()
    Next

    フリーズするが、同じコードで複数回やると、たまに70秒弱で完了する。

    Using XLWorkbook1 = New ClosedXML.Excel.XLWorkbook(StrFilePath, ClosedXML.Excel.XLEventTracking.Disabled)
    でないとフリーズする。

     ◇ ◇ ◇ ◇ ◇

    カウントダウン

    For i As Integer = 999 To 90 Step - 1
        Worksheet1.Rows(i.ToString & ":" & i.ToString).Delete()
    Next

    これも70秒弱で完了するが、フリーズもする。
    速度は変わらないがカウントアップより、成功率が高い。

    Using XLWorkbook1 = New ClosedXML.Excel.XLWorkbook(StrFilePath, ClosedXML.Excel.XLEventTracking.Disabled)
    でないとフリーズする。

     ◇ ◇ ◇ ◇ ◇

    ダルマ落とし方式

    For i As Integer = 90 To 999
        Worksheet1.Rows("90:90").Delete()
    Next

    40秒程度で完了する。
    これが一番マシ。でも、たまにフリーズする。

    Using XLWorkbook1 = New ClosedXML.Excel.XLWorkbook(StrFilePath, ClosedXML.Excel.XLEventTracking.Disabled)
    でないとフリーズする。

    2017年5月25日 1:19
  • ちなみに、「範囲指定しないなら、行指定でいいやん」と思って、

    For i As Integer = 90 To 999
        Worksheet1.Row(90).Delete()
    Next

    こうのようしても、
    For i As Integer = 90 To 999
        Worksheet1.Rows("90:90").Delete()
    Next

    と同じで、40秒程度。

    1.原本をコピー
    2.コピーをデータ編集
    3.余った行を削除

    のうち、
    1と2を合わせても1秒掛からないけど、3を入れる事で40秒かかる。
    ファイルは1つじゃないので、40秒は待てない。
    何か方法ないですか?


    '印刷範囲指定
    With Worksheet1.PageSetup
        .PrintAreas.Add("A1:O89")
    End With

    削除が遅すぎるので、とりあえずは消さずに放置して、印刷範囲外にしてしまう事で暫定処置をしています。

    これだと1秒掛からない。

    行削除の高速化は不可能でしょうか?

    2017年5月25日 1:38
  • 行削除の高速化には答えられませんが、
    印刷範囲外の暫定処置から一歩進んで、もう一回、適正範囲を別のシートにコピーして、用済み後ファイル削除では?。

    (脱線: 気になるのは、編集時に、不要なデータをシートに書かなければいけないのを回避出来ないのかなぁと言う素朴な疑問。)
    2017年5月25日 3:24
  • >印刷範囲外の暫定処置から一歩進んで、もう一回、適正範囲を別のシートにコピーして、用済み後ファイル削除では?。

    言うのは簡単だけど、すぐには、やり方が分かんないです。



    >脱線: 気になるのは、編集時に、不要なデータをシートに書かなければいけないのを回避出来ないのかなぁと言う素朴な疑問。

    回避する方法があれば教えてください。

    全体書式を書きながら
    ヘッダーを書きながら
    明細データを書きながら
    罫線を引きながら
    ってな感じでやってたのですけど、すごく遅いので、

    速度改善の為に

    書式を予め作っておいて、
    それをコピーして、
    そこに明細データだけ書き込んでいくというやり方にした。
    すると、秒で終わったので、そこまでは大成功。

    でも明細って何件あるかわかんないから、
    最大でこのぐらいあれば絶対超えないだろうという件数分の書式を用意している。
    最後にそれを掃除したら完了なのですけど、
    その部分が遅くて、書式もデータもVBで書くのと変わらない。

    というか、
    少ない件数だと、消す分が多くて、式もデータもVBで書く方が速い。
    逆に、最大件数に近いと、消す行が少ないので効果はあるという状態。

    とにかく、処理が遅すぎて話にならない。

    2017年5月25日 5:21
  • 明細のような形式であれば、別シートに1行分のテンプレートとなる行を用意しておき、それをIXLRow::CopyToでコピーする、という手法はどうでしょうか?

    // コピーのコストが大きいのであれば使えませんが…。

    2017年5月25日 5:58
  • CopyToもパフォーマンスが悪そうなので、手を入れる必要があるようです。
    最新のClosedXMLでは改善されている可能性もありますが・・・

    ClosedXMLのパフォーマンス改善
    http://pg.w-news.jp/?eid=15


    ★良い回答には回答済みマークを付けよう! MVP - .NET  http://d.hatena.ne.jp/trapemiya/

    2017年5月25日 7:24
    モデレータ
  • 明細のような形式であれば、別シートに1行分のテンプレートとなる行を用意しておき、それをIXLRow::CopyToでコピーする、という手法はどうでしょうか?

    // コピーのコストが大きいのであれば使えませんが…。

    どうでしょうか。。。

    1.描画しながらデータを書く

    2.描画済の行をコピーして、データを上書き

    3.描画済の行を削除


    普通に考えたら、3が一番速いハズなんですけど。。。

    2が速いなら、削除じゃなくて、空行を描画済の行の上に上書きするのも速いのでしょうか?

    2017年5月25日 7:27
  • CopyToもパフォーマンスが悪そうなので、手を入れる必要があるようです。
    最新のClosedXMLでは改善されている可能性もありますが・・・

    ClosedXMLのパフォーマンス改善
    http://pg.w-news.jp/?eid=15


    ★良い回答には回答済みマークを付けよう! MVP - .NET  http://d.hatena.ne.jp/trapemiya/


    拝見しました。

    もともと、ClosedXMLで書式をいじるとパフォーマンスが悪いのですね。

    後半に恐ろしい事を書いていますね。

    『結合セルを10個含む行のコピーで、対応前は1.5時間程度だったのが、1分弱になりました。』

    900行削除するのに1分以上かかると文句を言ってるのが恥ずかしくなるレベルです。



    『消さずに放置して、印刷範囲外にしてしまう』という暫定対応で特に問題が無いので、
    諦めモードで期待せずに動向を見守ることにします。
    2017年5月25日 7:35