This page should be a guide to avoid common performance pitfalls.

If you need to insert or delete more than one row or column or cell, don't do it in a loop. Use a range to perform the operations.

Example:

ws.Range("A1:A3000").Delete()

ClosedXML has to keep the internal state consistent. Inserting/deleting cells shifts other cells and they must be updated. If you delete a row, all rows below it must be updated to reflect the removed row, e.g.

• cell numbers have to be updated
• formulas that referenced the row must be shifted one row up (e.g.A5*4 turns intoA4*4)

Looping means the housekeeping must be done individually for each deleted/inserted row. That leads to O(n^2) complexity.

for(varrow=1;row<3000;++row)ws.Cell(row,1).Value=1;// First loop must update 3000 rows, second loop 2999 rows... The whole loop must update cells of 4 501 500 rows.for(varrow=1;row<3000;++row)ws.Row(row).Delete();