import zipfile,re,xml.etree.ElementTree as ET,collections,json
from pathlib import Path
BASE=Path('/Users/neo/.hermes/audits/cashflow-asset-hook-20260512-083049/wanchuan_anhui_v9_v2_1_long_asset_R29_R58_asset_class_atomic_SAFE_20260513-085849.xlsx')
PH11=Path('/Users/neo/.hermes/audits/cashflow-asset-hook-20260512-083049/wanchuan_anhui_v9_v2_1_long_asset_R29_R169_asset_schedule_atomic_EXCEL_REPAIR_SAFE_20260513-094726.xlsx')
PH10=Path('/Users/neo/.hermes/audits/cashflow-asset-hook-20260512-083049/wanchuan_anhui_v9_v2_1_long_asset_R29_R169_asset_schedule_atomic_SAFE_20260513-092323.xlsx')
S='长期资产循环_全量穿透校验'; NS={'m':'http://schemas.openxmlformats.org/spreadsheetml/2006/main','r':'http://schemas.openxmlformats.org/officeDocument/2006/relationships'}
def locate(z):
    wb=ET.fromstring(z.read('xl/workbook.xml')); rels=ET.fromstring(z.read('xl/_rels/workbook.xml.rels'))
    ridmap={r.attrib['Id']:r.attrib['Target'] for r in rels}
    for sh in wb.find('m:sheets',NS):
        if sh.attrib.get('name')==S: return 'xl/'+ridmap[sh.attrib['{'+NS['r']+'}id']].lstrip('/')
def colnum(col):
    n=0
    for ch in col: n=n*26+ord(ch)-64
    return n
def split(ref):
    m=re.match(r'([A-Z]+)(\d+)$',ref); return colnum(m.group(1)),int(m.group(2)),m.group(1)
def expand(rng):
    a,b=rng.split(':') if ':' in rng else (rng,rng); c1,r1,_=split(a); c2,r2,_=split(b); return c1,r1,c2,r2,a
def getxml(p):
    with zipfile.ZipFile(p) as z: return z.read(locate(z)).decode('utf-8')
def cells(xml):
    pat=r'<c\b(?=[^>]*\br="([A-Z]+\d+)")[^>]*/>|<c\b(?=[^>]*\br="([A-Z]+\d+)")[^>]*>.*?</c>'
    d={}
    for m in re.finditer(pat,xml,re.S): d[m.group(1) or m.group(2)]=m.group(0)
    return d
bxml=getxml(BASE); x10=getxml(PH10); x11=getxml(PH11)
merged=re.findall(r'<mergeCell\b[^>]*ref="([^"]+)"', bxml)
ranges=[expand(r) for r in merged]
for label,xml in [('PH10',x10),('PH11',x11)]:
    print('\n==',label,'==')
    cc=cells(xml); bc=cells(bxml)
    changed=[]; added=[]; diffpayload=[]
    for rng in merged:
        c1,r1,c2,r2,top=expand(rng); tc,tr,_=split(top)
        if r2<64 or r1>180: continue
        for ref,cell in cc.items():
            c,r,_=split(ref)
            if c1<=c<=c2 and r1<=r<=r2 and not(c==tc and r==tr):
                if bc.get(ref)!=cell:
                    changed.append((ref,rng,'ADD' if ref not in bc else 'DIFF',bc.get(ref,''),cell))
    print('changed merged followers 64-180',len(changed))
    for item in changed[:80]:
        ref,rng,kind,old,new=item
        print(ref,rng,kind,'old=',old[:120],'new=',new[:160])