Claude Skill Text

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

Ciza · 0 points · 47 views 140 listing impressions 0 install-command copies
Virus-scanned Reviewed automatically before listing.

Full trust report

Download anthropics-skills-skills_xlsx-f17010c.zip · 148 KB
Part of anthropics/skills — 21 skills

Install

skills CLI npx skills add https://github.com/anthropics/skills/tree/main/skills/xlsx
Claude Code claude plugin marketplace add https://llmmart.ai/marketplace.json && claude plugin install anthropics-skills@llmmart
Git git clone https://github.com/anthropics/skills.git

The skills CLI installs just this skill, for any of its supported agents. Claude Code installs the whole anthropics/skills 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, 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.

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)

Files (skills)
  • 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.

No comments yet.

Reviews (0)

No reviews yet.

Related