from pathlib import Path
import json,hashlib,zipfile,shutil,io,posixpath
from lxml import etree as E
from openpyxl import load_workbook
P=Path(__file__).resolve().parent
f=P/'数字技术中心&交付实施部周报20260907_项目图文与确收版.xlsx'
recalculated=Path('/tmp/w36-photo-recalc')/f.name
w=load_workbook(f);rw=load_workbook(recalculated,data_only=True)
formula_values={}
for s in w:
 for row in s:
  for c in row:
   if c.data_type=='f':
    value=rw[s.title][c.coordinate].value;assert isinstance(value,(float,int,str)) and value not in ['#REF!','#VALUE!','#DIV/0!','#NAME?','#N/A'],(s.title,c.coordinate,value)
    formula_values[(s.title,c.coordinate)]=value
assert len(formula_values)==5
assert formula_values[('9月确收清单','D21')]==3128000
assert formula_values[('9月确收清单','E21')]==2809665
assert formula_values[('9月确收清单','F21')]==27170
assert formula_values[('9月确收清单','D23')]==318335
assert formula_values[('9月确收清单','D24')]==2782495
# Transfer only formula caches into the original package, retaining all original drawings and styles.
ns={'s':'http://schemas.openxmlformats.org/spreadsheetml/2006/main','r':'http://schemas.openxmlformats.org/officeDocument/2006/relationships'}
with zipfile.ZipFile(f) as z:
 originals={n:z.read(n) for n in z.namelist()};infos=z.infolist()
 rels=E.fromstring(originals['xl/_rels/workbook.xml.rels']);mapping={a.get('Id'):a.get('Target') for a in rels}
 book=E.fromstring(originals['xl/workbook.xml']);files={}
 for sh in book.findall('s:sheets/s:sheet',ns):
  target=mapping[sh.get('{'+ns['r']+'}id')];files[sh.get('name')]=target.lstrip('/') if target.startswith('/') else posixpath.normpath('xl/'+target)
 updates={}
 for name in set(k[0]for k in formula_values):
  path=files[name];root=E.fromstring(originals[path])
  for (sn,address),value in formula_values.items():
   if sn!=name:continue
   node=root.find('.//s:c[@r="'+address+'"]',ns);assert node is not None
   vn=node.find('s:v',ns)
   if vn is None:vn=E.SubElement(node,'{'+ns['s']+'}v')
   if isinstance(value,str):node.set('t','str');vn.text=value
   else:node.pop('t',None) if hasattr(node,'pop') else None;node.attrib.pop('t',None);vn.text=str(value)
  updates[path]=E.tostring(root,encoding='utf-8')
 temp=f.with_suffix('.cache.xlsx')
 with zipfile.ZipFile(temp,'w',compression=zipfile.ZIP_DEFLATED) as out:
  for info in infos:out.writestr(info,updates.get(info.filename,originals[info.filename]))
 temp.replace(f)
with zipfile.ZipFile(f) as z:
 assert z.testzip() is None
 for name,value in originals.items():
  if name not in updates:assert z.read(name)==value,name
w=load_workbook(f);values=load_workbook(f,data_only=True)
assert w.active.title=='产研9月重点'
visible=[s for s in w if s.sheet_state=='visible'];assert len(visible)==13
assert '10个项目' in str(w['交付实施部管理概况']['A3'].value)
images=sum(len(s._images) for s in w);assert images==50,images
assert len(w['中央美院项目图解']._images)==4 and len(w['哈尔滨项目图解']._images)==2 and len(w['项目附图']._images)==3
assert len([r for r in w['9月确收清单'].iter_rows(min_row=7,max_row=20) if r[1].value])==14
assert '410已有' in w['硬件与证书申请']['D6'].value
assert '分页' in w['硬件与证书申请']['D9'].value
assert w['产研规划明细'].max_row==68
assert '63条' in w['产研9月重点']['A4'].value
for s in visible:
 for row in s:
  for c in row:
   if c.value is not None:assert c.font.name=='微软雅黑' and c.font.sz==12,(s.title,c.coordinate)
   assert c.data_type!='e'
for (name,cell),value in formula_values.items():assert values[name][cell].value==value
receipt=json.loads((P/'receipt.json').read_text());assert hashlib.sha256(Path(receipt['input']).read_bytes()).hexdigest()==receipt['input_sha256']
for item in json.loads((P/'image-manifest.json').read_text()):assert hashlib.sha256(Path(item['source']).read_bytes()).hexdigest()==item['source_sha256']
# Deliver into the normal weekly-report folder; don't overwrite the user's Desktop original.
dest=Path('/Users/jack/同步空间/cpt/05_经营管理与会议/005_日常管理/周例会（事业部层面）/2026年')/f.name
if dest.exists() and hashlib.sha256(dest.read_bytes()).hexdigest()!=hashlib.sha256(f.read_bytes()).hexdigest():dest=dest.with_name(dest.stem+'_修订2'+dest.suffix)
shutil.copy2(f,dest)
pdf=Path('/tmp/w36-photo-render')/f.with_suffix('.pdf').name;shutil.copy2(pdf,P/pdf.name)
receipt.update({'output_sha256':hashlib.sha256(f.read_bytes()).hexdigest(),'convenience_copy':str(dest),'pdf_sha256':hashlib.sha256((P/pdf.name).read_bytes()).hexdigest()});(P/'receipt.json').write_text(json.dumps(receipt,ensure_ascii=False,indent=2))
result={'status':'PASS','visible_sheets':13,'all_sheets':len(w.sheetnames),'embedded_images':images,'new_project_images':9,'revenue_rows':14,'hardware_items':8,'mlps_rows':7,'monthly_task_rows':63,'leadership_result_lanes':5,'formulas':5,'formula_errors':0,'source_workbook_and_9_images_unchanged':True,'all_nonformula_package_parts_unchanged_during_cache_transfer':True,'font':'微软雅黑12','source_actual':27170,'source_forecast':2809665,'source_plan':3128000,'pdf_pages':13}
(P/'validation.json').write_text(json.dumps(result,ensure_ascii=False,indent=2));print(json.dumps(result,ensure_ascii=False));print(dest)