60 lines
2.2 KiB
Python
60 lines
2.2 KiB
Python
# coding: utf-8
|
|
import openpyxl, io, re
|
|
wb=openpyxl.load_workbook('한국인터넷진흥원.xlsx'); ws=wb.active
|
|
out=io.open('_verify_out.txt','w',encoding='utf-8')
|
|
N=ws.max_row
|
|
issues=[]
|
|
# B continuity
|
|
bs=[ws.cell(r,2).value for r in range(3,N+1)]
|
|
exp=list(range(1,len(bs)+1))
|
|
if bs!=exp: issues.append('B not 1..%d (first mismatch idx %s)'%(len(bs),next((i for i,(a,b) in enumerate(zip(bs,exp)) if a!=b),'?')))
|
|
# merge overlaps
|
|
ranges=list(ws.merged_cells.ranges)
|
|
cells_seen={}
|
|
overlap=0
|
|
for mr in ranges:
|
|
for rr in range(mr.min_row,mr.max_row+1):
|
|
for cc in range(mr.min_col,mr.max_col+1):
|
|
if (rr,cc) in cells_seen: overlap+=1
|
|
cells_seen[(rr,cc)]=1
|
|
if overlap: issues.append('merge overlap cells=%d'%overlap)
|
|
# DEF gap: row where col c empty but deeper col c+1 filled (within D..J)
|
|
gap=0
|
|
for r in range(3,N+1):
|
|
vals=[ws.cell(r,c).value for c in range(4,11)]
|
|
# account for merged: get effective
|
|
pass
|
|
# effective check
|
|
eff={r:[ws.cell(r,c).value for c in range(4,11)] for r in range(3,N+1)}
|
|
for mr in ranges:
|
|
if mr.min_col>=4 and mr.max_col<=10:
|
|
v=ws.cell(mr.min_row,mr.min_col).value
|
|
for rr in range(mr.min_row,mr.max_row+1):
|
|
eff[rr][mr.min_col-4]=v
|
|
for r in range(3,N+1):
|
|
vv=eff[r]
|
|
seen_empty=False
|
|
for i,x in enumerate(vv):
|
|
if x in (None,''): seen_empty=True
|
|
elif seen_empty:
|
|
gap+=1; break
|
|
if gap: issues.append('DEF gap rows=%d'%gap)
|
|
# K hyperlink vs value mismatch
|
|
kmis=0
|
|
for r in range(3,N+1):
|
|
kc=ws.cell(r,11); v=kc.value
|
|
if isinstance(v,str) and v.startswith('http'):
|
|
if not kc.hyperlink or str(kc.hyperlink.target if hasattr(kc.hyperlink,'target') else kc.hyperlink)!=v:
|
|
kmis+=1
|
|
if kmis: issues.append('K hyperlink mismatch=%d'%kmis)
|
|
# header rows intact
|
|
h1=ws.cell(1,2).value; h2=ws.cell(2,2).value
|
|
out.write('rows=%d header B1=%r B2=%r\n'%(N,h1,h2))
|
|
out.write('merged ranges=%d\n'%len(ranges))
|
|
out.write('ISSUES: %s\n'%(issues if issues else 'NONE'))
|
|
# count L types
|
|
from collections import Counter
|
|
lc=Counter(ws.cell(r,12).value for r in range(3,N+1))
|
|
out.write('L dist: %r\n'%dict(lc))
|
|
out.close(); print(open('_verify_out.txt',encoding='utf-8').read())
|