SKILL.md
---
name: xlsx
description: "Use this skill any time a spreadsheet file is the primary input or output. This means any task where the user wants to: open, read, edit, or fix an existing .xlsx, .xlsm, .xltx, .csv, or .tsv file (e.g., adding columns, computing formulas, formatting, charting, cleaning messy data); create a new spreadsheet from scratch or from other data sources; or convert between tabular file formats. Trigger especially when the user references a spreadsheet file by name or path — even casually (like \"the xlsx in my downloads\") — and wants something done to it or produced from it. Also trigger for cleaning or restructuring messy tabular data files (malformed rows, misplaced headers, junk data) into proper spreadsheets. The deliverable must be a spreadsheet file. Do NOT trigger when the primary deliverable is a Word document, HTML report, standalone Python script, database pipeline, or Google Sheets API integration, even if tabular data is involved."
---
# XLSX creation, editing, and analysis
| Task | Approach |
|---|---|
| **Create** or **edit** with formulas/formatting | `openpyxl` — see gotchas below |
| **Bulk data** in or out | `pandas` (`read_excel`, `to_excel`) |
| **Quick look** at a sheet | `markitdown file.xlsx` — `## SheetName` per sheet; reads `.xlsm` too. No cell coordinates, so don't plan edits from it |
| **Read** a model (formulas *and* values) | two `load_workbook` passes — see gotchas |
> `openpyxl`, `pandas`, and `markitdown` are preinstalled — do not run `pip install` first; write the script and import directly. Only if an import fails (or the `markitdown` command is missing): `pip install` the missing package.
> Script paths below are relative to this skill's directory.
## Requirements for every output
- **Professional font** (Arial, Times New Roman) throughout, unless the user says otherwise.
- **Zero formula errors.** Never ship while `recalc.py` reports `errors_found`. If you think an error predates you, prove it: load the *original* with `data_only=True` and look at that cell. An error you introduced looks exactly like one you inherited.
- **Use formulas, never hardcoded results.** Write `sheet['B10'] = '=SUM(B2:B9)'`, not the Python-computed total. The sheet must recalculate when its inputs change.
- **Follow the user's spec literally.** Exact tab names, exact column headers, and the formula they spelled out. A redesign that computes something else fails, however elegant.
- **Document every assumption and hardcoded number** where the reader will see it — a cell comment, or an adjacent cell at a table's end. Cite a real source when one exists (`Source: Company 10-K, FY2024, Page 45, Revenue Note, [SEC EDGAR URL]`); when the number came from the user, say so plainly.
- **A workbook *you create* for someone to fill in** needs a short legend naming which cells to edit, and one example row of realistic values showing the expected format. Never add such a row to a file you were asked to edit.
- **Editing an existing file: match its conventions exactly.** They override every guideline here. Find its designated input cells first — a distinct font color, fill, or shading marks them — write only there, and leave every existing formula untouched.
## Recalculate (mandatory whenever the file contains formulas)
openpyxl writes formulas as strings with **no cached values**. Until you recalculate, every
formula cell reads back as `None` to anything reading cached values — `pandas`,
`load_workbook(data_only=True)`, and most previewers.
```bash
python scripts/recalc.py output.xlsx [timeout_seconds] # default 30
```
LibreOffice computes every formula, the file is **rewritten in place**, and you get JSON:
`status` (`success` | `errors_found`), `total_formulas`, `total_errors`, and an
`error_summary` naming up to 100 cells per error type (`locations_truncated` says how many it
withheld — trust `total_errors`, not the length of the list). Fix what it names and run it
again. **JSON with an `error` key instead of a `status` means nothing was recalculated**, and
only that case exits non-zero — `errors_found` exits 0, so never treat a clean exit as a clean
workbook.
**A green recalc proves your formulas *evaluate*, not that they are *right*.** An off-by-one
range or a reference to the wrong row yields a clean, error-free file with wrong numbers.
Write 2–3 formulas first and check they pull the values you expect, before building out a grid.
**A workbook that links to another file loses those links** if you re-save it with openpyxl and
then recalculate. Such a formula reads `='[1]Returns Analysis'!$B$2` — the `[1]` is an index
into the workbook's external-reference list, naming a *separate file on disk*, not a sheet.
That file is rarely present here, so the cell's cached value is the only thing holding its
data. openpyxl strips that value on save; LibreOffice then has to resolve the reference for
real, fails, writes `#NAME?`, and deletes every link. `recalc.py` refuses to run in that state
— copy those cells' values out of the original before you save over them (`--force` overrides,
and accepts the loss).
## Choosing formulas that survive verification
LibreOffice implements fewer functions than Excel, and one it cannot evaluate becomes a
literal `#NAME?` baked into the file you deliver.
- **Prefer Excel-2007-era functions** — `SUMIFS`, `INDEX`, `MATCH`, `IFERROR`, `SUMPRODUCT` — which need no prefix.
- **Six post-2007 functions work, but only with an `_xlfn.` prefix**, because openpyxl writes your formula into the XML verbatim and Excel stores post-2007 names prefixed (its UI hides the prefix): `_xlfn.TEXTJOIN`, `_xlfn.CONCAT`, `_xlfn.IFS`, `_xlfn.SWITCH`, `_xlfn.MAXIFS`, `_xlfn.MINIFS`. Written bare, each yields `#NAME?`.
- **Never use `XLOOKUP`, `XMATCH`, `SORT`, `FILTER`, `UNIQUE`, or `SEQUENCE`.** The runtime's LibreOffice cannot evaluate them under *any* prefix. Newer builds do evaluate them, but they are spilling array functions and an openpyxl-written file has no spill metadata, so only the top-left cell of the range gets a value — and `recalc.py` reports `total_errors: 0` on the truncated result. Use `INDEX`/`MATCH` for lookups, and sort, filter, and de-duplicate in Python before writing the cells.
- A formula LibreOffice could not parse is written back **lowercased** — a quick tell beside a `#NAME?`.
## openpyxl gotchas
- **Reading a model takes two loads.** `data_only=True` yields cached values with the formulas gone; the default yields formula strings with no values. One pass cannot give you both.
- **`data_only=True` is destructive if you save.** That workbook has no formulas left, so saving replaces every one with a literal — permanently.
- **`data_only=True` on a file openpyxl just wrote returns `None` everywhere** — run `recalc.py` first. (A formula whose result is `""` also reads back as `None`.)
- **Merged cells: write the top-left anchor only.** Every other cell in the range is a `MergedCell` whose `.value` is read-only.
- **`.xlsm` loses its macros unless you pass `keep_vba=True`** to `load_workbook`.
- **A sheet name containing a space must be quoted** in a cross-sheet reference: `='Assumptions Inputs'!$B$5`. Unquoted, it evaluates to `#VALUE!`.
## Financial models
Unless the user says otherwise, or the existing file already does something else.
**Color:** blue text (`0,0,255`) for hardcoded inputs and scenario levers · black for formulas ·
green (`0,128,0`) for links to another sheet · red (`255,0,0`) for links to another file ·
yellow fill (`255,255,0`) for key assumptions and cells the user should fill in.
**Numbers:** currency `$#,##0`, with the unit named in the header (`Revenue ($mm)`) · zeros
render as `-`, including in percentages (`$#,##0;($#,##0);-`) · negatives in parentheses ·
percentages `0.0%`, **stored as fractions** (`0.15` renders `15.0%`; storing `15` renders
`1500.0%`) · valuation multiples `0.0x` · years as text (`"2024"`, never `2,024`).
**Structure:** every assumption in its own labeled cell, referenced by the formulas that use it
(`=B5*(1+$B$6)`, never `=B5*1.05`) · formulas consistent across every projection period, since a
lone edited cell mid-row is the commonest silent error · guard denominators that can be zero.
## Dependencies
`openpyxl`, `pandas`, `markitdown` (pip, preinstalled — install only if an import fails or the command is missing) · LibreOffice (`soffice`, auto-configured for sandboxed environments via `scripts/office/soffice.py`)
scripts/office/helpers/__init__.py
import os
import posixpath
import re
import stat
import tempfile
import urllib.parse
import zipfile
from pathlib import Path
OOXML_FAMILY = {
".docx": "docx",
".dotx": "docx",
".pptx": "pptx",
".potx": "pptx",
".xlsx": "xlsx",
".xltx": "xlsx",
}
_SCHEME_RE = re.compile(r"^[A-Za-z][A-Za-z0-9+.\-]*:")
SLIDE_REL_TYPE = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/slide"
def opc_target(target: str, source_part: str, target_mode: str = "") -> str | None:
if not target:
return None
if target_mode.lower() == "external":
return None
target = urllib.parse.unquote(target)
if _SCHEME_RE.match(target):
return None
if "\\" in target:
raise ValueError(f"relationship target is not a POSIX part name: {target!r}")
if target.startswith("/"):
joined = target.lstrip("/")
else:
joined = posixpath.join(posixpath.dirname(source_part), target)
parts: list[str] = []
for segment in posixpath.normpath(joined).split("/"):
if segment in ("", "."):
continue
if segment == "..":
if not parts:
raise ValueError(f"relationship target escapes the package: {target!r}")
parts.pop()
else:
parts.append(segment)
if not parts:
raise ValueError(f"relationship target resolves to nothing: {target!r}")
return "/".join(parts)
def rels_source_part(rels_file: Path, unpacked_dir: Path) -> str:
owner_dir = rels_file.parent.parent.relative_to(unpacked_dir)
part = posixpath.join(owner_dir.as_posix(), rels_file.name[: -len(".rels")])
return part.removeprefix("./")
def part_text(data: bytes) -> str:
return data.decode("utf-8", "surrogateescape")
XML_SPACE = " \t\r\n"
def rendered_text(text: str, preserve: bool) -> str:
return text if preserve else text.strip(XML_SPACE)
def safe_extract(zf: zipfile.ZipFile, dest: Path) -> None:
dest = dest.resolve()
for m in zf.infolist():
if stat.S_ISLNK(m.external_attr >> 16):
raise ValueError(f"symlink archive entry not allowed: {m.filename!r}")
target = (dest / m.filename).resolve()
if not target.is_relative_to(dest):
raise ValueError(f"unsafe archive entry: {m.filename!r}")
zf.extract(m, dest)
def rezip(src_dir: Path, out_path: Path) -> None:
files = sorted(p for p in src_dir.rglob("*") if p.is_file())
ct = src_dir / "[Content_Types].xml"
fd, tmp_name = tempfile.mkstemp(
prefix=out_path.name + ".", suffix=".tmp", dir=out_path.parent
)
tmp_out = Path(tmp_name)
try:
with os.fdopen(fd, "wb") as fh:
with zipfile.ZipFile(fh, "w", zipfile.ZIP_DEFLATED) as zf:
if ct.exists():
zf.write(ct, ct.relative_to(src_dir), compress_type=zipfile.ZIP_STORED)
for f in files:
if f == ct:
continue
zf.write(f, f.relative_to(src_dir))
if out_path.exists():
mode = out_path.stat().st_mode & 0o777
else:
umask = os.umask(0)
os.umask(umask)
mode = 0o666 & ~umask
os.chmod(tmp_out, mode)
os.replace(tmp_out, out_path)
finally:
if tmp_out.exists():
tmp_out.unlink()
scripts/office/helpers/pptx_chart.py
"""Find chart XML that PowerPoint refuses but the schema accepts.
Detection only: for either fault more than one repair is valid, and only the
author knows which was meant.
"""
from __future__ import annotations
import re
from typing import Mapping
from . import part_text
_CHART_PART_RE = re.compile(r"ppt/charts/chart\d+\.xml")
_GROUPING_RE = re.compile(r"""<c:grouping\b[^>]*?\bval=["'](\w+)["']""")
_DLBL_POS_RE = re.compile(r"""<c:dLblPos\b[^>]*?\bval=["'](\w+)["']""")
def _strip_ext_lst(text: str) -> str:
out, cursor = [], 0
for lo, hi in _ext_lst_spans(text):
out.append(text[cursor:lo])
cursor = hi
out.append(text[cursor:])
return "".join(out)
_BAR_GROUP_RE = re.compile(r"<c:(bar3DChart|barChart)\b[^>]*(?<!/)>.*?</c:\1\s*>", re.DOTALL)
STACKED_GROUPINGS = frozenset({"stacked", "percentStacked"})
ILLEGAL_ON_STACKED = frozenset({"outEnd"})
LEGAL_ON_STACKED = ("ctr", "inEnd", "inBase")
def _check_stacked_label_positions(part: str, xml: str) -> list[str]:
problems: list[str] = []
for match in _BAR_GROUP_RE.finditer(xml):
block = _strip_ext_lst(match.group(0))
group = match.group(1)
grouping = _GROUPING_RE.search(block)
if grouping is None or grouping.group(1) not in STACKED_GROUPINGS:
continue
bad = [p for p in _DLBL_POS_RE.findall(block) if p in ILLEGAL_ON_STACKED]
for pos in sorted(set(bad)):
problems.append(
f'{part}: {bad.count(pos)} data label(s) use dLblPos="{pos}" on a '
f"{grouping.group(1)} {group}; PowerPoint allows only "
f"{', '.join(LEGAL_ON_STACKED)} there"
)
return problems
_ANY_CHART_GROUP_RE = re.compile(r"<c:(\w+Chart)\b[^>]*(?<!/)>.*?</c:\1\s*>", re.DOTALL)
_AXID_RE = re.compile(
r"""\s*<c:axId\b[^>]*?\bval=["'](-?\d+)["']\s*(?:/>|>\s*</c:axId\s*>)"""
)
_AXIS_DECL_RE = re.compile(
r"""<c:(catAx|valAx|serAx|dateAx)\b[^>]*(?<!/)>\s*<c:axId\b[^>]*?\bval=["'](-?\d+)["']"""
)
AXID_LIMIT = {
"barChart": 2, "lineChart": 2, "areaChart": 2, "scatterChart": 2,
"bubbleChart": 2, "radarChart": 2, "stockChart": 2,
"bar3DChart": 3, "line3DChart": 3, "area3DChart": 3,
"surfaceChart": 3, "surface3DChart": 3,
}
AXID_MINIMUM = {
"barChart": 2, "lineChart": 2, "areaChart": 2, "scatterChart": 2,
"bubbleChart": 2, "radarChart": 2, "stockChart": 2,
"bar3DChart": 2, "area3DChart": 2, "surfaceChart": 2,
"line3DChart": 3, "surface3DChart": 3,
}
def _declared_axes(xml: str) -> dict[str, list[str]]:
axes: dict[str, list[str]] = {}
for kind, axid in _AXIS_DECL_RE.findall(xml):
axes.setdefault(kind, []).append(axid)
return axes
def _canonical_ids(axes: dict[str, list[str]], limit: int) -> list[str] | None:
category = axes.get("catAx", []) + axes.get("dateAx", [])
value = axes.get("valAx", [])
series = axes.get("serAx", [])
if len(category) != 1 or len(value) != 1 or len(series) > 1:
return None
ids = [category[0], value[0]]
if limit >= 3 and series:
ids.append(series[0])
return ids
def _undeclared_axes(kind: str, block: str, axes: dict[str, list[str]]) -> list[str] | None:
if kind not in AXID_LIMIT:
return None
ids = _AXID_RE.findall(block)
declared = {i for group in axes.values() for i in group}
if len([i for i in ids if i in declared]) >= 2:
return None
return ids
def _check_chart_axis_references(part: str, xml: str) -> list[str]:
axes = _declared_axes(xml)
problems: list[str] = []
declared = {i for group in axes.values() for i in group}
for match in _ANY_CHART_GROUP_RE.finditer(xml):
kind, block = match.group(1), match.group(0)
ids = _undeclared_axes(kind, block, axes)
if ids is None:
continue
if not ids:
problems.append(
f"{part}: <c:{kind}> declares no <c:axId> this part can resolve; a chart "
f"group needs {AXID_MINIMUM[kind]}, and PowerPoint discards one with fewer"
)
continue
dead = [i for i in ids if i not in declared]
canonical = _canonical_ids(axes, AXID_LIMIT[kind])
if canonical is not None and len(canonical) >= AXID_MINIMUM[kind]:
hint = f"Fix: point them at the axes this part declares ({', '.join(canonical)})"
else:
hint = ("Fix: the part declares several axes of a kind -- declare the "
"secondary axes the series expects, or drop them")
detail = (f"of which {', '.join(dead)} name no declared axis"
if dead else f"only {len(ids)} of which this part declares")
problems.append(
f"{part}: <c:{kind}> references axId {', '.join(ids)}, {detail}, "
f"leaving fewer than two live axes; PowerPoint discards the chart. {hint}"
)
return problems
def _ext_lst_spans(text: str) -> list[tuple[int, int]]:
spans: list[tuple[int, int]] = []
depth = 0
start = 0
for match in re.finditer(r"<(/?)c:extLst\b[^>]*?(/?)>", text):
closing, self_closing = match.group(1), match.group(2)
if self_closing:
continue
if closing:
depth -= 1
if depth == 0:
spans.append((start, match.end()))
else:
if depth == 0:
start = match.start()
depth += 1
return spans
CHART_CHECKS = (_check_stacked_label_positions, _check_chart_axis_references)
def find_chart_problems(files: Mapping[str, bytes]) -> list[str]:
problems: list[str] = []
for part in sorted(n for n in files if _CHART_PART_RE.fullmatch(n)):
xml = part_text(files[part])
for check in CHART_CHECKS:
problems.extend(check(part, xml))
return problems
scripts/office/helpers/pptx_slide.py
"""Pick the slide-XML schema errors PowerPoint refuses the file over.
A denylist over lxml's messages, so an unrecognised error class is a miss rather
than a false alarm.
"""
from __future__ import annotations
import re
SLIDE_PART_RE = re.compile(
r"ppt/(slides|slideLayouts|slideMasters|notesSlides|notesMasters|handoutMasters)"
r"/[^/]+\.xml"
)
FATAL_SLIDE_ERRORS: tuple[tuple[re.Pattern[str], str], ...] = (
(
re.compile(r"\}tableStyleId': This element is not expected"),
"two <a:tableStyleId> in one <a:tblPr> (the schema allows one)",
),
(
re.compile(r"\}srgbClr', attribute 'val'"),
"a colour that is not six hex digits",
),
(
re.compile(r"\}txBody': Missing child element"),
"a <p:txBody> with no children",
),
(
re.compile(r"\}miter', attribute 'lim'"),
'a line join with lim="NaN"',
),
(
re.compile(r"\}uLnTx': This element is not expected"),
"<a:uLnTx> in a position the schema forbids",
),
(
re.compile(r"\}overrideClrMapping': This element is not expected"),
"<p:overrideClrMapping> in a position the schema forbids",
),
(
re.compile(r"\}nvGrpSpPr': Missing child element"),
"a <p:nvGrpSpPr> with no children",
),
)
def is_schema_verdict(error: str) -> bool:
return error.startswith("Element ")
def fatal_slide_errors(errors: set[str]) -> list[str]:
out = []
for error in sorted(errors):
for pattern, meaning in FATAL_SLIDE_ERRORS:
if pattern.search(error):
out.append(f"{meaning}: {error}")
break
return out
scripts/office/helpers/pptx_theme.py
"""Find masters sharing a theme part in the way PowerPoint refuses to open.
Reports only; the fix is to move <p:notesMasterIdLst> back to directly after
<p:sldIdLst> in ppt/presentation.xml.
"""
from __future__ import annotations
import posixpath
import re
from typing import Mapping
from . import part_text
THEME_REL_TYPE = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/theme"
_MASTER_RE = re.compile(
r"^ppt/(?P<group>slideMasters|notesMasters|handoutMasters)/"
r"(?:slide|notes|handout)Master(?P<num>\d+)\.xml$"
)
_GROUP_ORDER = {"slideMasters": 0, "notesMasters": 1, "handoutMasters": 2}
_RELATIONSHIP_RE = re.compile(
r"<Relationship\b[^>]*?(?:/>|>.*?</Relationship\s*>)", re.DOTALL
)
def _sort_key(name: str) -> tuple[int, int]:
m = _MASTER_RE.match(name)
assert m is not None
return (_GROUP_ORDER[m.group("group")], int(m.group("num")))
def _rels_path(part: str) -> str:
directory, base = posixpath.split(part)
return f"{directory}/_rels/{base}.rels"
def _resolve(rels_path: str, target: str) -> str:
if target.startswith("/"):
return target.lstrip("/")
part_dir = posixpath.dirname(posixpath.dirname(rels_path))
return posixpath.normpath(posixpath.join(part_dir, target))
def _theme_rel(files: Mapping[str, bytes], master: str):
rels_path = _rels_path(master)
rels = files.get(rels_path)
if rels is None:
return None
for element in _RELATIONSHIP_RE.findall(part_text(rels)):
if f'Type="{THEME_REL_TYPE}"' not in element:
continue
target = re.search(r'\bTarget="([^"]+)"', element)
if target is None:
continue
return rels_path, element, _resolve(rels_path, target.group(1))
return None
def _masters(files: Mapping[str, bytes]) -> list[str]:
return sorted((n for n in files if _MASTER_RE.match(n)), key=_sort_key)
_PRESENTATION = "ppt/presentation.xml"
_NOTES_MASTERS = "ppt/notesMasters/"
_IGNORABLE_RE = re.compile(r"<!--.*?-->|<\?.*?\?>", re.DOTALL)
_AFTER_SLDIDLST_RE = re.compile(
r"<p:sldIdLst\b(?:[^>]*/>|[^>]*>.*?</p:sldIdLst\s*>)\s*(<[^>\s/]+)", re.DOTALL
)
def _notes_master_share_is_inert(files: Mapping[str, bytes]) -> bool:
data = files.get(_PRESENTATION)
if data is None:
return False
match = _AFTER_SLDIDLST_RE.search(_IGNORABLE_RE.sub("", part_text(data)))
return match is not None and match.group(1) == "<p:notesMasterIdLst"
def _shares(files: Mapping[str, bytes]):
owner: dict[str, str] = {}
for master in _masters(files):
found = _theme_rel(files, master)
if found is None:
continue
rels_path, element, theme = found
if theme not in files:
continue
if theme in owner:
yield master, rels_path, element, theme, owner[theme]
else:
owner[theme] = master
def _is_inert(master: str, inert_notes: bool) -> bool:
return inert_notes and master.startswith(_NOTES_MASTERS)
def find_shared_master_themes(files: Mapping[str, bytes]) -> list[str]:
return [
f"{master} shares {theme} with {first}"
for master, _, _, theme, first in _shares(files)
]
def live_shared_master_themes(files: Mapping[str, bytes]) -> list[str]:
inert_notes = _notes_master_share_is_inert(files)
return [
f"{master} shares {theme} with {first}"
for master, _, _, theme, first in _shares(files)
if not _is_inert(master, inert_notes)
]
scripts/office/soffice.py
"""
Helper for running LibreOffice (soffice) in environments where AF_UNIX
sockets may be blocked (e.g., sandboxed VMs). Detects the restriction
at runtime and applies an LD_PRELOAD shim if needed.
Usage:
from office.soffice import run_soffice
result = run_soffice(["--headless", "--convert-to", "pdf", "input.docx"])
Call soffice through run_soffice, not through subprocess with get_soffice_env():
the env dict carries the shim but names no user profile, and a non-root sandbox
cannot bootstrap the default one -- soffice aborts with "User installation could
not be completed" and converts nothing. get_soffice_env() stays public for the
callers that build their own argv (they must pass -env:UserInstallation too).
"""
import contextlib
import os
import socket
import subprocess
import tempfile
from collections.abc import Iterable
from pathlib import Path
def get_soffice_env() -> dict:
env = os.environ.copy()
env["SAL_USE_VCLPLUGIN"] = "svp"
if _needs_shim():
shim = _ensure_shim()
env["LD_PRELOAD"] = str(shim)
return env
def run_soffice(args: Iterable[str], **kwargs) -> subprocess.CompletedProcess:
args = list(args)
with contextlib.ExitStack() as stack:
if not any(str(a).startswith("-env:UserInstallation") for a in args):
profile = stack.enter_context(
tempfile.TemporaryDirectory(prefix="lo_profile_", ignore_cleanup_errors=True)
)
args = [f"-env:UserInstallation={Path(profile).as_uri()}"] + args
return subprocess.run(["soffice"] + args, env=get_soffice_env(), **kwargs)
_SHIM_SO = Path(tempfile.gettempdir()) / "lo_socket_shim.so"
def _needs_shim() -> bool:
try:
s = socket.socket(socket.AF_UNIX, socket.SOCK_STREAM)
s.close()
return False
except OSError:
return True
def _ensure_shim() -> Path:
if _SHIM_SO.exists():
return _SHIM_SO
src = Path(tempfile.gettempdir()) / "lo_socket_shim.c"
src.write_text(_SHIM_SOURCE)
subprocess.run(
["gcc", "-shared", "-fPIC", "-o", str(_SHIM_SO), str(src), "-ldl"],
check=True,
capture_output=True,
)
src.unlink()
return _SHIM_SO
_SHIM_SOURCE = r"""
#define _GNU_SOURCE
#include <dlfcn.h>
#include <errno.h>
#include <signal.h>
#include <stdio.h>
#include <stdlib.h>
#include <sys/socket.h>
#include <unistd.h>
static int (*real_socket)(int, int, int);
static int (*real_socketpair)(int, int, int, int[2]);
static int (*real_listen)(int, int);
static int (*real_accept)(int, struct sockaddr *, socklen_t *);
static int (*real_close)(int);
static int (*real_read)(int, void *, size_t);
/* Per-FD bookkeeping (FDs >= 1024 are passed through unshimmed). */
static int is_shimmed[1024];
static int peer_of[1024];
static int wake_r[1024]; /* accept() blocks reading this */
static int wake_w[1024]; /* close() writes to this */
static int listener_fd = -1; /* FD that received listen() */
__attribute__((constructor))
static void init(void) {
real_socket = dlsym(RTLD_NEXT, "socket");
real_socketpair = dlsym(RTLD_NEXT, "socketpair");
real_listen = dlsym(RTLD_NEXT, "listen");
real_accept = dlsym(RTLD_NEXT, "accept");
real_close = dlsym(RTLD_NEXT, "close");
real_read = dlsym(RTLD_NEXT, "read");
for (int i = 0; i < 1024; i++) {
peer_of[i] = -1;
wake_r[i] = -1;
wake_w[i] = -1;
}
}
/* ---- socket ---------------------------------------------------------- */
int socket(int domain, int type, int protocol) {
if (domain == AF_UNIX) {
int fd = real_socket(domain, type, protocol);
if (fd >= 0) return fd;
/* socket(AF_UNIX) blocked – fall back to socketpair(). */
int sv[2];
if (real_socketpair(domain, type, protocol, sv) == 0) {
if (sv[0] >= 0 && sv[0] < 1024) {
is_shimmed[sv[0]] = 1;
peer_of[sv[0]] = sv[1];
int wp[2];
if (pipe(wp) == 0) {
wake_r[sv[0]] = wp[0];
wake_w[sv[0]] = wp[1];
}
}
return sv[0];
}
errno = EPERM;
return -1;
}
return real_socket(domain, type, protocol);
}
/* ---- listen ---------------------------------------------------------- */
int listen(int sockfd, int backlog) {
if (sockfd >= 0 && sockfd < 1024 && is_shimmed[sockfd]) {
listener_fd = sockfd;
return 0;
}
return real_listen(sockfd, backlog);
}
/* ---- accept ---------------------------------------------------------- */
int accept(int sockfd, struct sockaddr *addr, socklen_t *addrlen) {
if (sockfd >= 0 && sockfd < 1024 && is_shimmed[sockfd]) {
/* Block until close() writes to the wake pipe. */
if (wake_r[sockfd] >= 0) {
char buf;
real_read(wake_r[sockfd], &buf, 1);
}
errno = ECONNABORTED;
return -1;
}
return real_accept(sockfd, addr, addrlen);
}
/* ---- close ----------------------------------------------------------- */
int close(int fd) {
if (fd >= 0 && fd < 1024 && is_shimmed[fd]) {
int was_listener = (fd == listener_fd);
is_shimmed[fd] = 0;
if (wake_w[fd] >= 0) { /* unblock accept() */
char c = 0;
write(wake_w[fd], &c, 1);
real_close(wake_w[fd]);
wake_w[fd] = -1;
}
if (wake_r[fd] >= 0) { real_close(wake_r[fd]); wake_r[fd] = -1; }
if (peer_of[fd] >= 0) { real_close(peer_of[fd]); peer_of[fd] = -1; }
if (was_listener)
_exit(0); /* conversion done – exit */
}
return real_close(fd);
}
"""
if __name__ == "__main__":
import sys
result = run_soffice(sys.argv[1:])
sys.exit(result.returncode)
scripts/office/validate.py
"""
Command line tool to validate Office document XML files against XSD schemas and tracked changes.
Usage:
python validate.py <path> [--original <original_file>] [--auto-repair] [--author NAME]
The first argument can be either:
- An unpacked directory containing the Office document XML files
- A packed Office file (.docx/.pptx/.xlsx or .dotx/.potx/.xltx template) which will be unpacked to a temp directory
Auto-repair fixes:
- paraId/durableId values that exceed OOXML limits
- Missing xml:space="preserve" on w:t elements with whitespace
"""
import argparse
import sys
import tempfile
import zipfile
from pathlib import Path
import defusedxml.ElementTree as ET
from defusedxml.common import DefusedXmlException
from helpers import OOXML_FAMILY, rezip, safe_extract
from validators import DOCXSchemaValidator, PPTXSchemaValidator, RedliningValidator
WORD_NS = "http://schemas.openxmlformats.org/wordprocessingml/2006/main"
def _fail(message: str):
print(f"Error: {message}", file=sys.stderr)
sys.exit(2)
def _has_tracked_changes(unpacked_dir: Path) -> bool:
document = unpacked_dir / "word" / "document.xml"
if not document.is_file():
return False
try:
root = ET.parse(document).getroot()
except (ET.ParseError, DefusedXmlException):
return False
tracked = {f"{{{WORD_NS}}}ins", f"{{{WORD_NS}}}del"}
return any(elem.tag in tracked for elem in root.iter())
def main():
parser = argparse.ArgumentParser(description="Validate Office document XML files")
parser.add_argument(
"path",
help="Path to unpacked directory or packed Office file (.docx/.pptx/.xlsx or .dotx/.potx/.xltx)",
)
parser.add_argument(
"--original",
required=False,
default=None,
help="Path to original file (.docx/.pptx/.xlsx or .dotx/.potx/.xltx). If omitted, all XSD errors are reported and redlining validation is skipped.",
)
parser.add_argument(
"-v",
"--verbose",
action="store_true",
help="Enable verbose output",
)
parser.add_argument(
"--auto-repair",
action="store_true",
help="Automatically repair common issues (hex IDs, whitespace preservation). "
"Modifies the input in place: repairs to a packed file are written back to it.",
)
parser.add_argument(
"--author",
default=None,
help="The name you are redlining under. Passing it turns on the "
"tracked-change check: any text differing from --original without a "
"<w:ins>/<w:del> recording it is reported. Untracked edits carry no "
"author, so the check covers them whoever made them — the name marks "
"the run as redlining work and is not used to filter. Requires "
"--original; docx only.",
)
args = parser.parse_args()
if args.author is not None and not args.original:
_fail("--author requires --original")
path = Path(args.path)
if not path.exists():
_fail(f"{path} does not exist")
original_file = None
if args.original:
original_file = Path(args.original)
if not original_file.is_file():
_fail(f"{original_file} is not a file")
if original_file.suffix.lower() not in OOXML_FAMILY:
_fail(f"{original_file} must be one of: {', '.join(sorted(OOXML_FAMILY))}")
family = OOXML_FAMILY.get((original_file or path).suffix.lower())
if family is None:
_fail(
f"Cannot determine file type from {path}. Use --original or provide one of: {', '.join(sorted(OOXML_FAMILY))}."
)
if args.author is not None and family != "docx":
_fail(f"--author only applies to docx files, not {family}")
packed_file = None
temp_dir_ctx = None
if path.is_file() and path.suffix.lower() in OOXML_FAMILY:
packed_file = path
temp_dir_ctx = tempfile.TemporaryDirectory()
unpacked_dir = Path(temp_dir_ctx.name)
try:
with zipfile.ZipFile(path, "r") as zf:
safe_extract(zf, unpacked_dir)
except (zipfile.BadZipFile, ValueError, OSError) as e:
_fail(f"cannot unpack {path}: {e}")
else:
if not path.is_dir():
_fail(f"{path} is not a directory or Office file")
unpacked_dir = path
match family:
case "docx":
validators = [
DOCXSchemaValidator(unpacked_dir, original_file, verbose=args.verbose),
]
if args.author is not None:
validators.append(
RedliningValidator(unpacked_dir, original_file, verbose=args.verbose)
)
elif original_file and _has_tracked_changes(unpacked_dir):
print(
"Note: this document has tracked changes; they were not "
"checked against the original (pass --author to check)."
)
case "pptx":
validators = [
PPTXSchemaValidator(unpacked_dir, original_file, verbose=args.verbose),
]
case "xlsx":
exts = ", ".join(k for k, v in sorted(OOXML_FAMILY.items()) if v == "xlsx")
print(
f"No XSD schema validation is performed for xlsx-family files ({exts}). "
"For formula-error checking, use scripts/recalc.py instead."
)
sys.exit(0)
case _:
print(f"Error: Validation not supported for file type {family}")
sys.exit(1)
if args.auto_repair:
total_repairs = sum(v.repair() for v in validators)
if total_repairs:
print(f"Auto-repaired {total_repairs} issue(s)")
if packed_file is not None:
rezip(unpacked_dir, packed_file)
print(f"Wrote repaired file to {packed_file}")
success = all([v.validate() for v in validators])
if temp_dir_ctx is not None:
temp_dir_ctx.cleanup()
if success:
print("All validations PASSED!")
sys.exit(0 if success else 1)
if __name__ == "__main__":
main()
scripts/office/validators/__init__.py
"""
Validation modules for Word document processing.
"""
from .base import BaseSchemaValidator
from .docx import DOCXSchemaValidator
from .pptx import PPTXSchemaValidator
from .redlining import RedliningValidator
__all__ = [
"BaseSchemaValidator",
"DOCXSchemaValidator",
"PPTXSchemaValidator",
"RedliningValidator",
]
scripts/office/validators/base.py
"""
Base validator with common validation logic for document files.
"""
import re
from pathlib import Path
import defusedxml.minidom
from functools import lru_cache
import lxml.etree
from helpers import safe_extract
@lru_cache(maxsize=None)
def _load_schema(schema_path: str):
with open(schema_path, "rb") as xsd_file:
xsd_doc = lxml.etree.parse(
xsd_file, parser=lxml.etree.XMLParser(), base_url=schema_path
)
return lxml.etree.XMLSchema(xsd_doc)
class BaseSchemaValidator:
IGNORED_VALIDATION_ERRORS = [
"hyphenationZone",
"purl.org/dc/terms",
]
UNIQUE_ID_REQUIREMENTS = {
"comment": ("id", "file"),
"commentrangestart": ("id", "file"),
"commentrangeend": ("id", "file"),
"bookmarkstart": ("id", "file"),
"bookmarkend": ("id", "file"),
"sldid": ("id", "file"),
"sldmasterid": ("id", "global"),
"sldlayoutid": ("id", "global"),
"cm": ("authorid", "file"),
"sheet": ("sheetid", "file"),
"definedname": ("id", "file"),
"cxnsp": ("id", "file"),
"sp": ("id", "file"),
"pic": ("id", "file"),
"grpsp": ("id", "file"),
}
EXCLUDED_ID_CONTAINERS = {
"sectionlst",
}
ELEMENT_RELATIONSHIP_TYPES = {}
SCHEMA_MAPPINGS = {
"word": "ISO-IEC29500-4_2016/wml.xsd",
"ppt": "ISO-IEC29500-4_2016/pml.xsd",
"xl": "ISO-IEC29500-4_2016/sml.xsd",
"[Content_Types].xml": "ecma/fouth-edition/opc-contentTypes.xsd",
"app.xml": "ISO-IEC29500-4_2016/shared-documentPropertiesExtended.xsd",
"core.xml": "ecma/fouth-edition/opc-coreProperties.xsd",
"custom.xml": "ISO-IEC29500-4_2016/shared-documentPropertiesCustom.xsd",
".rels": "ecma/fouth-edition/opc-relationships.xsd",
"people.xml": "microsoft/wml-2012.xsd",
"commentsIds.xml": "microsoft/wml-cid-2016.xsd",
"commentsExtensible.xml": "microsoft/wml-cex-2018.xsd",
"commentsExtended.xml": "microsoft/wml-2012.xsd",
"chart": "ISO-IEC29500-4_2016/dml-chart.xsd",
"theme": "ISO-IEC29500-4_2016/dml-main.xsd",
"drawing": "ISO-IEC29500-4_2016/dml-main.xsd",
}
MC_NAMESPACE = "http://schemas.openxmlformats.org/markup-compatibility/2006"
XML_NAMESPACE = "http://www.w3.org/XML/1998/namespace"
PACKAGE_RELATIONSHIPS_NAMESPACE = (
"http://schemas.openxmlformats.org/package/2006/relationships"
)
OFFICE_RELATIONSHIPS_NAMESPACE = (
"http://schemas.openxmlformats.org/officeDocument/2006/relationships"
)
CONTENT_TYPES_NAMESPACE = (
"http://schemas.openxmlformats.org/package/2006/content-types"
)
MAIN_CONTENT_FOLDERS = {"word", "ppt", "xl"}
OOXML_NAMESPACES = {
"http://schemas.openxmlformats.org/officeDocument/2006/math",
"http://schemas.openxmlformats.org/officeDocument/2006/relationships",
"http://schemas.openxmlformats.org/schemaLibrary/2006/main",
"http://schemas.openxmlformats.org/drawingml/2006/main",
"http://schemas.openxmlformats.org/drawingml/2006/chart",
"http://schemas.openxmlformats.org/drawingml/2006/chartDrawing",
"http://schemas.openxmlformats.org/drawingml/2006/diagram",
"http://schemas.openxmlformats.org/drawingml/2006/picture",
"http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing",
"http://schemas.openxmlformats.org/drawingml/2006/wordprocessingDrawing",
"http://schemas.openxmlformats.org/wordprocessingml/2006/main",
"http://schemas.openxmlformats.org/presentationml/2006/main",
"http://schemas.openxmlformats.org/spreadsheetml/2006/main",
"http://schemas.openxmlformats.org/officeDocument/2006/sharedTypes",
"http://www.w3.org/XML/1998/namespace",
}
def __init__(self, unpacked_dir, original_file=None, verbose=False):
self.unpacked_dir = Path(unpacked_dir).resolve()
self.original_file = Path(original_file) if original_file else None
self.verbose = verbose
self.schemas_dir = Path(__file__).parent.parent / "schemas"
patterns = ["*.xml", "*.rels"]
self.xml_files = [
f for pattern in patterns for f in self.unpacked_dir.rglob(pattern)
]
if not self.xml_files:
print(f"Warning: No XML files found in {self.unpacked_dir}")
def validate(self):
raise NotImplementedError("Subclasses must implement the validate method")
def repair(self) -> int:
return self.repair_whitespace_preservation()
def repair_whitespace_preservation(self) -> int:
repairs = 0
for xml_file in self.xml_files:
try:
content = xml_file.read_text(encoding="utf-8")
dom = defusedxml.minidom.parseString(content)
pending = []
for elem in dom.getElementsByTagName("*"):
local_name = elem.tagName.rsplit(":", 1)[-1]
if local_name in ("t", "delText", "instrText", "delInstrText"):
text = "".join(
child.data
for child in elem.childNodes
if child.nodeType in (child.TEXT_NODE, child.CDATA_SECTION_NODE)
)
ws = (" ", "\t", "\n", "\r")
if text and (text.startswith(ws) or text.endswith(ws)):
if elem.getAttribute("xml:space") != "preserve":
elem.setAttribute("xml:space", "preserve")
text_preview = repr(text[:30]) + "..." if len(text) > 30 else repr(text)
pending.append(f" Repaired: {xml_file.name}: Added xml:space='preserve' to {elem.tagName}: {text_preview}")
if pending:
xml_file.write_bytes(dom.toxml(encoding="UTF-8"))
for message in pending:
print(message)
repairs += len(pending)
except Exception:
pass
return repairs
def validate_xml(self):
errors = []
for xml_file in self.xml_files:
try:
lxml.etree.parse(str(xml_file))
except lxml.etree.XMLSyntaxError as e:
errors.append(
f" {xml_file.relative_to(self.unpacked_dir)}: "
f"Line {e.lineno}: {e.msg}"
)
except Exception as e:
errors.append(
f" {xml_file.relative_to(self.unpacked_dir)}: "
f"Unexpected error: {str(e)}"
)
if errors:
print(f"FAILED - Found {len(errors)} XML violations:")
for error in errors:
print(error)
return False
else:
if self.verbose:
print("PASSED - All XML files are well-formed")
return True
def validate_namespaces(self):
errors = []
for xml_file in self.xml_files:
try:
root = lxml.etree.parse(str(xml_file)).getroot()
declared = set(root.nsmap.keys()) - {None}
for attr_val in [
v for k, v in root.attrib.items() if k.endswith("Ignorable")
]:
undeclared = set(attr_val.split()) - declared
errors.extend(
f" {xml_file.relative_to(self.unpacked_dir)}: "
f"Namespace '{ns}' in Ignorable but not declared"
for ns in undeclared
)
except lxml.etree.XMLSyntaxError:
continue
if errors:
print(f"FAILED - {len(errors)} namespace issues:")
for error in errors:
print(error)
return False
if self.verbose:
print("PASSED - All namespace prefixes properly declared")
return True
def validate_unique_ids(self):
errors = []
global_ids = {}
for xml_file in self.xml_files:
try:
root = lxml.etree.parse(str(xml_file)).getroot()
file_ids = {}
mc_elements = root.xpath(
".//mc:AlternateContent", namespaces={"mc": self.MC_NAMESPACE}
)
for elem in mc_elements:
elem.getparent().remove(elem)
for elem in root.iter():
if not hasattr(elem, "tag") or callable(elem.tag):
continue
tag = (
elem.tag.split("}")[-1].lower()
if "}" in elem.tag
else elem.tag.lower()
)
if tag in self.UNIQUE_ID_REQUIREMENTS:
in_excluded_container = any(
ancestor.tag.split("}")[-1].lower() in self.EXCLUDED_ID_CONTAINERS
for ancestor in elem.iterancestors()
)
if in_excluded_container:
continue
attr_name, scope = self.UNIQUE_ID_REQUIREMENTS[tag]
id_value = None
for attr, value in elem.attrib.items():
attr_local = (
attr.split("}")[-1].lower()
if "}" in attr
else attr.lower()
)
if attr_local == attr_name:
id_value = value
break
if id_value is not None:
if scope == "global":
if id_value in global_ids:
prev_file, prev_line, prev_tag = global_ids[
id_value
]
errors.append(
f" {xml_file.relative_to(self.unpacked_dir)}: "
f"Line {elem.sourceline}: Global ID '{id_value}' in <{tag}> "
f"already used in {prev_file} at line {prev_line} in <{prev_tag}>"
)
else:
global_ids[id_value] = (
xml_file.relative_to(self.unpacked_dir),
elem.sourceline,
tag,
)
elif scope == "file":
key = (tag, attr_name)
if key not in file_ids:
file_ids[key] = {}
if id_value in file_ids[key]:
prev_line = file_ids[key][id_value]
errors.append(
f" {xml_file.relative_to(self.unpacked_dir)}: "
f"Line {elem.sourceline}: Duplicate {attr_name}='{id_value}' in <{tag}> "
f"(first occurrence at line {prev_line})"
)
else:
file_ids[key][id_value] = elem.sourceline
except (lxml.etree.XMLSyntaxError, Exception) as e:
errors.append(
f" {xml_file.relative_to(self.unpacked_dir)}: Error: {e}"
)
if errors:
print(f"FAILED - Found {len(errors)} ID uniqueness violations:")
for error in errors:
print(error)
return False
else:
if self.verbose:
print("PASSED - All required IDs are unique")
return True
def validate_file_references(self):
errors = []
rels_files = list(self.unpacked_dir.rglob("*.rels"))
if not rels_files:
if self.verbose:
print("PASSED - No .rels files found")
return True
all_files = []
for file_path in self.unpacked_dir.rglob("*"):
if (
file_path.is_file()
and file_path.name != "[Content_Types].xml"
and not file_path.name.endswith(".rels")
):
all_files.append(file_path.resolve())
all_referenced_files = set()
if self.verbose:
print(
f"Found {len(rels_files)} .rels files and {len(all_files)} target files"
)
for rels_file in rels_files:
try:
rels_root = lxml.etree.parse(str(rels_file)).getroot()
rels_dir = rels_file.parent
referenced_files = set()
broken_refs = []
for rel in rels_root.findall(
".//ns:Relationship",
namespaces={"ns": self.PACKAGE_RELATIONSHIPS_NAMESPACE},
):
target = rel.get("Target")
if rel.get("TargetMode") == "External":
continue
if target and not target.startswith(
("http", "mailto:")
):
if target.startswith("/"):
target_path = self.unpacked_dir / target.lstrip("/")
elif rels_file.name == ".rels":
target_path = self.unpacked_dir / target
else:
base_dir = rels_dir.parent
target_path = base_dir / target
try:
target_path = target_path.resolve()
if target_path.exists() and target_path.is_file():
referenced_files.add(target_path)
all_referenced_files.add(target_path)
else:
broken_refs.append((target, rel.sourceline))
except (OSError, ValueError):
broken_refs.append((target, rel.sourceline))
if broken_refs:
rel_path = rels_file.relative_to(self.unpacked_dir)
for broken_ref, line_num in broken_refs:
errors.append(
f" {rel_path}: Line {line_num}: Broken reference to {broken_ref}"
)
except Exception as e:
rel_path = rels_file.relative_to(self.unpacked_dir)
errors.append(f" Error parsing {rel_path}: {e}")
unreferenced_files = set(all_files) - all_referenced_files
if unreferenced_files:
for unref_file in sorted(unreferenced_files):
unref_rel_path = unref_file.relative_to(self.unpacked_dir)
errors.append(f" Unreferenced file: {unref_rel_path}")
if errors:
print(f"FAILED - Found {len(errors)} relationship validation errors:")
for error in errors:
print(error)
print(
"CRITICAL: These errors will cause the document to appear corrupt. "
+ "Broken references MUST be fixed, "
+ "and unreferenced files MUST be referenced or removed."
)
return False
else:
if self.verbose:
print(
"PASSED - All references are valid and all files are properly referenced"
)
return True
def validate_all_relationship_ids(self):
import lxml.etree
errors = []
for xml_file in self.xml_files:
if xml_file.suffix == ".rels":
continue
rels_dir = xml_file.parent / "_rels"
rels_file = rels_dir / f"{xml_file.name}.rels"
if not rels_file.exists():
continue
try:
rels_root = lxml.etree.parse(str(rels_file)).getroot()
rid_to_type = {}
for rel in rels_root.findall(
f".//{{{self.PACKAGE_RELATIONSHIPS_NAMESPACE}}}Relationship"
):
rid = rel.get("Id")
rel_type = rel.get("Type", "")
if rid:
if rid in rid_to_type:
rels_rel_path = rels_file.relative_to(self.unpacked_dir)
errors.append(
f" {rels_rel_path}: Line {rel.sourceline}: "
f"Duplicate relationship ID '{rid}' (IDs must be unique)"
)
type_name = (
rel_type.split("/")[-1] if "/" in rel_type else rel_type
)
rid_to_type[rid] = type_name
xml_root = lxml.etree.parse(str(xml_file)).getroot()
r_ns = self.OFFICE_RELATIONSHIPS_NAMESPACE
rid_attrs_to_check = ["id", "embed", "link"]
for elem in xml_root.iter():
if not hasattr(elem, "tag") or callable(elem.tag):
continue
for attr_name in rid_attrs_to_check:
rid_attr = elem.get(f"{{{r_ns}}}{attr_name}")
if not rid_attr:
continue
xml_rel_path = xml_file.relative_to(self.unpacked_dir)
elem_name = (
elem.tag.split("}")[-1] if "}" in elem.tag else elem.tag
)
if rid_attr not in rid_to_type:
errors.append(
f" {xml_rel_path}: Line {elem.sourceline}: "
f"<{elem_name}> r:{attr_name} references non-existent relationship '{rid_attr}' "
f"(valid IDs: {', '.join(sorted(rid_to_type.keys())[:5])}{'...' if len(rid_to_type) > 5 else ''})"
)
elif attr_name == "id" and self.ELEMENT_RELATIONSHIP_TYPES:
expected_type = self._get_expected_relationship_type(
elem_name
)
if expected_type:
actual_type = rid_to_type[rid_attr]
if expected_type not in actual_type.lower():
errors.append(
f" {xml_rel_path}: Line {elem.sourceline}: "
f"<{elem_name}> references '{rid_attr}' which points to '{actual_type}' "
f"but should point to a '{expected_type}' relationship"
)
except Exception as e:
xml_rel_path = xml_file.relative_to(self.unpacked_dir)
errors.append(f" Error processing {xml_rel_path}: {e}")
if errors:
print(f"FAILED - Found {len(errors)} relationship ID reference errors:")
for error in errors:
print(error)
print("\nThese ID mismatches will cause the document to appear corrupt!")
return False
else:
if self.verbose:
print("PASSED - All relationship ID references are valid")
return True
def _get_expected_relationship_type(self, element_name):
elem_lower = element_name.lower()
if elem_lower in self.ELEMENT_RELATIONSHIP_TYPES:
return self.ELEMENT_RELATIONSHIP_TYPES[elem_lower]
if elem_lower.endswith("id") and len(elem_lower) > 2:
prefix = elem_lower[:-2]
if prefix.endswith("master"):
return prefix.lower()
elif prefix.endswith("layout"):
return prefix.lower()
else:
if prefix == "sld":
return "slide"
return prefix.lower()
if elem_lower.endswith("reference") and len(elem_lower) > 9:
prefix = elem_lower[:-9]
return prefix.lower()
return None
def validate_content_types(self):
errors = []
content_types_file = self.unpacked_dir / "[Content_Types].xml"
if not content_types_file.exists():
print("FAILED - [Content_Types].xml file not found")
return False
try:
root = lxml.etree.parse(str(content_types_file)).getroot()
declared_parts = set()
declared_extensions = set()
for override in root.findall(
f".//{{{self.CONTENT_TYPES_NAMESPACE}}}Override"
):
part_name = override.get("PartName")
if part_name is not None:
declared_parts.add(part_name.lstrip("/"))
for default in root.findall(
f".//{{{self.CONTENT_TYPES_NAMESPACE}}}Default"
):
extension = default.get("Extension")
if extension is not None:
declared_extensions.add(extension.lower())
declarable_roots = {
"sld",
"sldLayout",
"sldMaster",
"presentation",
"document",
"workbook",
"worksheet",
"theme",
}
media_extensions = {
"png": "image/png",
"jpg": "image/jpeg",
"jpeg": "image/jpeg",
"gif": "image/gif",
"bmp": "image/bmp",
"tiff": "image/tiff",
"wmf": "image/x-wmf",
"emf": "image/x-emf",
}
all_files = list(self.unpacked_dir.rglob("*"))
all_files = [f for f in all_files if f.is_file()]
for xml_file in self.xml_files:
path_str = str(xml_file.relative_to(self.unpacked_dir)).replace(
"\\", "/"
)
if any(
skip in path_str
for skip in [".rels", "[Content_Types]", "docProps/", "_rels/"]
):
continue
try:
root_tag = lxml.etree.parse(str(xml_file)).getroot().tag
root_name = root_tag.split("}")[-1] if "}" in root_tag else root_tag
if root_name in declarable_roots and path_str not in declared_parts:
errors.append(
f" {path_str}: File with <{root_name}> root not declared in [Content_Types].xml"
)
except Exception:
continue
for file_path in all_files:
if file_path.suffix.lower() in {".xml", ".rels"}:
continue
if file_path.name == "[Content_Types].xml":
continue
if "_rels" in file_path.parts or "docProps" in file_path.parts:
continue
extension = file_path.suffix.lstrip(".").lower()
if extension and extension not in declared_extensions:
if extension in media_extensions:
relative_path = file_path.relative_to(self.unpacked_dir)
errors.append(
f' {relative_path}: File with extension \'{extension}\' not declared in [Content_Types].xml - should add: <Default Extension="{extension}" ContentType="{media_extensions[extension]}"/>'
)
except Exception as e:
errors.append(f" Error parsing [Content_Types].xml: {e}")
if errors:
print(f"FAILED - Found {len(errors)} content type declaration errors:")
for error in errors:
print(error)
return False
else:
if self.verbose:
print(
"PASSED - All content files are properly declared in [Content_Types].xml"
)
return True
def validate_file_against_xsd(self, xml_file, verbose=False):
xml_file = Path(xml_file).resolve()
unpacked_dir = self.unpacked_dir.resolve()
is_valid, current_errors = self._validate_single_file_xsd(
xml_file, unpacked_dir
)
if is_valid is None:
return None, set()
elif is_valid:
return True, set()
original_errors = self._get_original_file_errors(xml_file)
assert current_errors is not None
new_errors = current_errors - original_errors
new_errors = {
e for e in new_errors
if not any(pattern in e for pattern in self.IGNORED_VALIDATION_ERRORS)
}
if new_errors:
if verbose:
relative_path = xml_file.relative_to(unpacked_dir)
print(f"FAILED - {relative_path}: {len(new_errors)} new error(s)")
for error in list(new_errors)[:3]:
truncated = error[:250] + "..." if len(error) > 250 else error
print(f" - {truncated}")
return False, new_errors
else:
if verbose:
print(
f"PASSED - No new errors (original had {len(current_errors)} errors)"
)
return True, set()
def validate_against_xsd(self):
new_errors = []
original_error_count = 0
valid_count = 0
skipped_count = 0
for xml_file in self.xml_files:
relative_path = str(xml_file.relative_to(self.unpacked_dir))
is_valid, new_file_errors = self.validate_file_against_xsd(
xml_file, verbose=False
)
if is_valid is None:
skipped_count += 1
continue
elif is_valid and not new_file_errors:
valid_count += 1
continue
elif is_valid:
original_error_count += 1
valid_count += 1
continue
new_errors.append(f" {relative_path}: {len(new_file_errors)} new error(s)")
for error in list(new_file_errors)[:3]:
new_errors.append(
f" - {error[:250]}..." if len(error) > 250 else f" - {error}"
)
if self.verbose:
print(f"Validated {len(self.xml_files)} files:")
print(f" - Valid: {valid_count}")
print(f" - Skipped (no schema): {skipped_count}")
if original_error_count:
print(f" - With original errors (ignored): {original_error_count}")
print(
f" - With NEW errors: {len(new_errors) > 0 and len([e for e in new_errors if not e.startswith(' ')]) or 0}"
)
if new_errors:
print("\nFAILED - Found NEW validation errors:")
for error in new_errors:
print(error)
return False
else:
if self.verbose:
print("\nPASSED - No new XSD validation errors introduced")
return True
def _get_schema_path(self, xml_file):
if xml_file.name in self.SCHEMA_MAPPINGS:
return self.schemas_dir / self.SCHEMA_MAPPINGS[xml_file.name]
if xml_file.suffix == ".rels":
return self.schemas_dir / self.SCHEMA_MAPPINGS[".rels"]
if "charts/" in str(xml_file) and xml_file.name.startswith("chart"):
return self.schemas_dir / self.SCHEMA_MAPPINGS["chart"]
if "theme/" in str(xml_file) and xml_file.name.startswith("theme"):
return self.schemas_dir / self.SCHEMA_MAPPINGS["theme"]
if xml_file.parent.name in self.MAIN_CONTENT_FOLDERS:
return self.schemas_dir / self.SCHEMA_MAPPINGS[xml_file.parent.name]
return None
def _clean_ignorable_namespaces(self, xml_doc):
xml_string = lxml.etree.tostring(xml_doc, encoding="unicode")
xml_copy = lxml.etree.fromstring(xml_string)
for elem in xml_copy.iter():
attrs_to_remove = []
for attr in elem.attrib:
if "{" in attr:
ns = attr.split("}")[0][1:]
if ns not in self.OOXML_NAMESPACES:
attrs_to_remove.append(attr)
for attr in attrs_to_remove:
del elem.attrib[attr]
self._remove_ignorable_elements(xml_copy)
return lxml.etree.ElementTree(xml_copy)
def _remove_ignorable_elements(self, root):
elements_to_remove = []
for elem in list(root):
if not hasattr(elem, "tag") or callable(elem.tag):
continue
tag_str = str(elem.tag)
if tag_str.startswith("{"):
ns = tag_str.split("}")[0][1:]
if ns not in self.OOXML_NAMESPACES:
elements_to_remove.append(elem)
continue
self._remove_ignorable_elements(elem)
for elem in elements_to_remove:
root.remove(elem)
def _preprocess_for_mc_ignorable(self, xml_doc):
root = xml_doc.getroot()
if f"{{{self.MC_NAMESPACE}}}Ignorable" in root.attrib:
del root.attrib[f"{{{self.MC_NAMESPACE}}}Ignorable"]
return xml_doc
def _preprocess_for_schema(self, xml_doc, relative_path):
return xml_doc
def _validate_single_file_xsd(self, xml_file, base_path, schema_path=None):
schema_path = schema_path or self._get_schema_path(xml_file)
if not schema_path:
return None, None
try:
schema = _load_schema(str(schema_path))
with open(xml_file, "r") as f:
xml_doc = lxml.etree.parse(f)
xml_doc, _ = self._remove_template_tags_from_text_nodes(xml_doc)
xml_doc = self._preprocess_for_mc_ignorable(xml_doc)
relative_path = xml_file.relative_to(base_path)
if (
relative_path.parts
and relative_path.parts[0] in self.MAIN_CONTENT_FOLDERS
):
xml_doc = self._clean_ignorable_namespaces(xml_doc)
xml_doc = self._preprocess_for_schema(xml_doc, relative_path)
if schema.validate(xml_doc):
return True, set()
else:
errors = set()
for error in schema.error_log:
errors.add(error.message)
return False, errors
except Exception as e:
return False, {str(e)}
def _get_original_file_errors(self, xml_file, schema_path=None):
if self.original_file is None:
return set()
import tempfile
import zipfile
xml_file = Path(xml_file).resolve()
unpacked_dir = self.unpacked_dir.resolve()
relative_path = xml_file.relative_to(unpacked_dir)
with tempfile.TemporaryDirectory() as temp_dir:
temp_path = Path(temp_dir)
try:
with zipfile.ZipFile(self.original_file, "r") as zip_ref:
safe_extract(zip_ref, temp_path)
except (zipfile.BadZipFile, ValueError, OSError):
return set()
original_xml_file = temp_path / relative_path
if not original_xml_file.exists():
return set()
is_valid, errors = self._validate_single_file_xsd(
original_xml_file, temp_path, schema_path=schema_path
)
return errors if errors else set()
def _remove_template_tags_from_text_nodes(self, xml_doc):
warnings = []
template_pattern = re.compile(r"\{\{[^}]*\}\}")
xml_string = lxml.etree.tostring(xml_doc, encoding="unicode")
xml_copy = lxml.etree.fromstring(xml_string)
def process_text_content(text, content_type):
if not text:
return text
matches = list(template_pattern.finditer(text))
if matches:
for match in matches:
warnings.append(
f"Found template tag in {content_type}: {match.group()}"
)
return template_pattern.sub("", text)
return text
for elem in xml_copy.iter():
if not hasattr(elem, "tag") or callable(elem.tag):
continue
tag_str = str(elem.tag)
if tag_str.endswith("}t") or tag_str == "t":
continue
elem.text = process_text_content(elem.text, "text content")
elem.tail = process_text_content(elem.tail, "tail content")
return lxml.etree.ElementTree(xml_copy), warnings
if __name__ == "__main__":
raise RuntimeError("This module should not be run directly.")
scripts/office/validators/docx.py
"""
Validator for Word document XML files against XSD schemas.
"""
import random
import re
import tempfile
import zipfile
from pathlib import Path
import defusedxml.minidom
import lxml.etree
from helpers import safe_extract
from .base import BaseSchemaValidator
class DOCXSchemaValidator(BaseSchemaValidator):
WORD_2006_NAMESPACE = "http://schemas.openxmlformats.org/wordprocessingml/2006/main"
W14_NAMESPACE = "http://schemas.microsoft.com/office/word/2010/wordml"
W16CID_NAMESPACE = "http://schemas.microsoft.com/office/word/2016/wordml/cid"
ELEMENT_RELATIONSHIP_TYPES = {}
def validate(self):
if not self.validate_xml():
return False
all_valid = True
if not self.validate_namespaces():
all_valid = False
if not self.validate_unique_ids():
all_valid = False
if not self.validate_file_references():
all_valid = False
if not self.validate_content_types():
all_valid = False
if not self.validate_against_xsd():
all_valid = False
if not self.validate_whitespace_preservation():
all_valid = False
if not self.validate_deletions():
all_valid = False
if not self.validate_insertions():
all_valid = False
if not self.validate_all_relationship_ids():
all_valid = False
if not self.validate_id_constraints():
all_valid = False
if not self.validate_comment_markers():
all_valid = False
self.compare_paragraph_counts()
return all_valid
def validate_whitespace_preservation(self):
errors = []
for xml_file in self.xml_files:
if xml_file.name != "document.xml":
continue
try:
root = lxml.etree.parse(str(xml_file)).getroot()
for elem in root.iter(f"{{{self.WORD_2006_NAMESPACE}}}t"):
if elem.text:
text = elem.text
if re.search(r"^[ \t\n\r]", text) or re.search(
r"[ \t\n\r]$", text
):
xml_space_attr = f"{{{self.XML_NAMESPACE}}}space"
if (
xml_space_attr not in elem.attrib
or elem.attrib[xml_space_attr] != "preserve"
):
text_preview = (
repr(text)[:50] + "..."
if len(repr(text)) > 50
else repr(text)
)
errors.append(
f" {xml_file.relative_to(self.unpacked_dir)}: "
f"Line {elem.sourceline}: w:t element with whitespace missing xml:space='preserve': {text_preview}"
)
except (lxml.etree.XMLSyntaxError, Exception) as e:
errors.append(
f" {xml_file.relative_to(self.unpacked_dir)}: Error: {e}"
)
if errors:
print(f"FAILED - Found {len(errors)} whitespace preservation violations:")
for error in errors:
print(error)
return False
else:
if self.verbose:
print("PASSED - All whitespace is properly preserved")
return True
def validate_deletions(self):
errors = []
for xml_file in self.xml_files:
if xml_file.name != "document.xml":
continue
try:
root = lxml.etree.parse(str(xml_file)).getroot()
namespaces = {"w": self.WORD_2006_NAMESPACE}
for t_elem in root.xpath(".//w:del//w:t", namespaces=namespaces):
if t_elem.text:
text_preview = (
repr(t_elem.text)[:50] + "..."
if len(repr(t_elem.text)) > 50
else repr(t_elem.text)
)
errors.append(
f" {xml_file.relative_to(self.unpacked_dir)}: "
f"Line {t_elem.sourceline}: <w:t> found within <w:del>: {text_preview}"
)
for instr_elem in root.xpath(
".//w:del//w:instrText", namespaces=namespaces
):
text_preview = (
repr(instr_elem.text or "")[:50] + "..."
if len(repr(instr_elem.text or "")) > 50
else repr(instr_elem.text or "")
)
errors.append(
f" {xml_file.relative_to(self.unpacked_dir)}: "
f"Line {instr_elem.sourceline}: <w:instrText> found within <w:del> (use <w:delInstrText>): {text_preview}"
)
except (lxml.etree.XMLSyntaxError, Exception) as e:
errors.append(
f" {xml_file.relative_to(self.unpacked_dir)}: Error: {e}"
)
if errors:
print(f"FAILED - Found {len(errors)} deletion validation violations:")
for error in errors:
print(error)
return False
else:
if self.verbose:
print("PASSED - No w:t elements found within w:del elements")
return True
def count_paragraphs_in_unpacked(self):
count = 0
for xml_file in self.xml_files:
if xml_file.name != "document.xml":
continue
try:
root = lxml.etree.parse(str(xml_file)).getroot()
paragraphs = root.findall(f".//{{{self.WORD_2006_NAMESPACE}}}p")
count = len(paragraphs)
except Exception as e:
print(f"Error counting paragraphs in unpacked document: {e}")
return count
def count_paragraphs_in_original(self):
original = self.original_file
if original is None:
return 0
count = 0
try:
with tempfile.TemporaryDirectory() as temp_dir:
with zipfile.ZipFile(original, "r") as zip_ref:
safe_extract(zip_ref, Path(temp_dir))
doc_xml_path = temp_dir + "/word/document.xml"
root = lxml.etree.parse(doc_xml_path).getroot()
paragraphs = root.findall(f".//{{{self.WORD_2006_NAMESPACE}}}p")
count = len(paragraphs)
except Exception as e:
print(f"Error counting paragraphs in original document: {e}")
return count
def validate_insertions(self):
errors = []
for xml_file in self.xml_files:
if xml_file.name != "document.xml":
continue
try:
root = lxml.etree.parse(str(xml_file)).getroot()
namespaces = {"w": self.WORD_2006_NAMESPACE}
invalid_elements = root.xpath(
".//w:ins//w:delText[not(ancestor::w:del)]", namespaces=namespaces
)
for elem in invalid_elements:
text_preview = (
repr(elem.text or "")[:50] + "..."
if len(repr(elem.text or "")) > 50
else repr(elem.text or "")
)
errors.append(
f" {xml_file.relative_to(self.unpacked_dir)}: "
f"Line {elem.sourceline}: <w:delText> within <w:ins>: {text_preview}"
)
except (lxml.etree.XMLSyntaxError, Exception) as e:
errors.append(
f" {xml_file.relative_to(self.unpacked_dir)}: Error: {e}"
)
if errors:
print(f"FAILED - Found {len(errors)} insertion validation violations:")
for error in errors:
print(error)
return False
else:
if self.verbose:
print("PASSED - No w:delText elements within w:ins elements")
return True
def compare_paragraph_counts(self):
new_count = self.count_paragraphs_in_unpacked()
if self.original_file is None:
print(f"\nParagraphs: {new_count}")
return
original_count = self.count_paragraphs_in_original()
diff = new_count - original_count
diff_str = f"+{diff}" if diff > 0 else str(diff)
print(f"\nParagraphs: {original_count} → {new_count} ({diff_str})")
def _parse_id_value(self, val: str, base: int = 16) -> int:
return int(val, base)
def validate_id_constraints(self):
errors = []
para_id_attr = f"{{{self.W14_NAMESPACE}}}paraId"
durable_id_attr = f"{{{self.W16CID_NAMESPACE}}}durableId"
for xml_file in self.xml_files:
try:
for elem in lxml.etree.parse(str(xml_file)).iter():
if val := elem.get(para_id_attr):
try:
if self._parse_id_value(val, base=16) >= 0x80000000:
errors.append(
f" {xml_file.name}:{elem.sourceline}: paraId={val} >= 0x80000000"
)
except ValueError:
errors.append(
f" {xml_file.name}:{elem.sourceline}: "
f"paraId={val} is not valid hex"
)
if val := elem.get(durable_id_attr):
if xml_file.name == "numbering.xml":
try:
if self._parse_id_value(val, base=10) >= 0x7FFFFFFF:
errors.append(
f" {xml_file.name}:{elem.sourceline}: "
f"durableId={val} >= 0x7FFFFFFF"
)
except ValueError:
errors.append(
f" {xml_file.name}:{elem.sourceline}: "
f"durableId={val} must be decimal in numbering.xml"
)
else:
try:
if self._parse_id_value(val, base=16) >= 0x7FFFFFFF:
errors.append(
f" {xml_file.name}:{elem.sourceline}: "
f"durableId={val} >= 0x7FFFFFFF"
)
except ValueError:
errors.append(
f" {xml_file.name}:{elem.sourceline}: "
f"durableId={val} is not valid hex"
)
except lxml.etree.XMLSyntaxError:
continue
if errors:
print(f"FAILED - {len(errors)} ID constraint violations:")
for e in errors:
print(e)
elif self.verbose:
print("PASSED - All paraId/durableId values within constraints")
return not errors
def validate_comment_markers(self):
errors = []
document_xml = None
comments_xml = None
for xml_file in self.xml_files:
if xml_file.name == "document.xml" and "word" in str(xml_file):
document_xml = xml_file
elif xml_file.name == "comments.xml":
comments_xml = xml_file
if not document_xml:
if self.verbose:
print("PASSED - No document.xml found (skipping comment validation)")
return True
try:
doc_root = lxml.etree.parse(str(document_xml)).getroot()
namespaces = {"w": self.WORD_2006_NAMESPACE}
range_starts = {
elem.get(f"{{{self.WORD_2006_NAMESPACE}}}id")
for elem in doc_root.xpath(
".//w:commentRangeStart", namespaces=namespaces
)
}
range_ends = {
elem.get(f"{{{self.WORD_2006_NAMESPACE}}}id")
for elem in doc_root.xpath(
".//w:commentRangeEnd", namespaces=namespaces
)
}
references = {
elem.get(f"{{{self.WORD_2006_NAMESPACE}}}id")
for elem in doc_root.xpath(
".//w:commentReference", namespaces=namespaces
)
}
orphaned_ends = range_ends - range_starts
for comment_id in sorted(
orphaned_ends, key=lambda x: int(x) if x and x.isdigit() else 0
):
errors.append(
f' document.xml: commentRangeEnd id="{comment_id}" has no matching commentRangeStart'
)
orphaned_starts = range_starts - range_ends
for comment_id in sorted(
orphaned_starts, key=lambda x: int(x) if x and x.isdigit() else 0
):
errors.append(
f' document.xml: commentRangeStart id="{comment_id}" has no matching commentRangeEnd'
)
comment_ids = set()
if comments_xml and comments_xml.exists():
comments_root = lxml.etree.parse(str(comments_xml)).getroot()
comment_ids = {
elem.get(f"{{{self.WORD_2006_NAMESPACE}}}id")
for elem in comments_root.xpath(
".//w:comment", namespaces=namespaces
)
}
marker_ids = range_starts | range_ends | references
invalid_refs = marker_ids - comment_ids
for comment_id in sorted(
invalid_refs, key=lambda x: int(x) if x and x.isdigit() else 0
):
if comment_id:
errors.append(
f' document.xml: marker id="{comment_id}" references non-existent comment'
)
except (lxml.etree.XMLSyntaxError, Exception) as e:
errors.append(f" Error parsing XML: {e}")
if errors:
print(f"FAILED - {len(errors)} comment marker violations:")
for error in errors:
print(error)
return False
else:
if self.verbose:
print("PASSED - All comment markers properly paired")
return True
def repair(self) -> int:
repairs = super().repair()
repairs += self.repair_durableId()
return repairs
def repair_durableId(self) -> int:
DURABLE_ID_ATTRS = ("w16cid:durableId", "w16cex:durableId")
repairs = 0
renames: dict = {}
for xml_file in self.xml_files:
try:
content = xml_file.read_text(encoding="utf-8")
dom = defusedxml.minidom.parseString(content)
is_numbering = xml_file.name == "numbering.xml"
base = 10 if is_numbering else 16
pending = []
seen_in_file = set()
modified = False
for elem in dom.getElementsByTagName("*"):
for attr_name in DURABLE_ID_ATTRS:
if not elem.hasAttribute(attr_name):
continue
durable_id = elem.getAttribute(attr_name)
try:
key = self._parse_id_value(durable_id, base=base)
needs_repair = key >= 0x7FFFFFFF
except ValueError:
key = durable_id
needs_repair = True
if needs_repair:
if key in seen_in_file:
value = random.randint(1, 0x7FFFFFFE)
else:
seen_in_file.add(key)
if key not in renames:
renames[key] = random.randint(1, 0x7FFFFFFE)
value = renames[key]
new_id = str(value) if is_numbering else f"{value:08X}"
elem.setAttribute(attr_name, new_id)
pending.append(
f" Repaired: {xml_file.name}: durableId {durable_id} → {new_id}"
)
modified = True
if modified:
xml_file.write_bytes(dom.toxml(encoding="UTF-8"))
for message in pending:
print(message)
repairs += len(pending)
except Exception:
pass
return repairs
if __name__ == "__main__":
raise RuntimeError("This module should not be run directly.")
scripts/office/validators/pptx.py
"""
Validator for PowerPoint presentation XML files against XSD schemas.
"""
import re
from pathlib import Path
from helpers import opc_target, rels_source_part, safe_extract
from .base import BaseSchemaValidator
class PPTXSchemaValidator(BaseSchemaValidator):
PRESENTATIONML_NAMESPACE = (
"http://schemas.openxmlformats.org/presentationml/2006/main"
)
ELEMENT_RELATIONSHIP_TYPES = {
"sldid": "slide",
"sldmasterid": "slidemaster",
"notesmasterid": "notesmaster",
"sldlayoutid": "slidelayout",
"themeid": "theme",
"tablestyleid": "tablestyles",
}
def validate(self):
if not self.validate_xml():
return False
all_valid = True
if not self.validate_namespaces():
all_valid = False
if not self.validate_unique_ids():
all_valid = False
if not self.validate_uuid_ids():
all_valid = False
if not self.validate_file_references():
all_valid = False
if not self.validate_slide_layout_ids():
all_valid = False
if not self.validate_content_types():
all_valid = False
if not self.validate_against_xsd():
all_valid = False
if not self.validate_notes_slide_references():
all_valid = False
if not self.validate_all_relationship_ids():
all_valid = False
if not self.validate_no_duplicate_slide_layouts():
all_valid = False
if not self.validate_master_theme_uniqueness():
all_valid = False
if not self.validate_charts():
all_valid = False
if not self.validate_slides():
all_valid = False
return all_valid
def _package_map(self) -> dict:
wanted = []
wanted += list(self.unpacked_dir.glob("[[]Content_Types[]].xml"))
wanted += list(self.unpacked_dir.glob("ppt/presentation.xml"))
wanted += list(self.unpacked_dir.glob("ppt/theme/*.xml"))
wanted += list(self.unpacked_dir.glob("ppt/theme/_rels/*.rels"))
wanted += list(self.unpacked_dir.glob("ppt/charts/chart*.xml"))
for group in ("slideMasters", "notesMasters", "handoutMasters"):
wanted += list(self.unpacked_dir.glob(f"ppt/{group}/*.xml"))
wanted += list(self.unpacked_dir.glob(f"ppt/{group}/_rels/*.rels"))
return {
p.relative_to(self.unpacked_dir).as_posix(): p.read_bytes()
for p in wanted
if p.is_file()
}
def validate_master_theme_uniqueness(self):
from helpers.pptx_theme import _NOTES_MASTERS, live_shared_master_themes
shared = live_shared_master_themes(self._package_map())
if shared:
print(f"FAILED - Found {len(shared)} master(s) sharing a theme part:")
for message in shared:
print(f" {message}")
if any(m.startswith(_NOTES_MASTERS) for m in shared):
print(" Fix: in ppt/presentation.xml, move <p:notesMasterIdLst> back to "
"directly after <p:sldIdLst>. PowerPoint reads that happily.")
else:
print(" Fix: give each master its own theme part.")
return False
if self.verbose:
print("PASSED - No master shares a theme part in a way PowerPoint refuses")
return True
def validate_charts(self):
from helpers.pptx_chart import find_chart_problems
problems = find_chart_problems(self._package_map())
if problems:
print(f"FAILED - Found {len(problems)} chart problem(s) PowerPoint rejects:")
for message in problems:
print(f" {message}")
return False
if self.verbose:
print("PASSED - Charts satisfy the constraints PowerPoint enforces")
return True
def _original_slide_defects(self, schema) -> set[str]:
import tempfile
import zipfile
from helpers.pptx_slide import SLIDE_PART_RE, fatal_slide_errors
if self.original_file is None:
return set()
found: set[str] = set()
with tempfile.TemporaryDirectory() as temp_dir:
temp_path = Path(temp_dir)
try:
with zipfile.ZipFile(self.original_file, "r") as zf:
safe_extract(zf, temp_path)
except (zipfile.BadZipFile, ValueError, OSError):
return set()
for part in sorted(temp_path.rglob("*.xml")):
relative = part.relative_to(temp_path).as_posix()
if not SLIDE_PART_RE.fullmatch(relative):
continue
ok, errors = self._validate_single_file_xsd(
part.resolve(), temp_path.resolve(), schema_path=schema
)
if ok is None or ok or not errors:
continue
found |= set(fatal_slide_errors(set(errors)))
return found
def validate_slides(self):
from helpers.pptx_slide import (
SLIDE_PART_RE,
fatal_slide_errors,
is_schema_verdict,
)
schema = self.schemas_dir / self.SCHEMA_MAPPINGS["ppt"]
inherited = self._original_slide_defects(schema)
problems: list[str] = []
broken: list[str] = []
for xml_file in self.xml_files:
relative = xml_file.relative_to(self.unpacked_dir).as_posix()
if not SLIDE_PART_RE.fullmatch(relative):
continue
ok, errors = self._validate_single_file_xsd(
xml_file.resolve(), self.unpacked_dir.resolve(), schema_path=schema
)
if ok is None or not errors:
continue
unreadable = [f"{relative}: {e}" for e in errors if not is_schema_verdict(e)]
if unreadable:
broken.extend(unreadable)
continue
if ok:
continue
for message in fatal_slide_errors(set(errors)):
if message in inherited:
continue
problems.append(f"{relative}: {message}")
if broken:
print(f"FAILED - Could not check {len(broken)} slide part(s):")
for message in sorted(broken):
print(f" {message[:240]}")
if problems:
print(f"FAILED - Found {len(problems)} slide problem(s) PowerPoint rejects:")
for message in sorted(problems):
print(f" {message[:240]}")
if broken or problems:
return False
if self.verbose:
print("PASSED - Slide XML has none of the defects PowerPoint refuses")
return True
def _get_schema_path(self, xml_file):
if xml_file.parent.name == "charts" and xml_file.name.startswith("chart"):
return None
return super()._get_schema_path(xml_file)
def _preprocess_for_schema(self, xml_doc, relative_path):
if relative_path.as_posix() != "ppt/presentation.xml":
return xml_doc
root = xml_doc.getroot()
ns = f"{{{self.PRESENTATIONML_NAMESPACE}}}"
notes = root.find(f"{ns}notesMasterIdLst")
slides = root.find(f"{ns}sldIdLst")
if notes is None or slides is None:
return xml_doc
children = list(root)
if children.index(notes) < children.index(slides):
return xml_doc
root.remove(notes)
root.insert(list(root).index(slides), notes)
return xml_doc
def validate_uuid_ids(self):
import lxml.etree
errors = []
uuid_pattern = re.compile(
r"^[\{\(]?[0-9A-Fa-f]{8}-?[0-9A-Fa-f]{4}-?[0-9A-Fa-f]{4}-?[0-9A-Fa-f]{4}-?[0-9A-Fa-f]{12}[\}\)]?$"
)
for xml_file in self.xml_files:
try:
root = lxml.etree.parse(str(xml_file)).getroot()
for elem in root.iter():
for attr, value in elem.attrib.items():
attr_name = attr.split("}")[-1].lower()
if attr_name == "id" or attr_name.endswith("id"):
if self._looks_like_uuid(value):
if not uuid_pattern.match(value):
errors.append(
f" {xml_file.relative_to(self.unpacked_dir)}: "
f"Line {elem.sourceline}: ID '{value}' appears to be a UUID but contains invalid hex characters"
)
except (lxml.etree.XMLSyntaxError, Exception) as e:
errors.append(
f" {xml_file.relative_to(self.unpacked_dir)}: Error: {e}"
)
if errors:
print(f"FAILED - Found {len(errors)} UUID ID validation errors:")
for error in errors:
print(error)
return False
else:
if self.verbose:
print("PASSED - All UUID-like IDs contain valid hex values")
return True
def _looks_like_uuid(self, value):
clean_value = value.strip("{}()").replace("-", "")
return len(clean_value) == 32 and all(c.isalnum() for c in clean_value)
def validate_slide_layout_ids(self):
import lxml.etree
errors = []
slide_masters = list(self.unpacked_dir.glob("ppt/slideMasters/*.xml"))
if not slide_masters:
if self.verbose:
print("PASSED - No slide masters found")
return True
for slide_master in slide_masters:
try:
root = lxml.etree.parse(str(slide_master)).getroot()
rels_file = slide_master.parent / "_rels" / f"{slide_master.name}.rels"
if not rels_file.exists():
errors.append(
f" {slide_master.relative_to(self.unpacked_dir)}: "
f"Missing relationships file: {rels_file.relative_to(self.unpacked_dir)}"
)
continue
rels_root = lxml.etree.parse(str(rels_file)).getroot()
valid_layout_rids = set()
for rel in rels_root.findall(
f".//{{{self.PACKAGE_RELATIONSHIPS_NAMESPACE}}}Relationship"
):
rel_type = rel.get("Type", "")
if "slideLayout" in rel_type:
valid_layout_rids.add(rel.get("Id"))
for sld_layout_id in root.findall(
f".//{{{self.PRESENTATIONML_NAMESPACE}}}sldLayoutId"
):
r_id = sld_layout_id.get(
f"{{{self.OFFICE_RELATIONSHIPS_NAMESPACE}}}id"
)
layout_id = sld_layout_id.get("id")
if r_id and r_id not in valid_layout_rids:
errors.append(
f" {slide_master.relative_to(self.unpacked_dir)}: "
f"Line {sld_layout_id.sourceline}: sldLayoutId with id='{layout_id}' "
f"references r:id='{r_id}' which is not found in slide layout relationships"
)
except (lxml.etree.XMLSyntaxError, Exception) as e:
errors.append(
f" {slide_master.relative_to(self.unpacked_dir)}: Error: {e}"
)
if errors:
print(f"FAILED - Found {len(errors)} slide layout ID validation errors:")
for error in errors:
print(error)
print(
"Remove invalid references or add missing slide layouts to the relationships file."
)
return False
else:
if self.verbose:
print("PASSED - All slide layout IDs reference valid slide layouts")
return True
def validate_no_duplicate_slide_layouts(self):
import lxml.etree
errors = []
slide_rels_files = list(self.unpacked_dir.glob("ppt/slides/_rels/*.xml.rels"))
for rels_file in slide_rels_files:
try:
root = lxml.etree.parse(str(rels_file)).getroot()
layout_rels = [
rel
for rel in root.findall(
f".//{{{self.PACKAGE_RELATIONSHIPS_NAMESPACE}}}Relationship"
)
if "slideLayout" in rel.get("Type", "")
]
if len(layout_rels) > 1:
errors.append(
f" {rels_file.relative_to(self.unpacked_dir)}: has {len(layout_rels)} slideLayout references"
)
except Exception as e:
errors.append(
f" {rels_file.relative_to(self.unpacked_dir)}: Error: {e}"
)
if errors:
print("FAILED - Found slides with duplicate slideLayout references:")
for error in errors:
print(error)
return False
else:
if self.verbose:
print("PASSED - All slides have exactly one slideLayout reference")
return True
def validate_notes_slide_references(self):
import lxml.etree
errors = []
notes_slide_references = {}
slide_rels_files = list(self.unpacked_dir.glob("ppt/slides/_rels/*.xml.rels"))
if not slide_rels_files:
if self.verbose:
print("PASSED - No slide relationship files found")
return True
for rels_file in slide_rels_files:
try:
root = lxml.etree.parse(str(rels_file)).getroot()
for rel in root.findall(
f".//{{{self.PACKAGE_RELATIONSHIPS_NAMESPACE}}}Relationship"
):
rel_type = rel.get("Type", "")
if "notesSlide" in rel_type:
part = opc_target(
rel.get("Target", ""),
rels_source_part(rels_file, self.unpacked_dir),
rel.get("TargetMode", ""),
)
if part:
slide_name = rels_file.stem.replace(
".xml", ""
)
notes_slide_references.setdefault(part, []).append(
(slide_name, rels_file)
)
except (lxml.etree.XMLSyntaxError, Exception) as e:
errors.append(
f" {rels_file.relative_to(self.unpacked_dir)}: Error: {e}"
)
for target, references in notes_slide_references.items():
if len(references) > 1:
slide_names = [ref[0] for ref in references]
errors.append(
f" Notes slide '{target}' is referenced by multiple slides: {', '.join(slide_names)}"
)
for slide_name, rels_file in references:
errors.append(f" - {rels_file.relative_to(self.unpacked_dir)}")
if errors:
print(
f"FAILED - Found {len([e for e in errors if not e.startswith(' ')])} notes slide reference validation errors:"
)
for error in errors:
print(error)
print("Each slide may optionally have its own slide file.")
return False
else:
if self.verbose:
print("PASSED - All notes slide references are unique")
return True
if __name__ == "__main__":
raise RuntimeError("This module should not be run directly.")
scripts/office/validators/redlining.py
"""
Validator for tracked changes in Word documents.
Detects untracked edits in word/document.xml: text that differs from the
original without a <w:ins>/<w:del> wrapper recording it. The tracked changes
that are new relative to the original are undone, and the result is compared
against the original; whatever text still differs was edited without being
tracked.
Only the document body is compared. Headers, footers, footnotes and endnotes
are separate parts and are not checked.
"""
import subprocess
import tempfile
import zipfile
from pathlib import Path
import defusedxml.ElementTree as ET
from defusedxml.common import DefusedXmlException
from helpers import rendered_text, safe_extract
class RedliningValidator:
def __init__(self, unpacked_dir, original_docx, verbose=False):
self.unpacked_dir = Path(unpacked_dir)
self.original_docx = Path(original_docx)
self.verbose = verbose
self.namespaces = {
"w": "http://schemas.openxmlformats.org/wordprocessingml/2006/main"
}
def repair(self) -> int:
return 0
def validate(self):
modified_file = self.unpacked_dir / "word" / "document.xml"
if not modified_file.exists():
print(f"FAILED - Modified document.xml not found at {modified_file}")
return False
with tempfile.TemporaryDirectory() as temp_dir:
temp_path = Path(temp_dir)
try:
with zipfile.ZipFile(self.original_docx, "r") as zip_ref:
safe_extract(zip_ref, temp_path)
except Exception as e:
print(f"FAILED - Error unpacking original docx: {e}")
return False
original_file = temp_path / "word" / "document.xml"
if not original_file.exists():
print(
f"FAILED - Original document.xml not found in {self.original_docx}"
)
return False
try:
modified_tree = ET.parse(modified_file)
modified_root = modified_tree.getroot()
original_tree = ET.parse(original_file)
original_root = original_tree.getroot()
except (ET.ParseError, DefusedXmlException) as e:
print(f"FAILED - Error parsing XML files: {e}")
return False
new_changes = self._new_tracked_changes(original_root, modified_root)
self._remove_tracked_changes(modified_root, new_changes)
modified_text = self._extract_text_content(modified_root)
original_text = self._extract_text_content(original_root)
if modified_text != original_text:
error_message = self._generate_detailed_diff(
original_text, modified_text
)
print(error_message)
return False
if self.verbose:
print(
f"PASSED - All {len(new_changes)} change(s) against the original "
"are properly tracked"
)
return True
def _tracked_change_elements(self, root):
ins_tag = f"{{{self.namespaces['w']}}}ins"
del_tag = f"{{{self.namespaces['w']}}}del"
return [elem for elem in root.iter() if elem.tag in (ins_tag, del_tag)]
def _rendered_text(self, elem):
preserve = elem.get("{http://www.w3.org/XML/1998/namespace}space") == "preserve"
return rendered_text(elem.text or "", preserve)
def _text_elements(self, elem):
w = self.namespaces["w"]
return [
node
for node in elem.iter()
if node.tag in (f"{{{w}}}t", f"{{{w}}}delText")
]
def _tracked_change_key(self, elem):
w = self.namespaces["w"]
text = "".join(self._rendered_text(node) for node in self._text_elements(elem))
return (elem.tag, elem.get(f"{{{w}}}author"), elem.get(f"{{{w}}}date"), text)
def _new_tracked_changes(self, original_root, modified_root):
original = self._tracked_change_elements(original_root)
modified = self._tracked_change_elements(modified_root)
pool = {}
for elem in original:
pool.setdefault(self._tracked_change_key(elem), []).append(elem)
matched, leftover = set(), []
for elem in modified:
bucket = pool.get(self._tracked_change_key(elem))
if bucket:
matched.add(bucket.pop())
else:
leftover.append(elem)
def group(elem):
return self._tracked_change_key(elem)[:3]
def text_of(elems):
return "".join(self._tracked_change_key(e)[3] for e in elems)
unmatched_original = {}
for elem in original:
if elem not in matched:
unmatched_original.setdefault(group(elem), []).append(elem)
by_group = {}
for elem in leftover:
by_group.setdefault(group(elem), []).append(elem)
new = set()
for key, elems in by_group.items():
rebuilt = text_of(elems)
if rebuilt and rebuilt == text_of(unmatched_original.get(key, [])):
continue
new.update(elems)
return new
def _generate_detailed_diff(self, original_text, modified_text):
error_parts = [
"FAILED - Document text doesn't match after removing the tracked changes",
"",
"Likely causes:",
" 1. Modified text inside another author's <w:ins> or <w:del> tags",
" 2. Made edits without proper tracked changes",
" 3. Didn't nest <w:del> inside <w:ins> when deleting another's insertion",
" 4. Rewrote another author's <w:ins>/<w:del> and changed its text on",
" the way. A tracked change from the original is recognised by its",
" author, date and text; anything that doesn't reproduce one exactly",
" reads as new, and the text it carried is reported missing.",
"",
"For pre-redlined documents, use correct patterns:",
" - To reject another's INSERTION: Nest <w:del> inside their <w:ins>",
" - To reject PART of one: nest <w:del> around only the runs you reject.",
" Their <w:ins> may be split around it, so long as the pieces keep",
" their author and date and still spell out the same text.",
" - To restore another's DELETION: Add new <w:ins> AFTER their <w:del>",
"",
]
git_diff = self._get_git_word_diff(original_text, modified_text)
if git_diff:
error_parts.extend(["Differences:", "============", git_diff])
else:
error_parts.append("Unable to generate word diff (git not available)")
return "\n".join(error_parts)
def _get_git_word_diff(self, original_text, modified_text):
try:
with tempfile.TemporaryDirectory() as temp_dir:
temp_path = Path(temp_dir)
original_file = temp_path / "original.txt"
modified_file = temp_path / "modified.txt"
original_file.write_text(original_text, encoding="utf-8")
modified_file.write_text(modified_text, encoding="utf-8")
result = subprocess.run(
[
"git",
"diff",
"--word-diff=plain",
"--word-diff-regex=.",
"-U0",
"--no-index",
str(original_file),
str(modified_file),
],
capture_output=True,
text=True,
)
if result.stdout.strip():
lines = result.stdout.split("\n")
content_lines = []
in_content = False
for line in lines:
if line.startswith("@@"):
in_content = True
continue
if in_content and line.strip():
content_lines.append(line)
if content_lines:
return "\n".join(content_lines)
result = subprocess.run(
[
"git",
"diff",
"--word-diff=plain",
"-U0",
"--no-index",
str(original_file),
str(modified_file),
],
capture_output=True,
text=True,
)
if result.stdout.strip():
lines = result.stdout.split("\n")
content_lines = []
in_content = False
for line in lines:
if line.startswith("@@"):
in_content = True
continue
if in_content and line.strip():
content_lines.append(line)
return "\n".join(content_lines)
except (subprocess.CalledProcessError, FileNotFoundError, Exception):
pass
return None
def _remove_tracked_changes(self, root, targets):
ins_tag = f"{{{self.namespaces['w']}}}ins"
del_tag = f"{{{self.namespaces['w']}}}del"
for parent in root.iter():
to_remove = []
for child in parent:
if child.tag == ins_tag and child in targets:
to_remove.append(child)
for elem in to_remove:
parent.remove(elem)
deltext_tag = f"{{{self.namespaces['w']}}}delText"
t_tag = f"{{{self.namespaces['w']}}}t"
for parent in root.iter():
to_process = []
for child in parent:
if child.tag == del_tag and child in targets:
to_process.append((child, list(parent).index(child)))
for del_elem, del_index in reversed(to_process):
for elem in del_elem.iter():
if elem.tag == deltext_tag:
elem.tag = t_tag
for child in reversed(list(del_elem)):
parent.insert(del_index, child)
parent.remove(del_elem)
def _extract_text_content(self, root):
p_tag = f"{{{self.namespaces['w']}}}p"
t_tag = f"{{{self.namespaces['w']}}}t"
paragraphs = []
for p_elem in root.findall(f".//{p_tag}"):
text_parts = []
for t_elem in p_elem.findall(f".//{t_tag}"):
text_parts.append(self._rendered_text(t_elem))
paragraph_text = "".join(text_parts)
if paragraph_text:
paragraphs.append(paragraph_text)
return "\n".join(paragraphs)
if __name__ == "__main__":
raise RuntimeError("This module should not be run directly.")
scripts/recalc.py
"""
Excel Formula Recalculation Script
Recalculates all formulas in an Excel file using LibreOffice
"""
import contextlib
import json
import os
import platform
import re
import shutil
import subprocess
import sys
import tempfile
import time
import zipfile
from pathlib import Path
from office.soffice import get_soffice_env, run_soffice
from openpyxl import load_workbook
from openpyxl.worksheet.formula import ArrayFormula
MACRO_FILENAME = "Module1.xba"
SOFFICE_MISSING = "soffice not found on PATH; LibreOffice is required to recalculate"
MAX_LOCATIONS = 100
EXTERNAL_REF_RE = re.compile(r"""(?<![\w"\[])'?\[\d+\][^!"\[\]]*'?!""")
RECALCULATE_MACRO = """<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE script:module PUBLIC "-//OpenOffice.org//DTD OfficeDocument 1.0//EN" "module.dtd">
<script:module xmlns:script="http://openoffice.org/2000/script" script:name="Module1" script:language="StarBasic">
Sub RecalculateAndSave()
ThisComponent.calculateAll()
ThisComponent.store()
ThisComponent.close(True)
End Sub
</script:module>"""
def has_gtimeout():
try:
subprocess.run(
["gtimeout", "--version"], capture_output=True, timeout=1, check=False
)
return True
except (FileNotFoundError, subprocess.TimeoutExpired):
return False
def _stamp(path):
st = os.stat(path)
return st.st_mtime_ns, st.st_size
def setup_libreoffice_macro(profile_dir: Path, timeout=30):
url = profile_dir.as_uri()
try:
run_soffice(
["--headless", "--terminate_after_init", f"-env:UserInstallation={url}"],
capture_output=True,
timeout=timeout,
)
except FileNotFoundError:
return None, SOFFICE_MISSING
except subprocess.TimeoutExpired:
return None, "LibreOffice timed out creating its profile; formulas were NOT recalculated"
macro_dir = profile_dir / "user" / "basic" / "Standard"
if not macro_dir.exists():
return None, "LibreOffice did not create a usable profile; formulas were NOT recalculated"
try:
(macro_dir / MACRO_FILENAME).write_text(RECALCULATE_MACRO)
except OSError as e:
return None, f"Could not install the recalculation macro: {e}"
return url, None
def _names_pattern(names):
if not names:
return None
return re.compile(r"\b(" + "|".join(re.escape(n) for n in sorted(names)) + r")\b")
def external_links_at_risk(filename):
try:
with zipfile.ZipFile(filename) as archive:
names = archive.namelist()
except (zipfile.BadZipFile, OSError):
return []
if not any(n.startswith("xl/externalLinks/") for n in names):
return []
with contextlib.ExitStack() as stack:
formulas = load_workbook(filename, data_only=False)
stack.callback(formulas.close)
values = load_workbook(filename, data_only=True)
stack.callback(values.close)
defined_names = list(formulas.defined_names.items())
for scope_ws in formulas.worksheets:
defined_names.extend(scope_ws.defined_names.items())
name_texts = [
(name, dn.value)
for name, dn in defined_names
if isinstance(getattr(dn, "value", None), str)
]
external_names = {name for name, text in name_texts if EXTERNAL_REF_RE.search(text)}
while True:
name_re = _names_pattern(external_names)
if name_re is None:
break
added = {
name
for name, text in name_texts
if name not in external_names and name_re.search(text)
}
if not added:
break
external_names |= added
name_re = _names_pattern(external_names)
at_risk = []
for sheet in formulas.sheetnames:
ws = formulas[sheet]
if not hasattr(ws, "iter_rows"):
continue
cached = values[sheet]
for row in ws.iter_rows():
for cell in row:
v = cell.value
if isinstance(v, ArrayFormula):
v = v.text
if not (isinstance(v, str) and v.startswith("=")):
continue
reaches_out = EXTERNAL_REF_RE.search(v) or (name_re and name_re.search(v))
if reaches_out and cached[cell.coordinate].value is None:
at_risk.append(f"{sheet}!{cell.coordinate}")
return at_risk
def recalc(filename, timeout=30, force=False):
if not Path(filename).exists():
return {"error": f"File {filename} does not exist"}
abs_path = str(Path(filename).absolute())
if not os.access(abs_path, os.W_OK):
return {"error": f"{filename} is not writable; recalculation rewrites the file in place"}
try:
get_soffice_env()
except Exception as e:
return {"error": f"Could not prepare the LibreOffice environment: {e}"}
if not force:
try:
at_risk = external_links_at_risk(filename)
except Exception as e:
return {"error": f"Could not inspect {filename} for external links: {e}"}
if at_risk:
shown = at_risk[:MAX_LOCATIONS]
return {
"error": (
"Refusing to recalculate: this workbook links to another workbook, and "
f"{len(at_risk)} linked cell(s) have lost their cached value (openpyxl strips "
"these on save). Recalculating would resolve them to #NAME? and delete the "
"external links for good. Copy those cells' values from the original file "
"before saving, or pass --force to accept the loss. Charts and conditional "
"formats can hold external references too, so this list may not be exhaustive."
),
"external_link_cells": shown,
"external_link_cells_truncated": max(0, len(at_risk) - len(shown)),
}
with tempfile.TemporaryDirectory(
prefix="recalc-lo-profile-", ignore_cleanup_errors=True
) as profile_dir:
return _recalc_with_profile(filename, abs_path, timeout, Path(profile_dir))
def _recalc_with_profile(filename, abs_path, timeout, profile_dir: Path):
started = time.monotonic()
profile_url, err = setup_libreoffice_macro(profile_dir, timeout=timeout)
if err:
return {"error": err}
timeout = max(5, int(timeout - (time.monotonic() - started)))
before = _stamp(abs_path)
cmd = [
"soffice",
"--headless",
"--norestore",
f"-env:UserInstallation={profile_url}",
"vnd.sun.star.script:Standard.Module1.RecalculateAndSave?language=Basic&location=application",
abs_path,
]
if platform.system() == "Linux" and shutil.which("timeout"):
cmd = ["timeout", str(timeout)] + cmd
elif platform.system() == "Darwin" and has_gtimeout():
cmd = ["gtimeout", str(timeout)] + cmd
timed_out = f"LibreOffice timed out after {timeout}s; formulas were NOT recalculated. Re-run with a longer timeout."
try:
result = subprocess.run(
cmd, capture_output=True, text=True, env=get_soffice_env(), timeout=timeout + 15
)
except subprocess.TimeoutExpired:
return {"error": timed_out}
except FileNotFoundError:
return {"error": SOFFICE_MISSING}
if result.returncode == 124:
return {"error": timed_out}
if result.returncode != 0:
detail = (result.stderr or "").strip() or f"soffice exited {result.returncode}"
return {"error": f"LibreOffice failed to recalculate: {detail}"}
if _stamp(abs_path) == before:
return {
"error": (
"LibreOffice exited cleanly but never rewrote the file, so nothing was "
"recalculated. Check that no other LibreOffice instance is running, then retry."
)
}
try:
wb = load_workbook(filename, data_only=True)
excel_errors = [
"#VALUE!",
"#DIV/0!",
"#REF!",
"#NAME?",
"#NULL!",
"#NUM!",
"#N/A",
]
error_details = {err: [] for err in excel_errors}
total_errors = 0
for sheet_name in wb.sheetnames:
ws = wb[sheet_name]
if not hasattr(ws, "iter_rows"):
continue
for row in ws.iter_rows():
for cell in row:
if cell.value is not None and isinstance(cell.value, str):
for err in excel_errors:
if err in cell.value:
location = f"{sheet_name}!{cell.coordinate}"
error_details[err].append(location)
total_errors += 1
break
result = {
"status": "success" if total_errors == 0 else "errors_found",
"total_errors": total_errors,
"error_summary": {},
}
for err_type, locations in error_details.items():
if locations:
entry = {"count": len(locations), "locations": locations[:MAX_LOCATIONS]}
if len(locations) > MAX_LOCATIONS:
entry["locations_truncated"] = len(locations) - MAX_LOCATIONS
result["error_summary"][err_type] = entry
wb.close()
wb_formulas = load_workbook(filename, data_only=False)
formula_count = 0
for sheet_name in wb_formulas.sheetnames:
ws = wb_formulas[sheet_name]
if not hasattr(ws, "iter_rows"):
continue
for row in ws.iter_rows():
for cell in row:
v = cell.value
if isinstance(v, ArrayFormula):
v = v.text
if isinstance(v, str) and v.startswith("="):
formula_count += 1
wb_formulas.close()
result["total_formulas"] = formula_count
return result
except Exception as e:
return {"error": str(e)}
def main():
args = [a for a in sys.argv[1:] if a != "--force"]
force = "--force" in sys.argv[1:]
if not args:
print("Usage: python recalc.py <excel_file> [timeout_seconds] [--force]")
print("\nRecalculates all formulas in an Excel file using LibreOffice")
print("\nReturns JSON with error details:")
print(" - status: 'success' or 'errors_found'")
print(" - total_errors: Total number of Excel errors found")
print(" - total_formulas: Number of formulas in the file")
print(" - error_summary: Breakdown by error type with locations")
print(" - #VALUE!, #DIV/0!, #REF!, #NAME?, #NULL!, #NUM!, #N/A")
print("\nOn any failure the JSON has an 'error' key and no 'status'.")
print("--force recalculates even when it would destroy external links.")
sys.exit(1)
filename = args[0]
try:
timeout = int(args[1]) if len(args) > 1 else 30
except ValueError:
result = {"error": f"timeout must be an integer number of seconds, got {args[1]!r}"}
print(json.dumps(result, indent=2))
sys.exit(1)
result = recalc(filename, timeout, force=force)
print(json.dumps(result, indent=2))
sys.exit(1 if "error" in result else 0)
if __name__ == "__main__":
main()