xlsx
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 dat
Install
npx skills add https://github.com/fcakyon/claude-codex-settings/tree/main/plugins/anthropic-office-skills/skills/xlsx
claude plugin marketplace add https://llmmart.ai/marketplace.json && claude plugin install fcakyon-claude-codex-settings@llmmart
git clone https://github.com/fcakyon/claude-codex-settings.git
The skills CLI installs just this skill, for any of its supported agents. Claude Code installs the whole fcakyon/claude-codex-settings collection as a plugin from our marketplace. Git is the plain clone.
Skill manifest
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, andmarkitdownare preinstalled — do not runpip installfirst; write the script and import directly. Only if an import fails (or themarkitdowncommand is missing):pip installthe 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.pyreportserrors_found. If you think an error predates you, prove it: load the original withdata_only=Trueand 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.
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, orSEQUENCE. 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 — andrecalc.pyreportstotal_errors: 0on the truncated result. UseINDEX/MATCHfor 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=Trueyields cached values with the formulas gone; the default yields formula strings with no values. One pass cannot give you both. data_only=Trueis destructive if you save. That workbook has no formulas left, so saving replaces every one with a literal — permanently.data_only=Trueon a file openpyxl just wrote returnsNoneeverywhere — runrecalc.pyfirst. (A formula whose result is""also reads back asNone.)- Merged cells: write the top-left anchor only. Every other cell in the range is a
MergedCellwhose.valueis read-only. .xlsmloses its macros unless you passkeep_vba=Truetoload_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)
Files (claude-codex-settings)
-
scripts
-
office
-
helpers
-
pptx_chart.py 5.7 KB
"""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 -
pptx_slide.py 1.6 KB
"""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 -
pptx_theme.py 3.4 KB
"""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) ] -
__init__.py 3.3 KB
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 if _SCHEME_RE.match(target): return None target = urllib.parse.unquote(target) 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) return posixpath.join(owner_dir.as_posix(), rels_file.name[: -len(".rels")]).lstrip("./") 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()
-
-
schemas
-
ecma
-
fouth-edition
-
opc-contentTypes.xsd 1.9 KB · in bundle
-
opc-coreProperties.xsd 2.5 KB · in bundle
-
opc-digSig.xsd 2.8 KB · in bundle
-
opc-relationships.xsd 1.3 KB · in bundle
-
-
-
ISO-IEC29500-4_2016
-
dml-chart.xsd 73.2 KB · in bundle
-
dml-chartDrawing.xsd 6.8 KB · in bundle
-
dml-diagram.xsd 50.1 KB · in bundle
-
dml-lockedCanvas.xsd 624 B · in bundle
-
dml-main.xsd 148.5 KB · in bundle
-
dml-picture.xsd 1.2 KB · in bundle
-
dml-spreadsheetDrawing.xsd 8.7 KB · in bundle
-
dml-wordprocessingDrawing.xsd 14.4 KB · in bundle
-
pml.xsd 81.7 KB · in bundle
-
shared-additionalCharacteristics.xsd 1.2 KB · in bundle
-
shared-bibliography.xsd 7.2 KB · in bundle
-
shared-commonSimpleTypes.xsd 6.2 KB · in bundle
-
shared-customXmlDataProperties.xsd 1.2 KB · in bundle
-
shared-customXmlSchemaProperties.xsd 880 B · in bundle
-
shared-documentPropertiesCustom.xsd 2.5 KB · in bundle
-
shared-documentPropertiesExtended.xsd 3.4 KB · in bundle
-
shared-documentPropertiesVariantTypes.xsd 7.3 KB · in bundle
-
shared-math.xsd 22.8 KB · in bundle
-
shared-relationshipReference.xsd 1.3 KB · in bundle
-
sml.xsd 236.6 KB · in bundle
-
vml-main.xsd 25.5 KB · in bundle
-
vml-officeDrawing.xsd 24.7 KB · in bundle
-
vml-presentationDrawing.xsd 535 B · in bundle
-
vml-spreadsheetDrawing.xsd 5.6 KB · in bundle
-
vml-wordprocessingDrawing.xsd 3.9 KB · in bundle
-
wml.xsd 167.4 KB · in bundle
-
xml.xsd 4.5 KB · in bundle
-
-
mce
-
mc.xsd 3.1 KB · in bundle
-
-
microsoft
-
wml-2010.xsd 25.9 KB · in bundle
-
wml-2012.xsd 3.7 KB · in bundle
-
wml-2018.xsd 901 B · in bundle
-
wml-cex-2018.xsd 1.7 KB · in bundle
-
wml-cid-2016.xsd 1002 B · in bundle
-
wml-sdtdatahash-2020.xsd 600 B · in bundle
-
wml-symex-2015.xsd 745 B · in bundle
-
-
-
validators
-
base.py 33 KB
""" 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.") -
docx.py 17.1 KB
""" 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.") -
pptx.py 15.6 KB
""" 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.") -
redlining.py 11 KB
""" 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.") -
__init__.py 336 B
""" 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", ]
-
-
soffice.py 5.8 KB
""" 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) -
validate.py 6 KB
""" 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()
-
-
recalc.py 10.4 KB
""" 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 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 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) external_names = [ name for name, dn in formulas.defined_names.items() if isinstance(getattr(dn, "value", None), str) and EXTERNAL_REF_RE.search(dn.value) ] name_re = ( re.compile(r"\b(" + "|".join(re.escape(n) for n in external_names) + r")\b") if external_names else None ) 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 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: if ( cell.value and isinstance(cell.value, str) and cell.value.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] timeout = int(args[1]) if len(args) > 1 else 30 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()
-
-
LICENSE.txt 1.4 KB
© 2025 Anthropic, PBC. All rights reserved. LICENSE: Use of these materials (including all code, prompts, assets, files, and other components of this Skill) is governed by your agreement with Anthropic regarding use of Anthropic's services. If no separate agreement exists, use is governed by Anthropic's Consumer Terms of Service or Commercial Terms of Service, as applicable: https://www.anthropic.com/legal/consumer-terms https://www.anthropic.com/legal/commercial-terms Your applicable agreement is referred to as the "Agreement." "Services" are as defined in the Agreement. ADDITIONAL RESTRICTIONS: Notwithstanding anything in the Agreement to the contrary, users may not: - Extract these materials from the Services or retain copies of these materials outside the Services - Reproduce or copy these materials, except for temporary copies created automatically during authorized use of the Services - Create derivative works based on these materials - Distribute, sublicense, or transfer these materials to any third party - Make, offer to sell, sell, or import any inventions embodied in these materials - Reverse engineer, decompile, or disassemble these materials The receipt, viewing, or possession of these materials does not convey or imply any license or right beyond those expressly granted above. Anthropic retains all right, title, and interest in these materials, including all copyrights, patents, and other intellectual property rights. -
SKILL.md 8.4 KB
--- 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." license: Proprietary. LICENSE.txt has complete terms --- # 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`)
Comments (0)
Sign in to join the conversation.
Reviews (0)
No reviews yet.
No comments yet.