xlsx_edit.py 11 KB

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