Skip to content

Performance Guide

Jahav edited this page Sep 22, 2022 · 1 revision

FAQ

Examples

Real world scenarios

Time Savers

Performance and Memory

Misc

Inserting Data/Tables

Styles

Ranges

Rows

Columns

Page Setup (Print Options)

AutoFilters

Comments

Clone this wiki locally

Performance Guide

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

Use range operation if available

Delete/insert cells/row/column

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

Explanation:

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 into A4*4)

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

for (var row = 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 (var row = 1; row < 3000; ++row)
  ws.Row(row).Delete();