xlsx/vlookup-formula

Metadata

XPath assertions

Render assertions

Generator source

scripts/gen_vlookup_formula.py

#!/usr/bin/env python3
"""Generate ``fixtures/xlsx/vlookup-formula.xlsx``.

A minimal one-sheet workbook with a two-row lookup table in A1:B2
('Alice'->100, 'Bob'->200), a lookup key in D1 ('Bob'), and an
E1 formula '=VLOOKUP(D1, A1:B2, 2, FALSE)' resolving to 200.
Designed to satisfy the assertions in
``features/xlsx/vlookup-formula.json``.
"""

from __future__ import annotations

from pathlib import Path

from xlsx import Workbook

import datetime as _dt
_REPRODUCIBLE_DT = _dt.datetime(2000, 1, 1, 0, 0, 0)

_REPO_ROOT = Path(__file__).resolve().parent.parent
_OUT = _REPO_ROOT / "fixtures" / "xlsx" / "vlookup-formula.xlsx"


def main() -> int:
    wb = Workbook()
    ws = wb.active
    ws["A1"] = "Alice"
    ws["B1"] = 100
    ws["A2"] = "Bob"
    ws["B2"] = 200
    ws["D1"] = "Bob"
    ws["E1"] = "=VLOOKUP(D1, A1:B2, 2, FALSE)"
    _OUT.parent.mkdir(parents=True, exist_ok=True)
    wb.save(_OUT, zip_date_time=_REPRODUCIBLE_DT)
    print(_OUT)
    return 0


if __name__ == "__main__":
    raise SystemExit(main())

Manifest (expanded)

{
  "$schema": "../manifest.schema.json",
  "id": "xlsx/vlookup-formula",
  "title": "VLOOKUP formula",
  "format": "xlsx",
  "category": "formulas",
  "summary": "A workbook with a two-row lookup table in A1:B2 and a VLOOKUP formula in E1 that resolves 'Bob' to 200 emits a <c r='E1'><f>VLOOKUP(...)</f></c> in the sheet part.",
  "spec": {
    "source": "ecma-376-5-part-1",
    "clause": "18.17",
    "element": "f",
    "rnc_reference": "spec/ecma-376-5/part-1/rnc/SpreadsheetML.rnc",
    "xsd_reference": "spec/ecma-376-5/part-1/xsd/sml.xsd",
    "notes": "VLOOKUP is one of the legacy lookup functions specified in ECMA-376 Part 1 clause 18.17.7 (function list); its cell-level encoding follows the generic formula shape from 18.3.1.40 — a <c> whose child <f> holds the A1-style body. Unlike XLOOKUP (a 2019+ dynamic-array function requiring the _xlfn prefix), plain VLOOKUP is in Excel's 1987-era function set and is emitted verbatim with no prefix. The first argument D1 is looked up in the table_array A1:B2 and the value from column 2 is returned when an exact match (FALSE) is found."
  },
  "fixtures": {
    "machine": "xlsx/vlookup-formula",
    "office": "xlsx/vlookup-formula"
  },
  "generator": {
    "python": "scripts/gen_vlookup_formula.py"
  },
  "assertions": [
    {
      "id": "vlookup-formula-present",
      "part": "xl/worksheets/sheet1.xml",
      "namespaces": {
        "x": "http://schemas.openxmlformats.org/spreadsheetml/2006/main"
      },
      "xpath": "//x:c[@r='E1']/x:f[starts-with(normalize-space(), 'VLOOKUP')]",
      "must": "exist",
      "description": "Cell E1 must carry a <f> child whose text, after whitespace normalisation, starts with 'VLOOKUP'. The full body ('VLOOKUP(D1, A1:B2, 2, FALSE)') is deliberately not asserted character-for-character because whitespace and argument formatting vary between writers."
    }
  ],
  "render_assertions": [
    {
      "id": "vlookup-formula-rendered",
      "kind": "css_selector",
      "selector": "section.xlsx td, section.xlsx th",
      "must": "match-text",
      "value": "^(200(?:\\.0+)?|=?VLOOKUP\\(.*\\))$",
      "description": "The rendered sheet must contain a cell showing either the evaluated result (200) or the formula text (=VLOOKUP(...) / VLOOKUP(...)) if the renderer does not evaluate formulas."
    },
    {
      "id": "sheet-count",
      "kind": "css_selector",
      "selector": "section.xlsx",
      "must": "equal-count",
      "count": 1,
      "description": "Exactly one rendered sheet section. Catches renderers that emit zero sheets (hard failure) or duplicate a sheet."
    }
  ]
}