df16c98366
공공기관2 작업 중. _temp 몽타주(재생성가능)는 제외. Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
124 lines
5.2 KiB
Python
124 lines
5.2 KiB
Python
# -*- coding: utf-8 -*-
|
|
"""임실 104~끝 menuCd 하위메뉴 재구성. F-아이템(소분류 ...000 랜딩, 자식보유)의 세부 자식을 G로 전개.
|
|
기존 라벨없는 자식행 보정 + 누락 자식 삽입 + 스타일복사 + F병합. 자식수<기존행이면 SKIP(보고).
|
|
사용: python -X utf8 _임실_submenu.py [--write]
|
|
"""
|
|
import sys, os, re, copy, shutil, importlib.util
|
|
from urllib.parse import urljoin
|
|
import openpyxl
|
|
from openpyxl.worksheet.hyperlink import Hyperlink
|
|
|
|
HERE=os.path.dirname(os.path.abspath(__file__))
|
|
s=importlib.util.spec_from_file_location('m',os.path.join(HERE,'_jeonbuk_phase234_all.py'))
|
|
M=importlib.util.module_from_spec(s); s.loader.exec_module(M)
|
|
XLSX=r'D:\01.프로젝트\DB수집\작업파일\광역_사이트맵\전북특별자치도\10.임실군\전북특별자치도_임실군.xlsx'
|
|
BASE='https://www.imsil.go.kr'
|
|
START=104
|
|
SESS=M.make_session()
|
|
BOARD_KW=re.compile(r'공지사항|고시공고|입찰공고|입법예고|자료실|갤러리|보도자료|새소식|알림|게시판')
|
|
|
|
def mcd(u):
|
|
mm=re.search(r'menuCd=(\w+)',u or ''); return mm.group(1) if mm else None
|
|
def clean(t): return re.sub(r'새창열림|\s+',' ',t or '').strip()
|
|
def leafcol(ws,r):
|
|
for c in range(10,3,-1):
|
|
if ws.cell(r,c).value not in (None,''): return c
|
|
return None
|
|
def children_of(url, fmc):
|
|
soup=M.fetch(SESS,url)
|
|
if soup is None: return None
|
|
pre=fmc[:-3]
|
|
out={}
|
|
for a in soup.find_all('a',href=True):
|
|
c=mcd(a['href'])
|
|
if c and c!=fmc and c.startswith(pre) and len(c)==len(fmc) and c[len(pre):]!='000':
|
|
out[c]=(clean(a.get_text()), urljoin(BASE,a['href']))
|
|
return [out[k] for k in sorted(out)]
|
|
|
|
def LMN(label):
|
|
if BOARD_KW.search(label or ''): return '게시판',0,'어문'
|
|
return '페이지',1,'어문'
|
|
|
|
def copy_style(ws, sr, dr):
|
|
for c in range(2,19):
|
|
s=ws.cell(sr,c); d=ws.cell(dr,c)
|
|
d.font=copy.copy(s.font); d.alignment=copy.copy(s.alignment)
|
|
d.border=copy.copy(s.border); d.fill=copy.copy(s.fill); d.number_format=s.number_format
|
|
|
|
def remap_insert(ws, ins, n):
|
|
ranges=[(x.min_col,x.min_row,x.max_col,x.max_row) for x in list(ws.merged_cells.ranges)]
|
|
for x in list(ws.merged_cells.ranges): ws.unmerge_cells(str(x))
|
|
ws.insert_rows(ins,n)
|
|
for mc,mr,xc,xr in ranges:
|
|
if mr>=ins: mr+=n; xr+=n
|
|
elif xr>=ins: xr+=n
|
|
ws.merge_cells(start_row=mr,start_column=mc,end_row=xr,end_column=xc)
|
|
|
|
def build_plan(ws):
|
|
plan=[]
|
|
r=START
|
|
while r<=ws.max_row:
|
|
K=ws.cell(r,11).value; lc=leafcol(ws,r)
|
|
fmc=mcd(K) if isinstance(K,str) else None
|
|
if not fmc or lc!=6 or not fmc.endswith('000'):
|
|
r+=1; continue
|
|
ch=children_of(K,fmc)
|
|
if not ch or len(ch)<1:
|
|
r+=1; continue
|
|
# consume following group rows (menuCd startswith prefix)
|
|
pre=fmc[:-3]; grp=[r]; k=r+1
|
|
while k<=ws.max_row:
|
|
kc=mcd(ws.cell(k,11).value)
|
|
if kc and kc.startswith(pre) and kc!=fmc: grp.append(k); k+=1
|
|
else: break
|
|
plan.append((r,lc,ws.cell(r,6).value,grp,ch))
|
|
r=k
|
|
return plan
|
|
|
|
def main():
|
|
write='--write' in sys.argv
|
|
wb=openpyxl.load_workbook(XLSX); ws=wb.active
|
|
C=ws.cell(3,3).value
|
|
plan=build_plan(ws)
|
|
print('F-아이템(자식보유) %d개'%len(plan))
|
|
skip=[x for x in plan if len(x[3])>len(x[4])]
|
|
for r,lc,lab,grp,ch in skip: print(' SKIP r%d %s 기존행%d>자식%d'%(r,lab,len(grp),len(ch)))
|
|
plan=[x for x in plan if len(x[3])<=len(x[4])]
|
|
if not write:
|
|
for r,lc,lab,grp,ch in plan[:25]:
|
|
print(' r%d %s 기존%d→자식%d(+%d): %s'%(r,lab,len(grp),len(ch),len(ch)-len(grp),[t for t,_ in ch]))
|
|
print('총 신규삽입=%d'%sum(len(ch)-len(grp) for r,lc,lab,grp,ch in plan))
|
|
return
|
|
shutil.copy(XLSX, XLSX.replace('.xlsx','_backup_submenu전.xlsx'))
|
|
tot=0
|
|
for r,lc,lab,grp,ch in sorted(plan,key=lambda t:-t[0]): # bottom-up
|
|
n_ins=len(ch)-len(grp)
|
|
if n_ins>0:
|
|
remap_insert(ws, grp[-1]+1, n_ins)
|
|
# 모든 자식 배치 (grp[0]은 F유지, 나머지 F=None)
|
|
for i,(clab,curl) in enumerate(ch):
|
|
rr=r+i
|
|
if i>=len(grp):
|
|
copy_style(ws, r, rr)
|
|
ws.cell(rr,3).value=C
|
|
l,mm,nn=LMN(clab)
|
|
ws.cell(rr,7).value=clab # G
|
|
ws.cell(rr,11).value=curl
|
|
ws.cell(rr,12).value=l; ws.cell(rr,13).value=mm; ws.cell(rr,14).value=nn
|
|
ws.cell(rr,15).value=None; ws.cell(rr,16).value=None; ws.cell(rr,17).value=None
|
|
if i>0:
|
|
ws.cell(rr,6).value=None # F=None (병합내부)
|
|
ws.row_dimensions[rr].height=ws.row_dimensions[r].height or 15
|
|
if len(ch)>1:
|
|
ws.merge_cells(start_row=r,start_column=6,end_row=r+len(ch)-1,end_column=6)
|
|
tot+=max(0,n_ins)
|
|
print(' r%d %s 자식%d(+%d)'%(r,lab,len(ch),max(0,n_ins)))
|
|
for rr in range(3,ws.max_row+1):
|
|
v=ws.cell(rr,11).value; cell=ws.cell(rr,11)
|
|
if isinstance(v,str) and v.startswith('http'): cell.hyperlink=Hyperlink(ref=cell.coordinate,target=v)
|
|
else: cell.hyperlink=None
|
|
wb.save(XLSX)
|
|
print('적용: 신규삽입+%d행 · 스타일복사 · 백업'%tot)
|
|
|
|
if __name__=='__main__': main()
|