136 lines
5.7 KiB
Python
136 lines
5.7 KiB
Python
# -*- coding: utf-8 -*-
|
|
"""KCOPA 본문탭 전수전개 v2 (GNB기준): 점검도구소개 G자식 정정 + C STORY/REPORT 자식."""
|
|
import openpyxl, copy, sys
|
|
from openpyxl.styles import PatternFill
|
|
sys.stdout.reconfigure(encoding='utf-8')
|
|
FOLDER=r'D:\01.프로젝트\DB수집\작업파일\공공기관3\13.한국저작권보호원'
|
|
SRC=FOLDER+r'\_backup_탭전개전_2026-06-23.xlsx' # GNB-clean 90행 원본
|
|
DST=FOLDER+r'\한국저작권보호원.xlsx'
|
|
BASE='https://www.kcopa.or.kr'
|
|
DRY = '--write' not in sys.argv
|
|
BLUE=PatternFill(fill_type='solid',fgColor='FFBDD7EE',bgColor='FFBDD7EE')
|
|
wb=openpyxl.load_workbook(SRC); ws=wb.active
|
|
MAXR=ws.max_row
|
|
owner={}
|
|
for mr in ws.merged_cells.ranges:
|
|
for r in range(mr.min_row,mr.max_row+1):
|
|
for c in range(mr.min_col,mr.max_col+1):
|
|
owner[(r,c)]=(mr.min_row,mr.min_col)
|
|
def ctl(r,c): return owner.get((r,c),(r,c))
|
|
def effval(r,c):
|
|
o=ctl(r,c); return ws.cell(o[0],o[1]).value
|
|
def effstyle(r,c):
|
|
o=ctl(r,c); return copy.copy(ws.cell(o[0],o[1])._style)
|
|
NCOL=27
|
|
def cap(r):
|
|
rec={'orig':r,'cells':{}}
|
|
for c in range(2,NCOL+1):
|
|
rec['cells'][c]={'v':effval(r,c),'s':effstyle(r,c),
|
|
'hl':(ws.cell(r,c).hyperlink.target if ws.cell(r,c).hyperlink else None)}
|
|
return rec
|
|
orig={r:cap(r) for r in range(3,MAXR+1)}
|
|
basestyle={c:copy.copy(ws.cell(3,c)._style) for c in range(4,16)}
|
|
kbase=copy.copy(ws.cell(3,11)._style)
|
|
def U(p): return p if p.startswith('http') else BASE+p
|
|
C='한국저작권보호원'
|
|
def newrec(colvals,blue_cols,hl=None):
|
|
rec={'orig':None,'cells':{}}
|
|
for c in range(2,NCOL+1):
|
|
v=colvals.get(c,None)
|
|
s=copy.copy(kbase) if c==11 else copy.copy(basestyle.get(c, ws.cell(3,c)._style))
|
|
rec['cells'][c]={'v':v,'s':s,'hl':hl if c==11 else None}
|
|
rec['_blue']=set(blue_cols)
|
|
return rec
|
|
|
|
# find orig rows for the two F-parents by leaf label
|
|
def findrow(label,col=6):
|
|
for r in range(3,MAXR+1):
|
|
if effval(r,col)==label: return r
|
|
return None
|
|
r_js = None # 점검도구 소개 (F)
|
|
r_cs = None # C STORY 및 KCOPA REPORT(통합본) (F)
|
|
for r in range(3,MAXR+1):
|
|
if effval(r,6)=='점검도구 소개': r_js=r
|
|
if str(effval(r,6) or '').startswith('C STORY 및 KCOPA REPORT'): r_cs=r
|
|
out=[]
|
|
for r in range(3,MAXR+1):
|
|
if r==r_cs:
|
|
# 통합본 F-parent -> becomes C STORY child (sublink redirects to C STORY); keep L/M/N/O
|
|
rec=orig[r]
|
|
rec['cells'][7]={'v':'C STORY','s':copy.copy(basestyle[7]),'hl':None}
|
|
rec['cells'][11]={'v':U('/lay1/bbs/S1T495C234/F/39/list.do'),'s':copy.copy(kbase),
|
|
'hl':U('/lay1/bbs/S1T495C234/F/39/list.do')}
|
|
rec['_blue']={7,11}
|
|
out.append(rec)
|
|
out.append(newrec({3:C,7:'KCOPA REPORT',11:U('/lay1/bbs/S1T495C236/F/52/list.do'),
|
|
12:'게시판',13:12,14:'어문,이미지',15:'미부착'},
|
|
{7,11,12,13,14,15}, hl=U('/lay1/bbs/S1T495C236/F/52/list.do')))
|
|
else:
|
|
out.append(orig[r])
|
|
if r==r_js:
|
|
# 점검도구 소개 stays F parent (its own intro page). Add G children.
|
|
for lab,path in [('Standalone','/lay1/S1T241C244/contents.do'),
|
|
('Web Module','/lay1/S1T241C245/contents.do'),
|
|
('내 PC 폰트 점검기(교육기관용)','/lay1/S1T241C246/contents.do')]:
|
|
# F(col6)=None -> inherits 점검도구 소개 via merge ; G(col7)=label
|
|
out.append(newrec({3:C,7:lab,11:U(path),12:'페이지',13:1,14:'어문',15:'미부착'},
|
|
{7,11,12,13,14,15}, hl=U(path)))
|
|
# eff fill-down for cat cols 4..10
|
|
carry={c:None for c in range(4,11)}
|
|
for rec in out:
|
|
for c in range(4,11):
|
|
v=rec['cells'][c]['v']
|
|
if v not in (None,''):
|
|
carry[c]=v
|
|
for d in range(c+1,11): carry[d]=None
|
|
else:
|
|
rec['cells'][c]['_inh']=carry[c]
|
|
N=len(out)
|
|
eff=[[None]*11 for _ in range(N)]
|
|
for i,rec in enumerate(out):
|
|
for c in range(4,11):
|
|
v=rec['cells'][c]['v']
|
|
eff[i][c]=v if v not in (None,'') else rec['cells'][c].get('_inh')
|
|
def show():
|
|
for i,rec in enumerate(out):
|
|
cs=[eff[i][c] for c in (4,5,6,7)]
|
|
cs=['' if v is None else str(v)[:22] for v in cs]
|
|
k=(rec['cells'][11]['v'] or '').replace(BASE,'')
|
|
L=rec['cells'][12]['v'] or ''; M=rec['cells'][13]['v']
|
|
bl='*' if rec.get('_blue') else ' '
|
|
tag='NEW' if rec['orig'] is None else str(rec['orig'])
|
|
if 22<=i+3<=45:
|
|
print(f"{bl}{i+3:>3}[{tag:>3}] D={cs[0]:<8} E={cs[1]:<12} F={cs[2]:<24} G={cs[3]:<22} {L} {M} {k}")
|
|
show(); print('rows',N,'js_parent_row',r_js,'cs_row',r_cs,'DRY' if DRY else 'WRITE')
|
|
if DRY: sys.exit(0)
|
|
for mr in list(ws.merged_cells.ranges):
|
|
if mr.min_row>=3: ws.unmerge_cells(str(mr))
|
|
for r in range(3,MAXR+60):
|
|
for c in range(2,NCOL+1):
|
|
cell=ws.cell(r,c); cell.value=None; cell.hyperlink=None
|
|
for i,rec in enumerate(out):
|
|
rr=i+3
|
|
for c in range(2,NCOL+1):
|
|
cell=ws.cell(rr,c)
|
|
cell.value=rec['cells'][c]['v']
|
|
cell._style=copy.copy(rec['cells'][c]['s'])
|
|
if rec.get('_blue') and c in rec['_blue']:
|
|
cell.fill=copy.copy(BLUE)
|
|
hl=rec['cells'][c]['hl']
|
|
if c==11:
|
|
tgt=hl or rec['cells'][c]['v']
|
|
if isinstance(tgt,str) and tgt.startswith('http'): cell.hyperlink=tgt
|
|
ws.cell(rr,2).value=i+1
|
|
ws.row_dimensions[rr].height=17
|
|
for c in range(4,11):
|
|
i=0
|
|
while i<N:
|
|
v=eff[i][c]
|
|
if v in (None,''): i+=1; continue
|
|
j=i
|
|
while j+1<N and eff[j+1][c]==v and all(eff[j+1][cc]==eff[i][cc] for cc in range(4,c)):
|
|
j+=1
|
|
if j>i: ws.merge_cells(start_row=i+3,start_column=c,end_row=j+3,end_column=c)
|
|
i=j+1
|
|
wb.save(DST); print('SAVED',DST)
|