| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259 |
- #!/usr/bin/env python3
- """Create an .xlsx workbook from a JSON spec.
- Spec (JSON object):
- {
- "full_calc_on_load": true, # force recalculation on open (optional)
- "defined_names": {"Rates": "'Data'!$B$2:$B$4"}, # workbook scope
- "sheets": [
- {
- "name": "Data",
- "rows": [["Header", 1, true], ...], # scalars or cell objects (see below)
- "cells": {"A1": {"value": 5, "format": "0.00%"}}, # sparse overrides
- "column_widths": {"A": 22, "B": 12},
- "row_heights": {"1": 24},
- "merges": ["A1:C1"],
- "freeze_panes": "A2",
- "autofilter": "A1:C10",
- "conditional_formats": [
- {"range": "B2:B9", "type": "cell_is", "operator": "greaterThan",
- "formula": ["100"], "fill": "FFC7CE"},
- {"range": "C2:C9", "type": "color_scale"}
- ],
- "charts": [
- {"type": "bar", "title": "Sales", "anchor": "F2",
- "data": "B1:B5", "categories": "A2:A5"}
- ],
- "validations": [
- {"range": "D2:D9", "type": "list", "formula1": "\"Yes,No,Maybe\""}
- ],
- "tables": [
- {"name": "Sales", "range": "A1:C4",
- "style": "TableStyleMedium9"} # native Excel table
- ],
- "protection": {"password": "your-password", # NOT security --
- "unlock": ["B2:B9"]} # see SKILL.md Pitfalls
- }
- ]
- }
- Cell object keys (all optional except value/formula):
- value scalar; JSON true/false -> bool, numbers stay numeric
- type "date" or "datetime" -> value parsed from ISO string
- formula e.g. "=SUM(A2:A9)" (leading '=' optional)
- hyperlink URL; value becomes the display text
- note cell note text (or {"text": ..., "author": ...})
- format Excel number format, e.g. "$#,##0.00", "0.0%", "yyyy-mm-dd"
- bold, italic booleans
- font_size points
- font_color hex RGB like "FF0000"
- fill solid fill hex RGB like "DDEBF7"
- border "thin" | "medium" | "thick" (all four sides)
- align "left" | "center" | "right"
- valign "top" | "center" | "bottom"
- wrap boolean (wrap text)
- Usage:
- xlsx_create.py spec.json out.xlsx
- xlsx_create.py - out.xlsx (spec on stdin)
- Prints a JSON summary to stdout; exits non-zero on failure.
- """
- from __future__ import annotations
- import argparse
- import json
- import sys
- from datetime import date, datetime
- from openpyxl import Workbook
- from openpyxl.chart import BarChart, LineChart, PieChart, Reference
- from openpyxl.comments import Comment
- from openpyxl.formatting.rule import CellIsRule, ColorScaleRule
- from openpyxl.styles import (Alignment, Border, Font, PatternFill,
- Protection, Side)
- from openpyxl.utils import column_index_from_string, range_boundaries
- from openpyxl.workbook.defined_name import DefinedName
- from openpyxl.worksheet.datavalidation import DataValidation
- from openpyxl.worksheet.table import Table, TableStyleInfo
- def parse_typed(value, type_hint=None):
- if type_hint == "date" and isinstance(value, str):
- return date.fromisoformat(value)
- if type_hint == "datetime" and isinstance(value, str):
- return datetime.fromisoformat(value)
- return value
- def apply_cell(ws, coord, spec):
- cell = ws[coord]
- if isinstance(spec, dict):
- if "formula" in spec:
- f = spec["formula"]
- cell.value = f if f.startswith("=") else "=" + f
- elif "value" in spec:
- cell.value = parse_typed(spec["value"], spec.get("type"))
- if "hyperlink" in spec:
- cell.hyperlink = spec["hyperlink"]
- if cell.value is None:
- cell.value = spec["hyperlink"]
- cell.style = "Hyperlink"
- if "note" in spec:
- note = spec["note"]
- if isinstance(note, dict):
- cell.comment = Comment(note.get("text", ""),
- note.get("author", "xlsx-skill"))
- else:
- cell.comment = Comment(str(note), "xlsx-skill")
- if "format" in spec:
- cell.number_format = spec["format"]
- font_kw = {}
- if spec.get("bold"):
- font_kw["bold"] = True
- if spec.get("italic"):
- font_kw["italic"] = True
- if "font_size" in spec:
- font_kw["size"] = spec["font_size"]
- if "font_color" in spec:
- font_kw["color"] = spec["font_color"]
- if font_kw:
- cell.font = Font(**font_kw)
- if "fill" in spec:
- cell.fill = PatternFill("solid", fgColor=spec["fill"])
- if "border" in spec:
- side = Side(style=spec["border"])
- cell.border = Border(left=side, right=side, top=side, bottom=side)
- align_kw = {}
- if "align" in spec:
- align_kw["horizontal"] = spec["align"]
- if "valign" in spec:
- align_kw["vertical"] = spec["valign"]
- if spec.get("wrap"):
- align_kw["wrap_text"] = True
- if align_kw:
- cell.alignment = Alignment(**align_kw)
- else:
- cell.value = spec
- def ref_from_range(ws, rng):
- min_col, min_row, max_col, max_row = range_boundaries(rng)
- return Reference(ws, min_col=min_col, min_row=min_row,
- max_col=max_col, max_row=max_row)
- def add_chart(ws, spec):
- kind = spec.get("type", "bar")
- chart = {"bar": BarChart, "line": LineChart, "pie": PieChart}[kind]()
- if "title" in spec:
- chart.title = spec["title"]
- data = ref_from_range(ws, spec["data"])
- chart.add_data(data, titles_from_data=spec.get("titles_from_data", True))
- if "categories" in spec:
- chart.set_categories(ref_from_range(ws, spec["categories"]))
- ws.add_chart(chart, spec.get("anchor", "H2"))
- def add_conditional(ws, spec):
- rng = spec["range"]
- kind = spec.get("type", "cell_is")
- if kind == "color_scale":
- rule = ColorScaleRule(
- start_type="min", start_color=spec.get("start_color", "FFF8696B"),
- end_type="max", end_color=spec.get("end_color", "FF63BE7B"))
- else:
- fill = PatternFill("solid", fgColor=spec.get("fill", "FFC7CE"))
- rule = CellIsRule(operator=spec.get("operator", "greaterThan"),
- formula=spec.get("formula", ["0"]), fill=fill)
- ws.conditional_formatting.add(rng, rule)
- def build_sheet(ws, spec):
- for row in spec.get("rows", []):
- values, styled = [], []
- for item in row:
- if isinstance(item, dict):
- values.append(None)
- styled.append(item)
- else:
- values.append(item)
- styled.append(None)
- ws.append(values)
- r = ws.max_row
- for idx, item in enumerate(styled, start=1):
- if item is not None:
- apply_cell(ws, ws.cell(row=r, column=idx).coordinate, item)
- for coord, cell_spec in spec.get("cells", {}).items():
- apply_cell(ws, coord, cell_spec)
- for col, width in spec.get("column_widths", {}).items():
- ws.column_dimensions[col].width = width
- for row, height in spec.get("row_heights", {}).items():
- ws.row_dimensions[int(row)].height = height
- for rng in spec.get("merges", []):
- ws.merge_cells(rng)
- if spec.get("freeze_panes"):
- ws.freeze_panes = spec["freeze_panes"]
- if spec.get("autofilter"):
- ws.auto_filter.ref = spec["autofilter"]
- for cf in spec.get("conditional_formats", []):
- add_conditional(ws, cf)
- for ch in spec.get("charts", []):
- add_chart(ws, ch)
- for dv_spec in spec.get("validations", []):
- dv = DataValidation(type=dv_spec.get("type", "list"),
- formula1=dv_spec["formula1"],
- allow_blank=dv_spec.get("allow_blank", True))
- dv.add(dv_spec["range"])
- ws.add_data_validation(dv)
- for t_spec in spec.get("tables", []):
- table = Table(displayName=t_spec["name"], ref=t_spec["range"])
- table.tableStyleInfo = TableStyleInfo(
- name=t_spec.get("style", "TableStyleMedium9"),
- showRowStripes=t_spec.get("row_stripes", True),
- showColumnStripes=t_spec.get("column_stripes", False))
- ws.add_table(table)
- prot = spec.get("protection")
- if prot:
- for rng in prot.get("unlock", []):
- for row in ws[rng]:
- for cell in row:
- cell.protection = Protection(locked=False)
- if prot.get("password"):
- ws.protection.password = prot["password"]
- ws.protection.sheet = True
- def main(argv=None):
- ap = argparse.ArgumentParser(description="Create .xlsx from a JSON spec.")
- ap.add_argument("spec", help="path to JSON spec, or '-' for stdin")
- ap.add_argument("output", help="output .xlsx path")
- args = ap.parse_args(argv)
- if args.spec == "-":
- spec = json.load(sys.stdin)
- else:
- with open(args.spec, encoding="utf-8") as fh:
- spec = json.load(fh)
- wb = Workbook()
- wb.remove(wb.active)
- for sheet_spec in spec.get("sheets", []):
- ws = wb.create_sheet(sheet_spec.get("name", "Sheet1"))
- build_sheet(ws, sheet_spec)
- for name, ref in spec.get("defined_names", {}).items():
- wb.defined_names[name] = DefinedName(name, attr_text=ref)
- if spec.get("full_calc_on_load"):
- wb.calculation.fullCalcOnLoad = True
- wb.save(args.output)
- print(json.dumps({"ok": True, "output": args.output,
- "sheets": wb.sheetnames}, ensure_ascii=False))
- return 0
- if __name__ == "__main__":
- try:
- sys.exit(main())
- except Exception as exc:
- print(json.dumps({"ok": False, "error": str(exc)}), file=sys.stderr)
- sys.exit(1)
|