xlsx_create.py 9.6 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259
  1. #!/usr/bin/env python3
  2. """Create an .xlsx workbook from a JSON spec.
  3. Spec (JSON object):
  4. {
  5. "full_calc_on_load": true, # force recalculation on open (optional)
  6. "defined_names": {"Rates": "'Data'!$B$2:$B$4"}, # workbook scope
  7. "sheets": [
  8. {
  9. "name": "Data",
  10. "rows": [["Header", 1, true], ...], # scalars or cell objects (see below)
  11. "cells": {"A1": {"value": 5, "format": "0.00%"}}, # sparse overrides
  12. "column_widths": {"A": 22, "B": 12},
  13. "row_heights": {"1": 24},
  14. "merges": ["A1:C1"],
  15. "freeze_panes": "A2",
  16. "autofilter": "A1:C10",
  17. "conditional_formats": [
  18. {"range": "B2:B9", "type": "cell_is", "operator": "greaterThan",
  19. "formula": ["100"], "fill": "FFC7CE"},
  20. {"range": "C2:C9", "type": "color_scale"}
  21. ],
  22. "charts": [
  23. {"type": "bar", "title": "Sales", "anchor": "F2",
  24. "data": "B1:B5", "categories": "A2:A5"}
  25. ],
  26. "validations": [
  27. {"range": "D2:D9", "type": "list", "formula1": "\"Yes,No,Maybe\""}
  28. ],
  29. "tables": [
  30. {"name": "Sales", "range": "A1:C4",
  31. "style": "TableStyleMedium9"} # native Excel table
  32. ],
  33. "protection": {"password": "your-password", # NOT security --
  34. "unlock": ["B2:B9"]} # see SKILL.md Pitfalls
  35. }
  36. ]
  37. }
  38. Cell object keys (all optional except value/formula):
  39. value scalar; JSON true/false -> bool, numbers stay numeric
  40. type "date" or "datetime" -> value parsed from ISO string
  41. formula e.g. "=SUM(A2:A9)" (leading '=' optional)
  42. hyperlink URL; value becomes the display text
  43. note cell note text (or {"text": ..., "author": ...})
  44. format Excel number format, e.g. "$#,##0.00", "0.0%", "yyyy-mm-dd"
  45. bold, italic booleans
  46. font_size points
  47. font_color hex RGB like "FF0000"
  48. fill solid fill hex RGB like "DDEBF7"
  49. border "thin" | "medium" | "thick" (all four sides)
  50. align "left" | "center" | "right"
  51. valign "top" | "center" | "bottom"
  52. wrap boolean (wrap text)
  53. Usage:
  54. xlsx_create.py spec.json out.xlsx
  55. xlsx_create.py - out.xlsx (spec on stdin)
  56. Prints a JSON summary to stdout; exits non-zero on failure.
  57. """
  58. from __future__ import annotations
  59. import argparse
  60. import json
  61. import sys
  62. from datetime import date, datetime
  63. from openpyxl import Workbook
  64. from openpyxl.chart import BarChart, LineChart, PieChart, Reference
  65. from openpyxl.comments import Comment
  66. from openpyxl.formatting.rule import CellIsRule, ColorScaleRule
  67. from openpyxl.styles import (Alignment, Border, Font, PatternFill,
  68. Protection, Side)
  69. from openpyxl.utils import column_index_from_string, range_boundaries
  70. from openpyxl.workbook.defined_name import DefinedName
  71. from openpyxl.worksheet.datavalidation import DataValidation
  72. from openpyxl.worksheet.table import Table, TableStyleInfo
  73. def parse_typed(value, type_hint=None):
  74. if type_hint == "date" and isinstance(value, str):
  75. return date.fromisoformat(value)
  76. if type_hint == "datetime" and isinstance(value, str):
  77. return datetime.fromisoformat(value)
  78. return value
  79. def apply_cell(ws, coord, spec):
  80. cell = ws[coord]
  81. if isinstance(spec, dict):
  82. if "formula" in spec:
  83. f = spec["formula"]
  84. cell.value = f if f.startswith("=") else "=" + f
  85. elif "value" in spec:
  86. cell.value = parse_typed(spec["value"], spec.get("type"))
  87. if "hyperlink" in spec:
  88. cell.hyperlink = spec["hyperlink"]
  89. if cell.value is None:
  90. cell.value = spec["hyperlink"]
  91. cell.style = "Hyperlink"
  92. if "note" in spec:
  93. note = spec["note"]
  94. if isinstance(note, dict):
  95. cell.comment = Comment(note.get("text", ""),
  96. note.get("author", "xlsx-skill"))
  97. else:
  98. cell.comment = Comment(str(note), "xlsx-skill")
  99. if "format" in spec:
  100. cell.number_format = spec["format"]
  101. font_kw = {}
  102. if spec.get("bold"):
  103. font_kw["bold"] = True
  104. if spec.get("italic"):
  105. font_kw["italic"] = True
  106. if "font_size" in spec:
  107. font_kw["size"] = spec["font_size"]
  108. if "font_color" in spec:
  109. font_kw["color"] = spec["font_color"]
  110. if font_kw:
  111. cell.font = Font(**font_kw)
  112. if "fill" in spec:
  113. cell.fill = PatternFill("solid", fgColor=spec["fill"])
  114. if "border" in spec:
  115. side = Side(style=spec["border"])
  116. cell.border = Border(left=side, right=side, top=side, bottom=side)
  117. align_kw = {}
  118. if "align" in spec:
  119. align_kw["horizontal"] = spec["align"]
  120. if "valign" in spec:
  121. align_kw["vertical"] = spec["valign"]
  122. if spec.get("wrap"):
  123. align_kw["wrap_text"] = True
  124. if align_kw:
  125. cell.alignment = Alignment(**align_kw)
  126. else:
  127. cell.value = spec
  128. def ref_from_range(ws, rng):
  129. min_col, min_row, max_col, max_row = range_boundaries(rng)
  130. return Reference(ws, min_col=min_col, min_row=min_row,
  131. max_col=max_col, max_row=max_row)
  132. def add_chart(ws, spec):
  133. kind = spec.get("type", "bar")
  134. chart = {"bar": BarChart, "line": LineChart, "pie": PieChart}[kind]()
  135. if "title" in spec:
  136. chart.title = spec["title"]
  137. data = ref_from_range(ws, spec["data"])
  138. chart.add_data(data, titles_from_data=spec.get("titles_from_data", True))
  139. if "categories" in spec:
  140. chart.set_categories(ref_from_range(ws, spec["categories"]))
  141. ws.add_chart(chart, spec.get("anchor", "H2"))
  142. def add_conditional(ws, spec):
  143. rng = spec["range"]
  144. kind = spec.get("type", "cell_is")
  145. if kind == "color_scale":
  146. rule = ColorScaleRule(
  147. start_type="min", start_color=spec.get("start_color", "FFF8696B"),
  148. end_type="max", end_color=spec.get("end_color", "FF63BE7B"))
  149. else:
  150. fill = PatternFill("solid", fgColor=spec.get("fill", "FFC7CE"))
  151. rule = CellIsRule(operator=spec.get("operator", "greaterThan"),
  152. formula=spec.get("formula", ["0"]), fill=fill)
  153. ws.conditional_formatting.add(rng, rule)
  154. def build_sheet(ws, spec):
  155. for row in spec.get("rows", []):
  156. values, styled = [], []
  157. for item in row:
  158. if isinstance(item, dict):
  159. values.append(None)
  160. styled.append(item)
  161. else:
  162. values.append(item)
  163. styled.append(None)
  164. ws.append(values)
  165. r = ws.max_row
  166. for idx, item in enumerate(styled, start=1):
  167. if item is not None:
  168. apply_cell(ws, ws.cell(row=r, column=idx).coordinate, item)
  169. for coord, cell_spec in spec.get("cells", {}).items():
  170. apply_cell(ws, coord, cell_spec)
  171. for col, width in spec.get("column_widths", {}).items():
  172. ws.column_dimensions[col].width = width
  173. for row, height in spec.get("row_heights", {}).items():
  174. ws.row_dimensions[int(row)].height = height
  175. for rng in spec.get("merges", []):
  176. ws.merge_cells(rng)
  177. if spec.get("freeze_panes"):
  178. ws.freeze_panes = spec["freeze_panes"]
  179. if spec.get("autofilter"):
  180. ws.auto_filter.ref = spec["autofilter"]
  181. for cf in spec.get("conditional_formats", []):
  182. add_conditional(ws, cf)
  183. for ch in spec.get("charts", []):
  184. add_chart(ws, ch)
  185. for dv_spec in spec.get("validations", []):
  186. dv = DataValidation(type=dv_spec.get("type", "list"),
  187. formula1=dv_spec["formula1"],
  188. allow_blank=dv_spec.get("allow_blank", True))
  189. dv.add(dv_spec["range"])
  190. ws.add_data_validation(dv)
  191. for t_spec in spec.get("tables", []):
  192. table = Table(displayName=t_spec["name"], ref=t_spec["range"])
  193. table.tableStyleInfo = TableStyleInfo(
  194. name=t_spec.get("style", "TableStyleMedium9"),
  195. showRowStripes=t_spec.get("row_stripes", True),
  196. showColumnStripes=t_spec.get("column_stripes", False))
  197. ws.add_table(table)
  198. prot = spec.get("protection")
  199. if prot:
  200. for rng in prot.get("unlock", []):
  201. for row in ws[rng]:
  202. for cell in row:
  203. cell.protection = Protection(locked=False)
  204. if prot.get("password"):
  205. ws.protection.password = prot["password"]
  206. ws.protection.sheet = True
  207. def main(argv=None):
  208. ap = argparse.ArgumentParser(description="Create .xlsx from a JSON spec.")
  209. ap.add_argument("spec", help="path to JSON spec, or '-' for stdin")
  210. ap.add_argument("output", help="output .xlsx path")
  211. args = ap.parse_args(argv)
  212. if args.spec == "-":
  213. spec = json.load(sys.stdin)
  214. else:
  215. with open(args.spec, encoding="utf-8") as fh:
  216. spec = json.load(fh)
  217. wb = Workbook()
  218. wb.remove(wb.active)
  219. for sheet_spec in spec.get("sheets", []):
  220. ws = wb.create_sheet(sheet_spec.get("name", "Sheet1"))
  221. build_sheet(ws, sheet_spec)
  222. for name, ref in spec.get("defined_names", {}).items():
  223. wb.defined_names[name] = DefinedName(name, attr_text=ref)
  224. if spec.get("full_calc_on_load"):
  225. wb.calculation.fullCalcOnLoad = True
  226. wb.save(args.output)
  227. print(json.dumps({"ok": True, "output": args.output,
  228. "sheets": wb.sheetnames}, ensure_ascii=False))
  229. return 0
  230. if __name__ == "__main__":
  231. try:
  232. sys.exit(main())
  233. except Exception as exc:
  234. print(json.dumps({"ok": False, "error": str(exc)}), file=sys.stderr)
  235. sys.exit(1)