xlsx

1#!/usr/bin/env python32"""Reference-aware row/column insert and delete for .xlsx workbooks.3 4Unlike plain openpyxl insert_rows/delete_cols (and xlsx_edit.py's thin5wrappers), this script also rewrites everything that points at the moved6cells:7 8  * formula references in ALL sheets, including absolute refs ($B$2),9    ranges (B2:B9), and cross-sheet refs ('My Sheet'!A1 / Data!$B$8).10    References into a deleted region become #REF!.11  * merged-cell ranges (shifted; expanded when they span the insertion12    point; removed when fully deleted)13  * autofilter range, freeze panes, data-validation ranges,14    conditional-formatting applied ranges (sqref)15  * native table (ListObject) refs on the edited sheet16  * workbook-scope defined names that point at the edited sheet17  * row heights / column widths18 19It prints a JSON report of every rewrite it made and lists what it20could NOT shift (chart anchors, images, conditional-format RULE21formulas). Full rules and limits: references/restructuring.md.22 23One structural operation per invocation:24 25Usage:26  xlsx_restructure.py book.xlsx --sheet Data --insert-rows 3:227  xlsx_restructure.py book.xlsx --sheet Data --delete-rows 528  xlsx_restructure.py book.xlsx --sheet Data --insert-cols B:1 --out new.xlsx29  xlsx_restructure.py book.xlsx --sheet Data --delete-cols 4:230"""31from __future__ import annotations32 33import argparse34import json35import re36import sys37 38from openpyxl import load_workbook39from openpyxl.formatting.formatting import ConditionalFormattingList40from openpyxl.utils import (column_index_from_string, get_column_letter,41                            range_boundaries)42 43# A1-style reference, optionally sheet-qualified, optionally a range.44# Guards: not preceded by a word char/$/. (avoids ABC123 identifiers) and45# not followed by a word char or "(" (avoids function names like LOG10().46REF_RE = re.compile(47    r"(?<![\w$.:])"48    r"(?P<sheet>(?:'(?:[^']|'')+'|[A-Za-z_][A-Za-z0-9_.]*)!)?"49    r"(?P<start>\$?[A-Za-z]{1,3}\$?[0-9]{1,7})"50    r"(?::(?P<end>\$?[A-Za-z]{1,3}\$?[0-9]{1,7}))?"51    r"(?![\w(])")52STRING_RE = re.compile(r'"(?:[^"]|"")*"')53COORD_RE = re.compile(r"^(\$?)([A-Za-z]{1,3})(\$?)([0-9]+)$")54 55 56def shift_point(v, idx, n, delete):57    """New 1-based index for a single row/col, or None if deleted."""58    if delete:59        if v < idx:60            return v61        if v >= idx + n:62            return v - n63        return None64    return v + n if v >= idx else v65 66 67def shift_span(a, b, idx, n, delete):68    """New (start, end) for an inclusive span, or None if fully deleted."""69    if delete:70        na = a if a < idx else (a - n if a >= idx + n else idx)71        nb = b if b < idx else (b - n if b >= idx + n else idx - 1)72        return None if na > nb else (na, nb)73    return (a + n if a >= idx else a, b + n if b >= idx else b)74 75 76def shift_range(rng, axis, idx, n, delete):77    """Shift an A1 range string (no sheet prefix). None = fully deleted."""78    min_col, min_row, max_col, max_row = range_boundaries(rng)79    if axis == "rows":80        span = shift_span(min_row, max_row, idx, n, delete)81        if span is None:82            return None83        min_row, max_row = span84    else:85        span = shift_span(min_col, max_col, idx, n, delete)86        if span is None:87            return None88        min_col, max_col = span89    start = f"{get_column_letter(min_col)}{min_row}"90    end = f"{get_column_letter(max_col)}{max_row}"91    return start if start == end and ":" not in rng else f"{start}:{end}"92 93 94class RefRewriter:95    """Rewrite A1 references in formula-like text for one shift op."""96 97    def __init__(self, target_sheet, axis, idx, n, delete):98        self.target = target_sheet.lower()99        self.axis, self.idx, self.n, self.delete = axis, idx, n, delete100 101    def _shift_coord(self, coord):102        m = COORD_RE.match(coord)103        col_abs, col, row_abs, row = m.groups()104        ci, ri = column_index_from_string(col.upper()), int(row)105        if self.axis == "rows":106            ri = shift_point(ri, self.idx, self.n, self.delete)107            if ri is None:108                return None109        else:110            ci = shift_point(ci, self.idx, self.n, self.delete)111            if ci is None:112                return None113        return f"{col_abs}{get_column_letter(ci)}{row_abs}{ri}"114 115    def _shift_pair(self, start, end):116        """Shift a range preserving $ flags; None = collapsed to #REF!."""117        new_start = self._shift_coord(start)118        new_end = self._shift_coord(end)119        if new_start is None or new_end is None:120            # spans may survive partial deletion: clamp via span math121            s, e = COORD_RE.match(start), COORD_RE.match(end)122            if self.axis == "rows":123                span = shift_span(int(s.group(4)), int(e.group(4)),124                                  self.idx, self.n, self.delete)125                if span is None:126                    return None127                new_start = f"{s.group(1)}{s.group(2)}{s.group(3)}{span[0]}"128                new_end = f"{e.group(1)}{e.group(2)}{e.group(3)}{span[1]}"129            else:130                span = shift_span(column_index_from_string(s.group(2).upper()),131                                  column_index_from_string(e.group(2).upper()),132                                  self.idx, self.n, self.delete)133                if span is None:134                    return None135                new_start = (f"{s.group(1)}{get_column_letter(span[0])}"136                             f"{s.group(3)}{s.group(4)}")137                new_end = (f"{e.group(1)}{get_column_letter(span[1])}"138                           f"{e.group(3)}{e.group(4)}")139        return new_start, new_end140 141    def _sub(self, match, home_sheet):142        prefix = match.group("sheet") or ""143        if prefix:144            name = prefix[:-1]145            if name.startswith("'"):146                name = name[1:-1].replace("''", "'")147            ref_sheet = name148        else:149            ref_sheet = home_sheet150        if ref_sheet.lower() != self.target:151            return match.group(0)152        start, end = match.group("start"), match.group("end")153        if end is None:154            new = self._shift_coord(start)155            return prefix + ("#REF!" if new is None else new)156        pair = self._shift_pair(start, end)157        return (prefix + "#REF!" if pair is None158                else f"{prefix}{pair[0]}:{pair[1]}")159 160    def rewrite(self, text, home_sheet):161        """Rewrite refs outside quoted string literals. Returns new text."""162        out, pos = [], 0163        for lit in STRING_RE.finditer(text):164            out.append(REF_RE.sub(lambda m: self._sub(m, home_sheet),165                                  text[pos:lit.start()]))166            out.append(lit.group(0))167            pos = lit.end()168        out.append(REF_RE.sub(lambda m: self._sub(m, home_sheet), text[pos:]))169        return "".join(out)170 171 172def shift_dimensions(dims, idx, n, delete, is_row):173    """Rebuild a row/column dimensions map with shifted keys."""174    items = list(dims.items())175    saved = {}176    for key, dim in items:177        pos = key if is_row else column_index_from_string(key)178        new = shift_point(pos, idx, n, delete)179        if new is not None and new != pos:180            saved[new if is_row else get_column_letter(new)] = dim181            del dims[key]182    for key, dim in saved.items():183        if is_row:184            dim.index = key185        else:186            dim.index = column_index_from_string(key)187        dims[key] = dim188    return len(saved)189 190 191def main(argv=None):192    ap = argparse.ArgumentParser(193        description="Insert/delete rows or columns AND rewrite formula "194                    "references, merges, filters, validations, tables, and "195                    "defined names to match.",196        epilog="Cannot shift: chart anchors, images, conditional-format "197               "rule formulas. See references/restructuring.md.")198    ap.add_argument("file", help="path to .xlsx file")199    ap.add_argument("--sheet", help="target sheet (default: active)")200    ap.add_argument("--out", help="output path (default: edit in place)")201    op = ap.add_mutually_exclusive_group(required=True)202    op.add_argument("--insert-rows", metavar="IDX[:N]")203    op.add_argument("--delete-rows", metavar="IDX[:N]")204    op.add_argument("--insert-cols", metavar="COL[:N]",205                    help="COL is a letter (B) or 1-based number")206    op.add_argument("--delete-cols", metavar="COL[:N]")207    args = ap.parse_args(argv)208 209    raw = (args.insert_rows or args.delete_rows210           or args.insert_cols or args.delete_cols)211    idx_s, _, n_s = raw.partition(":")212    n = int(n_s) if n_s else 1213    axis = "rows" if (args.insert_rows or args.delete_rows) else "cols"214    delete = bool(args.delete_rows or args.delete_cols)215    if axis == "cols" and idx_s.isalpha():216        idx = column_index_from_string(idx_s.upper())217    else:218        idx = int(idx_s)219 220    wb = load_workbook(args.file)221    ws = wb[args.sheet] if args.sheet else wb.active222    rewriter = RefRewriter(ws.title, axis, idx, n, delete)223    report = {"ok": True, "sheet": ws.title, "axis": axis,224              "op": "delete" if delete else "insert", "index": idx, "count": n,225              "formulas": [], "merges": [], "tables": {}, "defined_names": {},226              "validations": [], "conditional_formats": [],227              "not_shifted": ["chart anchors", "images",228                              "conditional-format rule formulas"]}229 230    # 1. capture merge ranges (openpyxl does not move them), then unmerge231    old_merges = [str(r) for r in list(ws.merged_cells.ranges)]232    for rng in old_merges:233        ws.unmerge_cells(rng)234 235    # 2. structural move of cell values/styles/comments236    getattr(ws, f"{report['op']}_{axis}")(idx, n)237 238    # 3. formulas everywhere239    for sheet in wb.worksheets:240        for row in sheet.iter_rows():241            for cell in row:242                if isinstance(cell.value, str) and cell.value.startswith("="):243                    new = rewriter.rewrite(cell.value, sheet.title)244                    if new != cell.value:245                        report["formulas"].append(246                            {"sheet": sheet.title, "cell": cell.coordinate,247                             "from": cell.value, "to": new})248                        cell.value = new249 250    # 4. merges back, shifted251    for rng in old_merges:252        new = shift_range(rng, axis, idx, n, delete)253        if new is None:254            report["merges"].append({"from": rng, "to": None})255        else:256            ws.merge_cells(new)257            if new != rng:258                report["merges"].append({"from": rng, "to": new})259 260    # 5. autofilter + freeze panes261    if ws.auto_filter.ref:262        new = shift_range(ws.auto_filter.ref, axis, idx, n, delete)263        if new != ws.auto_filter.ref:264            report["autofilter"] = {"from": ws.auto_filter.ref, "to": new}265            ws.auto_filter.ref = new266    if ws.freeze_panes:267        m = COORD_RE.match(ws.freeze_panes)268        ci = column_index_from_string(m.group(2).upper())269        ri = int(m.group(4))270        if axis == "rows":271            ri = shift_point(ri, idx, n, delete) or max(idx, 2)272        else:273            ci = shift_point(ci, idx, n, delete) or max(idx, 2)274        new = f"{get_column_letter(ci)}{ri}"275        if new != ws.freeze_panes:276            report["freeze_panes"] = {"from": ws.freeze_panes, "to": new}277            ws.freeze_panes = new278 279    # 6. data validations + conditional formatting applied ranges280    for dv in ws.data_validations.dataValidation:281        old = str(dv.sqref)282        parts = [shift_range(p, axis, idx, n, delete) for p in old.split()]283        parts = [p for p in parts if p]284        if parts and " ".join(parts) != old:285            dv.sqref = " ".join(parts)286            report["validations"].append({"from": old, "to": str(dv.sqref)})287    new_cf = ConditionalFormattingList()288    for cf in ws.conditional_formatting:289        old = str(cf.sqref)290        parts = [shift_range(p, axis, idx, n, delete) for p in old.split()]291        parts = [p for p in parts if p]292        if not parts:293            report["conditional_formats"].append({"from": old, "to": None})294            continue295        new = " ".join(parts)296        for rule in cf.rules:297            new_cf.add(new, rule)298        if new != old:299            report["conditional_formats"].append({"from": old, "to": new})300    ws.conditional_formatting = new_cf301 302    # 7. native tables on the edited sheet303    for table in ws.tables.values():304        new = shift_range(table.ref, axis, idx, n, delete)305        if new and new != table.ref:306            report["tables"][table.displayName] = {"from": table.ref,307                                                   "to": new}308            table.ref = new309 310    # 8. workbook-scope defined names311    for name, dn in wb.defined_names.items():312        if dn.attr_text and "!" in dn.attr_text:313            new = rewriter.rewrite(dn.attr_text, ws.title)314            if new != dn.attr_text:315                report["defined_names"][name] = {"from": dn.attr_text,316                                                 "to": new}317                dn.attr_text = new318 319    # 9. row heights / column widths320    if axis == "rows":321        shift_dimensions(ws.row_dimensions, idx, n, delete, is_row=True)322    else:323        shift_dimensions(ws.column_dimensions, idx, n, delete, is_row=False)324 325    out = args.out or args.file326    wb.save(out)327    report["output"] = out328    print(json.dumps(report, ensure_ascii=False, indent=2))329    return 0330 331 332if __name__ == "__main__":333    try:334        sys.exit(main())335    except Exception as exc:  # noqa: BLE001336        print(json.dumps({"ok": False, "error": str(exc)}), file=sys.stderr)337        sys.exit(1)338