df16c98366
공공기관2 작업 중. _temp 몽타주(재생성가능)는 제외. Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
325 lines
16 KiB
Python
325 lines
16 KiB
Python
# -*- coding: utf-8 -*-
|
||
"""
|
||
충남표준CMS 본문 콘텐츠탭 펼치기 전개(서천식 풀리빌드). 범용·읽기/리빌드 분리.
|
||
사용:
|
||
dry: python _tab_expand_cms.py --xlsx <경로> --dom <도메인> --org <기관명> [--sel basic_tab,tab-ul]
|
||
write: 위 + --write -> 같은 폴더에 _<원본명>_NEW.xlsx 생성(원본 무수정)
|
||
|
||
규칙(펼치기): 페이지탭→별도행 / 게시판탭→행+M=건수 / 인페이지#앵커탭→랜딩 M=탭수(행X) / 외부=사이트.
|
||
랜딩URL과 같은 탭=랜딩행 재사용(G=탭명), 그 외 탭=신규행. 이미 시트에 있는 탭(covered)은 스킵.
|
||
"""
|
||
import openpyxl, requests, sys, re, json, os, argparse
|
||
from copy import copy
|
||
from bs4 import BeautifulSoup
|
||
from urllib.parse import urljoin, urlparse
|
||
from openpyxl.utils import get_column_letter
|
||
from concurrent.futures import ThreadPoolExecutor, as_completed
|
||
import urllib3
|
||
urllib3.disable_warnings()
|
||
sys.stdout.reconfigure(encoding='utf-8')
|
||
UA = {'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64)'}
|
||
|
||
DEFAULT_SEL = ['basic_tab', 'tab-ul', 'slave_tab', 'tab_menu', 'tab_button',
|
||
'tabmenu', 'sub_tab', 'con_tab', 'tab_wrap', 'fiexd_tab', 'basic_tab3']
|
||
BAD_TOKENS = {'table', 'tablewrap', 'response', 'grap', 'no-more-tables',
|
||
'depth', 'side', 'location', 'gnb', 'lnb', 'snb', 'allmenu', 'footer'}
|
||
NAV_ANCESTORS = ('header', 'gnb', 'lnb', 'snb', 'nav', 'footer', 'top', 'aside', 'allmenu', 'sitemap')
|
||
|
||
def make_norm(dom):
|
||
def norm(u):
|
||
if not u: return ''
|
||
u = u.split('#')[0].rstrip('/')
|
||
return re.sub(r'^https?://(www\.)?' + re.escape(dom).replace(r'\.', r'\.'), '', u)
|
||
return norm
|
||
|
||
def in_nav(el):
|
||
p = el
|
||
for _ in range(8):
|
||
p = p.parent
|
||
if p is None: break
|
||
idc = ((p.get('id') or '') + ' ' + ' '.join(p.get('class') or [])).lower()
|
||
if any(k in idc for k in NAV_ANCESTORS): return True
|
||
return False
|
||
|
||
def is_cms_tab(el, sel):
|
||
cls = ' '.join(el.get('class') or [])
|
||
if not cls: return False
|
||
if any(b in cls.lower() for b in BAD_TOKENS): return False
|
||
if not any(re.search(r'(^|\s)' + re.escape(t) + r'(\d|\s|$)', cls + ' ') for t in sel):
|
||
return False
|
||
if in_nav(el): return False
|
||
return True
|
||
|
||
def classify(a, href, dom):
|
||
target = (a.get('target') or ''); cls = ' '.join(a.get('class') or []).lower()
|
||
host = urlparse(href).netloc.lower(); h = href.split('#')[0]
|
||
if not h or href.strip().startswith('#') or href.strip().lower().startswith('javascript'):
|
||
return 'anchor'
|
||
if target == '_blank' or 'link_3th' in cls or (host and dom not in host):
|
||
return 'site'
|
||
hl = h.lower()
|
||
if 'selectboardlist' in hl or '/bbs/' in hl or 'bbsmstr' in hl or '/cop/bbs/' in hl:
|
||
return 'bbs'
|
||
return 'page'
|
||
|
||
def total_of(html):
|
||
txt = BeautifulSoup(html, 'html.parser').get_text(' ')
|
||
for pat in [r'Total\s*[:：]\s*([\d,]+)', r'총\s*([\d,]+)\s*건', r'게시물\s*[:：]?\s*([\d,]+)',
|
||
r'전체\s*[:：]?\s*([\d,]+)\s*건']:
|
||
m = re.search(pat, txt)
|
||
if m: return int(m.group(1).replace(',', ''))
|
||
return None
|
||
|
||
def build_plan(xlsx, dom, sel, workers=16):
|
||
wb = openpyxl.load_workbook(xlsx); ws = wb.active
|
||
norm = make_norm(dom)
|
||
rows = []
|
||
for r in range(3, ws.max_row + 1):
|
||
c = ws.cell(r, 11); L = ws.cell(r, 12).value
|
||
u = c.hyperlink.target if c.hyperlink else (c.value if isinstance(c.value, str) and c.value.startswith('http') else None)
|
||
if u: rows.append({'r': r, 'url': u, 'L': L, 'F': ws.cell(r, 6).value, 'G': ws.cell(r, 7).value})
|
||
sheet_norm = set(norm(x['url']) for x in rows)
|
||
targets = [x for x in rows if x['L'] in ('페이지', '게시판') and dom in urlparse(x['url']).netloc]
|
||
uniq = {}
|
||
for x in targets: uniq.setdefault(norm(x['url']), x)
|
||
sess = requests.Session(); sess.headers.update(UA)
|
||
def fetch(u):
|
||
try:
|
||
rr = sess.get(u, timeout=25, verify=False); rr.encoding = rr.apparent_encoding or 'utf-8'; return u, rr.text
|
||
except Exception: return u, None
|
||
htmlcache = {}
|
||
with ThreadPoolExecutor(max_workers=workers) as ex:
|
||
for fu in as_completed([ex.submit(fetch, x['url']) for x in uniq.values()]):
|
||
u, h = fu.result(); htmlcache[u] = h
|
||
|
||
groups = []
|
||
for x in uniq.values():
|
||
html = htmlcache.get(x['url'])
|
||
if not html: continue
|
||
soup = BeautifulSoup(html, 'html.parser')
|
||
found = None
|
||
for el in soup.find_all(['ul', 'div']):
|
||
if not is_cms_tab(el, sel): continue
|
||
lis = [li for li in el.find_all('li') if li.find('a')]
|
||
if len(lis) < 2: continue
|
||
tabs = []
|
||
for li in lis:
|
||
a = li.find('a'); href = urljoin(x['url'], a.get('href', '').strip())
|
||
tabs.append({'label': ' '.join(li.get_text().split()), 'href': href,
|
||
'kind': classify(a, href, dom), 'norm': norm(href)})
|
||
pb = [t for t in tabs if t['kind'] in ('page', 'bbs')]
|
||
anchors = [t for t in tabs if t['kind'] == 'anchor']
|
||
# 인페이지 판정: page/bbs 탭의 '서로 다른 URL' 수가 2 미만이면(=같은 .do#앵커로 수렴)
|
||
# 행으로 펼치지 말고 랜딩 M=탭수. (순수 # 앵커탭 + sub.do#anchor 동일페이지탭 모두 커버)
|
||
distinct_pb = {t['norm'] for t in pb}
|
||
if len(distinct_pb) < 2:
|
||
if len(anchors) + len(pb) >= 2:
|
||
found = {'row': x['r'], 'F': x['F'], 'G': x['G'], 'url': x['url'], 'norm': norm(x['url']),
|
||
'inpage': True, 'anchor_n': len(tabs)}
|
||
break
|
||
continue
|
||
# 실제 다중페이지 탭: 외부 site 탭은 별도행(L=사이트) 유지. norm 중복은 제거(같은URL 1회만).
|
||
keep = []; seen_n = set()
|
||
for t in tabs:
|
||
if t['kind'] == 'anchor': continue
|
||
if t['norm'] in seen_n: continue
|
||
seen_n.add(t['norm']); keep.append(t)
|
||
found = {'row': x['r'], 'F': x['F'], 'G': x['G'], 'url': x['url'], 'norm': norm(x['url']),
|
||
'inpage': False, 'tabs': keep}
|
||
break
|
||
if found: groups.append(found)
|
||
|
||
# board totals (재시도 포함, 동시성 낮춰 빌드시 누락 방지)
|
||
board_urls = list({t['href'] for g in groups if not g['inpage'] for t in g['tabs'] if t['kind'] == 'bbs'})
|
||
def fetch_total(u):
|
||
for attempt in range(3):
|
||
try:
|
||
rr = sess.get(u, timeout=30, verify=False); rr.encoding = rr.apparent_encoding or 'utf-8'
|
||
t = total_of(rr.text)
|
||
if t is not None: return t
|
||
# 본문에 게시판 시그니처 없으면 진짜 0/비보드일 수 있음 → 0
|
||
return 0 if BeautifulSoup(rr.text, 'html.parser').get_text() else None
|
||
except Exception:
|
||
continue
|
||
return None
|
||
btot = {}
|
||
with ThreadPoolExecutor(max_workers=6) as ex:
|
||
futs = {ex.submit(fetch_total, u): u for u in board_urls}
|
||
for fu in as_completed(futs):
|
||
btot[futs[fu]] = fu.result()
|
||
for g in groups:
|
||
if g['inpage']: continue
|
||
for t in g['tabs']:
|
||
if t['kind'] == 'bbs': t['total'] = btot.get(t['href'])
|
||
return ws, groups, sheet_norm, norm
|
||
|
||
def kind_fields(kind, total=None):
|
||
if kind == 'site': return dict(L='사이트', M=None, N=None, O=None)
|
||
if kind == 'bbs': return dict(L='게시판', M=(total if total is not None else 0), N='어문', O='미부착')
|
||
return dict(L='페이지', M=1, N='어문', O='미부착')
|
||
|
||
def rebuild(xlsx, groups, sheet_norm, norm, org, outpath):
|
||
wb = openpyxl.load_workbook(xlsx); ws = wb.active
|
||
NCOL = 27
|
||
DATA_LO = 3
|
||
# 실데이터 경계: B/C/D/E/F/G/L 중 하나라도 값 있는 마지막 행.
|
||
# (gb.go.kr 템플릿 잔재=K하이퍼링크만 남은 유령행은 제외)
|
||
SIG = (2, 3, 4, 5, 6, 7, 12)
|
||
DATA_HI = DATA_LO
|
||
for r in range(DATA_LO, ws.max_row + 1):
|
||
if any(ws.cell(r, c).value not in (None, '') for c in SIG):
|
||
DATA_HI = r
|
||
plan_by_row = {g['row']: g for g in groups}
|
||
# 전역위젯 가드: 같은 타깃 URL이 ≥3개 다른 랜딩그룹의 탭으로 반복 → nav 위젯(고정타깃)으로 보고 제외.
|
||
# (진짜 per-entity 콘텐츠탭은 랜딩마다 타깃 URL이 유니크하므로 1개 그룹에만 등장)
|
||
from collections import Counter as _C
|
||
_gt = _C()
|
||
for g in groups:
|
||
if g.get('inpage'): continue
|
||
for tn in {t['norm'] for t in g['tabs']}:
|
||
_gt[tn] += 1
|
||
GLOBAL_WIDGET = {tn for tn, c in _gt.items() if c >= 3}
|
||
|
||
def covering(col, r):
|
||
for m in ws.merged_cells.ranges:
|
||
if m.min_col <= col <= m.max_col and m.min_row <= r <= m.max_row:
|
||
return str(m)
|
||
return None
|
||
def keymap(col):
|
||
return {r: (covering(col, r) or f'{get_column_letter(col)}{r}') for r in range(DATA_LO, DATA_HI + 1)}
|
||
DKEY = keymap(4); EKEY = keymap(5); FKEY = keymap(6)
|
||
|
||
out = []; fgrp_id = 0; added_norms = set()
|
||
for r in range(DATA_LO, DATA_HI + 1):
|
||
base = {'tpl': r, 'over': {}, 'klink': None, 'dkey': DKEY[r], 'ekey': EKEY[r], 'fkey': FKEY[r], 'fgrp': None, 'ggrp': None}
|
||
out.append(base)
|
||
g = plan_by_row.get(r)
|
||
if not g: continue
|
||
if g.get('inpage'):
|
||
base['over']['M'] = g['anchor_n'] # 인페이지탭: 랜딩 M=탭수, 행X
|
||
continue
|
||
# 전역위젯 탭 제거(랜딩 자기 자신은 유지). 제거 후 page/board <2면 전개 안 함.
|
||
tabs = [t for t in g['tabs'] if t['norm'] not in GLOBAL_WIDGET or t['norm'] == g['norm']]
|
||
if sum(1 for t in tabs if t['kind'] in ('page', 'bbs')) < 2:
|
||
continue
|
||
# 신규로 추가할 탭: 랜딩과 같은 탭(매치)은 랜딩행 재사용, 나머지는 covered 아닌 것만 신규행
|
||
midx = next((i for i, t in enumerate(tabs) if t['norm'] == g['norm']), None)
|
||
if midx is not None:
|
||
base['over']['G'] = tabs[midx]['label']
|
||
others = [t for i, t in enumerate(tabs) if i != midx]
|
||
else:
|
||
t0 = tabs[0]
|
||
base['over'].update({'G': t0['label'], 'K': t0['href'], **kind_fields(t0['kind'], t0.get('total'))})
|
||
base['klink'] = t0['href']
|
||
added_norms.add(t0['norm'])
|
||
others = tabs[1:]
|
||
fgrp_id += 1
|
||
ffull = g['F']
|
||
base['fgrp'] = ('F', fgrp_id) if ffull is not None else None
|
||
for t in others:
|
||
if t['norm'] != g['norm'] and t['norm'] in sheet_norm:
|
||
continue # 이미 시트에 있음(covered) → 중복 방지
|
||
if t['norm'] in added_norms:
|
||
continue # 이번 리빌드에서 이미 추가한 URL → 중복 방지
|
||
added_norms.add(t['norm'])
|
||
nr = {'tpl': r, 'dkey': DKEY[r], 'ekey': EKEY[r], 'fkey': FKEY[r],
|
||
'over': {'C': org, 'F': None, 'G': t['label'], 'K': t['href'], **kind_fields(t['kind'], t.get('total'))},
|
||
'klink': t['href'], 'fgrp': (('F', fgrp_id) if ffull is not None else None), 'ggrp': None}
|
||
out.append(nr)
|
||
|
||
nwb = openpyxl.Workbook(); nws = nwb.active; nws.title = ws.title
|
||
for col, dim in ws.column_dimensions.items():
|
||
nws.column_dimensions[col].width = dim.width; nws.column_dimensions[col].hidden = dim.hidden
|
||
nws.sheet_format = copy(ws.sheet_format)
|
||
try: nws.freeze_panes = ws.freeze_panes
|
||
except Exception: pass
|
||
|
||
def copy_cell(srow, scol, drow, dcol, value='__keep__', link='__none__'):
|
||
s = ws.cell(srow, scol); d = nws.cell(drow, dcol)
|
||
if s.has_style:
|
||
d.font = copy(s.font); d.fill = copy(s.fill); d.border = copy(s.border)
|
||
d.alignment = copy(s.alignment); d.protection = copy(s.protection); d.number_format = s.number_format
|
||
d.value = s.value if value == '__keep__' else value
|
||
if link != '__none__':
|
||
if link: d.hyperlink = link
|
||
else:
|
||
if s.hyperlink is not None: d.hyperlink = copy(s.hyperlink)
|
||
return d
|
||
|
||
for r in (1, 2):
|
||
h = ws.row_dimensions[r].height
|
||
if h: nws.row_dimensions[r].height = h
|
||
for c in range(1, NCOL + 1): copy_cell(r, c, r, c)
|
||
|
||
L2C = {get_column_letter(c): c for c in range(1, NCOL + 1)}
|
||
for i, ro in enumerate(out):
|
||
drow = 3 + i; tpl = ro['tpl']
|
||
h = ws.row_dimensions[tpl].height
|
||
if h: nws.row_dimensions[drow].height = h
|
||
overcols = {L2C[k]: v for k, v in ro['over'].items()}
|
||
for c in range(1, NCOL + 1):
|
||
if c == 11 and ro['klink'] is not None:
|
||
copy_cell(tpl, c, drow, c, value=ro['over'].get('K', ws.cell(tpl, c).value), link=ro['klink'])
|
||
elif c in overcols:
|
||
copy_cell(tpl, c, drow, c, value=overcols[c], link='' if c == 11 else '__none__')
|
||
else:
|
||
copy_cell(tpl, c, drow, c)
|
||
nws.cell(drow, 2).value = i + 1
|
||
ro['drow'] = drow
|
||
|
||
for m in ws.merged_cells.ranges:
|
||
if m.max_row <= 2: nws.merge_cells(str(m))
|
||
|
||
def merge_runs(keyattr, col):
|
||
i = 0; n = len(out)
|
||
while i < n:
|
||
j = i
|
||
while j + 1 < n and out[j + 1][keyattr] == out[i][keyattr]: j += 1
|
||
if j > i:
|
||
nws.merge_cells(start_row=out[i]['drow'], start_column=col, end_row=out[j]['drow'], end_column=col)
|
||
i = j + 1
|
||
# D/E/F 병합: 원본 병합키(FKEY 포함)로 재구성 → 비-탭 원본 F병합도 보존,
|
||
# 탭 신규행은 랜딩 FKEY 상속이라 같은 F블록으로 자동 병합.
|
||
merge_runs('dkey', 4); merge_runs('ekey', 5); merge_runs('fkey', 6)
|
||
|
||
nwb.save(outpath)
|
||
return len(out)
|
||
|
||
if __name__ == '__main__':
|
||
ap = argparse.ArgumentParser()
|
||
ap.add_argument('--xlsx', required=True); ap.add_argument('--dom', required=True)
|
||
ap.add_argument('--org', required=True); ap.add_argument('--sel', default=','.join(DEFAULT_SEL))
|
||
ap.add_argument('--write', action='store_true')
|
||
a = ap.parse_args()
|
||
sel = [s.strip() for s in a.sel.split(',') if s.strip()]
|
||
ws, groups, sheet_norm, norm = build_plan(a.xlsx, a.dom, sel)
|
||
from collections import Counter as _C
|
||
_gt = _C()
|
||
for g in groups:
|
||
if g.get('inpage'): continue
|
||
for tn in {t['norm'] for t in g['tabs']}: _gt[tn] += 1
|
||
WID = {tn for tn, c in _gt.items() if c >= 3}
|
||
exp = [g for g in groups if not g['inpage']]
|
||
inp = [g for g in groups if g['inpage']]
|
||
addcnt = 0; skipped_w = 0
|
||
print(f"== {a.org} == 탭그룹 {len(groups)} (전개 {len(exp)} / 인페이지 {len(inp)}) / 전역위젯URL {len(WID)}")
|
||
for g in sorted(exp, key=lambda g: -len(g['tabs'])):
|
||
tabs = [t for t in g['tabs'] if t['norm'] not in WID or t['norm'] == g['norm']]
|
||
if sum(1 for t in tabs if t['kind'] in ('page', 'bbs')) < 2:
|
||
skipped_w += 1; continue
|
||
new = [t for t in tabs if t['norm'] == g['norm'] or t['norm'] not in sheet_norm]
|
||
matched = any(t['norm'] == g['norm'] for t in tabs)
|
||
add = len(new) - (1 if matched else 0)
|
||
addcnt += add
|
||
kinds = {}
|
||
for t in tabs: kinds[t['kind']] = kinds.get(t['kind'], 0) + 1
|
||
print(f" R{g['row']:>3} {str(g['F'])[:18]:18} tabs={len(tabs)} +{add} {kinds}")
|
||
for g in inp:
|
||
print(f" R{g['row']:>3} {str(g['F'])[:18]:18} [인페이지] M={g['anchor_n']}")
|
||
print(f"예상 신규행 +{addcnt} (전역위젯으로 제외된 그룹 {skipped_w})")
|
||
if a.write:
|
||
out = os.path.join(os.path.dirname(os.path.abspath(a.xlsx)),
|
||
'_' + os.path.splitext(os.path.basename(a.xlsx))[0] + '_NEW.xlsx')
|
||
n = rebuild(a.xlsx, groups, sheet_norm, norm, a.org, out)
|
||
print(f"SAVED {out} 데이터행={n}")
|