# -*- 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}")