Excel 변환 사본에 저장된 드롭다운 규칙이 남았는지 대조하기

#!/usr/bin/env python3
"""Inspect stored list dataValidation fields in two .xlsx files. No Excel execution."""
from __future__ import annotations

import argparse
from collections import Counter
import hashlib
from io import BytesIO
import json
import os
from pathlib import Path
import platform
import posixpath
import sys
import warnings
from xml.etree import ElementTree
from zipfile import BadZipFile, ZipFile

import openpyxl
from openpyxl import load_workbook

FIELDS = ("type", "formula1", "formula2", "sqref")
SCOPE = {
    "supported_format": "unencrypted Transitional OOXML .xlsx; Strict/unknown workbook or declared worksheet namespaces refused",
    "rule_type_filter": "list",
    "selected_fields": list(FIELDS),
    "sheet_grouping": "exact worksheet names; worksheet-name membership also compared",
    "formula_comparison": "exact stored strings including whitespace; no evaluation or semantic equivalence",
    "sqref_normalization": "openpyxl-parsed standard range representation, then sort whitespace-separated tokens; no general merge/split equivalence",
    "sqref_parser_normalizations": ["absolute markers removed: $B$2:$B$6 -> B2:B6", "coordinate case normalized: b2:b6 -> B2:B6", "singleton repeated endpoint collapsed: B2:B2 -> B2", "duplicate sqref tokens removed: B2:B6 B2:B6 -> B2:B6"],
    "rule_collection_comparison": "sorted multiset; duplicate rule counts preserved",
    "excluded_attributes": ["showDropDown", "allowBlank", "errorStyle", "operator", "showErrorMessage", "showInputMessage", "error", "errorTitle", "prompt", "promptTitle", "imeMode", "all other dataValidation attributes"],
    "excluded_checks": ["raw XML byte equality", "non-list validation rules", "chartsheets", "actual Excel UI", "drop-down arrow visibility", "formula evaluation", "list-item evaluation", "invalid-input blocking", "conversion execution", "other workbook contents"],
    "actual_excel_execution": "NOT_RUN",
    "warning_policy": "preserve every load/parse warning per input; any such warning makes inspection partial",
    "input_policy": ".xlsx only; open input bytes for reading; never save either workbook",
}


class OutputRefusal(Exception):
    """The requested new JSON cannot be created safely."""


class UnsupportedOOXMLNamespace(Exception):
    """Prevent silent skips of unsupported workbook or worksheet content."""


def sha256_bytes(payload):
    return hashlib.sha256(payload).hexdigest()


def rule_sort_key(rule):
    return json.dumps(rule, ensure_ascii=False, sort_keys=True)


def validate_ooxml_namespaces(archive):
    """Gate workbook/declared worksheet namespaces; do not parse rules."""
    main_ns = "http://schemas.openxmlformats.org/spreadsheetml/2006/main"
    document_rel_ns = "http://schemas.openxmlformats.org/officeDocument/2006/relationships"
    types = ElementTree.fromstring(archive.read("[Content_Types].xml"))
    parts = [node.attrib.get("PartName", "").lstrip("/") for node in types
             if node.attrib.get("ContentType") == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"]
    if len(parts) != 1:
        raise ValueError("Transitional .xlsx의 워크북 본문을 한 개 찾을 수 없습니다.")
    workbook_part = parts[0]
    workbook = ElementTree.fromstring(archive.read(workbook_part))
    if workbook.tag != f"{{{main_ns}}}workbook":
        raise UnsupportedOOXMLNamespace("워크북이 Transitional OOXML 네임스페이스가 아닙니다. Strict OOXML은 지원하지 않습니다.")
    declared_ids = set()
    for sheet in workbook.findall(f"{{{main_ns}}}sheets/{{{main_ns}}}sheet"):
        rid = sheet.attrib.get(f"{{{document_rel_ns}}}id")
        if rid is None:
            raise UnsupportedOOXMLNamespace("선언된 시트의 관계 네임스페이스가 지원되지 않거나 관계 ID가 없습니다.")
        declared_ids.add(rid)
    folder, filename = posixpath.split(workbook_part)
    rels_part = posixpath.join(folder, "_rels", filename + ".rels")
    relationships = ElementTree.fromstring(archive.read(rels_part))
    matched_ids = set()
    for relationship in relationships:
        rid = relationship.attrib.get("Id")
        if rid not in declared_ids:
            continue
        matched_ids.add(rid)
        kind = relationship.attrib.get("Type", "")
        if not kind.endswith("/worksheet"):
            continue
        if kind != document_rel_ns + "/worksheet":
            raise UnsupportedOOXMLNamespace("워크시트 관계가 Transitional OOXML 형식이 아닙니다.")
        if relationship.attrib.get("TargetMode") == "External":
            raise ValueError("외부 워크시트 본문은 지원하지 않습니다.")
        target = relationship.attrib.get("Target", "")
        part = target.lstrip("/") if target.startswith("/") else posixpath.normpath(posixpath.join(folder, target))
        worksheet = ElementTree.fromstring(archive.read(part))
        if worksheet.tag != f"{{{main_ns}}}worksheet":
            raise UnsupportedOOXMLNamespace("선언된 워크시트가 Transitional OOXML 네임스페이스가 아닙니다. Strict OOXML은 지원하지 않습니다.")
    if matched_ids != declared_ids:
        raise ValueError("선언된 시트의 본문 관계를 모두 찾을 수 없습니다.")


def inspect_workbook(path):
    """Read a byte snapshot and inspect the selected fields without saving input."""
    path = Path(path)
    result = {"path": str(path), "sha256_before": None, "sha256_after": None,
              "input_unchanged": None, "inspected": False, "partial_inspection": False,
              "warnings": [], "error": None, "list_rule_count": 0,
              "non_list_rule_count_skipped": 0, "worksheets": []}
    payload = None
    wb = None
    captured = []
    try:
        if path.suffix.lower() != ".xlsx":
            result["error"] = {"code": "unsupported_extension", "message": ".xlsx 파일만 지원합니다. .xls/.xlsm 및 다른 형식은 검사하지 않습니다."}
            return result
        payload = path.read_bytes()
        result["sha256_before"] = sha256_bytes(payload)
        if payload.startswith(bytes.fromhex("d0cf11e0a1b11ae1")):
            result["error"] = {"code": "encrypted_or_legacy_container", "message": "OLE 컨테이너입니다. 암호화 OOXML 또는 구형 Excel 형식일 수 있어 검사할 수 없습니다. 암호를 해제한 정상 .xlsx 사본을 준비하세요."}
        else:
            with ZipFile(BytesIO(payload)) as archive:
                if any(info.flag_bits & 1 for info in archive.infolist()):
                    raise ValueError("암호화된 ZIP 항목은 지원하지 않습니다.")
                types = archive.read("[Content_Types].xml")
                if b"macroEnabled" in types or b"vbaProject" in types:
                    raise ValueError("매크로 형식을 .xlsx로 바꾼 파일은 지원하지 않습니다.")
                validate_ooxml_namespaces(archive)
            with warnings.catch_warnings(record=True) as captured:
                warnings.simplefilter("always")
                # read_only=True does not expose this metadata reliably. The
                # normal parser reads a BytesIO snapshot; there is no save call.
                wb = load_workbook(BytesIO(payload), read_only=False, data_only=False, keep_links=False)
                for ws in sorted(wb.worksheets, key=lambda sheet: sheet.title):
                    selected = []
                    skipped = 0
                    for validation in ws.data_validations.dataValidation:
                        if validation.type != "list":
                            skipped += 1
                            continue
                        selected.append({"type": validation.type,
                                         "formula1": validation.formula1,
                                         "formula2": validation.formula2,
                                         "sqref": " ".join(sorted(str(validation.sqref).split()))})
                    selected.sort(key=rule_sort_key)
                    result["worksheets"].append({"sheet_name": ws.title,
                                                 "list_rules": selected,
                                                 "non_list_rule_count_skipped": skipped})
                    result["list_rule_count"] += len(selected)
                    result["non_list_rule_count_skipped"] += skipped
                result["inspected"] = True
    except UnsupportedOOXMLNamespace as exc:
        result["error"] = {"code": "unsupported_ooxml_namespace", "message": str(exc)}
    except OSError as exc:
        result["error"] = {"code": "input_read_error", "message": f"입력 파일을 읽을 수 없습니다: {exc}"}
    except Exception as exc:
        # Includes invalid ZIP, malformed OOXML/XML, unsupported workbook data,
        # and encrypted ZIP members. No errors are reclassified as equality.
        result["error"] = {"code": "invalid_or_unsupported_xlsx", "message": f"정상적으로 검사할 수 없는 .xlsx입니다 ({type(exc).__name__}): {exc}"}
    finally:
        if wb is not None:
            wb.close()
        result["warnings"] = [{"category": item.category.__name__, "message": str(item.message)} for item in captured]
        result["partial_inspection"] = bool(result["warnings"])
        if payload is not None:
            try:
                result["sha256_after"] = sha256_bytes(path.read_bytes())
                result["input_unchanged"] = result["sha256_before"] == result["sha256_after"]
                if not result["input_unchanged"]:
                    result["error"] = {"code": "input_changed_during_inspection", "message": "검사 중 입력 바이트가 달라졌습니다. 결과를 보존 여부 판정에 사용하지 마세요."}
            except OSError as exc:
                result["input_unchanged"] = False
                result["error"] = {"code": "input_changed_during_inspection", "message": f"검사 후 입력 SHA-256을 확인할 수 없습니다: {exc}"}
    return result


def counter_for(rules):
    return Counter(tuple(rule[field] for field in FIELDS) for rule in rules)


def counter_records(counter):
    records = [dict(zip(FIELDS, key), count=count) for key, count in counter.items()]
    return sorted(records, key=rule_sort_key)


def compare_snapshots(original, converted):
    left = {sheet["sheet_name"]: sheet for sheet in original["worksheets"]}
    right = {sheet["sheet_name"]: sheet for sheet in converted["worksheets"]}
    name_membership_equal = set(left) == set(right)
    equal = name_membership_equal
    sheets = []
    for name in sorted(set(left) | set(right)):
        a = counter_for(left.get(name, {}).get("list_rules", []))
        b = counter_for(right.get(name, {}).get("list_rules", []))
        this_equal = name in left and name in right and a == b
        equal = equal and this_equal
        sheets.append({"sheet_name": name, "original_present": name in left,
                       "converted_present": name in right,
                       "observed_selected_fields_equal": this_equal,
                       "missing_from_converted": counter_records(a - b),
                       "added_in_converted": counter_records(b - a)})
    return {"sheet_name_membership_equal": name_membership_equal,
            "observed_selected_fields_equal": equal, "sheets": sheets}


def input_paths_alias(original, converted):
    if original.resolve() == converted.resolve():
        return True
    try:
        return os.path.samefile(original, converted)
    except OSError:
        return False


def build_report(original_path, converted_path):
    original_path, converted_path = Path(original_path), Path(converted_path)
    original = inspect_workbook(original_path)
    converted = inspect_workbook(converted_path)
    errors = [{"input": role, **item["error"]} for role, item in (("original", original), ("converted", converted)) if item["error"]]
    if input_paths_alias(original_path, converted_path):
        errors.insert(0, {"input": "both", "code": "input_alias", "message": "원본과 변환 사본이 같은 파일 또는 같은 파일의 별칭입니다. 서로 다른 파일을 지정하세요."})
    partial = original["partial_inspection"] or converted["partial_inspection"]
    comparison = {"sheet_name_membership_equal": None, "observed_selected_fields_equal": None, "sheets": []}
    if not errors and original["inspected"] and converted["inspected"]:
        comparison = compare_snapshots(original, converted)
    if errors:
        status, exit_code = "inspection_error", 2
    elif partial:
        status, exit_code = "inspection_incomplete", 2
    elif comparison["observed_selected_fields_equal"]:
        status, exit_code = "selected_fields_equal", 0
    else:
        status, exit_code = "selected_fields_different", 1
    report = {"schema_version": "1.0", "status": status,
              "inspection_complete": not errors and not partial,
              "scope": SCOPE,
              "runtime": {"python": platform.python_version(), "openpyxl": openpyxl.__version__},
              "inputs": {"original": original, "converted": converted},
              "comparison": comparison, "errors": errors}
    return report, exit_code


def validate_new_output(path):
    path = Path(path)
    # lexists also detects dangling symlinks. Exclusive creation below repeats
    # this protection at write time, closing the exists/check-create race.
    if os.path.lexists(path):
        raise OutputRefusal(f"출력 경로가 이미 존재합니다. 덮어쓰지 않습니다: {path}")
    if path.suffix.lower() != ".json":
        raise OutputRefusal("출력 파일 이름은 .json으로 끝나야 합니다.")
    if not path.parent.is_dir():
        raise OutputRefusal(f"출력 폴더가 없거나 폴더가 아닙니다: {path.parent}")


def write_report_exclusive(path, report):
    """Create a new UTF-8 JSON only. Never replace an existing path."""
    path = Path(path)
    text = json.dumps(report, ensure_ascii=False, indent=2) + "\n"
    try:
        with path.open("x", encoding="utf-8", newline="\n") as handle:
            handle.write(text)
    except OSError as exc:
        raise OutputRefusal(f"출력 JSON을 새로 만들 수 없습니다. 폴더와 쓰기 권한을 확인하세요: {exc}") from exc


def main(argv=None):
    parser = argparse.ArgumentParser(description="두 .xlsx의 저장된 목록(type=list) 규칙 네 필드를 읽기 전용으로 비교합니다. 실제 Excel 동작 검사는 하지 않습니다.")
    parser.add_argument("original", type=Path, help="원본 .xlsx")
    parser.add_argument("converted", type=Path, help="변환된 사본 .xlsx")
    parser.add_argument("-o", "--output", required=True, type=Path, help="새로 만들 JSON 경로 (덮어쓰기 불가)")
    args = parser.parse_args(argv)
    try:
        validate_new_output(args.output)
        report, exit_code = build_report(args.original, args.converted)
        write_report_exclusive(args.output, report)
    except OutputRefusal as exc:
        print(f"출력 거부: {exc}", file=sys.stderr)
        return 3
    print(f"{report['status']} | JSON: {args.output}", file=sys.stderr)
    return exit_code


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