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

Question

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

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

 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() ならＯＫ

何が悪いのでしょうか？

Answer 1

単に時間がかかっているだけということはないでしょうか？
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/

Answer 2

ClosedXMLなのでちょっと違うと思われますが、自動計算などは関係あるかもしれません。

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

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

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

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

Answer 3

単に時間がかかっているだけということはないでしょうか？
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がどこの下にいるのか分かりません。

Answer 4

ＣＰＵは３４％程度
メモリは５６％程度
で安定しています。

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


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

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

Answer 5

それは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)な感じ

Answer 6

ApplicationやxlCalculationManualがどこの下にいるのか分かりません。

XLWorkbook1 .CalculateMode = XLCalculateMode.Manual
・
・
・
XLWorkbook1 .CalculateMode = XLCalculateMode.Auto

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

---

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

Answer 7

ＣＰＵは３４％程度
メモリは５６％程度
で安定しています。

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


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

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

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

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

Answer 8

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

こういう状態で実行しましたが、フリーズ変わらずです。

Answer 9

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

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

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

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

Answer 10

１行でも10秒以上かかりました。
１０行だと既にフリーズでした。

例えば

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

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


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

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

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

いや、でも、
普通にエクセル開いてマウスでクリックして削除してもここまで遅延しないし、フリーズもしないし、何なのでしょうか。

Answer 11

For Loop を昇順にせず、降順にしても変わりませんか？。

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

Answer 12

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

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

　◇　◇　◇　◇　◇

１０行

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

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

　◇　◇　◇　◇　◇

１行

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)
でないとフリーズする。

Answer 13

ちなみに、「範囲指定しないなら、行指定でいいやん」と思って、

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秒程度。

１．原本をコピー
２．コピーをデータ編集
３．余った行を削除

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


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

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

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

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

Answer 14

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

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

Answer 15

＞印刷範囲外の暫定処置から一歩進んで、もう一回、適正範囲を別のシートにコピーして、用済み後ファイル削除では？。

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



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

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

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

速度改善の為に

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

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

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

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

Answer 16

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

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

Answer 17

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

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

---

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

Answer 18

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

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

どうでしょうか。。。

１．描画しながらデータを書く

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

３．描画済の行を削除


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

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

Answer 19

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分以上かかると文句を言ってるのが恥ずかしくなるレベルです。



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