xlsx/external-link

Parameter axes

• case (2 values): external-workbook-ref, external-cached-value

Spec notes

Generator source

scripts/gen_external_link.py — runs with --arg_template --case {case.id} --out fixtures/xlsx/external-link--{case.id}.xlsx

#!/usr/bin/env python3
"""Generate ``fixtures/xlsx/external-link--<case>.xlsx`` fixtures.

Parameterised generator for the ``xlsx/external-link`` manifest.  Two
cases exercise the two common shapes of an externalLink payload:

- ``external-workbook-ref`` — reference-only.  The <externalBook> lists
  the target sheet name but carries no <sheetDataSet>; Excel resolves
  the reference lazily on open.
- ``external-cached-value`` — the same reference plus a
  <sheetDataSet>/<sheetData>/<row>/<cell> cache so the workbook stays
  usable when the external target is offline.

The referenced external file does not need to exist on disk; Excel (and
LibreOffice) resolves lazily on open.  The primary sheet cell A1
carries a formula using the external reference so both the formula
emission path and the externalLink part are exercised end to end.
"""

from __future__ import annotations

import argparse
import datetime as _dt
from pathlib import Path

from xlsx import Workbook
from xlsx.packaging.relationship import Relationship
from xlsx.workbook.external_link.external import (
    ExternalBook,
    ExternalCell,
    ExternalLink,
    ExternalRow,
    ExternalSheetData,
    ExternalSheetDataSet,
    ExternalSheetNames,
)

_REPRODUCIBLE_DT = _dt.datetime(2000, 1, 1, 0, 0, 0)
_REPO_ROOT = Path(__file__).resolve().parent.parent


def main() -> int:
    parser = argparse.ArgumentParser()
    parser.add_argument("--case", required=True,
                        choices=("external-workbook-ref",
                                 "external-cached-value"))
    parser.add_argument("--out", required=True)
    args = parser.parse_args()

    out_path = Path(args.out)
    if not out_path.is_absolute():
        out_path = _REPO_ROOT / out_path

    wb = Workbook()
    ws = wb.active
    ws.title = "Sheet1"
    # A formula referencing the external workbook; Excel carries the
    # reference as a structured cell-formula token, but python-xlsx
    # preserves the literal string form on save.
    ws["A1"] = "=[external.xlsx]Sheet1!A1"

    sheet_data_set = None
    if args.case == "external-cached-value":
        # Cached value: the external Sheet1!A1 holds 42 per the cache.
        sheet_data_set = ExternalSheetDataSet(sheetData=[
            ExternalSheetData(
                sheetId=0,
                row=[ExternalRow(r=1,
                                 cell=[ExternalCell(r="A1", t="n", v="42")])],
            ),
        ])

    link = ExternalLink()
    link.externalBook = ExternalBook(
        sheetNames=ExternalSheetNames(sheetName=["Sheet1"]),
        definedNames=(),
        sheetDataSet=sheet_data_set,
    )
    link.file_link = Relationship(Target="external.xlsx",
                                  TargetMode="External",
                                  type="externalLink")
    wb._external_links.append(link)

    out_path.parent.mkdir(parents=True, exist_ok=True)
    wb.save(out_path, zip_date_time=_REPRODUCIBLE_DT)
    print(out_path)
    return 0


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

Manifest (parent, unexpanded)

{
  "$schema": "../manifest.schema.json",
  "id": "xlsx/external-link",
  "kind": "parameterised",
  "title": "External-workbook link — reference only vs cached values",
  "format": "xlsx",
  "category": "workbook-structure",
  "summary": "The package carries an xl/externalLinks/externalLink1.xml part containing an <externalBook> pointing at another workbook. Two cases exercise the bare reference form (sheetNames only) and the cached-value form (sheetDataSet with a cached <cell> value alongside the reference).",
  "spec": {
    "source": "ecma-376-5-part-1",
    "clause": "18.14",
    "element": "externalLink",
    "rnc_reference": "spec/ecma-376-5/part-1/rnc/SpreadsheetML.rnc",
    "xsd_reference": "spec/ecma-376-5/part-1/xsd/sml.xsd",
    "notes": "An external-workbook reference is carried in a separate part at xl/externalLinks/externalLink{n}.xml. The root <externalLink> wraps an <externalBook> whose r:id points at a Relationship with TargetMode='External' in the matching .rels file. The referenced external file does not need to exist on disk at save time — Excel resolves it lazily on open. When cached values are present Excel emits a <sheetDataSet>/<sheetData>/<row>/<cell> tree mirroring the referenced sheet's structure so the workbook stays usable while the external target is offline. python-xlsx exposes ExternalLink, ExternalBook, ExternalSheetNames, ExternalSheetDataSet, ExternalSheetData, ExternalRow, ExternalCell; the generator wires them together by hand since there's no higher-level wrapper. SpreadsheetML parts use a default namespace with no prefix; XPath binds 'x'."
  },
  "fixtures": {
    "machine": "xlsx/external-link"
  },
  "generator": {
    "python": "scripts/gen_external_link.py",
    "arg_template": "--case {case.id} --out fixtures/xlsx/external-link--{case.id}.xlsx"
  },
  "parameters": {
    "case": [
      {
        "id": "external-workbook-ref",
        "cached": "false"
      },
      {
        "id": "external-cached-value",
        "cached": "true"
      }
    ]
  },
  "assertions_template": [
    {
      "id": "external-book-present-{case.id}",
      "part": "xl/externalLinks/externalLink1.xml",
      "namespaces": {
        "x": "http://schemas.openxmlformats.org/spreadsheetml/2006/main"
      },
      "xpath": "//x:externalLink/x:externalBook",
      "must": "exist",
      "description": "The externalLink part must contain an <externalBook> element wrapping the reference."
    },
    {
      "id": "external-sheet-name-{case.id}",
      "part": "xl/externalLinks/externalLink1.xml",
      "namespaces": {
        "x": "http://schemas.openxmlformats.org/spreadsheetml/2006/main"
      },
      "xpath": "//x:externalBook/x:sheetNames/x:sheetName/@val",
      "must": "equal",
      "value": "Sheet1",
      "description": "The externalBook must list the target workbook's sheet names; this case targets 'Sheet1'."
    }
  ]
}