복사할 수식의 상대 참조와 고정 참조를 새 위치에서 검산하기

"""Read-only, narrow A1 formula-string translation exercise; no Excel calculation."""
import argparse
import csv
import io
import os
import re
import sys
import zipfile
from pathlib import Path
from xml.etree.ElementTree import ParseError

from openpyxl import load_workbook
from openpyxl.formula.translate import Translator, TranslatorError
from openpyxl.utils.cell import column_index_from_string

ADDRESS = re.compile(r'([A-Z]{1,3})([1-9][0-9]{0,6})\Z')
REF = r'\$?[A-Z]{1,3}\$?[1-9][0-9]{0,6}'
ARITHMETIC = re.compile(rf'=({REF})([+*/-])({REF})\Z')
SUM_RANGE = re.compile(rf'=SUM\(({REF}):({REF})\)\Z')
HEADER = ['case_id', 'sheet', 'origin', 'target', 'expected']
REPORT_HEADER = HEADER + ['source_formula', 'translated', 'status']


def coordinates(address):
    """Require one uppercase, unanchored A1 address inside the Excel grid."""
    if not isinstance(address, str) or not (m := ADDRESS.fullmatch(address)):
        raise ValueError(f'invalid address: {address!r}')
    col, row = column_index_from_string(m[1]), int(m[2])
    if col > 16384 or row > 1048576:
        raise ValueError(f'address outside Excel grid: {address}')
    return row, col


def formula_references(formula):
    """Accept only ref operator ref or SUM(one ordered rectangular range)."""
    if not isinstance(formula, str) or len(formula) > 128:
        raise ValueError('formula must be a supported string, at most 128 characters')
    if m := ARITHMETIC.fullmatch(formula):
        refs = (m[1], m[3])
    elif m := SUM_RANGE.fullmatch(formula):
        refs = (m[1], m[2])
        a, b = (coordinates(x.replace('$', '')) for x in refs)
        if a[0] > b[0] or a[1] > b[1]:
            raise ValueError('SUM range must be top-left to bottom-right')
    else:
        raise ValueError('unsupported formula: use two A1 references or SUM(one range)')
    for ref in refs:
        coordinates(ref.replace('$', ''))
    return refs


def translate_checked(formula, origin, target):
    coordinates(origin)
    coordinates(target)
    formula_references(formula)
    try:
        translated = Translator(formula, origin=origin).translate_formula(target)
    except TranslatorError as exc:
        raise ValueError('translated reference is below row 1 or left of column A') from exc
    # Translator 3.1.5 may emit XFE1 or row 1048577. Check the upper bound too.
    formula_references(translated)
    return translated


def read_cases(path):
    if path.is_symlink() or not path.is_file() or path.stat().st_size > 65536:
        raise ValueError('plan must be a regular CSV file of at most 64 KiB')
    with path.open(encoding='utf-8-sig', newline='') as f:
        reader = csv.DictReader(f, strict=True)
        if reader.fieldnames != HEADER:
            raise ValueError('CSV header must exactly match: ' + ','.join(HEADER))
        rows = []
        ids = set()
        for row in reader:
            if len(rows) >= 100 or None in row or any(v is None for v in row.values()):
                raise ValueError('invalid CSV width or more than 100 cases')
            cid = row['case_id']
            if not re.fullmatch(r'[a-z0-9_]{1,40}', cid) or cid in ids:
                raise ValueError('case_id must be unique lowercase letters, digits or underscores')
            ids.add(cid)
            if not row['sheet'] or len(row['sheet']) > 31:
                raise ValueError('invalid sheet name')
            coordinates(row['origin'])
            coordinates(row['target'])
            formula_references(row['expected'])
            rows.append(row)
    if not rows:
        raise ValueError('CSV needs at least one case')
    return rows


def inspect_xlsx(path):
    if path.suffix.lower() != '.xlsx' or path.is_symlink() or not path.is_file():
        raise ValueError('source must be a regular .xlsx file')
    if path.stat().st_size > 1048576:
        raise ValueError('this exercise limits the workbook to 1 MiB')
    with zipfile.ZipFile(path) as z:
        items = z.infolist()
        if len(items) > 100 or sum(i.file_size for i in items) > 5242880:
            raise ValueError('workbook package exceeds exercise limits')
        if any('externallinks/' in i.filename.lower() or 'vbaproject' in i.filename.lower() for i in items):
            raise ValueError('external-link or VBA package is outside this exercise')


def check_workbook(source, plan):
    cases = read_cases(plan)
    inspect_xlsx(source)
    wb = load_workbook(source, read_only=True, data_only=False, keep_links=False)
    results = []
    try:
        for row in cases:
            if row['sheet'] not in wb.sheetnames:
                raise ValueError(f"{row['case_id']}: sheet not found")
            cell = wb[row['sheet']][row['origin']]
            if cell.data_type != 'f' or not isinstance(cell.value, str):
                raise ValueError(f"{row['case_id']}: origin cell has no ordinary formula string")
            translated = translate_checked(cell.value, row['origin'], row['target'])
            results.append(dict(row, source_formula=cell.value, translated=translated,
                                status='MATCH' if translated == row['expected'] else 'MISMATCH'))
    finally:
        wb.close()
    return results


def csv_text(results):
    # Literal display when opened in a spreadsheet. Compare unprefixed strings above.
    def literal(value):
        return "'" + value if value.startswith(('=', '+', '-', '@')) else value
    stream = io.StringIO(newline='')
    writer = csv.DictWriter(stream, fieldnames=REPORT_HEADER, lineterminator='\n')
    writer.writeheader()
    for row in results:
        writer.writerow({key: literal(row[key]) for key in REPORT_HEADER})
    return stream.getvalue()


def main(argv=None):
    parser = argparse.ArgumentParser(description=__doc__)
    parser.add_argument('source', type=Path)
    parser.add_argument('plan', type=Path)
    parser.add_argument('output', type=Path)
    args = parser.parse_args(argv)
    output = args.output
    # Includes broken symlinks and existing directories; never offer overwrite.
    if os.path.lexists(output):
        print('ERROR: output path already exists; choose a new filename', file=sys.stderr)
        return 3
    created = False
    try:
        results = check_workbook(args.source, args.plan)
        report = csv_text(results)  # Validate every case before creating output.
        with output.open('x', encoding='utf-8-sig', newline='') as f:
            created = True
            f.write(report)
    except FileExistsError:
        print('ERROR: output appeared before write; preserved', file=sys.stderr)
        return 3
    except (ValueError, OSError, zipfile.BadZipFile, KeyError, IndexError, UnicodeError, csv.Error, ParseError) as exc:
        if created:
            # Remove only the newly created incomplete report owned by this run.
            try:
                output.unlink()
            except OSError:
                print('WARNING: incomplete new report could not be removed', file=sys.stderr)
        print(f'ERROR: {exc}', file=sys.stderr)
        return 2
    mismatches = sum(r['status'] == 'MISMATCH' for r in results)
    print(f'MATCH={len(results)-mismatches} MISMATCH={mismatches}; formula strings only')
    print('Excel calculation/UI: NOT CHECKED')
    return 1 if mismatches else 0


if __name__ == '__main__':
    sys.exit(main())