xlsx

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