137 lines
4.8 KiB
Python
137 lines
4.8 KiB
Python
# coding: utf-8
|
|
import openpyxl, json, io, copy, re
|
|
from openpyxl.styles import Font, PatternFill
|
|
from openpyxl.utils import get_column_letter
|
|
|
|
SKY='FFBDD7EE'
|
|
wb=openpyxl.load_workbook('한국인터넷진흥원.xlsx'); ws=wb.active
|
|
NC=27 # AA
|
|
plan=json.load(io.open('_plan.json',encoding='utf-8'))
|
|
planmap={p['leaf_row']:p for p in plan}
|
|
|
|
# effective D..J
|
|
maxr=ws.max_row
|
|
eff={r:[ws.cell(r,c).value for c in range(4,11)] for r in range(3,maxr+1)}
|
|
for mr in ws.merged_cells.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
|
|
|
|
# snapshot per source row: values B..AA, styles, rowheight
|
|
def snap(r):
|
|
vals={c:ws.cell(r,c).value for c in range(2,NC+1)}
|
|
vals.update({4+i:eff[r][i] for i in range(7)}) # effective D..J
|
|
stys={c:copy.copy(ws.cell(r,c)._style) for c in range(2,NC+1)}
|
|
h=ws.row_dimensions[r].height
|
|
return {'vals':vals,'stys':stys,'h':h}
|
|
|
|
# build records
|
|
recs=[] # each: {'vals':{c:v}, 'stys':{c:style}, 'h':, 'newcells':set(cols to sky)}
|
|
for r in range(3,maxr+1):
|
|
s=snap(r)
|
|
rec={'vals':s['vals'],'stys':s['stys'],'h':s['h'],'newcells':set()}
|
|
if r in planmap:
|
|
p=planmap[r]; lc=p['leaf_col']
|
|
ch=p['children']
|
|
# self child = first child with self True
|
|
self_child=next(c for c in ch if c['self'])
|
|
# leaf row becomes self-child: add self name at lc+1
|
|
rec['vals'][lc+1]=self_child['name']
|
|
rec['newcells'].add(lc+1)
|
|
# clear deeper cols beyond lc+1 on self row
|
|
for cc in range(lc+2,11):
|
|
rec['vals'][cc]=None
|
|
recs.append(rec)
|
|
# new children
|
|
for c in ch:
|
|
if c['self']: continue
|
|
nr={'vals':{},'stys':{},'h':s['h'],'newcells':set()}
|
|
# copy path D..lc from leaf (effective)
|
|
for cc in range(4,11):
|
|
nr['vals'][cc]=rec['vals'][cc] if cc<=lc else None
|
|
nr['vals'][lc+1]=c['name']
|
|
# B,C
|
|
nr['vals'][2]=None # renumber later
|
|
nr['vals'][3]=ws.cell(r,3).value # site name
|
|
# K..AA
|
|
nr['vals'][11]=c['url']
|
|
nr['vals'][12]=c['L']
|
|
nr['vals'][13]=c['M']
|
|
# N default
|
|
if c['ctype']=='URL':
|
|
nr['vals'][14]='기타'; nr['vals'][15]=None # site: O none
|
|
else:
|
|
nr['vals'][14]='어문'; nr['vals'][15]='미부착'
|
|
nr['vals'][16]=None; nr['vals'][17]=None
|
|
for cc in range(18,NC+1): nr['vals'][cc]=None
|
|
# styles: copy from leaf row per column
|
|
for cc in range(2,NC+1):
|
|
nr['stys'][cc]=copy.copy(s['stys'][cc])
|
|
nr['newcells']=set([lc+1,11,12,13,14,15])
|
|
recs.append(nr)
|
|
else:
|
|
recs.append(rec)
|
|
|
|
# write out: clear data region first (rows 3..) values+styles+merges
|
|
# remove existing merges in D..J (and any) for data rows
|
|
for mr in list(ws.merged_cells.ranges):
|
|
if mr.min_row>=3:
|
|
ws.unmerge_cells(str(mr))
|
|
# clear old data rows fully
|
|
old_max=ws.max_row
|
|
for r in range(3,old_max+1):
|
|
for c in range(2,NC+1):
|
|
cell=ws.cell(r,c); cell.value=None; cell.hyperlink=None
|
|
|
|
N=len(recs)
|
|
# write records
|
|
for i,rec in enumerate(recs):
|
|
r=3+i
|
|
ws.row_dimensions[r].height=rec['h'] if rec['h'] else 17
|
|
for c in range(2,NC+1):
|
|
cell=ws.cell(r,c)
|
|
cell.value=rec['vals'].get(c)
|
|
cell._style=copy.copy(rec['stys'][c])
|
|
# B renumber
|
|
ws.cell(r,2).value=i+1
|
|
|
|
# K hyperlinks (blue underline)
|
|
linkfont=Font(name='맑은 고딕',size=10,color='FF0000FF',underline='single')
|
|
for i,rec in enumerate(recs):
|
|
r=3+i
|
|
url=rec['vals'].get(11)
|
|
kc=ws.cell(r,11)
|
|
if url and isinstance(url,str) and url.startswith('http'):
|
|
kc.hyperlink=url
|
|
f=copy.copy(kc.font)
|
|
kc.font=Font(name=f.name or '맑은 고딕',size=f.sz or 10,color='FF0000FF',underline='single')
|
|
|
|
# sky-blue new/changed cells
|
|
fill=PatternFill(fill_type='solid',fgColor=SKY,bgColor=SKY)
|
|
for i,rec in enumerate(recs):
|
|
r=3+i
|
|
for c in rec['newcells']:
|
|
ws.cell(r,c).fill=fill
|
|
|
|
# re-merge D..J by hierarchical equal-value runs
|
|
def colvals(c): return [rec['vals'].get(c) for rec in recs]
|
|
for c in range(4,11):
|
|
i=0
|
|
while i<N:
|
|
v=recs[i]['vals'].get(c)
|
|
if v in (None,''):
|
|
i+=1; continue
|
|
# path key up to c
|
|
def pk(idx): return tuple(recs[idx]['vals'].get(cc) for cc in range(4,c+1))
|
|
j=i+1
|
|
while j<N and recs[j]['vals'].get(c)==v and pk(j)==pk(i):
|
|
j+=1
|
|
if j-i>1:
|
|
ws.merge_cells(start_row=3+i,start_column=c,end_row=3+(j-1),end_column=c)
|
|
i=j
|
|
|
|
# center valign merged category cells already inherited from style; ensure header freeze ok
|
|
wb.save('한국인터넷진흥원.xlsx')
|
|
print('done rows=%d (was %d)'%(N, old_max-2))
|