Как проверить формулы и структуру XLSX после правки ИИ в Windows

python --version
New-Item -ItemType Directory -Path .\xlsx-formula-check
Set-Location .\xlsx-formula-check
Get-Location

notepad .\compare_xlsx.py

#!/usr/bin/env python3
"""Read-only comparison of worksheet names and formula records in two XLSX files."""

from __future__ import annotations

import argparse
import posixpath
import re
import sys
import zipfile
from dataclasses import dataclass
from pathlib import Path, PurePosixPath
from xml.etree import ElementTree as ET


MAX_ARCHIVE_BYTES = 25 * 1024 * 1024
MAX_ENTRIES = 2_000
MAX_ENTRY_BYTES = 16 * 1024 * 1024
MAX_UNCOMPRESSED_BYTES = 80 * 1024 * 1024
MAX_COMPRESSION_RATIO = 100
MAX_XML_BYTES = 16 * 1024 * 1024

NS_MAIN = "http://schemas.openxmlformats.org/spreadsheetml/2006/main"
NS_REL_DOC = "http://schemas.openxmlformats.org/officeDocument/2006/relationships"
NS_REL_PKG = "http://schemas.openxmlformats.org/package/2006/relationships"
WORKSHEET_REL_SUFFIX = "/worksheet"
FORBIDDEN_XML = re.compile(r"<!\s*(?:DOCTYPE|ENTITY)\b", re.IGNORECASE)
XML_DECLARATION_ENCODING = re.compile(
    r"^\s*<\?xml\b[^>]*\bencoding\s*=\s*['\"]([^'\"]+)['\"]",
    re.IGNORECASE,
)


class UnsafeWorkbook(Exception):
    """The workbook cannot be inspected within this script's safety contract."""


@dataclass(frozen=True)
class Formula:
    coordinate: str
    text: str
    formula_type: str
    shared_index: str
    reference: str


@dataclass(frozen=True)
class Sheet:
    name: str
    sheet_id: str
    target: str
    order: int
    formulas: tuple[Formula, ...]

    @property
    def identity(self) -> tuple[str, str]:
        return self.sheet_id, self.target


def safe_member_name(name: str) -> None:
    if not name or "\\" in name or "\x00" in name:
        raise UnsafeWorkbook(f"опасное имя ZIP-записи: {name!r}")
    path = PurePosixPath(name)
    if path.is_absolute() or any(part in {"", ".", ".."} for part in path.parts):
        raise UnsafeWorkbook(f"опасное имя ZIP-записи: {name!r}")
    if not path.parts:
        raise UnsafeWorkbook(f"опасное имя ZIP-записи: {name!r}")
    if ":" in path.parts[0]:
        raise UnsafeWorkbook(f"опасное имя ZIP-записи: {name!r}")


def validate_archive(path: Path) -> zipfile.ZipFile:
    try:
        size = path.stat().st_size
    except OSError as error:
        raise UnsafeWorkbook(f"не удалось прочитать файл {path}: {error}") from error
    if size > MAX_ARCHIVE_BYTES:
        raise UnsafeWorkbook(f"архив больше лимита {MAX_ARCHIVE_BYTES} байт")
    try:
        archive = zipfile.ZipFile(path, "r")
    except (OSError, zipfile.BadZipFile) as error:
        raise UnsafeWorkbook(f"неверный ZIP/XLSX: {error}") from error

    try:
        infos = archive.infolist()
        if len(infos) > MAX_ENTRIES:
            raise UnsafeWorkbook(f"слишком много ZIP-записей: {len(infos)} > {MAX_ENTRIES}")
        seen: set[str] = set()
        total_size = 0
        total_compressed = 0
        for info in infos:
            safe_member_name(info.filename)
            if info.filename in seen:
                raise UnsafeWorkbook(f"повторяющаяся ZIP-запись: {info.filename}")
            seen.add(info.filename)
            if info.flag_bits & 0x1:
                raise UnsafeWorkbook(f"зашифрованная ZIP-запись не поддерживается: {info.filename}")
            if info.file_size > MAX_ENTRY_BYTES:
                raise UnsafeWorkbook(
                    f"распакованный размер {info.filename} больше {MAX_ENTRY_BYTES} байт"
                )
            total_size += info.file_size
            total_compressed += info.compress_size
            if total_size > MAX_UNCOMPRESSED_BYTES:
                raise UnsafeWorkbook(
                    f"суммарный распакованный размер больше {MAX_UNCOMPRESSED_BYTES} байт"
                )
            if info.file_size and info.compress_size == 0:
                raise UnsafeWorkbook(f"нулевой сжатый размер у непустой записи: {info.filename}")
            if info.compress_size and info.file_size / info.compress_size > MAX_COMPRESSION_RATIO:
                raise UnsafeWorkbook(
                    f"степень сжатия {info.filename} выше {MAX_COMPRESSION_RATIO}:1"
                )
        if total_size and total_compressed and total_size / total_compressed > MAX_COMPRESSION_RATIO:
            raise UnsafeWorkbook(f"общая степень сжатия выше {MAX_COMPRESSION_RATIO}:1")
        return archive
    except Exception:
        archive.close()
        raise


def read_xml(archive: zipfile.ZipFile, name: str) -> ET.Element:
    try:
        info = archive.getinfo(name)
    except KeyError as error:
        raise UnsafeWorkbook(f"нет обязательной части {name}") from error
    if info.file_size > MAX_XML_BYTES:
        raise UnsafeWorkbook(f"XML {name} больше {MAX_XML_BYTES} байт")
    try:
        data = archive.read(info)
    except (OSError, RuntimeError, zipfile.BadZipFile) as error:
        raise UnsafeWorkbook(f"не удалось прочитать {name}: {error}") from error
    if b"\x00" in data:
        raise UnsafeWorkbook(f"XML {name} должен быть в UTF-8, UTF-16/32 не поддерживаются")
    try:
        xml_text = data.decode("utf-8-sig")
    except UnicodeDecodeError as error:
        raise UnsafeWorkbook(f"XML {name} должен быть в UTF-8: {error}") from error
    encoding_match = XML_DECLARATION_ENCODING.match(xml_text)
    if encoding_match and encoding_match.group(1).lower().replace("_", "-") not in {"utf-8", "utf8"}:
        raise UnsafeWorkbook(f"XML {name} объявляет неподдерживаемую кодировку")
    if FORBIDDEN_XML.search(xml_text):
        raise UnsafeWorkbook(f"DOCTYPE/ENTITY запрещены в {name}")
    try:
        return ET.fromstring(xml_text)
    except ET.ParseError as error:
        raise UnsafeWorkbook(f"ошибка XML в {name}: {error}") from error


def normalize_target(base: str, target: str) -> str:
    if not target or "\\" in target or target.startswith("/"):
        raise UnsafeWorkbook(f"опасная цель relationship: {target!r}")
    joined = posixpath.normpath(posixpath.join(posixpath.dirname(base), target))
    safe_member_name(joined)
    if not joined.startswith("xl/"):
        raise UnsafeWorkbook(f"worksheet relationship выходит за xl/: {target!r}")
    return joined


def detect_unsupported_parts(archive: zipfile.ZipFile) -> None:
    names = {item.filename.lower() for item in archive.infolist()}
    if any(name.endswith("vbaproject.bin") for name in names):
        raise UnsafeWorkbook("книги с макросами VBA не поддерживаются")
    if any(name.startswith("xl/externallinks/") for name in names):
        raise UnsafeWorkbook("книги с внешними связями не поддерживаются")


def load_workbook(path: Path) -> tuple[Sheet, ...]:
    with validate_archive(path) as archive:
        detect_unsupported_parts(archive)
        workbook = read_xml(archive, "xl/workbook.xml")
        rels_root = read_xml(archive, "xl/_rels/workbook.xml.rels")
        relationships: dict[str, str] = {}
        seen_relationship_ids: set[str] = set()
        seen_worksheet_targets: set[str] = set()
        for rel in rels_root.findall(f"{{{NS_REL_PKG}}}Relationship"):
            rel_id = rel.get("Id", "")
            rel_type = rel.get("Type", "")
            if not rel_id or rel_id in seen_relationship_ids:
                raise UnsafeWorkbook(f"пустой или повторный relationship Id: {rel_id!r}")
            seen_relationship_ids.add(rel_id)
            if rel.get("TargetMode", "Internal") == "External":
                raise UnsafeWorkbook(f"внешняя relationship {rel_id} не поддерживается")
            if rel_type.endswith(WORKSHEET_REL_SUFFIX):
                target = normalize_target("xl/workbook.xml", rel.get("Target", ""))
                if target in seen_worksheet_targets:
                    raise UnsafeWorkbook(f"повторная цель worksheet relationship: {target}")
                seen_worksheet_targets.add(target)
                relationships[rel_id] = target

        sheets_node = workbook.find(f"{{{NS_MAIN}}}sheets")
        if sheets_node is None:
            raise UnsafeWorkbook("в workbook.xml нет списка sheets")
        result: list[Sheet] = []
        seen_sheet_names: set[str] = set()
        seen_sheet_ids: set[str] = set()
        seen_sheet_targets: set[str] = set()
        for order, node in enumerate(sheets_node.findall(f"{{{NS_MAIN}}}sheet"), start=1):
            name = node.get("name", "")
            sheet_id = node.get("sheetId", "")
            rel_id = node.get(f"{{{NS_REL_DOC}}}id", "")
            if not name or not sheet_id or rel_id not in relationships:
                raise UnsafeWorkbook(f"неполная или неверная запись sheet: {name!r}")
            target = relationships[rel_id]
            if name in seen_sheet_names or sheet_id in seen_sheet_ids or target in seen_sheet_targets:
                raise UnsafeWorkbook(
                    f"повторные name, sheetId или worksheet target в списке sheets: {name!r}"
                )
            seen_sheet_names.add(name)
            seen_sheet_ids.add(sheet_id)
            seen_sheet_targets.add(target)
            root = read_xml(archive, target)
            formulas: list[Formula] = []
            seen_coordinates: set[str] = set()
            for cell in root.findall(f".//{{{NS_MAIN}}}c"):
                formula = cell.find(f"{{{NS_MAIN}}}f")
                if formula is None:
                    continue
                coordinate = cell.get("r", "")
                if not coordinate or coordinate in seen_coordinates:
                    raise UnsafeWorkbook(f"неверная или повторная координата формулы в {target}")
                seen_coordinates.add(coordinate)
                formula_type = formula.get("t", "normal")
                if formula_type != "normal":
                    raise UnsafeWorkbook(
                        f"неподдерживаемый тип формулы {formula_type!r} в {name}!{coordinate}; "
                        "shared/array/dataTable требуют отдельной проверки"
                    )
                formulas.append(
                    Formula(
                        coordinate=coordinate,
                        text=formula.text or "",
                        formula_type=formula_type,
                        shared_index=formula.get("si", ""),
                        reference=formula.get("ref", ""),
                    )
                )
            formulas.sort(key=lambda item: item.coordinate)
            result.append(Sheet(name, sheet_id, target, order, tuple(formulas)))
        return tuple(result)


def describe_formula(item: Formula) -> str:
    attrs = []
    if item.formula_type != "normal":
        attrs.append(f"type={item.formula_type}")
    if item.shared_index:
        attrs.append(f"si={item.shared_index}")
    if item.reference:
        attrs.append(f"ref={item.reference}")
    suffix = f" [{', '.join(attrs)}]" if attrs else ""
    shown = item.text if item.text else "<пустой текст формулы>"
    return f"{item.coordinate}: ={shown}{suffix}"


def compare(before: tuple[Sheet, ...], after: tuple[Sheet, ...]) -> list[str]:
    changes: list[str] = []
    before_map = {sheet.identity: sheet for sheet in before}
    after_map = {sheet.identity: sheet for sheet in after}

    for identity in sorted(before_map.keys() - after_map.keys()):
        changes.append(f"SHEET REMOVED: {before_map[identity].name}")
    for identity in sorted(after_map.keys() - before_map.keys()):
        changes.append(f"SHEET ADDED: {after_map[identity].name}")

    for identity in sorted(before_map.keys() & after_map.keys()):
        old = before_map[identity]
        new = after_map[identity]
        label = new.name
        if old.name != new.name:
            changes.append(f"SHEET RENAMED: {old.name} -> {new.name}")
            label = f"{old.name} -> {new.name}"
        if old.order != new.order:
            changes.append(f"SHEET MOVED: {label}: {old.order} -> {new.order}")
        old_formulas = {item.coordinate: item for item in old.formulas}
        new_formulas = {item.coordinate: item for item in new.formulas}
        for coordinate in sorted(old_formulas.keys() - new_formulas.keys()):
            changes.append(f"FORMULA REMOVED [{label}] {describe_formula(old_formulas[coordinate])}")
        for coordinate in sorted(new_formulas.keys() - old_formulas.keys()):
            changes.append(f"FORMULA ADDED [{label}] {describe_formula(new_formulas[coordinate])}")
        for coordinate in sorted(old_formulas.keys() & new_formulas.keys()):
            if old_formulas[coordinate] != new_formulas[coordinate]:
                changes.append(
                    f"FORMULA CHANGED [{label}] {coordinate}: "
                    f"{describe_formula(old_formulas[coordinate]).split(': ', 1)[1]} -> "
                    f"{describe_formula(new_formulas[coordinate]).split(': ', 1)[1]}"
                )
    return changes


def print_inventory(title: str, sheets: tuple[Sheet, ...]) -> None:
    print(title)
    for sheet in sheets:
        print(f"  {sheet.order}. {sheet.name} — формул: {len(sheet.formulas)}")
        for formula in sheet.formulas:
            print(f"     {describe_formula(formula)}")


def main() -> int:
    parser = argparse.ArgumentParser(
        description="Сравнить список листов и формулы в двух XLSX, не изменяя и не распаковывая их."
    )
    parser.add_argument("before", type=Path, help="копия XLSX до правки")
    parser.add_argument("after", type=Path, help="копия XLSX после правки")
    args = parser.parse_args()
    try:
        before = load_workbook(args.before)
        after = load_workbook(args.after)
    except UnsafeWorkbook as error:
        print(f"ОШИБКА: {error}", file=sys.stderr)
        return 2

    print_inventory("ДО:", before)
    print_inventory("ПОСЛЕ:", after)
    changes = compare(before, after)
    if not changes:
        print("Изменений в списке листов и записях формул не найдено.")
        return 0
    print(f"Найдено изменений: {len(changes)}")
    for change in changes:
        print(change)
    return 1


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

python .\compare_xlsx.py .\before.xlsx .\after.xlsx
$code = $LASTEXITCODE
"Код завершения: $code"

Найдено изменений: 5
SHEET REMOVED: Notes
SHEET ADDED: Added
SHEET RENAMED: Budget -> Plan
FORMULA REMOVED [Budget -> Plan] C2: =A1*2
FORMULA CHANGED [Budget -> Plan] B2: =SUM(A1:A2) -> =SUM(A1:A3)
Код завершения: 1

Get-Location
Get-ChildItem -LiteralPath .
Remove-Item -LiteralPath .\compare_xlsx.py, .\before.xlsx, .\after.xlsx