xlsx/round-trip-formulas-preserved

Parameter axes

• case (6 values): shared-formula, array-formula-row, array-formula-matrix, sparkline-line, sparkline-column, sparkline-winloss

Spec notes

Generator source

scripts/gen_round_trip_formulas_xlsx.py — runs with --arg_template --source fixtures/{case.source}.xlsx --out fixtures/xlsx/round-trip-formulas-preserved--{case.id}.xlsx

#!/usr/bin/env python3
"""Generate an xlsx formulas-preserved round-trip fixture.

Identical body to :mod:`gen_round_trip_identity_xlsx` — the
distinguishing behaviour lives in the feature manifest's XPath
probes, not in the script.
"""

from __future__ import annotations

import argparse
import zipfile
from pathlib import Path

import xlsx  # type: ignore[import-not-found]


def _rezip_with_epoch_timestamps(path: Path) -> None:
    tmp = path.with_suffix(path.suffix + ".tmp")
    with zipfile.ZipFile(path, "r") as src, zipfile.ZipFile(
        tmp, "w", zipfile.ZIP_DEFLATED
    ) as dst:
        for info in src.infolist():
            data = src.read(info.filename)
            info.date_time = (1980, 1, 1, 0, 0, 0)
            dst.writestr(info, data)
    tmp.replace(path)


def main() -> None:
    ap = argparse.ArgumentParser()
    ap.add_argument("--source", required=True)
    ap.add_argument("--out", required=True)
    args = ap.parse_args()

    out = Path(args.out)
    out.parent.mkdir(parents=True, exist_ok=True)
    xlsx.load_workbook(args.source).save(str(out))
    _rezip_with_epoch_timestamps(out)


if __name__ == "__main__":
    main()

Manifest (parent, unexpanded)

{
  "$schema": "../manifest.schema.json",
  "id": "xlsx/round-trip-formulas-preserved",
  "kind": "parameterised",
  "title": "Round-trip preserves shared/array formulas and sparklines",
  "format": "xlsx",
  "category": "round-trip",
  "summary": "Formulas and chart extensions are the highest-risk areas for xlsx round-trip drift. A shared formula group (f@t='shared') can be expanded into N independent f elements on save; an array formula (f@t='array') can be rewritten as a plain scalar; a sparklineGroup lives inside worksheet extLst and is easy to drop. This family asserts each class survives a python-xlsx load/save cycle. The shared-formula case is expected to surface a KNOWN round-trip regression where python-xlsx loses the f@t='shared' group structure on save.",
  "spec": {
    "source": "ecma-376-5-part-1",
    "clause": "18.3.1.40",
    "element": "f",
    "xsd_reference": "spec/ecma-376-5/part-1/xsd/sml.xsd",
    "notes": "Each parameter case pairs a committed fixture (from W2-F shared-formula and W2-C array-formula / sparkline families) with an XPath that must still hold after load+save. Shared-formula assertion targets the canonical anchor cell carrying @t='shared' @ref='B1:B5' — if it fails, python-xlsx has expanded the group into scalar f cells (a silent but material correctness regression: the saved file opens in Excel as scalar formulas, not a shared group, which Excel will rewrite back but third-party consumers may reject)."
  },
  "fixtures": {
    "machine": "xlsx/round-trip-formulas-preserved"
  },
  "round_trip": {
    "library": "python-xlsx",
    "source_fixture": "{case.source}"
  },
  "parameters": {
    "case": [
      {
        "id": "shared-formula",
        "source": "xlsx/shared-formula",
        "probe_part": "xl/worksheets/sheet1.xml",
        "probe_xpath": "//x:c[@r='B1']/x:f[@t='shared' and @ref='B1:B5' and @si='0']"
      },
      {
        "id": "array-formula-row",
        "source": "xlsx/array-formula--row-array-formula",
        "probe_part": "xl/worksheets/sheet1.xml",
        "probe_xpath": "//x:f[@t='array' and @ref='A1:A5']"
      },
      {
        "id": "array-formula-matrix",
        "source": "xlsx/array-formula--matrix-array-formula",
        "probe_part": "xl/worksheets/sheet1.xml",
        "probe_xpath": "//x:f[@t='array' and @ref='C1:C5']"
      },
      {
        "id": "sparkline-line",
        "source": "xlsx/sparkline--line",
        "probe_part": "xl/worksheets/sheet1.xml",
        "probe_xpath": "//*[local-name()='sparkline']"
      },
      {
        "id": "sparkline-column",
        "source": "xlsx/sparkline--column",
        "probe_part": "xl/worksheets/sheet1.xml",
        "probe_xpath": "//*[local-name()='sparkline']"
      },
      {
        "id": "sparkline-winloss",
        "source": "xlsx/sparkline--winloss",
        "probe_part": "xl/worksheets/sheet1.xml",
        "probe_xpath": "//*[local-name()='sparkline']"
      }
    ]
  },
  "generator": {
    "python": "scripts/gen_round_trip_formulas_xlsx.py",
    "arg_template": "--source fixtures/{case.source}.xlsx --out fixtures/xlsx/round-trip-formulas-preserved--{case.id}.xlsx"
  },
  "assertions_template": [
    {
      "id": "workbook-parses",
      "part": "xl/workbook.xml",
      "namespaces": {
        "x": "http://schemas.openxmlformats.org/spreadsheetml/2006/main"
      },
      "xpath": "//x:workbook",
      "must": "exist",
      "description": "Baseline: round-tripped workbook.xml parses."
    },
    {
      "id": "formula-or-sparkline-preserved",
      "part": "{case.probe_part}",
      "namespaces": {
        "x": "http://schemas.openxmlformats.org/spreadsheetml/2006/main"
      },
      "xpath": "{case.probe_xpath}",
      "must": "exist",
      "description": "The feature-specific marker (shared-formula anchor, array-formula f@t='array', or sparkline in extLst) must survive a python-xlsx load/save round-trip."
    }
  ]
}