# The `yxl` spec format A reference for `*.yxl.yaml`. Worked examples live in [`examples/`](../examples); this page is the exhaustive list. > **Not frozen.** The schema may change until v1.0 (see > [`ROADMAP.md`](../ROADMAP.md)). Every change is in the §11 changelog. Throughout: **unknown keys are errors**, not ignored. A bad key, reference, or value fails the build with a diagnostic naming the file — `yxl` never guesses and never silently drops part of a spec. --- ## 1. Document A spec is a YAML mapping. Only `sheets` is required. ```yaml output: report.xlsx # where `yxl build` writes it (default: beside the spec) sheets: [...] # the workbook's sheets, in tab order active: Summary # the sheet Excel opens on (default: the first) params: {...} # named values substituted as ${name} → §7 defs: {...} # named styles, values, and formulas → §6 overrides: [...] # deliberate one-off deviations → §23 properties: {...} # what the file says about itself → §15 calc: {...} # when Excel recalculates → §15 protect: {...} # workbook protection → §16 date1904: false # use Excel's 1904 date epoch default_font: Calibri # the workbook's default font face ``` | Key | Type | Notes | |---|---|---| | `sheets` | sequence | **Required.** Order here is tab order. | | `output` | text | A `.xlsx` path, relative to the spec. See below. | | `active` | text | Must name a declared, **visible** sheet. | | `params` | mapping | §7. | | `defs` | mapping | §6. | | `overrides` | sequence | One-off deviations, applied after every cell. §23. | | `properties` | mapping | Document properties. §15. | | `calc` | mapping | Calculation settings. §15. | | `protect` | mapping | Workbook protection. §16. | | `date1904` | boolean | `true` selects the 1904 epoch. Affects how dates serialize. | | `default_font` | text | Face name only; size and colour are per-style. | Any node may instead be `{ $include: path }` — see §8. `output` lets a spec say where it builds to, so `yxl build report.yxl.yaml` needs no `-o`. It resolves against the spec's directory, as an include does (§8), and may use parameters (§7): `output: dist/report-${month}.xlsx`. A directory it names is created if missing. `-o` on the command line wins; with neither, the workbook goes beside the spec under the spec's name (`report.yxl.yaml` → `report.xlsx`). And `yxl build` with no spec at all builds the only `*.yxl.yaml` in the current directory — none, or several, is an error. The smallest document that compiles is one sheet with nothing in it, which is what `yxl init -o sheet.yxl.yaml` writes when you want somewhere to start: ```yaml sheets: - name: Sheet1 ``` `init` refuses to overwrite a file that already exists; `--force` says to anyway. ## 2. Sheets ```yaml sheets: - name: Sales # required cells: {...} # → §3 formulas: [...] # → §3 data: [...] # → §9 columns: [...] # → §4 layouts: [...] # → §25 rows: [...] # → §4 merges: [A1:C1] visibility: visible | hidden | very_hidden freeze: B2 # or: split: { x: 120, y: 60 } gridlines: true tab_color: "1F77B4" print: {...} # → §5 filter: A1:D1 # → §10 validations: [...] # → §10 links: {...} # → §10 conditional: [...] # → §10 comments: {...} # → §10 tables: [...] # → §11 charts: [...] # → §12 images: [...] # → §13 shapes: [...] # → §18 background: assets/logo.png # tiled behind the cells → §13 sparklines: [...] # → §19 controls: [...] # → §20 slicers: [...] # → §21 pivots: [...] # → §14 protect: {...} # → §16 ``` | Key | Type | Notes | |---|---|---| | `name` | text | **Required.** Must be unique in the workbook, and follow Excel's rules — see below. | | `cells` | mapping | A1 reference → value. §3. | | `formulas` | sequence | One formula filled across a region. §3. | | `data` | sequence | Anchored tables — rows written inline, or read from CSV/JSON. §9. | | `columns` / `rows` | sequence | Bands. §4. | | `layouts` | sequence | Regions of named columns. §25. | | `merges` | sequence of `A1:B2` | Corners in any order; the merge shows the top-left value. | | `visibility` | bareword | `hidden` can be undone in Excel's UI; `very_hidden` only via VBA. **At least one sheet must stay visible.** | | `freeze` | cell | Rows above and columns left of it stay put. `A1` freezes nothing and is an error. | | `split` | `{ x, y }` | A draggable splitter, in **points** from the top-left; `0` leaves that axis unsplit. Mutually exclusive with `freeze`. | | `gridlines` | boolean | The on-screen grid, *not* cell borders. | | `tab_color` | hex `RRGGBB` | | | `print` | mapping | §5. | | `filter` | range | Excel's auto filter. §10. | | `validations` | sequence | What cells will accept. §10. | | `links` | mapping | Hyperlinks, by cell. §10. | | `conditional` | sequence | Formatting decided by the value. §10. | | `comments` | mapping | Notes, by cell. §10. | | `tables` | sequence | Excel tables over the sheet's regions. §11. | | `charts` | sequence | Charts anchored on the sheet. §12. | | `images` | sequence | Pictures anchored on the sheet. §13. | | `shapes` | sequence | Boxes and other geometries floating over the sheet. §18. | | `background` | path | An image tiled behind the cells. §13. | | `sparklines` | sequence | Charts inside single cells, in groups. §19. | | `controls` | sequence | Buttons, check boxes, and sliders over the grid. §20. | | `slicers` | sequence | Button panels filtering declared tables. §21. | | `pivots` | sequence | Pivot tables placed on the sheet. §14. | | `protect` | mapping | Sheet protection. §16. | Sheet keys apply **in the order written**, so where a `data:` table and `cells:` overlap, whichever comes last wins. Where that ordering is the *point* — one cell held apart from the rule that produced it — write it as an override (§23) instead, which applies last whatever the file's key order. **Sheet names follow Excel's own rules**, checked when the spec loads rather than left to fail deep in the writer: 1–31 characters (counted as characters, so a Japanese name is as long as it looks), none of `: \ / ? * [ ]`, not starting or ending with an apostrophe, and not `History`, which Excel keeps for a shared workbook's change log. Breaking one names the sheet and the rule. ## 3. Cells A cell is either a scalar or a mapping. ```yaml cells: A1: Region # text B1: 2400000 # number C1: true # boolean D1: "007" # quoted → stays text A2: { value: 0.085, format: "0.0%" } B2: { formula: "SUM(B1:B1)", value: 2400000 } C2: { value: "2026-07-23", type: date } D2: { $ref: tax_rate } # a named value → §6 E2: { value: "26:30:00", type: duration } # an elapsed time, not a clock time A3: rich: - "Plain then " - { text: bold, font: { bold: true } } B3: { style: shaded } # a look and nothing in it ``` YAML's own types carry over: a bare `1` is a number, a quoted `"1"` is text. **Write a formula the way Excel's formula bar shows it.** A function newer than the file format, such as `XLOOKUP`, `FILTER`, `TEXTJOIN` or `MAXIFS`, is stored in the file under Excel's `_xlfn.` prefix, and `yxl build` adds it wherever a formula goes: a cell, a formula range, a conditional format, a defined name. `yxl extract` takes it off again. The parameter names of `LET` and `LAMBDA` need a prefix of their own in the file (`_xlpm.`), which `yxl` does not add yet, so a formula using them may open as `#NAME?`. ### The expanded form | Key | Notes | |---|---| | `value` | A scalar, or `{ $ref: name }` naming a `defs.values` entry. | | `formula` | A formula body; a leading `=` is accepted and stripped. Or `{ $ref: name }` naming a `defs.formulas` entry. With `value:`, that value is the **cached result** Excel shows until it recomputes. | | `rich` | A sequence of runs: a plain string, or `{ text:, font: }`. Mixes fonts inside one cell. | | `type` | `text` \| `number` \| `bool` \| `date` \| `duration` \| `error` — coerces the value. Cannot be combined with `formula`. | | `format` | An Excel number-format code, e.g. `"#,##0.00"`, `"0.0%"`. | | `style` | A style name (bareword) or an inline style mapping. §6. On its own — with no `value` or `formula` — it makes a blank cell that carries the styling. | `type: date` accepts `YYYY-MM-DD` or `YYYY-MM-DD HH:MM:SS` and stores an Excel serial. Without an explicit `format`, a date defaults to `yyyy-mm-dd` and a date-time to `yyyy-mm-dd hh:mm:ss`. `type: duration` accepts `H:MM` or `H:MM:SS` — an *elapsed* time, so the hours may exceed 23 (`26:30:00` is twenty-six and a half hours). Excel stores it as a plain number, the length as a fraction of a day; the default format `[h]:mm:ss` is what makes it display as hours, because `[h]` keeps counting past 24 instead of rolling into days. `type: error` accepts Excel's error literals: `#DIV/0!`, `#N/A`, `#NAME?`, `#NULL!`, `#NUM!`, `#REF!`, `#VALUE!`, `#SPILL!`, `#CALC!`, and `#GETTING_DATA`. A cell may be a **style and nothing else** — `B3: { style: shaded }` — which is how a shaded box, the ruled edge of a table, or the blank half of a merged heading is drawn. `value:` and `formula:` are then both absent, and `type:` has nothing to apply itself to, so it is refused. A cell needs at least one of `value`, `formula`, `rich`, `style`, or `format`. > **The Excel backend cannot yet write a *truly* blank styled cell**: it stores > one as an empty string, so `ISBLANK` on it is `FALSE` and `COUNTA` counts it. > The drawing is right and the formulas around it may not be; where that matters, > style the region with a `columns:` / `rows:` band (§4) instead, which reaches > the cell without creating it. **Line breaks inside a cell** are `\n` in a double-quoted scalar (`"line one\nline two"`), the character Excel itself stores for Alt+Enter. Write `\n` rather than `\r` or `\r\n`: a carriage return goes into the file literally, and XML 1.0 §2.11 has every reader — Excel included — normalize it to a line feed on the way back in, so the two spellings open the same and only the second one survives a re-`extract` unchanged. > **`yxl` emits formulas; Excel computes them.** There is no evaluator here. ### Filled formula ranges A calculation column is one formula that *moves*: `D2` is `B2*C2`, `D3` is `B3*C3`, and so on down. Writing that as `cells:` costs a line per row, and inserting a row rewrites every line below it. `formulas:` says it once. ```yaml formulas: - at: D2:D500 formula: "B2*C2" ``` The formula is written **as it applies at the range's top-left cell**. Every other cell gets it with its relative references shifted by that cell's offset; absolute references (`$C$2`) do not move. A range may span columns as well as rows, and a one-cell range is allowed. | Key | Notes | |---|---| | `at` | **Required.** A `TopLeft:BottomRight` range. | | `formula` | **Required.** The formula body; a leading `=` is accepted and stripped. | This compiles to Excel's own **shared formula**: the file stores the text once and marks the rest of the range as following it, so a 500-row column is one stored formula, not five hundred. A `{ $ref: name }` is **refused** here. A `defs.formulas` reference compiles to a defined name (§6), which gives every cell that references it the *same* formula — the opposite of filling a range with one that shifts per row. Write the formula in place instead. **Formatting is not part of a formula range**, for the same reason it is not part of a `data:` block (§9): style the region with a `columns:` / `rows:` band, which reaches every cell the range fills (§4). A range may not overlap another range, or any cell that `cells:` or `data:` writes — a cell holds one formula, and letting one side silently win would hide the clash. Split the range around the exception: ```yaml formulas: - at: D2:D9 formula: "B2*C2" - at: D11:D500 # D10 is written by hand formula: "B11*C11" ``` Where the exception is a *deliberate one-off* rather than a change of rule, `overrides:` (§23) says so in one line and leaves the range whole. ## 4. Column and row bands A band applies to a span of columns or rows. `at` selects it: a column label (`B`, `D-F`) or a row number (`1`, `2-4`). ```yaml columns: - at: B width: 18 # character units style: header # or an inline mapping format: "#,##0" - at: D-F group: 1 # outline level 0-7; Excel draws a collapsible bracket hidden: true # both together = a collapsed group rows: - at: 1 height: 28 # points ``` | Key | Columns | Rows | |---|---|---| | `at` | **required** | **required** | | `style`, `format`, `hidden`, `group` | ✔ | ✔ | | `width` (character units) | ✔ | — | | `height` (points) | — | ✔ | A band that sets nothing contributes nothing. A column band that sets no `width` keeps Excel's standard width, 8.43 characters. `group` must be a whole number between 0 and 7; `0` means ungrouped, which is distinct from omitting the key. ### When bands on one axis overlap Two `columns:` bands, or two `rows:` bands, may cover the same column or row. The later band in the list wins, but what it replaces differs by key: - **`style` / `format` is replaced whole.** The column (or row) takes its look from the last band that sets either key. It is not merged with an earlier band's look attribute by attribute. - **`width`, `height`, `hidden` and `group` are replaced one key at a time.** Each comes from the last band that sets *that* key. A later band that leaves a key out keeps the earlier band's value. ```yaml columns: - at: A-C style: { fill: "DDEEFF", format: "#,##0" } - at: B style: { font: { italic: true } } # B is italic only: no fill, no format - at: E-F width: 30 - at: F hidden: true # F is hidden, and still 30 wide ``` To add to an earlier band's look rather than replace it, name the look in `defs.styles` and `extends` it (§6): ```yaml defs: styles: money: { fill: "DDEEFF", format: "#,##0" } sheets: - name: S columns: - at: A-C style: money - at: B style: { extends: money, font: { italic: true } } # fill, format and italic ``` ### How a band's styling reaches a cell A band's `style` / `format` applies to every cell in its span, written or not. Where several apply, they **layer**, innermost last: the column band, then the row band over it, then the cell's own `style` / `format` over both. (The column band and the row band here are each the one that wins on its axis, as described above.) Layering is per attribute, as `extends` is (§6), so a column can set the number format while the cell sets the font and both survive. ```yaml columns: - at: B format: "#,##0" # every B cell counts in thousands rows: - at: 1 style: { font: { bold: true } } # …and the header row is bold cells: B1: Revenue # bold, and shown as text B2: 2400000 # bold and 2,400,000 ``` A cell can also refuse an attribute outright rather than override it — `style: { fill: null }` under a filled column — which §6 spells out. One exception, and it is Excel's rather than `yxl`'s: **an inherited number format does not apply to a text cell.** An Excel number format is `positive;negative;zero;text`, so a code with fewer than four sections says nothing about text and Excel displays it plain. A `format:` written on the cell itself is always honoured — that is a request, not an inheritance. ## 5. Print setup ```yaml print: area: A1:D50 orientation: portrait | landscape margins: { top: 1, bottom: 1, left: 0.7, right: 0.7, header: 0.3, footer: 0.3 } scale: 80 # percent, 10-400 fit: { width: 1, height: 0 } # pages across / down; 0 = unconstrained header: "&CQuarterly report" footer: "&LPage &P of &N" breaks: [A21, C1] # each starts a new page above and left of itself ``` Margins are in **inches** (Excel's unit here) and may not be negative. `scale` and `fit` are the two halves of Excel's scaling control and **cannot be combined**. `header`/`footer` use Excel's `&`-code syntax (ECMA-376 §18.3.1.46). A break at `A1` breaks nothing and is an error. ## 6. Definitions and references Declare once, reference by name. Reuse compiles to Excel's *native* sharing: a style becomes one `cellXfs` id however many cells wear it, and a value becomes a **defined name**, so editing it in Excel updates every reference. ```yaml defs: styles: base: { font: { name: Calibri, size: 11 } } header: { extends: base, font: { bold: true }, fill: "1F3864" } values: tax_rate: 0.085 formulas: subtotal: "SUM(B2:B10)" blocks: yoy: { columns: [...] } # a group of layout columns → §25 ``` - A **style** is referenced by bareword: `style: header`. - A **value** or **formula** is referenced by `{ $ref: name }`. - A **block** is placed in a layout's `columns:` by `{ block: name }` (§25). - The four namespaces are separate in the spec, but values and formulas both become Excel defined names, and Excel keeps one namespace for defined names and table names (a named layout's columns included, §25), compared ignoring case. A name that repeats there is an error, even between `values` and `formulas`. - A reference to an undeclared name is an error, and so is a cycle. ### Style attributes | Key | Value | |---|---| | `extends` | Another declared style's name. Merges *inside* font, alignment, and each border edge, so a child setting `bold` keeps the base's face and size. A fill or number format has no parts to blend, so setting it replaces it. Works in inline styles too. Forward references are fine; cycles are an error. | | `font` | `{ bold, italic, underline, strike, size, name, color }` — all optional. | | `fill` | A hex `RRGGBB`, or `{ color: RRGGBB }`. Solid fills only. | | `border` | A style name for all four edges (`border: thin`), or a mapping of `all` / `left` / `right` / `top` / `bottom`, each a style name or `{ style, color }`. Styles: `thin`, `medium`, `thick`, `dashed`, `dotted`, `double`, `hair`. | | `protection` | `{ locked, hidden }` — what sheet protection does to a cell wearing this style. §16. | | `format` | An Excel number-format code, as on a cell. A `format:` written beside the reference layers over this one. | | `align` | `{ horizontal, vertical, wrap }`. Horizontal: `left`, `center`, `right`, `fill`, `justify`, `distributed`. Vertical: `top`, `middle`, `bottom`, `justify`, `distributed`. | A cell's own `format` layers on top of a referenced style. ### Saying an attribute is *not* set Leaving a key out means "inherit": whatever the extended style, the column band, or the row band says comes through. **`null` is the other thing** — the attribute is explicitly not set, and nothing underneath supplies one. ```yaml columns: - at: C style: { fill: "FFF2CC" } # the whole column is filled… cells: C1: 1 C2: { value: 2, style: { fill: null } } # …except this cell ``` It reads the same wherever a style does, and at every attribute: ```yaml A1: { value: 1, style: { extends: header, fill: null } } # a header, unfilled B2: { value: 2, style: { font: { color: null }, format: null } } B3: { value: 3, style: { align: { horizontal: null }, border: { left: null } } } D4: { value: 4, style: { font: null } } # a whole group at once E5: { value: 5, format: null } # the `format:` shorthand too ``` A boolean needs none of this — `font: { bold: false }` is already a value, and says the same thing. `null` is for the attributes that have no "off": a fill, a colour, a number format, an alignment, a border edge. Three rules, and they follow from what layering already is (§4): - **It clears one attribute, not a look.** `{ extends: header, fill: null }` keeps the header's font and drops its fill. - **It stays cleared until something sets it.** A row band that clears the column band's format hands its cells no format, and a cell may still ask for one of its own. - **A value beside it wins.** `{ format: "0.00" }` written next to a style whose `format` is `null` is a format, not an absence — the two states are exclusive. One place refuses it: a `rich:` run's font, which inherits nothing, so there is nothing there for `null` to take away. ## 7. Parameters One spec, many workbooks. ```yaml params: region: APAC rate: 0.085 title: "${quarter} ${region}" # a default may use another parameter ``` ```bash yxl build report.yxl.yaml -o emea.xlsx --set region=EMEA ``` - `${name}` substitutes in **any string** — values *and* mapping keys, so a sheet name or a cell reference can be parameterized. - A string that is **exactly one placeholder** keeps the parameter's type, so `B1: "${rate}"` is a number cell, not the text `0.085`. - A `--set` value is read as the scalar it looks like, so `--set rate=0.15` stays a number. - `$$` is a literal `$`; a `$` that begins neither escape is itself, so Excel's absolute references (`$A$1`) are safe. Where a literal `$` immediately precedes a placeholder, write `$B$$${n}`. - Referencing an undeclared parameter is an error, as is `--set` naming one, as is a cycle among defaults. ## 8. Includes `{ $include: path }` stands wherever a node does — a whole document, one sheet, a `cells:` block, a `defs.styles` map. ```yaml defs: styles: $include: styles/theme.yaml sheets: - name: Sales cells: $include: data/q3.yaml ``` - Paths resolve **relative to the file containing the include**, so a spec directory can be moved as a unit. - Either separator works: `/` and `\` both split a path, on every platform. Use `/` inside a spec if you want it to read the same everywhere. (On Japanese Windows the backslash *displays* as `¥` — that is the same character. A real yen sign is an ordinary filename character, as it is to Windows itself.) - Non-ASCII paths are fine **inside** a spec. On the *command line*, Windows cannot carry them today — see the README's install notes. - Includes may nest. A cycle is an error, reported with the whole chain. - `$include` replaces its entire node, so combining it with sibling keys is an error rather than an implied merge. > A YAML `!include` **tag** is not supported and never will be: the parser drops > unknown tags silently, which would turn a typo into a plain string instead of > a diagnostic (ADR-014). ## 9. Tabular data `data:` anchors a table at a cell; its rows run down and its fields right. The rows may be written **here** or read from a **CSV or JSON file**. Either way the values become ordinary cells. ```yaml data: - at: A2 values: # rows written here - [APAC, 2400000] - [EMEA, 1750000] - at: A8 csv: data/sales.csv # or read from a file - at: D1 json: data/notes.json columns: [label, count] ``` | Key | Notes | |---|---| | `at` | **Required.** The top-left field lands here. | | `values` / `csv` / `json` | Where the rows come from. **Exactly one.** | | `columns` | **Only for JSON objects**: the fields to take, in order. | **Formatting is not part of a data block** — style the region with `columns:` / `rows:` bands. That is what keeps data and formatting separable. ### Inline rows, and why they exist A `cells:` mapping is keyed by address, so **inserting a row rewrites every key below it** and `git diff` reports the whole block as changed. An anchored table puts the addresses in one place — `at:` — so inserting a row is a one-line diff and moving the block is a one-word one: ```yaml data: - at: A2 values: - [APAC, 2400000] - [LATAM, 900000] # <- the only line a `git diff` shows - [EMEA, 1750000] ``` Fields keep the types YAML gave them, exactly as a `cells:` value does (§3): a bare `007` is the number 7 and a quoted `"007"` is text. That is sharper than CSV, where every field arrives as text and has to be guessed at. `null` is a blank cell, and a short row simply stops — nothing is padded. Neither writes a cell at all, so a `formulas:` range (§3) may cover the gap: a column of `null`s inside a block of rows reserves it for a derived column. Use `cells:` for scattered, individually-styled cells, which is what it is good at, and `values:` for a block of rows that belong together. ### CSV RFC 4180: commas separate fields, newlines separate records, and a `"`-quoted field may contain either and doubles an inner quote. CRLF is accepted, and rows need not be the same length. CSV carries no types, so a **bare** field is read as a number or boolean when it looks like one and text otherwise, while a **quoted** field always stays text — so `"007"` survives. An empty field writes no cell at all. ### JSON An **array of arrays** maps straight to rows. An **array of objects** requires `columns:`, because JSON object key order is not dependable and deriving a layout from it would break determinism. `null` is a blank cell; a named field that is missing is an error. > **Path resolution differs from `$include`:** a `data:` path resolves against > the spec passed to `yxl build`, not against the file the entry was written in. > It fails loudly with the path it tried, never silently reading the wrong file. > (ADR-016 — the tree carries no per-node provenance.) ## 10. Validation, filters, links, notes, and conditional formatting These decorate cells rather than fill them. A validation over an empty range is legal, and a link supplies no text of its own — the value you see still comes from `cells:` or `data:`. ### Auto filter ```yaml filter: A1:D1 # the header row Excel hangs its dropdowns off ``` One per sheet. Excel reads the range's top row as the header and filters what lies beneath it. To save the filter *with criteria*, write the range as `at:` and give `columns:`: ```yaml filter: at: A1:D200 columns: - { at: B, values: [North America, EMEA, APAC] } # the checkbox list - { at: C, at_least: 1000 } # a custom filter ``` Each column names its letter, inside the range, once, and exactly one criterion: - `values:` — the ticked boxes, as the cells **display** them: text, or a whole number written as its digits. - one of the eight comparisons `validations:` and `conditional:`'s `cell:` spell (`equals`, `not_equals`, `greater_than`, `less_than`, `at_least`, `at_most`, `between`, `not_between`). A bound is a number, a date, or text; text may use Excel's `*` and `?` wildcards with `equals`. `top`, `dynamic`, `color` and `icon` filters are refused by name. So is a comparison against the text `blanks` or `nonblanks`, which the Excel backend reads as its empty-cell filter. > **Excel does not re-run a saved filter when it opens the file.** The criteria > are what the dropdowns show ticked; the rows they leave out are hidden only if > the spec hides them too, under `rows:` (§4). `examples/interactive.yxl.yaml` > does both. ### Validations ```yaml validations: - at: B2:B200 list: [Draft, Sent, Paid] # the choices themselves - at: C2:C200 list: { from: "Statuses!A1:A3" } # or the cells holding them allow_blank: false prompt: { title: Status, body: "Pick one." } error: { title: "Not a status", body: "Choose from the list.", style: stop } - at: D2:D200 whole: { between: [1, 1000] } - at: E2:E200 decimal: { at_least: 0 } - at: F2:F200 text_length: { at_most: 12 } - at: G2:G200 date: { at_least: "2026-01-01" } ``` | Key | Notes | |---|---| | `at` | **Required.** The range the rule covers. | | `list` | A sequence of choices, or `{ from: range }` naming the cells holding them. | | `whole` / `decimal` / `text_length` / `date` | A comparison (below). `text_length` measures the text; `whole` refuses a fractional bound. | | `allow_blank` | Excel's "Ignore blank", **default `true`**. | | `prompt` | `{ title, body }` — shown when the cell is selected. | | `error` | `{ title, body, style }` — shown when a value is refused. | Exactly one rule per entry. The comparison is exactly one of `between`, `not_between` (each `[low, high]`), `equals`, `not_equals`, `at_least`, `at_most`, `greater_than`, `less_than`. `error.style` is `stop` (the default, which refuses the value), `warning`, or `information` (which let it through). A `date` bound is written as a date (`YYYY-MM-DD`); every other kind takes a number. An inline `list` is stored as one comma-joined string and must fit Excel's 255-character limit — over that, source it from cells instead. Write `from:` as a **plain** range — `Statuses!A1:A3`. Excel stores the source as an absolute, sheet-qualified reference and yxl writes it that way for you; a `$` written by hand is not a valid A1 reference here and is refused. ### Links ```yaml links: A2: https://example.com/orders/1001 # outside the workbook B1: { to: "Statuses!A1", tip: "The statuses" } # inside it C1: { url: "https://example.com", tip: "The dashboard" } ``` A bare value is a URL. `to:` is an in-workbook target — `Sheet!A1` or a defined name — and is *not* inferred from shape, since `Summary!A1` and a URL are both just text. | Key | Notes | |---|---| | `url` | A target outside the workbook. Exactly one of `url` / `to`. | | `to` | A target inside it. | | `tip` | The hover tooltip. | A sheet named by `to:` or by a validation's `from:` must be declared, or the build fails — Excel reports neither, so a typo would otherwise ship as a drop-down that comes up empty or a link that goes nowhere. **Write the sheet name plainly; the quoting is added for you.** Excel reads a link's target as a reference, and a reference to a sheet whose name is not a bare word has to quote it — `to: "Q1 Sales!B2"` reaches the file as `'Q1 Sales'!B2`. Quoting it yourself is fine too, and an apostrophe in the name is doubled either way (`O'Brien` → `'O''Brien'!A1`). A `to:` naming no sheet is a defined name and is left exactly as written. ### Notes ```yaml comments: C3: "Bulk order — check stock before confirming." B1: { text: "Sourced from the Statuses sheet.", author: Finance } ``` A note (Excel's older name for it is a comment) appears on hover. Like a link, it decorates the cell: the value shown is still the cell's own. | Key | Notes | |---|---| | `text` | **Required** in the expanded form; a bare value is the text. | | `author` | Shown above the text. A note always carries one in the file, so leaving it out does not omit it — Excel writes a generic name instead. | ### Conditional formatting Formatting decided by the value rather than the address. ```yaml conditional: - at: B2:B50 cell: { at_least: 1000000 } # the same comparisons validations use style: strong # a declared style, or an inline mapping - at: B2:B50 cell: { less_than: 0 } style: weak stop_if_true: true # matched here, Excel tries no later rule - at: C2:C50 text: { contains: urgent } style: weak - at: D2:D50 formula: "AND($D2>0, $E2<0)" style: strong - at: E2:E50 top: 10 # or { count: 10, percent: true } style: strong - at: F2:F50 duplicate: true # or unique: true style: weak - at: G2:G50 color_scale: { low: "F8696B", middle: "FFEB84", high: "63BE7B" } - at: H2:H50 data_bar: { color: "638EC6" } - at: I2:I50 icon_set: 3TrafficLights1 # or { style:, reverse:, icons_only: } ``` | Key | Notes | |---|---| | `at` | **Required.** The range the rule covers. | | `cell` | A comparison, spelled exactly as in a validation (§ above). The bound is read as it is written: a number, a date if it parses as one, otherwise text. | | `formula` | An expression, true where the rule should apply. Written relative to the range's **top-left** cell, as Excel's own dialog does — `$B2` holds the column and lets the row move. | | `text` | Exactly one of `contains`, `not_contains`, `begins_with`, `ends_with`. | | `top` / `bottom` | A count (1–1000), or `{ count, percent: true }` for a percentage (1–100). | | `duplicate` / `unique` | `true`. Values appearing more than once, or exactly once, in the range. | | `color_scale` | `low` and `high`, optionally `middle` — a two- or three-color gradient. | | `data_bar` | `color`, and `bar_only: true` to hide the value behind the bar. | | `icon_set` | One of Excel's own names — `3Arrows`, `3TrafficLights1`, `4Rating`, `5Rating`, … — optionally with `reverse` and `icons_only`. The seventeen are listed below. | | `style` / `format` | The look applied where the rule matches. | | `stop_if_true` | Stop evaluating later rules on a cell this one matched. | Rules apply **in the order written**, which is Excel's priority order. `style` and `format` are **required** for the rules that highlight (`cell`, `formula`, `text`, `top`, `bottom`, `duplicate`, `unique`) — one without a look would match cells and change nothing. They are **refused** for the three that draw their own appearance (`color_scale`, `data_bar`, `icon_set`), which would have nothing to apply it to. Excel keeps these looks in a table of its own, separate from the styles cells wear, but they are declared the same way and shared the same way: a look used by ten rules is stored once. ### The icon sets `3Arrows`, `3ArrowsGray`, `3Flags`, `3Signs`, `3Symbols`, `3Symbols2`, `3TrafficLights1`, `3TrafficLights2`, `4Arrows`, `4ArrowsGray`, `4Rating`, `4RedToBlack`, `4TrafficLights`, `5Arrows`, `5ArrowsGray`, `5Quarters`, `5Rating`. **Three more that Excel offers are refused, and named as such.** `3Stars`, `3Triangles` and `5Boxes` arrived with Excel 2010 and live only in an extension schema; the Excel backend writes only the base attribute, whose enumeration (ECMA-376 §18.18.42) is the seventeen above: ``` yxl: conditional 'C2:C50' 'icon_set' asks for '5Boxes', which Excel 2010 added in its x14 extension schema; the Excel backend writes only the base 'iconSet' attribute, whose ECMA-376 §18.18.42 enumeration does not have it. Pick one of the seventeen the base schema names ``` They become plain schema additions the day the backend routes them through the extension, as it already does for advanced data bars. ## 11. Tables An *Excel table* (a "ListObject") declares a region to **be** a table rather than merely look like one: it arrives with filter buttons, banded shading, a name formulas can use (`=SUM(Revenue[Revenue])`), and it grows over any row typed beneath it. ```yaml tables: - at: A1:B4 # required; the top row is the header name: Revenue # what formulas call it (default: Table1, Table2, …) style: TableStyleMedium2 banded_rows: true # shade alternate rows (default: true) banded_columns: false # shade alternate columns (default: false) first_column: false # emphasize the first column (default: false) last_column: false # emphasize the last column (default: false) ``` | Key | Type | Notes | |---|---|---| | `at` | range | **Required.** Includes the header row, so it spans at least two rows. | | `name` | text | Excel's defined-name rules: starts with a letter or `_`, then letters, digits, `.` and `_` — **no spaces** — and never looks like a cell reference. Unique across the workbook, ignoring case. | | `style` | text | One of Excel's built-ins: `TableStyleLight1`–`TableStyleLight21`, `TableStyleMedium1`–`TableStyleMedium28`, `TableStyleDark1`–`TableStyleDark11`. | | `banded_rows`, `banded_columns`, `first_column`, `last_column` | boolean | The four toggles of Excel's "Table Design" ribbon. What each does depends on the style, which supplies the colours. | The cells stay ordinary cells — write them with `cells:` or `data:` as usual; `tables:` only says what the region is. **The top row must name every column, as text**, and no two names may repeat (Excel compares them ignoring case). A number is not a column name: quote it if that is what you meant. ```yaml cells: A1: Region # ← the column names B1: Revenue A2: APAC B2: 2400000 tables: - at: A1:B2 name: Revenue ``` A table may not overlap another table or the sheet's own `filter:` — a table carries its own filter buttons, so it needs no separate one. Excel repairs (or refuses) a workbook that breaks any of this, so `yxl` refuses the spec first. A table with its header row turned off is not expressible yet. ## 12. Charts A chart is a picture of cells that already exist. It holds no values of its own — every part of it points at a range — so editing a cell redraws the chart, and a chart may plot a sheet other than the one it sits on. ```yaml charts: - at: E2 # required; the chart's top-left corner floats here type: column # required title: Revenue by region legend: bottom # bottom | top | left | right | top_right | none size: { width: 520, height: 300 } # pixels x_axis: { title: Region } y_axis: { title: Amount, min: 0, max: 4000000 } series: # required; at least one - values: B2:B4 # required categories: A2:A4 # the labels down the category axis name_from: B1 # the legend entry, read from a cell - values: Figures!C2:C4 categories: Figures!A2:A4 name: Cost # …or written out ``` | Key | Type | Notes | |---|---|---| | `at` | cell | **Required.** A chart floats above the grid; the cells beneath it keep whatever they hold. | | `type` | bareword | **Required.** See below. | | `series` | sequence | **Required**, and not empty. | | `title` | text | | | `legend` | bareword | `none` leaves the chart without one. | | `size` | `{ width, height }` | **Both required**, in whole pixels. | | `x_axis` / `y_axis` | `{ title, min, max }` | At least one of the three. An unset end leaves Excel scaling the axis to the data. | **Types:** `column`, `column_stacked`, `column_percent_stacked`, `bar`, `bar_stacked`, `bar_percent_stacked`, `line`, `area`, `area_stacked`, `area_percent_stacked`, `pie`, `doughnut`, `scatter`, `radar`. Excel's 3-D variants, stock charts, and bubble charts are not expressible yet. ### Series | Key | Notes | |---|---| | `values` | **Required.** The cells plotted. `Sheet!A1:A9` names another sheet, which must be declared. | | `categories` | **Required in practice** — see below. The labels down the category axis; for a `scatter` chart, the X values. | | `name` | What the legend calls the series, written out. | | `name_from` | A cell to read that name from — usually the column header, so renaming the header renames the series. Mutually exclusive with `name`. | **`categories` cannot be omitted today.** Excel numbers the points 1, 2, 3, … for a series that names no category range, but the Excel backend refuses to build such a series at all, so a spec leaving it out fails to compile: ``` yxl: Excel backend: InvalidSheetOperation(msg="chart series categories/values must be non-empty") ``` Give every series a `categories` range until that is resolved. Which way it is resolved — the key becomes required, or the omission starts working — is open and is settled before the schema freeze. A literal `name` may not contain `!`: Excel reads a series name holding one as a reference to a cell, so it would quietly become a lookup. Use `name_from`. A `pie` or `doughnut` draws only its first series. Every range is emitted sheet-qualified and absolute (`'Figures'!$B$2:$B$4`), which is how Excel stores one — a chart lives in a part of its own, where a bare `B2:B4` names nothing. ## 13. Images A picture floats above the grid: `at` positions its top-left corner, and the cells under it keep whatever they hold. ```yaml images: - at: E1 file: assets/logo.png # required alt: Example Ltd logo scale: 0.5 # or: { x: 2, y: 0.5 } offset: { x: 4, y: 4 } # pixels in from the cell's corner positioning: move # move | move_and_size | fixed ``` | Key | Type | Notes | |---|---|---| | `at` | cell | **Required.** | | `file` | path | **Required.** Resolved like a `data:` path — see §9. | | `alt` | text | What a screen reader announces; Excel's "Alt Text". | | `scale` | number or `{ x, y }` | A factor over the image's natural size, above `0` and at most `100`. One number scales both directions. | | `offset` | `{ x, y }` | **Both required**, in whole pixels, and never negative — OOXML's anchor measures in from the cell's corner (ECMA-376 §20.5.2.3). | | `positioning` | bareword | What happens when the cells beneath change. | **Positioning**, in the words of Excel's own "Size and Properties" pane: | Written | Excel calls it | |---|---| | `move` (the default) | "Move but don't size with cells" | | `move_and_size` | "Move and size with cells" | | `fixed` | "Don't move or size with cells" | **Formats:** `png`, `jpg`/`jpeg`, `gif`, `bmp`, `tif`/`tiff`, `ico`, `svg`, `emf`/`emz`, `wmf`/`wmz`. The format is taken from the file's extension, since that is what Excel decodes by — it never inspects the bytes. An unknown extension, a name with no extension at all, and an empty file are each diagnostics. The bytes are read while the spec compiles and travel into the workbook, so the `.xlsx` carries the picture itself and no longer needs the file. ### Sheet backgrounds `background: ` on a sheet tiles an image behind its cells, like a watermark — the same formats, path resolution, and diagnostics as an `images:` file. One per sheet. > **Excel never prints a background.** It shows on screen only — a surprise > worth knowing before building letterhead around one. A printed watermark is a > header/footer picture, which `yxl` does not emit yet. ## 14. Pivot tables A pivot table summarizes cells that live somewhere else — usually a plain data sheet — grouping them down the rows and along the columns and aggregating in the middle. The spec says what to summarize and how; **Excel does the arithmetic**, and redoes it whenever the source changes. ```yaml pivots: - at: A3:F30 # required; where the pivot is drawn source: Orders!A1:D7 # required; header row first rows: [Region] columns: [Quarter] filters: [Channel] values: - field: Revenue function: sum # default name: Total revenue # Excel would say "Sum of Revenue" name: RevenueByRegion style: PivotStyleMedium9 row_grand_totals: true column_grand_totals: true ``` | Key | Type | Notes | |---|---|---| | `at` | range | **Required.** The corner the pivot starts from; Excel grows it past this as it needs to. | | `source` | range | **Required.** `Sheet!A1:D7` names another sheet, which must be declared. Its **top row names the fields**, and there must be at least one row beneath and more than one column. | | `rows` / `columns` | sequence | Field names, or `{ field, name }` to relabel one. | | `filters` | sequence | The same, for Excel's "Filters" box — which it draws in the rows *above* `at`. | | `values` | sequence | Field names (summed), or `{ field, function, name }`. | | `name` | text | Default: `PivotTable1`, `PivotTable2`, … | | `style` | text | `PivotStyleLight1`–`28`, `PivotStyleMedium1`–`28`, `PivotStyleDark1`–`28`. | | `row_grand_totals` / `column_grand_totals` | boolean | Both default to `true`, as Excel does. | **Functions:** `sum` (the default), `count`, `count_numbers`, `average`, `max`, `min`, `product`, `std_dev`, `std_dev_p`, `var`, `var_p`. Every field named on any axis must be a column of the source's header row, and that row must name **every** column of the range, as text — a pivot cannot refer to a field Excel would not find. A pivot may not be drawn over the cells it summarizes. > **The file carries no summary.** `yxl` writes the pivot's definition and an > empty cache marked "refresh on load", so the numbers appear when Excel opens > the workbook — the same arrangement as a formula, which `yxl` also emits > without evaluating. > **A filter field draws above `at`.** Excel puts the "Filters" box in the rows > over the pivot's own range, so leave them clear — `at: A3:F30` with two filter > fields uses rows 1 and 2 as well. ## 15. Document properties and calculation ```yaml properties: title: Quarterly report subject: Regional sales author: Finance team # Excel's "creator" keywords: "sales, q3" description: Revenue by region. category: Reports status: Final # Excel's "content status" language: en-GB version: "1.2" company: Example Ltd custom: # names of your own devising Cost centre: 4210 Reviewed: true calc: mode: manual # automatic | automatic_no_tables | manual on_load: true # recalculate once when the workbook opens ``` Every `properties:` key is optional, and one left out is left out of the file — a reader can tell "not said" from "said to be blank". A `custom:` value keeps its YAML type: `4210` is a number, `true` a boolean, anything else text. Write a date as text; Excel's date type here wants a full timestamp with a zone, and one written without would have to be guessed at. `calc:` only tells Excel what to do **on open** — `yxl` emits formulas and never evaluates them (§3). `mode: manual` leaves a workbook showing whatever cached values the spec supplied, which is worth setting where recalculation is slow; `on_load: true` forces one full pass regardless, which is what you want where the spec supplied no cached values at all. ## 16. Protection ```yaml defs: styles: entry: protection: { locked: false } # this is what makes a form fillable sheets: - name: Orders protect: # the sheet's cells password: "${sheet_password}" allow: sort: true auto_filter: true select_locked_cells: false ``` **Excel locks every cell by default**, so protecting a sheet freezes all of it. The way to leave a form's input boxes editable is to give *them* a style with `protection: { locked: false }`. `hidden: true` additionally keeps the cell's formula out of the formula bar — the value still shows, and the file still contains the formula. A sheet's `allow:` names what a reader may still do; anything unnamed keeps Excel's default, which is **selection allowed, everything else blocked** — exactly what its own "Protect Sheet" dialog opens with. The full list: `select_locked_cells`, `select_unlocked_cells`, `format_cells`, `format_columns`, `format_rows`, `insert_columns`, `insert_rows`, `insert_hyperlinks`, `delete_columns`, `delete_rows`, `sort`, `auto_filter`, `pivot_tables`, `edit_objects`, `edit_scenarios`. A misspelt one is an error, not a permission that silently never applies. > **A protection password is not encryption.** It stops accidents and is > trivially removed; Excel stores a hash, not the word, and the file's contents > are readable either way. A spec is usually version-controlled, so write the > password as a parameter (§7) and pass it with `--set` rather than committing > it. Encrypting the *file* is not supported. ### Locking the workbook itself ```yaml protect: structure: true # no adding, removing, renaming, reordering sheets windows: false # no moving or resizing its windows password: "${book_password}" ``` Top-level `protect:` locks the workbook rather than its cells, and at least one of `structure` and `windows` must be set — locking neither would write nothing. Excel for Windows has ignored `windows` since 2013; it is written for older readers. The password carries the same caveat as a sheet's. ## 17. Diagnostics A failed build prints one diagnostic and exits non-zero. YAML **syntax** errors carry a line and column with the source quoted: ```text report.yaml:5:8: invalid YAML: while parsing a block mapping, did not find expected key | 5 | B1: y | ^ ``` **Schema** errors name the file and the construct they are about — `cell 'A1'`, `column 'B'`, `sheet 'Sales'`, `parameter 'region'` — but not a line; the parser exposes no per-node positions (ADR-016). | Exit code | Meaning | |---|---| | `0` | success | | `1` | the spec is invalid, or a file could not be read or written | | `2` | the command line itself was wrong | ## 18. Shapes A shape — a box, star, or other preset geometry — floats above the grid, optionally carrying text: `at` positions its top-left corner, and the cells under it keep whatever they hold. ```yaml shapes: - at: E2 kind: cloud # required — see the geometry list below text: Approved # or a list of lines, each with its own font size: { width: 240, height: 120 } fill: "1F77B4" line: { color: "333333", width: 2 } alt: An approval stamp positioning: move # move | move_and_size | fixed ``` | Key | Type | Notes | |---|---|---| | `at` | cell | **Required.** | | `kind` | bareword | **Required.** One of the geometries below. | | `text` | text or list | A plain string, or a sequence of lines — each a string or `{ text, font }`, the font written as a style's `font:` (§4 of the styling keys). Each entry is its own *line*: one font covers one line. | | `size` | `{ width, height }` | **Both required**, in whole pixels. Unset, the shape is 160 × 160. | | `fill` | hex color | The fill. Unset means **no fill at all**, not a theme colour — see the backend limits below. | | `line` | hex color or `{ color, width }` | The outline. `width` is in points and must be above `0`; a bare hex is just the color. | | `alt` | text | What a screen reader announces; Excel's "Alt Text". | | `positioning` | bareword | The same three anchors an image takes (§13). | **Geometries.** `kind` is one of: `rectangle`, `rounded_rectangle`, `ellipse`, `triangle`, `right_triangle`, `diamond`, `parallelogram`, `trapezoid`, `pentagon`, `hexagon`, `octagon`, `decagon`, `star_5`, `plus`, `chevron`, `cube`, `can`, `donut`, `frame`, `heart`, `moon`, `sun`, `cloud`, `pie`, `line`, `arrow_right`, `arrow_left`, `arrow_up`, `arrow_down`, `arrow_left_right`, `arrow_up_down`, `callout_rectangle`, `callout_rounded_rectangle`, `callout_ellipse`, `callout_cloud`. Each maps to a DrawingML preset (ECMA-376 §20.1.10.56); an unknown name is a diagnostic. **Backend limits.** An offset in from the anchor cell (which images take) is not available: the backend's shape constructor does not accept one. The backend also writes **no theme style reference** for a shape — none of the `fillRef`/`lnRef` that Excel's own "Insert Shape" puts on one. So a shape given neither `fill` nor `line` is written with no fill and no outline, and Excel draws nothing: the shape is there and selectable, but invisible. Give a shape a `fill:` or a `line:` if it is meant to be seen. ## 19. Sparklines A sparkline is a chart inside one cell, for the row of figures beside it. Each `sparklines:` entry is a **group**: Excel scales and styles a group as one unit, so a column of them reads as one series. ```yaml sparklines: - at: F2 # one sparkline … data: B2:E2 # … of these cells (may name another sheet) markers: true # dot every point (line only) high: true # pick out the highest point color: "1F77B4" - cells: # or several cells, styled as one group - { at: G2, data: Results!B2:E2 } - { at: G3, data: Results!B3:E3 } type: win_loss # line (default) | column | win_loss axis: true # draw the horizontal axis at zero ``` | Key | Type | Notes | |---|---|---| | `at` / `data` | cell / range | One sparkline: the cell it sits in, the cells it plots. `data` may name another (declared) sheet. | | `cells` | sequence of `{ at, data }` | Several sparklines in one group. A group is placed one way: `at`/`data` **or** `cells`. | | `type` | bareword | `line` (default), `column`, or `win_loss` — win/loss plots only each point's sign. | | `markers` | boolean | Dot every point. Line only — the other kinds already draw one mark per point. | | `high` / `low` | boolean | Emphasize the highest / lowest point. | | `min` / `max` | whole number | Manual vertical bounds; unset, Excel scales each end to the data. Whole numbers — the backend accepts nothing finer. | | `weight` | number | The line weight in points, above `0`. Line only. | | `color` | hex color | The series colour. | | `colors` | `{ markers, high, low }` | Colours for the marks that can show. | | `axis` | boolean | Draw the horizontal axis at zero. | **Backend limits.** Excel's *first point*, *last point*, and *negative points* markers are carried by the backend as options nothing can set, so `first:`, `last:`, and `negative:` are refused by name with the reason — they become plain schema additions the day the setters exist upstream. ## 20. Form controls A form control sits over the grid and writes into its **linked cell** — a boolean for a check box, the chosen option's index for option buttons, the number for a scroll bar or spin button. That linked value is what formulas react to, and it is the other half of the story `protection` (§16) tells: lock the sheet, unlock the entry cells, and let the controls drive the rest. ```yaml controls: - at: F2 kind: check_box # see the kinds below text: Rush order checked: false link: G2 # the cell the control writes into (same sheet) - at: F4 kind: scroll_bar link: H4 min: 0 max: 100 step: 5 # one arrow click page: 20 # one click in the trough value: 40 horizontal: true size: { width: 160, height: 20 } ``` **Kinds:** `button`, `check_box`, `option_button`, `scroll_bar`, `spin_button`, `group_box`, `label`. Each key is admitted only on the kinds whose own "Format Control" dialog shows it — anything else is a diagnostic naming where the key belongs: | Key | Belongs to | Notes | |---|---|---| | `at` / `kind` | all | **Required.** | | `size` | all | `{ width, height }`, both in whole pixels. | | `text` | `button`, `check_box`, `option_button`, `group_box`, `label` | The caption. | | `checked` | `check_box`, `option_button` | Starts ticked. | | `link` | `check_box`, `option_button`, `scroll_bar`, `spin_button` | A **same-sheet** cell; the backend refuses a qualified reference. | | `min` / `max` / `step` / `value` | `scroll_bar`, `spin_button` | Whole numbers `0`–`30000`, Excel's own dialog limit; `min` may not exceed `max`. | | `page` | `scroll_bar` | A click in the trough; a spin button has none. | | `horizontal` | `scroll_bar`, `spin_button` | Lay it sideways. | No `macro:` — an `.xlsx` carries no macros, and a button without one is a caption that clicks. Assigning behavior is Excel's side of the contract. > **On a protected sheet, unlock the linked cell.** Excel writes a control's > value into its `link` like any other edit, so a locked target makes the > control show "the cell is on a protected sheet" instead of working. Give the > linked cell a style with `protection: { locked: false }` (§16), exactly as > for typed-into entry cells. ## 21. Slicers A slicer is a table's filter as a panel of buttons — one per distinct value of one column — floating over the grid. Clicking a button filters the table, visibly. ```yaml sheets: - name: Sales cells: { A1: Region, B1: Revenue, A2: APAC, B2: 2400 } tables: - at: A1:B4 name: Revenue # a slicer needs the table *named* slicers: - at: G1 table: Revenue # the declared table, on any sheet column: Region # one of its header cells caption: Filter by region size: { width: 160, height: 140 } header: true ``` | Key | Type | Notes | |---|---|---| | `at` | cell | **Required.** Where the panel's top-left corner sits. | | `table` | text | **Required.** The `name:` of a declared table, compared without case (Excel's own rule). A table the spec left unnamed cannot be sliced. | | `column` | text | **Required.** One of the table's header cells; anything else is a diagnostic naming what the table has. | | `caption` | text | The panel's title; unset, Excel shows the column name. | | `size` | `{ width, height }` | Both in whole pixels; unset, 200 × 200. | | `header` | boolean | Whether the panel shows its title bar. | The slicer may sit on a different sheet than its table — the panel goes where the reader looks, the data stays where it lives. **Pivot slicers are not offered yet.** The backend defect that bound `pivots:` is fixed, so a slicer over a pivot is now a schema question rather than a blocked one; it is not answered here. ## 22. Going the other way: `yxl extract` An existing workbook becomes a starting spec instead of a retyping job. ``` yxl extract report.xlsx -o report.yxl.yaml # a spec, plus its tables yxl extract report.xlsx -o report.yxl.yaml --flat # one file, whatever it costs ``` This is a **one-way migration aid, not a round trip**. What the spec format can say is recovered; what it cannot is dropped, and each kind of loss is named once on the way out. Treat the result as a starting point and edit it — that is what it is for. ### It may write more than one file A sheet that is nothing but a rectangle of plain values — no formulas, no styles, no gaps — is written out as a **CSV beside the spec** and named by a `data:` entry (§9), because a thousand `cells:` lines is not a spec anybody keeps. The file lands next to the spec at the path the spec names it by: ``` report.yxl.yaml data/sales.csv assets/summary_e1.png ``` The rule for tables is deliberately narrow. A styled cell, a formula, a ragged edge, or a region too small to be worth opening a second file all keep the whole sheet inline — a wrong "yes" here would silently drop formatting `data:` cannot carry, where a wrong "no" only leaves a long block. `--flat` turns it off entirely, which is what to use when comparing two extractions. **Pictures are always written out**, `--flat` or not: an image has no inline form to fall back to. Its bytes come through unchanged, so the file beside the spec is the picture the workbook held. ### What it recovers Cell values, formulas, and mixed-font rich text — including the cells that **follow a shared formula**, which come back as the `formulas:` range (§3) they were declared as where the whole range is unstyled, and as their own translated `formula:` where it is not. Cells that are a **style and nothing else** (§3). Styles, **interned**: a look worn by forty cells becomes one `defs.styles` entry, because the file kept the sharing even though it lost the name — with **theme and palette colours resolved to RGB**, tint included, against the theme the workbook carries. Merged ranges. Column and row bands — widths, heights, hidden, outline levels — with adjacent equal ones collapsed back into a single entry, and a cell whose own formatting is *missing* something its band sets written with the `null` that says so (§6): a `` in a file takes its whole look from its own style, so the unfilled cell in a filled column is a cell, not a band. Frozen and split panes, gridlines, tab colours, sheet order, hidden sheets, the active tab, and the 1904 date system. Pictures, including the one tiled behind a sheet: the bytes come out unchanged, and each is written beside the spec with the `images:` or `background:` entry that names it. The decorations too: notes, hyperlinks, data validations (with their prompts, error dialogs, and — for a `date` rule — bounds turned back from serials into written dates), conditional formats of every rule the format names, Excel tables, and the auto filter's range. Charts (§12), with the kind — including which way the bars run and how they are grouped — the ranges each series plots, the title, the legend's place, the size where it is not Excel's own, and each axis' title and manual bounds. Every range comes back sheet-qualified, because that is how the file stores one. Pivot tables (§14): where each is drawn, the region and sheet it summarizes, all four axes with the names it gives their fields, every aggregation, its own name, its style, and either grand total. The field names come from the cache part, which is what a pivot's own definition refers to by position. The four things that float over a sheet rather than sitting in it: shapes (§18) with their geometry, text, size, fill, outline, and anchor; sparklines (§19) with their kind, points, bounds, weight, and any colour the file names outright; form controls (§20) with what each writes into its linked cell; and slicers (§21) with the table column each filters. Four details of these do not survive, and each is listed below. And what the workbook says about itself: named definitions (`defs.values` and `defs.formulas`), the document properties including the custom ones, the calculation settings, the default font, and each sheet's protection. Workbook-level `protect:` (§16) is **not** among them: the Excel backend keeps its workbook-protection record opaque, so `extract` cannot see what was locked. ### What it does not, and why - **Names are invented, because the file has none.** A style is given a descriptive name only on strong evidence — bold on a fill is `header`, a lone percentage format is `percent` — and a neutral `style`, `style_2`, … otherwise. A wrong name misleads where a meaningless one merely says nothing, and renaming means replacing every reference rather than one definition. - **A number is left a number**, even under a date format. `type: date` would be a guess about intent, and the number plus its format compiles back to the identical cell. - **A formula's cached result is left behind.** Excel recomputes it on open. - **A theme colour on a *border* is not recovered.** Fonts and fills resolve theirs against the theme in the file; the Excel backend's border reader takes a literal `rgb` alone and has no field for a theme index, so a theme-coloured edge arrives already colourless — and, since that is also what an Excel-default border looks like, it cannot even be reported. - **A column marked `customWidth="0"` comes back with its width written out.** Such a column is at Excel's *default* width and the stored number is informational, but the backend does not read the flag, so rebuilding turns it into an explicitly sized column and Excel stops auto-fitting it. Delete the `width:` where the original did not set one. - **A picture's scale is not recovered.** The file records how big to draw it in absolute units rather than as a factor, and the Excel backend reports the factor back as `1` whatever it was — so a scaled picture cannot be told from an unscaled one, and every picture comes back at its natural size. `extract` says so on the way out rather than leaving it to be noticed. - **A form control's size is not recovered.** The file records it in the far corner of the control's VML anchor, and the Excel backend's reader takes only the near corner from there — so every control comes back at Excel's default, and one that was sized cannot be told from one that was not. - **A shape geometry outside §18's table is dropped and named.** DrawingML has about 180 presets and the schema spells a subset of them; a shape drawn with one of the others is reported rather than guessed at. - **The sheet a sparkline plots from is not recovered.** The file writes `'Data'!B2:E2` and the backend's reader keeps only what follows the `!`, so a sparkline that plotted another sheet comes back plotting this one — the same shape of answer with the wrong numbers in it. `extract` says so whenever a workbook has more than one sheet; with a single sheet nothing can be lost. - **A sheet that is not a grid is skipped and named.** A chart sheet — a whole tab that is one chart, which Excel makes when you move a chart to its own sheet — has no shape the spec can declare, and the chart on it is raw XML besides. It is left out with a note rather than recovered as an empty sheet, which would claim a data tab the workbook does not have. - **A pivot's compact layout and its field sorting are not kept.** Excel's own pivots are in compact form — every row field sharing one column — and may sort a field by its values; the schema says neither, so a rebuilt pivot is in outline form with a column per field, in the order the spec lists them. Both are reported. - **A pivot the schema cannot describe is refused whole, not half-read.** One summarizing an external source, another pivot, or an Excel table rather than a region of cells; one with a calculated field, whose name stands for a formula and not a source column; one aggregating with a function outside §14's list; one wearing a style outside the three families. Each is reported with the reason and no pivot is written. A number format on a value field is dropped while the pivot is kept, and named — the schema formats cells, not aggregations. - **A chart that says something the schema cannot is refused whole, not half-read.** A combination chart (bars with a line over them), a grouping the schema has no word for such as a stacked line, a kind outside §12's list, a series holding its numbers rather than pointing at cells: each is reported with the reason and no chart is written. Half of one would compile to a picture of the wrong thing, and nothing about the result would look wrong. Two details are dropped while the chart is kept, and both are named: a title **read from a cell** rather than written out, and a series name that points at more than one cell. - **A colour scale's stops are not kept**, only its colours: the schema places them at the range's own low and high, which is Excel's default and what an author writing the spec by hand would get. - **A conditional rule with nothing the spec can apply is not kept.** A highlighting rule whose style holds only what the schema has no words for — a theme-indexed colour, say — would match cells and change nothing, which is a spec the loader refuses (§10). It is dropped and reported instead; so is a style found on a scale, a bar, or an icon set, which draw their own appearance and give a style no place. - **A filter's saved criteria come back** as `values:` or a comparison (§10). A top-ten, dynamic, colour or icon filter, or two comparisons other than a `between`/`not_between` pair, is reported instead. A date criterion comes back as its serial number, as a date in a cell does. The rows the filter hid come back under `rows:`, as any hidden row does. - **Print setup comes back as far as §5 reaches.** Paper size, a first page number, black-and-white printing, page order, centring, and a separate first-page or even-page header are reported rather than kept. A value equal to Excel's default is left out, so a sheet nobody set up gets no `print:`. A manual break comes back as one cell per axis (`C5` becomes `A5` and `C1`), which builds the same breaks. - **A sheet-protection password is not recovered.** The file keeps a hash, not the word. It is reported, so a spec that needs one can have it set again rather than looking protected while opening to anyone. - **A name defined for one sheet only is not recovered.** The spec's definitions are workbook-wide, and widening one would change which cells it resolves for. ### It checks its own work Every extraction compiles the spec it produced and compares what the cells hold against what it read — their values, which is where an inference about compression or naming goes wrong. A mismatch is reported and the exit code says so — the spec is still written, because a starting point with a known gap beats none, but it tells you rather than leaving it to be found in Excel. ## 23. Overrides Some edits have no home in the rules that produced the cell. The value comes from a `${quarter}` parameter and *this one cell* has to differ; it comes from row 12 of a CSV the source system refreshes monthly; it comes from a `formulas:` range covering `E2:E500` and row 37 does not follow the rule. Every answer available without this section damages something: inline the parameter, split the range into three, stop reading the column from the file. Each turns one deliberate exception into a permanent change of structure — which is the compression this format exists for, given up for one cell. `overrides:` is that exception, said out loud. ```yaml overrides: - at: Sales!A1 value: "2026年3月期 実績（確定）" reason: "監査対応で当期のみ手修正" - at: Sales!E37 formula: "=D37" reason: "この行だけ按分しない" ``` | Key | Notes | |---|---| | `at` | **Required.** One **sheet-qualified** cell — `Sales!E37`, or `'Q3 data'!A1` where Excel would quote the name. Never a range. Or a cell of a named layout, given by meaning (§25). | | `reason` | Free text, for whoever reads the spec in six months. Nothing in the compiler reads it. | | `value` `formula` `rich` `type` `format` `style` | Exactly as in a `cells:` entry (§3). | **They are applied last, by construction.** Sheet keys apply in the order written (§2), so a `cells:` entry placed after a `data:` block already wins — but only for as long as nobody reorders the keys and no tool sorts them. An override does not depend on where it sits in the file, or on where the thing it overrides sits. **The facets are independent.** An override that gives a `value` leaves the cell's styling alone; one that gives only a `style` or a `format` leaves its value alone. A cell's value and its formatting come from different places in a spec, and overriding one should not quietly discard the other. **An override must have something to override.** If no `cells:`, `data:`, or `formulas:` entry writes that cell, the spec is refused and says so: an exception to nothing is a `cells:` entry with a misleading name. `overrides:` is not a second way to write cells. **One cell, one override.** A second entry for the same cell is refused rather than resolved by order — the thing this section exists to avoid. ### Inside a filled range This is the case that has no other answer. An override may land on any cell of a `formulas:` range **except its top-left**, and it takes that one cell out of the range: ```yaml sheets: - name: Sales formulas: - at: E2:E500 formula: "C2*D2" overrides: - at: Sales!E37 formula: "=D37" reason: "この行だけ按分しない" ``` The range stays one range and one stored formula; row 37 holds its own. Written as a split (§3) the same exception costs three ranges and re-anchors two of them, and nothing in the file says why. The top-left is refused because that is where the shared formula is *stored* (ECMA-376 §18.3.1.40) — overriding it would take the formula away from every other row of the range. Start the range below the exception instead. ### Why they are worth writing as overrides A `cells:` entry placed after a `data:` block does the same job to the bytes. What it cannot do is say that it is an exception: - **They can be counted.** "This spec has 23 hand-patched cells" is the most useful single thing anyone can tell you about a spec's health, and after the fact nobody can work it out. - **They carry a reason.** `監査対応で当期のみ手修正` is the part a reader six months later actually needs, and a YAML comment is not something a tool can summarize. - **They can be folded back.** Five overrides sharing a shape are a rule waiting to be written; finding them means knowing which five they are. If the list is growing, that is the format telling you something: twenty overrides is a spec whose rules no longer match its workbook. ## 24. Editor support [`yxl.schema.json`](./yxl.schema.json) is a JSON Schema for this page, **generated from it**. Point an editor at it and a spec gets completion on every key, the sentence this reference gives it on hover, and a red underline beneath a key that does not exist — before the build says so. One spec at a time, with a modeline as the first line of the file: ```yaml # yaml-language-server: $schema=https://raw.githubusercontent.com/t-ujiie-g/yxl/main/docs/yxl.schema.json sheets: - name: Sales cells: A1: Region ``` Or once for a whole project, in `.vscode/settings.json` — the naming convention is the reason `*.yxl.yaml` is worth keeping to, since an `$include`d fragment must not match: ```json { "yaml.schemas": { "https://raw.githubusercontent.com/t-ujiie-g/yxl/main/docs/yxl.schema.json": "*.yxl.yaml" } } ``` VS Code needs the YAML extension for either route (`code --install-extension redhat.vscode-yaml`); nothing else is configured. The modeline is `yaml-language-server`'s own, so it works the same in Neovim, Helix, and anything else that speaks it. A **local** schema works too — an absolute path, or one relative to the file for a modeline and to the project root for `yaml.schemas` — which is what to do while writing a spec against an unreleased `yxl`. **The schema checks shapes; the compiler checks meaning.** It knows every key, every enumeration — the icon sets, the chart types, the table styles, the geometries — and what kind of thing each key takes, which is what catches the mistakes an editor should catch: a misspelt key, a value from the wrong list, a number where text belongs. It deliberately does not decide the rest: whether a `$ref` names something declared, whether two ranges overlap, whether a `data:` entry gave exactly one source, whether the sheet a link names exists. Those need more than one key to answer, and each is worth a sentence rather than a squiggle, which is what `yxl build` gives them (§17). **A spec the editor is happy with can still be refused**, and the refusal will say more. Two things it does not describe. **An included fragment is not a document**: a sheet in a file of its own, or a `defs.styles` map, is a *part* of one, so the schema is written for `*.yxl.yaml` and the pieces beside it are left alone. And `${parameter}` placeholders are accepted wherever a value goes, since a placeholder is text until it is substituted (§7) — so a parameter is never flagged for standing where a number belongs. ## 25. Layouts A layout names the columns of a region, so nothing inside it has to address a column by letter. It anchors at one cell, and its columns follow from there in the order they are written. Each column carries its own formatting, formula, and conditional rules. Inserting a column is then a one-entry diff, where the same sheet written with `columns:`, `formulas:` and `conditional:` would rewrite every letter to the right of it. ```yaml layouts: - at: A2 # the header's top-left; the only letter here csv: data/sales.csv # the rows, matched to columns by name header_style: header columns: - { name: item, header: 部門, width: 16 } - { name: cy, header: 当年, format: "#,##0" } - { name: py, header: 前年, format: "#,##0" } - name: ratio header: 前年比 format: "0.0%" formula: 'IFERROR({{cy}}/{{py}}-1,"")' conditional: - { cell: { less_than: 0 }, style: weak } ``` | Key | Notes | |---|---| | `at` | **Required.** The top-left cell. The header rows start here when any column has a `header`; otherwise the body starts here. | | `rows` | How many body rows, at least 1. With a source it defaults to the rows read, and may only reserve more. **Required** without one. | | `values` / `csv` / `json` | Where the body's rows come from, as in §9. **At most one.** They fill only the columns without a `formula`. | | `footer` | Rows placed straight after the body: totals, and totals per group. See below. | | `name` | Makes each column's body a workbook defined name, `name.column`, and lets an override find a cell by meaning. See below. | | `header_style` | A style (bareword or inline) for every header cell. Needs at least one `header`. | | `columns` | **Required.** The columns, left to right from `at`. At least one. | Each column: | Key | Notes | |---|---| | `name` | **Required.** Letters, digits and `_`, or any non-ASCII character, not starting with a digit. Unique on the sheet. | | `field` | The CSV or JSON field this column reads, when it differs from `name`. Only for a `csv`, or `json` of objects. | | `header` | A cell (§3), or a list of them, one per header row, top first. `null` in the list is a blank. | | `formula` | Filled down the body as a formula range (§3), written for the body's first row. | | `conditional` | Conditional formats (§10) over the body, written without `at`. | | `width`, `style`, `format`, `hidden`, `group` | The column's band, exactly as in §4. | **A header may have levels.** Written as a list, a column's header takes one row per entry, and the layout's header is as deep as its deepest column: ```yaml columns: - { name: item, header: 部門 } # merged down both rows - { name: cy, header: [売上, 当年] } # 売上 merged across the three - { name: py, header: [売上, 前年] } - { name: ratio, header: [売上, 前年比] } ``` - Neighbouring columns merge across a row when they hold the same cell there and the same cells in every row above it. `当年` under `売上` stays apart from `当年` under `粗利`. - A column with fewer levels than the header has rows stretches its last one down to the body. A stretched cell never also merges across. - `null` is a blank cell and merges with nothing. So `[null, 部門]` puts `部門` in the bottom row, and `[売上, 当年]` beside `[null, 前年]` leaves the cell above `前年` blank rather than merging `売上` over it. - Every header cell wears `header_style`, the blanks inside a merge and the `null`s included, so a fill or a border runs unbroken. A header written in the expanded form (`{ value: 部門, style: key }`) lays its own style over it. **`{{name}}` is that column's cell in the same row.** It is read in a column's `formula` and in a conditional rule's `formula`, and nowhere else: a `{{` anywhere else in a spec is ordinary text. Inside a formula it is skipped within a `"…"` string literal. A name that is not a column of the layout, or a `{{` never closed, is an error. **A layout compiles to exactly what the hand-written keys would**: a band per column that sets one, a header cell, a formula range, and a conditional format per rule. So everything those keys refuse, a layout refuses too. In particular, a `cells:` entry inside a formula column is refused as in §3, and one row that differs is an override (§23). A sheet may hold several layouts. Two layouts may not both give one sheet column a band. **Rows come from data without addressing a column either.** A layout's source fills its *input* columns, the ones without a `formula`. A formula column is skipped rather than reserved with `null`, so adding a derived column changes no data row. - `values:` and a JSON array of arrays fill the input columns in order. A row may stop short. A row longer than the input columns is an error. - A `csv:` **must start with a header row**. Each input column takes the field named like its `field`, or like its `name` when it has none. The CSV's field order does not matter, and fields no column asks for are ignored. A field a column asks for that is not in the header is an error. - A `json:` array of objects is matched the same way, by key. ```yaml layouts: - at: A1 csv: data/sales.csv # 部門名,売上金額,前年売上 … columns: - { name: item, header: 部門, field: 部門名 } - { name: cy, header: 当年, field: 売上金額 } - { name: py, header: 前年, field: 前年売上 } - { name: ratio, header: 前年比, formula: "{{cy}}/{{py}}-1" } ``` A monthly file of a different length needs no edit: the body, and every formula range down it, follows the rows read. Set `rows:` to reserve a fixed body instead, with the formula filled beyond the data. ### Naming a layout: reaching it from outside Inside a layout, `{{name}}` addresses a column. Outside it — another sheet's summary, a formula range, a cell anywhere — the layout's `name:` does: ```yaml sheets: - name: 店舗別 layouts: - at: A2 name: 店舗 csv: data/stores.csv columns: [...] # branch, store, cy, py, … - name: サマリ cells: B1: { formula: "SUM(店舗.cy)" } B2: { formula: 'SUMIFS(店舗.cy,店舗.branch,"東京")' } overrides: - at: { layout: 店舗, column: ratio, where: { store: 渋谷 } } value: 改装 reason: 改装で前年比較の対象外 ``` - **Each column's body becomes a workbook defined name**, `layout.column` (`店舗.cy`, and `売上.sales.cy` for a block's column). It refers to the body's absolute range, so any formula can use it, on any sheet, in any order. Excel keeps the name in step when rows are inserted, and it is what Name Manager lists. The footer is not part of it. **The layout's own name** refers to the whole table: its bottom header row, when it has one, and its body, across every column. - **The name** follows Excel's rules for a defined name: letters, digits, `_` and `.`, starting with a letter or `_`, and not a cell reference in either case. It is unique among layouts. Each `layout.column` it produces must not repeat another defined name or a table name (§6). - **An override may find its cell by meaning.** Its `at:` is then `{ layout, column, where }`, with `where` naming input columns and the values they hold. It must match exactly one row of the layout's data, so a key that is not unique, or a row that is gone, is an error, never a guess. `row: n` instead of `where` takes the body's *n*th row. The layout must have a `name`. **Where a spec reads a range, it may name a layout instead.** The layout may be on any sheet, declared before or after the reader, because every layout is placed before any sheet loads: - a chart series' `values` and `categories`: `values: 店舗.cy` - a validation's `list: { from: 店舗.branch }` - a sparkline's `data` - a pivot table's `source: 店舗`, the table with its header row On the layout's own sheet, a range that *covers* cells may name it too: a table's `at: 店舗`, which declares the layout an Excel table, and a validation's or a conditional format's `at: 店舗.cy`. **Anything anchored at a cell may follow a layout instead.** Write `at: { below: 店舗 }` for a chart, an image, a shape, a control, a slicer, a `data:` table, or another layout: | Key | Notes | |---|---| | `below` | A named layout on the same sheet, declared earlier in the spec. The anchor takes its first column. | | `gap` | Blank rows between the layout's last row (its footer included) and the anchor. Default 1. | A layout whose length follows its data then pushes everything below it down, with no row number to update. ### Blocks: a group of columns written once A report often repeats one *meaning* across items: `当年 / 前年 / 前年比` for sales, then again for gross profit. `defs.blocks` declares the group once; a layout places it as many times as it needs. ```yaml defs: blocks: yoy: columns: - { name: cy, header: 当年, format: "#,##0" } - { name: py, header: 前年, format: "#,##0" } - name: ratio header: 前年比 formula: 'IFERROR({{cy}}/{{py}}-1,"")' conditional: [{ cell: { less_than: 0 }, style: weak }] sheets: - name: 実績 layouts: - at: A2 columns: - { name: item, header: 部門 } - { block: yoy, as: sales, header: 売上 } - { block: yoy, as: gross, header: 粗利 } ``` A block's `columns` take everything a layout column does (the table above), and nothing places a block inside a block. Where a layout column is `{ block: … }`, these keys apply: | Key | Notes | |---|---| | `block` | **Required.** A name `defs.blocks` declares. | | `as` | The instance's name, which qualifies its columns: `sales.cy`. Defaults to the block's name; unique within the layout. | | `header` | A cell, or a list of them, laid above each column's own header levels, so it merges across the instance (see header levels above). | | `fields` | Column name → CSV or JSON field, for this instance's input columns. Without it a column reads the field named like its qualified name. | - The instance's columns take its place, left to right, and are named `as.name`. That is the name a footer row, `field` defaults and `{{…}}` outside the instance use. - Inside the block, `{{cy}}` is the instance's own `cy`. A bare name the block does not declare falls through to the layout's other columns, so a block may use a shared `{{rate}}`. `{{sales.cy}}` reaches any instance by name. - The block and its instances compile to exactly the columns they stand for, written out. The formula, the format and the rule exist once in the spec, so "one instance references the wrong column" cannot be written. ### Footer rows and grouped totals A body that follows its data has no fixed last row, so a totals row beneath it cannot be written with `cells:`. `footer:` places rows straight after the body, and a footer row's cells are named by column, like everything else here. ```yaml footer: - by: branch # once per 支店 order: asc rows: - by: channel # once per 直営/FC within it order: [直営, FC] rows: - row: { store: "{{branch}} {{channel}} 計", cy: { total: sum } } - row: { store: "{{branch}} 計", cy: { total: sum } } style: subtotal - row: store: 総合計 cy: { total: sum } ratio: { formula: "{{cy}}/{{py}}-1" } style: total ``` Each footer entry is a row or a group: | Key | Notes | |---|---| | `row` | A row: column name → cell. A cell is written as in §3, or as `{ total: … }`. Columns it leaves out stay blank. | | `style` | With `row`: the row's style, worn by every cell of the layout's width. A cell's own style is laid over it. | | `by` | A group: the column whose values it repeats over. The data must fill that column. | | `order` | With `by`: `data` (as the rows list them, the default), `asc`, `desc`, or a list of the values. | | `rows` | With `by`: **required**. The entries repeated for each value, which may be groups themselves. | A footer cell takes, beyond §3's keys: | Key | Notes | |---|---| | `total` | `sum`, `count`, `average`, `min`, or `max` over the column's body. | | `merge_to` | A column to the right: the cell merges across to it. The covered cells wear its style, and the row may write nothing inside the span. | A label goes in whichever column suits the report. Write it alone in the first column and the columns after it stay blank; add `merge_to` and they merge under it: ```yaml - row: { branch: "{{branch}}", channel: { value: "{{channel}} 計", merge_to: store } } - row: { branch: { value: 総合計, merge_to: store }, cy: { total: sum } } ``` - **`{ total: sum }`** aggregates the column's body. The choices are `sum`, `count` (non-blank cells), `average`, `min` and `max`. Inside groups it becomes `SUMIFS`, `COUNTIFS`, `AVERAGEIFS`, `MINIFS` or `MAXIFS`, with one criterion per enclosing group. So `東京 直営 計` sums the rows whose branch is 東京 *and* whose channel is 直営. The criterion is the value itself, with Excel's wildcards escaped, so a label may sit in any column. `MINIFS` and `MAXIFS` need Excel 2019 or later. - **A group's values** are those the data holds within the enclosing groups, taken in `order`. `asc` puts numbers before text, and text in code-point order, not in reading order. A listed order gives a row to *every* value listed, present or not. A value the data holds that the list leaves out is an error: its rows would silently fall outside every subtotal. - **`{{name}}` is still this row's `name`.** In a formula it is the cell, so `{{cy}}/{{py}}-1` in a totals row is the ratio of the totals. In a footer row's text it is the value, which only a group's column has while the spec compiles. So `"{{branch}} 計"` is a label, and `{{cy}}` in text is an error. - The group rows are fixed when the spec is built. A value typed into the workbook later gets no subtotal row until the next build.