Excel 파일의 웹 링크와 로컬 경로를 읽기 전용 목록으로 만들기

"""Small trusted XLSX cell-hyperlink inventory. Never opens link destinations."""
from __future__ import annotations
import argparse
import hashlib
import io
import json
import math
from pathlib import Path
import re
import sys
import warnings
import xml.etree.ElementTree as ET
from zipfile import BadZipFile, ZIP_DEFLATED, ZIP_STORED, ZipFile
from openpyxl import load_workbook
from openpyxl.utils.exceptions import CellCoordinatesException
from openpyxl.utils.cell import coordinate_from_string, column_index_from_string, range_boundaries

MAX_FILE_BYTES = 2 * 1024 * 1024
MAX_TOTAL_BYTES = 8 * 1024 * 1024
MAX_MEMBER_BYTES = 2 * 1024 * 1024
MAX_MEMBERS = 128
MAX_RATIO = 100
MAX_CELLS = 20000
MAX_LINKS = 5000
MAIN_NS = '{http://schemas.openxmlformats.org/spreadsheetml/2006/main}'
HYPERLINK_REL = 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink'
BLOCKED_PREFIXES = ('xl/drawings/', 'xl/media/', 'xl/externallinks/', 'xl/embeddings/',
                    'xl/activex/', 'xl/querytables/', 'xl/pivottables/', 'xl/pivotcache/',
                    'xl/charts/', 'xl/chartsheets/')

class InventoryError(ValueError):
    """The input is outside this deliberately narrow exercise."""

def sha256(data: bytes) -> str:
    return hashlib.sha256(data).hexdigest()

def read_small(path: Path) -> bytes:
    if path.suffix.lower() != '.xlsx' or path.is_symlink() or not path.is_file():
        raise InventoryError('INPUT: 일반 .xlsx 파일을 지정하세요. 심볼릭 링크는 지원하지 않습니다.')
    with path.open('rb') as f:
        data = f.read(MAX_FILE_BYTES + 1)
    if len(data) > MAX_FILE_BYTES:
        raise InventoryError('SIZE: 입력 파일 크기 제한을 넘었습니다.')
    return data

def _coordinate(ref: str) -> None:
    col, row = coordinate_from_string(ref)
    if not (1 <= column_index_from_string(col) <= 16384 and 1 <= row <= 1048576):
        raise InventoryError('CELL: 잘못된 셀 주소입니다.')

def preflight(data: bytes) -> None:
    """Bounded ZIP/XML check, not a security sandbox for hostile files."""
    try:
        with ZipFile(io.BytesIO(data)) as z:
            infos = z.infolist()
            names = [i.filename for i in infos]
            if len(infos) > MAX_MEMBERS or len(names) != len(set(names)):
                raise InventoryError('ZIP: 멤버 수 제한 또는 중복 이름입니다.')
            if not {'[Content_Types].xml', 'xl/workbook.xml'}.issubset(names):
                raise InventoryError('ZIP: XLSX 필수 구조가 없습니다.')
            if sum(i.file_size for i in infos) > MAX_TOTAL_BYTES:
                raise InventoryError('ZIP: 압축 해제 크기 제한을 넘었습니다.')
            rectangle_total = stored_cells = hyperlink_total = 0
            for item in infos:
                name = item.filename
                low = name.lower()
                if '\\' in name or name.startswith('/') or '..' in name.split('/'):
                    raise InventoryError('ZIP: 지원하지 않는 멤버 경로입니다.')
                if (item.flag_bits & 1 or item.compress_type not in (ZIP_STORED, ZIP_DEFLATED)
                        or item.file_size > MAX_MEMBER_BYTES
                        or item.file_size > max(1, item.compress_size) * MAX_RATIO):
                    raise InventoryError('ZIP: 암호화·압축 방식·크기·압축비 제한입니다.')
                if (low.startswith(BLOCKED_PREFIXES) or 'vbaproject' in low
                        or low == 'xl/connections.xml' or low.endswith('.vml')):
                    raise InventoryError('SCOPE: 매크로·도형·외부 연결 등의 파일은 지원하지 않습니다.')
                payload = z.read(item)  # CRC is checked; nothing is extracted to disk.
                if not (low.endswith('.xml') or low.endswith('.rels')):
                    continue
                try:
                    text = payload.decode('utf-8-sig')
                except UnicodeDecodeError as exc:
                    raise InventoryError('XML: 이 실습은 UTF-8 XML만 지원합니다.') from exc
                if '\x00' in text or '<!DOCTYPE' in text.upper() or '<!ENTITY' in text.upper():
                    raise InventoryError('XML: DTD·ENTITY 선언은 지원하지 않습니다.')
                root = ET.fromstring(text)
                if low.endswith('.rels'):
                    for rel in root:
                        if rel.get('Type') == HYPERLINK_REL and rel.get('TargetMode') != 'External':
                            raise InventoryError('SCOPE: hyperlink 관계는 TargetMode=External만 지원합니다.')
                        if rel.get('TargetMode') == 'External' and rel.get('Type') != HYPERLINK_REL:
                            raise InventoryError('SCOPE: 셀 하이퍼링크 이외의 외부 관계는 지원하지 않습니다.')
                if low == '[content_types].xml':
                    if any('macroenabled' in x.get('ContentType', '').lower() for x in root):
                        raise InventoryError('SCOPE: 매크로 형식은 지원하지 않습니다.')
                if root.tag == MAIN_NS + 'worksheet':
                    max_row = max_col = 1
                    for cell in root.iter(MAIN_NS + 'c'):
                        formula = cell.find(MAIN_NS + 'f')
                        if formula is not None and formula.get('t') not in (None, 'normal'):
                            raise InventoryError('SCOPE: 배열·데이터표·공유 수식은 지원하지 않습니다.')
                        ref = cell.get('r', '')
                        _coordinate(ref)
                        col, row = coordinate_from_string(ref)
                        max_row = max(max_row, row)
                        max_col = max(max_col, column_index_from_string(col))
                        stored_cells += 1
                    dimension = root.find(MAIN_NS + 'dimension')
                    if dimension is not None:
                        c1, r1, c2, r2 = range_boundaries(dimension.get('ref', ''))
                        if any(x is None for x in (c1, r1, c2, r2)):
                            raise InventoryError('CELL: 전체 행·열 범위는 지원하지 않습니다.')
                        max_row = max(max_row, r2); max_col = max(max_col, c2)
                    merged_bounds = []
                    for merge in root.iter(MAIN_NS + 'mergeCell'):
                        c1, r1, c2, r2 = range_boundaries(merge.get('ref', ''))
                        if (any(x is None for x in (c1, r1, c2, r2))
                                or not (1 <= c1 <= c2 <= 16384 and 1 <= r1 <= r2 <= 1048576)):
                            raise InventoryError('CELL: 지원하지 않는 병합 범위입니다.')
                        max_row = max(max_row, r2); max_col = max(max_col, c2)
                        merged_bounds.append((c1, r1, c2, r2))
                    seen_refs = set()
                    for link in root.iter(MAIN_NS + 'hyperlink'):
                        ref = link.get('ref', '')
                        _coordinate(ref)  # Range hyperlinks are excluded.
                        col, row = coordinate_from_string(ref)
                        col = column_index_from_string(col)
                        key = (col, row)
                        if key in seen_refs:
                            raise InventoryError('SCOPE: 같은 셀의 중복 하이퍼링크는 지원하지 않습니다.')
                        seen_refs.add(key)
                        if any(c1 <= col <= c2 and r1 <= row <= r2
                               for c1, r1, c2, r2 in merged_bounds):
                            raise InventoryError('SCOPE: 병합 범위 안의 하이퍼링크는 지원하지 않습니다.')
                        max_row = max(max_row, row)
                        max_col = max(max_col, col)
                        hyperlink_total += 1
                    rectangle_total += max_row * max_col
                    if max(rectangle_total, stored_cells) > MAX_CELLS or hyperlink_total > MAX_LINKS:
                        raise InventoryError('CELL: 셀 범위·개수 또는 링크 수 제한입니다.')
    except InventoryError:
        raise
    except (BadZipFile, OSError, RuntimeError, ValueError, TypeError, KeyError, ET.ParseError, CellCoordinatesException) as exc:
        raise InventoryError('FORMAT: 손상되었거나 지원 범위를 벗어난 XLSX입니다.') from exc

def classify(target: str | None, location: str | None) -> str:
    """Text shape only: never URL validity or local-file existence."""
    if target is not None and location:
        return 'target_with_location'
    if target is None:
        return 'internal_location' if location else 'empty_metadata'
    low = target.lower()
    if low.startswith(('https://', 'http://')):
        return 'web_text'
    if low.startswith('mailto:'):
        return 'mail_text'
    if target.startswith(('/', '\\')) or re.match(r'^[A-Za-z]:[\\/]', target):
        return 'absolute_path_text'
    if not target:
        return 'empty_target_text'
    if target.startswith('#'):
        return 'fragment_target_text'
    if not re.match(r'^[A-Za-z][A-Za-z0-9+.-]*:', target):
        return 'relative_path_text'
    return 'other_target_text'

def json_value(value):
    if isinstance(value, float) and not math.isfinite(value):
        return str(value)
    if value is None or isinstance(value, (str, int, float, bool)):
        return value
    return value.isoformat() if hasattr(value, 'isoformat') else str(value)

def inventory(source: Path | str) -> dict:
    source = Path(source)
    try:
        before = read_small(source)
        preflight(before)
        with warnings.catch_warnings(record=True) as observed:
            warnings.simplefilter('always')
            wb = load_workbook(io.BytesIO(before), read_only=False, data_only=False,
                               keep_vba=False, keep_links=False)
            try:
                links = []; formulas = 0
                for ws in wb.worksheets:
                    for row in ws.iter_rows():
                        for cell in row:
                            formulas += cell.data_type == 'f'
                            h = cell.hyperlink
                            if h is None:
                                continue
                            links.append({'sheet': ws.title, 'cell': cell.coordinate,
                                          'kind': classify(h.target, h.location),
                                          'target': h.target, 'location': h.location,
                                          'display': h.display, 'cell_value': json_value(cell.value),
                                          'cell_value_type': type(cell.value).__name__})
            finally:
                wb.close()  # No workbook.save call.
        if observed:
            raise InventoryError('WARNING: openpyxl 경고가 발생했습니다. 결과를 완전한 목록으로 보지 말고 파일을 확인하세요.')
        if sha256(read_small(source)) != sha256(before):
            raise InventoryError('CHANGED: 읽는 동안 입력 파일이 달라졌습니다. 출력하지 않았습니다.')
        return {'format': 'p46-cell-hyperlink-inventory-v1', 'source_name': source.name,
                'source_sha256': sha256(before), 'link_count': len(links),
                'formula_cell_count': formulas,
                'scope': 'cell hyperlink metadata only; no destination access or existence check',
                'links': links}
    except InventoryError:
        raise
    except (OSError, ValueError, TypeError, KeyError, AttributeError, BadZipFile) as exc:
        raise InventoryError('READ: 입력을 읽지 못했습니다. 원본은 저장하지 않았습니다.') from exc

def write_json_new(destination: Path | str, result: dict) -> None:
    destination = Path(destination)
    if destination.suffix.lower() != '.json':
        raise InventoryError('OUTPUT: 출력 확장자는 .json이어야 합니다.')
    payload = json.dumps(result, ensure_ascii=False, indent=2, allow_nan=False) + '\n'
    with destination.open('x', encoding='utf-8', newline='\n') as f:
        f.write(payload)  # Existing files and symlinks are never overwritten.

def run(source: Path | str, destination: Path | str) -> dict:
    source, destination = Path(source), Path(destination)
    if source.resolve() == destination.resolve():
        raise InventoryError('OUTPUT: 입력과 출력은 다른 파일이어야 합니다.')
    result = inventory(source)
    write_json_new(destination, result)
    return result

def main() -> int:
    parser = argparse.ArgumentParser(description=__doc__)
    parser.add_argument('source', type=Path); parser.add_argument('destination', type=Path)
    args = parser.parse_args()
    try:
        result = run(args.source, args.destination)
    except (InventoryError, OSError) as exc:
        print(f'ERROR: {exc}', file=sys.stderr); return 2
    print(f"OK: {result['link_count']} links; input SHA-256 {result['source_sha256']}")
    return 0

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