df16c98366
공공기관2 작업 중. _temp 몽타주(재생성가능)는 제외. Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
100 lines
4.8 KiB
Python
100 lines
4.8 KiB
Python
# -*- coding: utf-8 -*-
|
|
"""정읍 ul.colN 본문탭 전개(시트에 없는 탭만). board/list=게시판(글수)·index=페이지·_blank=사이트.
|
|
부모URL과 같은 탭(전체/self)은 제외. 새행 파란색. 사용: python -X utf8 _정읍_coltab.py [--write]
|
|
"""
|
|
import sys, os, re, shutil, importlib.util
|
|
from urllib.parse import urljoin
|
|
from concurrent.futures import ThreadPoolExecutor, as_completed
|
|
import openpyxl
|
|
from openpyxl.worksheet.hyperlink import Hyperlink
|
|
from openpyxl.styles import PatternFill
|
|
HERE=os.path.dirname(os.path.abspath(__file__))
|
|
LN=importlib.util.module_from_spec(importlib.util.spec_from_file_location('ln',os.path.join(HERE,'_정읍_LN.py')))
|
|
importlib.util.spec_from_file_location('ln',os.path.join(HERE,'_정읍_LN.py')).loader.exec_module(LN)
|
|
M=LN.M; XLSX=LN.XLSX; SESS=LN.SESS
|
|
BLUE=PatternFill(start_color='BDD7EE',end_color='BDD7EE',fill_type='solid')
|
|
def key(u2):
|
|
mm=re.search(r'menuCd=(\w+)',u2 or '')
|
|
if mm: return 'm:'+mm.group(1)
|
|
bb=re.search(r'boardId=(\w+)',u2 or '')
|
|
if bb: return 'b:'+bb.group(1)
|
|
return u2
|
|
def lc(ws,r):
|
|
for c in range(10,3,-1):
|
|
if ws.cell(r,c).value not in (None,''): return c
|
|
return None
|
|
def coltabs(K):
|
|
soup=M.fetch(SESS,K)
|
|
if soup is None: return None
|
|
b=M.get_body(soup,M.BODY_SEL)
|
|
for ul in b.select('ul[class*=col]'):
|
|
cl=' '.join(ul.get('class') or [])
|
|
if not re.match(r'col\d',cl): continue
|
|
tabs=[(re.sub(r'\s+',' ',c.find('a').get_text()).strip(), urljoin(K,c.find('a')['href']), c.find('a').get('target')) for c in ul.find_all('li',recursive=False) if c.find('a',href=True)]
|
|
if len(tabs)>=2: return tabs
|
|
return None
|
|
def tabLMN(url,tgt):
|
|
if tgt=='_blank': return '사이트',None,None
|
|
if 'board/list' in url:
|
|
soup=M.fetch(SESS,url)
|
|
if soup is None: return '게시판',0,'어문'
|
|
b=M.get_body(soup,M.BODY_SEL); txt=b.get_text(' ',strip=True)
|
|
mm=LN.TOT.search(txt); Mv=int([x for x in mm.groups() if x][0].replace(',','')) if mm else 0
|
|
img,vid,aud,t=LN.media(b)
|
|
for du in LN.detail_urls(b,url,10):
|
|
ds=M.fetch(SESS,du)
|
|
if ds:
|
|
i2,v2,a2,t2=LN.media(M.get_body(ds,M.BODY_SEL)); img=img or i2;vid=vid or v2;aud=aud or a2;t=t or t2
|
|
return '게시판',Mv,LN.njoin(t,img,vid,aud)
|
|
# page
|
|
soup=M.fetch(SESS,url)
|
|
if soup is None: return '페이지',1,'어문'
|
|
img,vid,aud,t=LN.media(M.get_body(soup,M.BODY_SEL))
|
|
return '페이지',1,LN.njoin(t,img,vid,aud)
|
|
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 main():
|
|
write='--write' in sys.argv
|
|
wb=openpyxl.load_workbook(XLSX); ws=wb.active
|
|
C=ws.cell(3,3).value
|
|
sheet=set(key(ws.cell(r,11).value) for r in range(3,ws.max_row+1) if isinstance(ws.cell(r,11).value,str)); sheet.discard(None)
|
|
plan=[]
|
|
for r in range(3,ws.max_row+1):
|
|
if ws.cell(r,12).value not in ('페이지','게시판'): continue
|
|
K=ws.cell(r,11).value
|
|
if not(isinstance(K,str) and 'jeongeup' in K): continue
|
|
L=lc(ws,r)
|
|
if not L or L>=10 or ws.cell(r,L+1).value not in (None,''): continue
|
|
tabs=coltabs(K)
|
|
if not tabs: continue
|
|
pk=key(K)
|
|
miss=[(t,u2,tg) for t,u2,tg in tabs if key(u2) not in sheet and key(u2)!=pk]
|
|
if miss: plan.append((r,L,ws.cell(r,L).value,miss))
|
|
print('col탭 전개대상 %d행, 신규탭합 %d'%(len(plan),sum(len(mi) for _,_,_,mi in plan)))
|
|
for r,L,lab,mi in plan: print(' r%d %s +%d: %s'%(r,lab,len(mi),[(t[:12],'board/list' in u2) for t,u2,_ in mi]))
|
|
if not write: return
|
|
shutil.copy(XLSX, XLSX.replace('.xlsx','_backup_coltab전.xlsx'))
|
|
tot=0
|
|
for r,L,lab,miss in sorted(plan,key=lambda t:-t[0]):
|
|
n=len(miss); remap_insert(ws,r+1,n)
|
|
for i,(t,u2,tg) in enumerate(miss):
|
|
rr=r+1+i; ws.cell(rr,3).value=C; ws.cell(rr,L+1).value=t; ws.cell(rr,11).value=u2
|
|
Lv,Mv,Nv=tabLMN(u2,tg)
|
|
ws.cell(rr,12).value=Lv; ws.cell(rr,13).value=Mv; ws.cell(rr,14).value=Nv
|
|
ws.row_dimensions[rr].height=15
|
|
for c in range(2,19): ws.cell(rr,c).fill=BLUE
|
|
ws.merge_cells(start_row=r,start_column=L,end_row=r+n,end_column=L)
|
|
tot+=n; print(' r%d %s +%d'%(r,lab,n))
|
|
for r in range(3,ws.max_row+1):
|
|
v=ws.cell(r,11).value; cell=ws.cell(r,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()
|