#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
xlsx 最小改动写入
=================
报销汇总表的格式「一丁点都不能改」——openpyxl 重存会丢掉绘图/部分样式。
本模块直接改 sheet XML 里的单元格，其余 zip 条目**逐字节原样复制**：
  · 空单元格 `<c r="H8" s="21"/>` → `<c r="H8" s="21"><v>242.7</v></c>`（样式 s 保留）
  · 已有值或公式的单元格 → 跳过，绝不覆盖
"""
import os
import re
import zipfile


def _fmt(v):
    if isinstance(v, bool):
        return '1' if v else '0'
    if isinstance(v, int):
        return str(v)
    return ('%.10g' % float(v))


def _sheet_path(z, sheet_name):
    wb = z.read('xl/workbook.xml').decode('utf-8')
    rels = z.read('xl/_rels/workbook.xml.rels').decode('utf-8')
    m = re.search(r'<sheet[^>]*name="%s"[^>]*r:id="([^"]+)"' % re.escape(sheet_name), wb)
    if not m:
        m = re.search(r'<sheet[^>]*r:id="([^"]+)"[^>]*name="%s"' % re.escape(sheet_name), wb)
    if not m:
        raise KeyError('找不到工作表：%s' % sheet_name)
    rid = m.group(1)
    r = re.search(r'Id="%s"[^>]*Target="([^"]+)"' % re.escape(rid), rels)
    tgt = r.group(1).lstrip('/')
    return tgt if tgt.startswith('xl/') else 'xl/' + tgt


def _esc(s):
    return str(s).replace('&', '&amp;').replace('<', '&lt;').replace('>', '&gt;')


def fill(src, dst, sheet_name, edits, overwrite=False):
    """edits: {'H8': 242.7, 'B2': '大客户销售部', ...}；返回 (已填, 跳过) 列表
    overwrite=True 时允许覆盖已有值（样式 s 保留），用于改部门/报销人等抬头字段。"""
    # ★ src==dst 时先写临时文件再原子替换；否则打开 dst 会把 src 截断 → BadZipFile
    same_path = os.path.abspath(src) == os.path.abspath(dst)
    real_dst = (dst + '.tmp') if same_path else dst
    z = zipfile.ZipFile(src)
    path = _sheet_path(z, sheet_name)
    x = z.read(path).decode('utf-8')
    filled, skipped = [], []
    for ref, val in edits.items():
        pat = re.compile(r'<c r="%s"([^>]*?)(/>|>.*?</c>)' % re.escape(ref), re.S)
        m = pat.search(x)
        if not m:
            skipped.append((ref, '单元格不存在'))
            continue
        attrs, body = m.group(1), m.group(2)
        inner = '' if body == '/>' else body[1:-4]
        if not overwrite and ('<f>' in inner or '<v>' in inner or '<is>' in inner):
            skipped.append((ref, '已有内容（不覆盖）'))
            continue
        attrs = re.sub(r'\st="[^"]*"', '', attrs)
        if isinstance(val, dict) and 'f' in val:          # 公式：{'f': 'SUM(D5:N5)'}
            new = '<c r="%s"%s><f>%s</f></c>' % (ref, attrs, _esc(val['f']))
        elif val is None:
            new = '<c r="%s"%s/>' % (ref, attrs)
        elif isinstance(val, str):
            new = '<c r="%s"%s t="inlineStr"><is><t xml:space="preserve">%s</t></is></c>' % (ref, attrs, _esc(val))
        else:
            new = '<c r="%s"%s><v>%s</v></c>' % (ref, attrs, _fmt(val))
        x = x[:m.start()] + new + x[m.end():]
        filled.append(ref)
    os.makedirs(os.path.dirname(dst), exist_ok=True)
    with zipfile.ZipFile(real_dst, 'w', zipfile.ZIP_DEFLATED) as zo:
        for n in z.namelist():
            zo.writestr(n, x.encode('utf-8') if n == path else z.read(n))
    z.close()
    if same_path:
        os.replace(real_dst, dst)
    return filled, skipped


def verify(src, dst, sheet_name):
    """逐条比对：除目标 sheet 外所有条目必须逐字节相同；目标 sheet 的单元格数/样式不变"""
    a, b = zipfile.ZipFile(src), zipfile.ZipFile(dst)
    diff_entries, same = [], 0
    for n in a.namelist():
        if n == _sheet_path(a, sheet_name):
            continue
        if a.read(n) != b.read(n):
            diff_entries.append(n)
        else:
            same += 1
    return same, diff_entries


if __name__ == '__main__':
    import sys
    src, dst = sys.argv[1], sys.argv[2]
    n, d = verify(src, dst, '差旅费报销汇总表')
    print('非目标条目逐字节一致: %d ｜ 有差异: %s' % (n, d or '无'))