xlsx_restructure.py 16 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397
  1. #!/usr/bin/env python3
  2. """Reference-aware row/column insert and delete for .xlsx workbooks.
  3. Unlike plain openpyxl insert_rows/delete_cols (and xlsx_edit.py's thin
  4. wrappers), this script also rewrites everything that points at the moved
  5. cells:
  6. * formula references in ALL sheets, including absolute refs ($B$2),
  7. ranges (B2:B9), and cross-sheet refs ('My Sheet'!A1 / Data!$B$8).
  8. References into a deleted region become #REF!.
  9. * merged-cell ranges (shifted; expanded when they span the insertion
  10. point; removed when fully deleted)
  11. * autofilter range, freeze panes, data-validation ranges,
  12. conditional-formatting applied ranges (sqref)
  13. * native table (ListObject) refs on the edited sheet
  14. * workbook-scope defined names that point at the edited sheet
  15. * row heights / column widths
  16. It prints a JSON report of every rewrite it made and lists what it
  17. could NOT shift (chart anchors, images, conditional-format RULE
  18. formulas). Full rules and limits: references/restructuring.md.
  19. One structural operation per invocation:
  20. Usage:
  21. xlsx_restructure.py book.xlsx --sheet Data --insert-rows 3:2
  22. xlsx_restructure.py book.xlsx --sheet Data --delete-rows 5
  23. xlsx_restructure.py book.xlsx --sheet Data --insert-cols B:1 --out new.xlsx
  24. xlsx_restructure.py book.xlsx --sheet Data --delete-cols 4:2
  25. """
  26. from __future__ import annotations
  27. import argparse
  28. import json
  29. import re
  30. import sys
  31. from openpyxl import load_workbook
  32. from openpyxl.formatting.formatting import ConditionalFormattingList
  33. from openpyxl.utils import (column_index_from_string, get_column_letter,
  34. range_boundaries)
  35. # A1-style reference, optionally sheet-qualified, optionally a range.
  36. # Guards: not preceded by a word char/$/. (avoids ABC123 identifiers) and
  37. # not followed by a word char or "(" (avoids function names like LOG10().
  38. SHEET_PATTERN = r"(?:'(?:[^']|'')+'|[A-Za-z_][A-Za-z0-9_.]*)!"
  39. REF_RE = re.compile(
  40. r"(?<![\w$.:])"
  41. rf"(?P<sheet>{SHEET_PATTERN})?"
  42. r"(?P<start>\$?[A-Za-z]{1,3}\$?[0-9]{1,7})"
  43. r"(?::(?P<end>\$?[A-Za-z]{1,3}\$?[0-9]{1,7}))?"
  44. r"(?![\w(])")
  45. WHOLE_COL_RE = re.compile(
  46. r"(?<![\w$.:])"
  47. rf"(?P<sheet>{SHEET_PATTERN})?"
  48. r"(?P<start>\$?[A-Za-z]{1,3}):(?P<end>\$?[A-Za-z]{1,3})"
  49. r"(?![\w(])")
  50. WHOLE_ROW_RE = re.compile(
  51. r"(?<![\w$.:])"
  52. rf"(?P<sheet>{SHEET_PATTERN})?"
  53. r"(?P<start>\$?[0-9]{1,7}):(?P<end>\$?[0-9]{1,7})"
  54. r"(?![\w(])")
  55. # String literals and innermost [...] groups (table column names in structured
  56. # references such as Table1[[A:B]]) are copied through unrewritten.
  57. LITERAL_RE = re.compile(r'"(?:[^"]|"")*"|\[[^\[\]]*\]')
  58. COORD_RE = re.compile(r"^(\$?)([A-Za-z]{1,3})(\$?)([0-9]+)$")
  59. def shift_point(v, idx, n, delete):
  60. """New 1-based index for a single row/col, or None if deleted."""
  61. if delete:
  62. if v < idx:
  63. return v
  64. if v >= idx + n:
  65. return v - n
  66. return None
  67. return v + n if v >= idx else v
  68. def shift_span(a, b, idx, n, delete):
  69. """New (start, end) for an inclusive span, or None if fully deleted."""
  70. if delete:
  71. na = a if a < idx else (a - n if a >= idx + n else idx)
  72. nb = b if b < idx else (b - n if b >= idx + n else idx - 1)
  73. return None if na > nb else (na, nb)
  74. return (a + n if a >= idx else a, b + n if b >= idx else b)
  75. def shift_range(rng, axis, idx, n, delete):
  76. """Shift an A1 range string (no sheet prefix). None = fully deleted."""
  77. min_col, min_row, max_col, max_row = range_boundaries(rng)
  78. if axis == "rows":
  79. span = shift_span(min_row, max_row, idx, n, delete)
  80. if span is None:
  81. return None
  82. min_row, max_row = span
  83. else:
  84. span = shift_span(min_col, max_col, idx, n, delete)
  85. if span is None:
  86. return None
  87. min_col, max_col = span
  88. start = f"{get_column_letter(min_col)}{min_row}"
  89. end = f"{get_column_letter(max_col)}{max_row}"
  90. return start if start == end and ":" not in rng else f"{start}:{end}"
  91. class RefRewriter:
  92. """Rewrite A1 references in formula-like text for one shift op."""
  93. def __init__(self, target_sheet, axis, idx, n, delete):
  94. self.target = target_sheet.lower()
  95. self.axis, self.idx, self.n, self.delete = axis, idx, n, delete
  96. def _shift_coord(self, coord):
  97. m = COORD_RE.match(coord)
  98. col_abs, col, row_abs, row = m.groups()
  99. ci, ri = column_index_from_string(col.upper()), int(row)
  100. if self.axis == "rows":
  101. ri = shift_point(ri, self.idx, self.n, self.delete)
  102. if ri is None:
  103. return None
  104. else:
  105. ci = shift_point(ci, self.idx, self.n, self.delete)
  106. if ci is None:
  107. return None
  108. return f"{col_abs}{get_column_letter(ci)}{row_abs}{ri}"
  109. def _shift_pair(self, start, end):
  110. """Shift a range preserving $ flags; None = collapsed to #REF!."""
  111. new_start = self._shift_coord(start)
  112. new_end = self._shift_coord(end)
  113. if new_start is None or new_end is None:
  114. # spans may survive partial deletion: clamp via span math
  115. s, e = COORD_RE.match(start), COORD_RE.match(end)
  116. if self.axis == "rows":
  117. span = shift_span(int(s.group(4)), int(e.group(4)),
  118. self.idx, self.n, self.delete)
  119. if span is None:
  120. return None
  121. new_start = f"{s.group(1)}{s.group(2)}{s.group(3)}{span[0]}"
  122. new_end = f"{e.group(1)}{e.group(2)}{e.group(3)}{span[1]}"
  123. else:
  124. span = shift_span(column_index_from_string(s.group(2).upper()),
  125. column_index_from_string(e.group(2).upper()),
  126. self.idx, self.n, self.delete)
  127. if span is None:
  128. return None
  129. new_start = (f"{s.group(1)}{get_column_letter(span[0])}"
  130. f"{s.group(3)}{s.group(4)}")
  131. new_end = (f"{e.group(1)}{get_column_letter(span[1])}"
  132. f"{e.group(3)}{e.group(4)}")
  133. return new_start, new_end
  134. def _targets_sheet(self, prefix, home_sheet):
  135. if prefix:
  136. name = prefix[:-1]
  137. if name.startswith("'"):
  138. name = name[1:-1].replace("''", "'")
  139. ref_sheet = name
  140. else:
  141. ref_sheet = home_sheet
  142. return ref_sheet.lower() == self.target
  143. def _sub(self, match, home_sheet):
  144. prefix = match.group("sheet") or ""
  145. if not self._targets_sheet(prefix, home_sheet):
  146. return match.group(0)
  147. start, end = match.group("start"), match.group("end")
  148. if end is None:
  149. new = self._shift_coord(start)
  150. return prefix + ("#REF!" if new is None else new)
  151. pair = self._shift_pair(start, end)
  152. return (prefix + "#REF!" if pair is None
  153. else f"{prefix}{pair[0]}:{pair[1]}")
  154. def _sub_whole(self, match, home_sheet, axis):
  155. prefix = match.group("sheet") or ""
  156. if axis != self.axis or not self._targets_sheet(prefix, home_sheet):
  157. return match.group(0)
  158. start, end = match.group("start"), match.group("end")
  159. start_abs, end_abs = start.startswith("$"), end.startswith("$")
  160. if axis == "cols":
  161. a = column_index_from_string(start.lstrip("$").upper())
  162. b = column_index_from_string(end.lstrip("$").upper())
  163. else:
  164. a, b = int(start.lstrip("$")), int(end.lstrip("$"))
  165. span = shift_span(a, b, self.idx, self.n, self.delete)
  166. if span is None:
  167. return prefix + "#REF!"
  168. if axis == "cols":
  169. new_start, new_end = map(get_column_letter, span)
  170. else:
  171. new_start, new_end = map(str, span)
  172. return (f"{prefix}{'$' if start_abs else ''}{new_start}:"
  173. f"{'$' if end_abs else ''}{new_end}")
  174. def _rewrite_segment(self, text, home_sheet):
  175. text = WHOLE_COL_RE.sub(
  176. lambda m: self._sub_whole(m, home_sheet, "cols"), text)
  177. text = WHOLE_ROW_RE.sub(
  178. lambda m: self._sub_whole(m, home_sheet, "rows"), text)
  179. return REF_RE.sub(lambda m: self._sub(m, home_sheet), text)
  180. def rewrite(self, text, home_sheet):
  181. """Rewrite refs outside quoted string literals. Returns new text."""
  182. out, pos = [], 0
  183. for lit in LITERAL_RE.finditer(text):
  184. out.append(self._rewrite_segment(text[pos:lit.start()], home_sheet))
  185. out.append(lit.group(0))
  186. pos = lit.end()
  187. out.append(self._rewrite_segment(text[pos:], home_sheet))
  188. return "".join(out)
  189. def shift_dimensions(dims, idx, n, delete, is_row):
  190. """Rebuild a row/column dimensions map with shifted keys."""
  191. items = list(dims.items())
  192. saved = {}
  193. for key, dim in items:
  194. pos = key if is_row else column_index_from_string(key)
  195. new = shift_point(pos, idx, n, delete)
  196. if new is None:
  197. del dims[key]
  198. elif new != pos:
  199. saved[new if is_row else get_column_letter(new)] = dim
  200. del dims[key]
  201. for key, dim in saved.items():
  202. if is_row:
  203. dim.index = key
  204. else:
  205. dim.index = column_index_from_string(key)
  206. dims[key] = dim
  207. return len(saved)
  208. def _rewrite_formulas(wb, rewriter, report):
  209. for sheet in wb.worksheets:
  210. for row in sheet.iter_rows():
  211. for cell in row:
  212. if isinstance(cell.value, str) and cell.value.startswith("="):
  213. new = rewriter.rewrite(cell.value, sheet.title)
  214. if new != cell.value:
  215. report["formulas"].append(
  216. {"sheet": sheet.title, "cell": cell.coordinate,
  217. "from": cell.value, "to": new})
  218. cell.value = new
  219. def _shift_freeze_panes(ws, axis, idx, n, delete, report):
  220. m = COORD_RE.match(ws.freeze_panes)
  221. ci = column_index_from_string(m.group(2).upper())
  222. ri = int(m.group(4))
  223. if axis == "rows":
  224. ri = shift_point(ri, idx, n, delete) or max(idx, 2)
  225. else:
  226. ci = shift_point(ci, idx, n, delete) or max(idx, 2)
  227. new = f"{get_column_letter(ci)}{ri}"
  228. if new != ws.freeze_panes:
  229. report["freeze_panes"] = {"from": ws.freeze_panes, "to": new}
  230. ws.freeze_panes = new
  231. def main(argv=None):
  232. ap = argparse.ArgumentParser(
  233. description="Insert/delete rows or columns AND rewrite formula "
  234. "references, merges, filters, validations, tables, and "
  235. "defined names to match.",
  236. epilog="Cannot shift: chart anchors, images, conditional-format "
  237. "rule formulas. See references/restructuring.md.")
  238. ap.add_argument("file", help="path to .xlsx file")
  239. ap.add_argument("--sheet", help="target sheet (default: active)")
  240. ap.add_argument("--out", help="output path (default: edit in place)")
  241. op = ap.add_mutually_exclusive_group(required=True)
  242. op.add_argument("--insert-rows", metavar="IDX[:N]")
  243. op.add_argument("--delete-rows", metavar="IDX[:N]")
  244. op.add_argument("--insert-cols", metavar="COL[:N]",
  245. help="COL is a letter (B) or 1-based number")
  246. op.add_argument("--delete-cols", metavar="COL[:N]")
  247. args = ap.parse_args(argv)
  248. raw = (args.insert_rows or args.delete_rows
  249. or args.insert_cols or args.delete_cols)
  250. idx_s, _, n_s = raw.partition(":")
  251. n = int(n_s) if n_s else 1
  252. axis = "rows" if (args.insert_rows or args.delete_rows) else "cols"
  253. delete = bool(args.delete_rows or args.delete_cols)
  254. if axis == "cols" and idx_s.isalpha():
  255. idx = column_index_from_string(idx_s.upper())
  256. else:
  257. idx = int(idx_s)
  258. wb = load_workbook(args.file)
  259. ws = wb[args.sheet] if args.sheet else wb.active
  260. rewriter = RefRewriter(ws.title, axis, idx, n, delete)
  261. report = {"ok": True, "sheet": ws.title, "axis": axis,
  262. "op": "delete" if delete else "insert", "index": idx, "count": n,
  263. "formulas": [], "merges": [], "tables": {}, "defined_names": {},
  264. "validations": [], "conditional_formats": [],
  265. "not_shifted": ["chart anchors", "images",
  266. "conditional-format rule formulas"]}
  267. # 1. capture merge ranges (openpyxl does not move them), then unmerge
  268. old_merges = [str(r) for r in list(ws.merged_cells.ranges)]
  269. for rng in old_merges:
  270. ws.unmerge_cells(rng)
  271. # 2. structural move of cell values/styles/comments
  272. getattr(ws, f"{report['op']}_{axis}")(idx, n)
  273. # 3. formulas everywhere
  274. _rewrite_formulas(wb, rewriter, report)
  275. # 4. merges back, shifted
  276. for rng in old_merges:
  277. new = shift_range(rng, axis, idx, n, delete)
  278. if new is None:
  279. report["merges"].append({"from": rng, "to": None})
  280. else:
  281. ws.merge_cells(new)
  282. if new != rng:
  283. report["merges"].append({"from": rng, "to": new})
  284. # 5. autofilter + freeze panes
  285. if ws.auto_filter.ref:
  286. new = shift_range(ws.auto_filter.ref, axis, idx, n, delete)
  287. if new != ws.auto_filter.ref:
  288. report["autofilter"] = {"from": ws.auto_filter.ref, "to": new}
  289. ws.auto_filter.ref = new
  290. if ws.freeze_panes:
  291. _shift_freeze_panes(ws, axis, idx, n, delete, report)
  292. # 6. data validations + conditional formatting applied ranges
  293. for dv in list(ws.data_validations.dataValidation):
  294. old = str(dv.sqref)
  295. parts = [shift_range(p, axis, idx, n, delete) for p in old.split()]
  296. parts = [p for p in parts if p]
  297. new = " ".join(parts) if parts else None
  298. if new != old:
  299. if new is None:
  300. ws.data_validations.dataValidation.remove(dv)
  301. else:
  302. dv.sqref = new
  303. report["validations"].append({"from": old, "to": new})
  304. new_cf = ConditionalFormattingList()
  305. for cf in ws.conditional_formatting:
  306. old = str(cf.sqref)
  307. parts = [shift_range(p, axis, idx, n, delete) for p in old.split()]
  308. parts = [p for p in parts if p]
  309. if not parts:
  310. report["conditional_formats"].append({"from": old, "to": None})
  311. continue
  312. new = " ".join(parts)
  313. for rule in cf.rules:
  314. new_cf.add(new, rule)
  315. if new != old:
  316. report["conditional_formats"].append({"from": old, "to": new})
  317. ws.conditional_formatting = new_cf
  318. # 7. native tables on the edited sheet
  319. for table in list(ws.tables.values()):
  320. old = table.ref
  321. new = shift_range(old, axis, idx, n, delete)
  322. if new != old:
  323. report["tables"][table.displayName] = {"from": old, "to": new}
  324. if new is None:
  325. del ws.tables[table.displayName]
  326. else:
  327. table.ref = new
  328. # 8. workbook-scope defined names
  329. for name, dn in wb.defined_names.items():
  330. if dn.attr_text and "!" in dn.attr_text:
  331. new = rewriter.rewrite(dn.attr_text, ws.title)
  332. if new != dn.attr_text:
  333. report["defined_names"][name] = {"from": dn.attr_text,
  334. "to": new}
  335. dn.attr_text = new
  336. # 9. row heights / column widths
  337. if axis == "rows":
  338. shift_dimensions(ws.row_dimensions, idx, n, delete, is_row=True)
  339. else:
  340. shift_dimensions(ws.column_dimensions, idx, n, delete, is_row=False)
  341. out = args.out or args.file
  342. wb.save(out)
  343. report["output"] = out
  344. print(json.dumps(report, ensure_ascii=False, indent=2))
  345. return 0
  346. if __name__ == "__main__":
  347. try:
  348. sys.exit(main())
  349. except Exception as exc:
  350. print(json.dumps({"ok": False, "error": str(exc)}), file=sys.stderr)
  351. sys.exit(1)