openpyxl.preserve.rewrite

Rewrite references for row/column inserts and deletes the way EXCEL does — not the way fill/copy translation does.

Upstream's Translator implements FILL semantics: $-anchored parts are pinned and every reference shifts unconditionally. Excel's INSERT semantics differ on every axis that matters: references at or below the insertion point shift including $B$2-style absolutes, references above it stay, and ranges spanning the point EXPAND. Both behaviors fall out of one rule: shift each endpoint independently when it sits at/after the edit index. Deletes are the mirror image, with endpoints inside the deleted zone clamped and fully-deleted references becoming #REF! — exactly what Excel writes.

Only the Tokenizer is reused (reference isolation); Translator stays untouched — it is load-bearing for shared-formula expansion.

`EXCEL_MAX_COL`

attributeEXCEL_MAX_COL

= 16384

`EXCEL_MAX_ROW`

attributeEXCEL_MAX_ROW

= 1048576

`REF_ERROR`

attributeREF_ERROR

= '#REF!'

`rename_sheet_in_formula`

funcrename_sheet_in_formula(formula, old_title, new_title)

Rewrite sheet-prefixed references from old_title to new_title (case-insensitive, quote-aware, 3-D span endpoints included). Returns (new_formula, changed).

paramformula

paramold_title

paramnew_title

`rename_sheet_in_formula_fragment`

funcrename_sheet_in_formula_fragment(value, old_title, new_title)

paramvalue

paramold_title

paramnew_title

`rename_sheets_in_formula`

funcrename_sheets_in_formula(formula, mapping)

Simultaneous multi-title rewrite: every sheet component maps through mapping (casefold keys resolved per component) exactly once — a swap can never cascade.

paramformula

parammapping

`rename_sheets_in_formula_fragment`

funcrename_sheets_in_formula_fragment(value, mapping)

Rename sheet references in a CF/DV formula with optional =.

paramvalue

parammapping

`row_mapping`

funcrow_mapping(operation, index, amount)

old_row -> new_row, or None when the row is deleted.

paramoperation

paramindex

paramamount

`shift_cell_range`

funcshift_cell_range(cell_range, axis, index, amount, is_delete)

Shift a CellRange in place. Returns 'changed', 'unchanged' or 'deleted' (range fully inside a deleted zone — the caller removes it).

paramcell_range

paramaxis

paramindex

paramamount

paramis_delete

`shift_formula`

funcshift_formula(formula, context_sheet, target_sheet, axis, index, amount, is_delete)

Rewrite one formula for a shift on target_sheet.

context_sheet is the sheet the formula lives on (unprefixed references resolve to it). Returns (new_formula, changed).

paramformula

paramcontext_sheet

paramtarget_sheet

paramaxis

paramindex

paramamount

paramis_delete

`shift_formula_fragment`

funcshift_formula_fragment(value, context_sheet, target_sheet, axis, index, amount, is_delete)

Rewrite a CF/DV formula, which may legally omit the leading =.

paramvalue

paramcontext_sheet

paramtarget_sheet

paramaxis

paramindex

paramamount

paramis_delete

`shift_name_value`

funcshift_name_value(value, target_sheet, axis, index, amount, is_delete)

Defined-name / print-area values are formula fragments with explicit sheet prefixes; rewrite them with the same machinery.

paramvalue

paramtarget_sheet

paramaxis

paramindex

paramamount

paramis_delete

`shift_ref`

funcshift_ref(ref, axis, index, amount, is_delete)

Shift one bare A1 reference (no sheet prefix). Returns the new text, ref unchanged when unaffected, or #REF!. $ markers are kept positionally — insert/delete moves absolutes too (Excel semantics).

paramref

paramaxis

paramindex

paramamount

paramis_delete

`title_in_string_literals`

functitle_in_string_literals(formula, title)

True when a formula's STRING literals mention title — the textual (INDIRECT-style) references a rename cannot rewrite.

paramformula

paramtitle

openpyxl.preserve.rewrite

On this page