from pathlib import Path
import json,csv,zipfile,math
from openpyxl import load_workbook
P=Path(__file__).resolve().parent
f=P/'产研与交付周报_20260831-0906.xlsx'
w=load_workbook(f,data_only=False);v=load_workbook(f,data_only=True)
assert w.sheetnames==['管理摘要','9月目标','周会协同','项目','产品','标准产品建设','数据口径','项目总览','来源索引']
assert w['项目'].max_row-4==15 and w['产品'].max_row-4==12 and w['项目总览'].max_row-4==52
assert len(set((r[0],r[1]) for r in w['产品'].iter_rows(min_row=5,max_row=16,values_only=True)))==12
formulas=[];visible=0
for sh in w:
 for row in sh:
  for c in row:
   if c.value is None:continue
   visible+=1;assert c.font.name=='微软雅黑' and c.font.sz==12,(sh.title,c.coordinate,c.font.name,c.font.sz)
   assert c.data_type!='e',(sh.title,c.coordinate)
   if c.data_type=='f':formulas.append((sh.title,c.coordinate,v[sh.title][c.coordinate].value));assert v[sh.title][c.coordinate].value is not None
for sheet,colblue,colorblue,colororange,colnext in [('项目',3,'FFEAF3FF','FFFFF0DF',5),('产品',3,'FFEAF3FF','FFFFF0DF',5)]:
 assert w[sheet].cell(5,colblue).fill.fgColor.rgb==colorblue
 assert w[sheet].cell(5,colnext).fill.fgColor.rgb==colororange
assert len(formulas)==11
for cell,value in {'B8':96,'C8':68,'D8':28,'F8':56,'E8':68/96}.items():assert math.isclose(v['数据口径'][cell].value,value)
recalc=Path('/tmp/w36-recalculated')/f.name
rw=load_workbook(recalc,data_only=True)
for s,a,value in formulas:assert math.isclose(rw[s][a].value,value,rel_tol=1e-12),(s,a,rw[s][a].value,value)
with zipfile.ZipFile(f) as z:assert z.testzip() is None
h=(P/'product-rnd-delivery-weekly-report.html').read_text()
assert h.count('class="project-card"')==15 and h.count('class="spu"')==3
assert 'https://www.mankebao.com/product/' in h and '53行/52条有名称' in h
assert '候选密码' not in h and 'Cpt@' not in h and 'woQsFF' not in h
for token in ['13个重点项目','9月交付完成率100%']:assert token not in h
r={'result':'PASS','sheets':9,'visible_cells':visible,'project_rows':15,'product_cells':12,'portfolio_rows':52,'formula_count':len(formulas),'formula_errors':0,'libreoffice_recalc_match':True,'font':'微软雅黑 12','colors':'blue=this week; orange=next week','source_csv_counts':'105/30/32/24/13 verified'}
(P/'validation').mkdir(exist_ok=True)
(P/'validation'/'workbook-check.json').write_text(json.dumps(r,ensure_ascii=False,indent=2))
print(json.dumps(r,ensure_ascii=False))