xlsx

1#!/usr/bin/env python32"""Edit an existing .xlsx workbook in place (or to --out).3 4Operations (repeatable where noted, applied in the order listed below):5  --rename-sheet OLD:NEW        rename a sheet6  --copy-sheet SRC:NEW          duplicate a sheet under a new name7  --insert-rows IDX[:N]         insert N rows before row IDX (default N=1)8  --delete-rows IDX[:N]         delete N rows starting at row IDX9  --insert-cols IDX[:N]         insert N columns before column IDX (number)10  --delete-cols IDX[:N]         delete N columns starting at column IDX11  --set CELL=VALUE              repeatable; type-inferred (int, float, bool,12                                ISO date, else string). '=...' sets a formula.13  --append ROWJSON              repeatable; JSON array appended as a row14  --add-table NAME:RANGE[:STYLE]  create a native Excel table (ListObject)15  --table-append NAME=ROWJSON   append a row inside a table, auto-extending16                                the table's range (repeatable)17  --list-tables                 print tables on the target sheet and exit18  --define-name NAME=REF        workbook-scope defined name, e.g.19                                "Rates='Data'!$B$2:$B$9" (repeatable)20  --delete-name NAME            remove a defined name (repeatable)21  --hyperlink CELL=URL[|TEXT]   set a hyperlink (optional display text)22  --note CELL=TEXT[|AUTHOR]     set a cell note/comment (repeatable)23  --clear-note CELL             remove a cell note (repeatable)24  --protect [PASSWORD]          enable sheet protection; combine with25                                --unlock RANGE to leave ranges editable.26                                NOT security: trivially strippable (see27                                SKILL.md Pitfalls).28  --recalc                      set fullCalcOnLoad so Excel/LibreOffice29                                recomputes all formulas on next open30 31WARNING: openpyxl does NOT shift merged-cell ranges, chart anchors, or32formula references when rows/columns are inserted or deleted. Verify any33sheet containing merges or formulas after structural edits — or use34xlsx_restructure.py, which rewrites references for you.35 36Usage:37  xlsx_edit.py book.xlsx --sheet Data --set B2=42 --set C2=2026-01-01 \38      --set "D2==SUM(B2:C2)" --recalc39  xlsx_edit.py book.xlsx --sheet Data --append '["Widget", 9.99, true]'40  xlsx_edit.py book.xlsx --copy-sheet Data:Backup --rename-sheet Data:Main41"""42from __future__ import annotations43 44import argparse45import json46import sys47from datetime import date, datetime48 49from openpyxl import load_workbook50from openpyxl.comments import Comment51from openpyxl.styles import Protection52from openpyxl.utils import get_column_letter, range_boundaries53from openpyxl.workbook.defined_name import DefinedName54from openpyxl.worksheet.table import Table, TableStyleInfo55 56 57def infer(text):58    if text.startswith("="):59        return text  # formula60    low = text.lower()61    if low in ("true", "false"):62        return low == "true"63    for caster in (int, float):64        try:65            return caster(text)66        except ValueError:67            pass68    for parser in (date.fromisoformat, datetime.fromisoformat):69        try:70            return parser(text)71        except ValueError:72            pass73    return text74 75 76def parse_idx(arg):77    if ":" in arg:78        idx, n = arg.split(":", 1)79        return int(idx), int(n)80    return int(arg), 181 82 83def add_table(ws, spec):84    parts = spec.split(":")85    if len(parts) < 3:86        raise ValueError("--add-table needs NAME:RANGE like Sales:A1:C9")87    name = parts[0]88    rng = ":".join(parts[1:3])89    style = parts[3] if len(parts) > 3 else "TableStyleMedium9"90    table = Table(displayName=name, ref=rng)91    table.tableStyleInfo = TableStyleInfo(name=style, showRowStripes=True)92    ws.add_table(table)93 94 95def table_append(ws, name, row_values):96    table = ws.tables[name]97    min_col, min_row, max_col, max_row = range_boundaries(table.ref)98    new_row = max_row + 199    for offset, value in enumerate(row_values):100        ws.cell(row=new_row, column=min_col + offset, value=value)101    table.ref = (f"{get_column_letter(min_col)}{min_row}:"102                 f"{get_column_letter(max_col)}{new_row}")103 104 105def main(argv=None):106    ap = argparse.ArgumentParser(107        description="Edit an existing .xlsx workbook.",108        epilog="Plain insert/delete does not shift merges/formula refs — "109               "use xlsx_restructure.py for reference-aware moves.")110    ap.add_argument("file", help="path to .xlsx file")111    ap.add_argument("--sheet", help="target sheet (default: active)")112    ap.add_argument("--out", help="output path (default: edit in place)")113    ap.add_argument("--rename-sheet", action="append", default=[],114                    metavar="OLD:NEW")115    ap.add_argument("--copy-sheet", action="append", default=[],116                    metavar="SRC:NEW")117    ap.add_argument("--insert-rows", action="append", default=[],118                    metavar="IDX[:N]")119    ap.add_argument("--delete-rows", action="append", default=[],120                    metavar="IDX[:N]")121    ap.add_argument("--insert-cols", action="append", default=[],122                    metavar="IDX[:N]")123    ap.add_argument("--delete-cols", action="append", default=[],124                    metavar="IDX[:N]")125    ap.add_argument("--set", action="append", default=[], metavar="CELL=VALUE")126    ap.add_argument("--append", action="append", default=[], metavar="ROWJSON")127    ap.add_argument("--add-table", action="append", default=[],128                    metavar="NAME:RANGE[:STYLE]")129    ap.add_argument("--table-append", action="append", default=[],130                    metavar="NAME=ROWJSON")131    ap.add_argument("--list-tables", action="store_true",132                    help="print tables on the target sheet and exit")133    ap.add_argument("--define-name", action="append", default=[],134                    metavar="NAME=REF")135    ap.add_argument("--delete-name", action="append", default=[],136                    metavar="NAME")137    ap.add_argument("--hyperlink", action="append", default=[],138                    metavar="CELL=URL[|TEXT]")139    ap.add_argument("--note", action="append", default=[],140                    metavar="CELL=TEXT[|AUTHOR]")141    ap.add_argument("--clear-note", action="append", default=[],142                    metavar="CELL")143    ap.add_argument("--protect", nargs="?", const="", metavar="PASSWORD",144                    help="protect the target sheet (integrity signal only, "145                    "NOT security)")146    ap.add_argument("--unlock", action="append", default=[], metavar="RANGE",147                    help="cell range left editable under --protect")148    ap.add_argument("--recalc", action="store_true",149                    help="force full recalculation when the file is opened")150    args = ap.parse_args(argv)151 152    wb = load_workbook(args.file)153    changes = []154 155    for pair in args.rename_sheet:156        old, new = pair.split(":", 1)157        wb[old].title = new158        changes.append(f"rename {old}->{new}")159    for pair in args.copy_sheet:160        src, new = pair.split(":", 1)161        copy = wb.copy_worksheet(wb[src])162        copy.title = new163        changes.append(f"copy {src}->{new}")164 165    ws = wb[args.sheet] if args.sheet else wb.active166 167    if args.list_tables:168        print(json.dumps({"ok": True, "sheet": ws.title,169                          "tables": {t.displayName: {170                              "ref": t.ref,171                              "style": t.tableStyleInfo.name172                              if t.tableStyleInfo else None}173                              for t in ws.tables.values()}},174                         ensure_ascii=False))175        return 0176 177    for arg in args.insert_rows:178        idx, n = parse_idx(arg)179        ws.insert_rows(idx, n)180        changes.append(f"insert_rows {idx}x{n}")181    for arg in args.delete_rows:182        idx, n = parse_idx(arg)183        ws.delete_rows(idx, n)184        changes.append(f"delete_rows {idx}x{n}")185    for arg in args.insert_cols:186        idx, n = parse_idx(arg)187        ws.insert_cols(idx, n)188        changes.append(f"insert_cols {idx}x{n}")189    for arg in args.delete_cols:190        idx, n = parse_idx(arg)191        ws.delete_cols(idx, n)192        changes.append(f"delete_cols {idx}x{n}")193 194    for assignment in args.set:195        coord, raw = assignment.split("=", 1)196        ws[coord] = infer(raw)197        changes.append(f"set {coord}")198    for row_json in args.append:199        ws.append(json.loads(row_json))200        changes.append(f"append row {ws.max_row}")201 202    for spec in args.add_table:203        add_table(ws, spec)204        changes.append(f"add_table {spec.split(':')[0]}")205    for spec in args.table_append:206        name, row_json = spec.split("=", 1)207        table_append(ws, name, json.loads(row_json))208        changes.append(f"table_append {name} -> {ws.tables[name].ref}")209 210    for spec in args.define_name:211        name, ref = spec.split("=", 1)212        wb.defined_names[name] = DefinedName(name, attr_text=ref)213        changes.append(f"define_name {name}")214    for name in args.delete_name:215        del wb.defined_names[name]216        changes.append(f"delete_name {name}")217 218    for spec in args.hyperlink:219        coord, rest = spec.split("=", 1)220        url, _, text = rest.partition("|")221        cell = ws[coord]222        cell.hyperlink = url223        cell.value = text or (cell.value if cell.value is not None else url)224        cell.style = "Hyperlink"225        changes.append(f"hyperlink {coord}")226    for spec in args.note:227        coord, rest = spec.split("=", 1)228        text, _, author = rest.partition("|")229        ws[coord].comment = Comment(text, author or "xlsx-skill")230        changes.append(f"note {coord}")231    for coord in args.clear_note:232        ws[coord].comment = None233        changes.append(f"clear_note {coord}")234 235    if args.protect is not None:236        for rng in args.unlock:237            for row in ws[rng]:238                for cell in row:239                    cell.protection = Protection(locked=False)240        if args.protect:241            ws.protection.password = args.protect242        ws.protection.sheet = True243        changes.append(f"protect {ws.title}"244                       + (f" (unlocked {len(args.unlock)} ranges)"245                          if args.unlock else ""))246 247    if args.recalc:248        wb.calculation.fullCalcOnLoad = True249        changes.append("fullCalcOnLoad")250 251    out = args.out or args.file252    wb.save(out)253    print(json.dumps({"ok": True, "output": out, "sheet": ws.title,254                      "changes": changes}, ensure_ascii=False))255    return 0256 257 258if __name__ == "__main__":259    try:260        sys.exit(main())261    except Exception as exc:  # noqa: BLE001262        print(json.dumps({"ok": False, "error": str(exc)}), file=sys.stderr)263        sys.exit(1)264