# -*- coding: utf-8 -*- """기관 구조(양식) 검수 — 읽기전용. 매뉴얼 1-8 체크리스트. 사용: python _검수체크.py <기관명> 출력: PASS (이상없음) 또는 FAIL + 이슈목록. 검수 항목(구조/양식만; 내용 정확도는 대상 아님): - 헤더병합 B1:R1·S1:W1·Y1:AA1 - 시트명 {번호}_{기관명} - B순번 1..N 연속(gap/중복/비정수 없음) - D/E/F 병합이 그룹 경계 안 넘음(E⊆D, F⊆D+E) - K열 빈값/javascript/# 없음 - 병합 범위 겹침 없음 - 행높이 15(표본) """ import sys, glob, os, re import openpyxl from openpyxl.utils import range_boundaries HEADER_MERGES = {'B1:R1', 'S1:W1', 'Y1:AA1'} def find_file(name): base = os.path.dirname(os.path.abspath(__file__)) cands = [] for p in glob.glob(os.path.join(base, '*', '*', '*.xlsx')): if 'backup' in p or '_r2' in p or os.path.basename(p).startswith('_'): continue if os.path.basename(p).replace('.xlsx', '') == name: cands.append(p) if not cands: # 부분일치 fallback for p in glob.glob(os.path.join(base, '*', '*', '*.xlsx')): if 'backup' in p or '_r2' in p: continue if name in os.path.basename(p): cands.append(p) return cands[0] if cands else None def inspect(path): issues = [] wb = openpyxl.load_workbook(path) ws = wb.active # 시트명 if not re.match(r'^\d+_.+', ws.title or ''): issues.append(f'시트명 형식 이상: {ws.title!r} (기대 "번호_기관명")') # 헤더병합 hm = {str(m) for m in ws.merged_cells.ranges} miss = [h for h in HEADER_MERGES if h not in hm] if miss: issues.append(f'헤더병합 누락: {miss}') # 데이터행 + flatten D/E/F data = [r for r in range(3, ws.max_row + 1) if ws.cell(r, 2).value not in (None, '')] fv = {r: {c: ws.cell(r, c).value for c in (4, 5, 6)} for r in data} for mr in ws.merged_cells.ranges: if mr.min_col in (4, 5, 6) and mr.min_row >= 3: top = ws.cell(mr.min_row, mr.min_col).value for rr in range(mr.min_row, mr.max_row + 1): if rr in fv and fv[rr].get(mr.min_col) in (None, ''): fv[rr][mr.min_col] = top # B순번 연속 bs = [ws.cell(r, 2).value for r in data] exp = list(range(1, len(bs) + 1)) if bs != exp: nums = [b for b in bs if isinstance(b, (int, float))] s = set(nums) gaps = [i for i in range(1, int(max(nums)) + 1) if i not in s] if nums else [] dups = sorted({x for x in nums if nums.count(x) > 1}) nonint = [b for b in bs if not isinstance(b, (int, float))] issues.append(f'B순번 비연속: 행수={len(bs)} 최대={max(nums) if nums else None} 빠진={gaps[:15]} 중복={dups[:10]} 비정수={nonint[:5]}') # D/E/F 병합 경계 for mr in ws.merged_cells.ranges: if mr.min_row < 3: continue rng = range(mr.min_row, mr.max_row + 1) if mr.min_col == 5: if len({fv.get(r, {}).get(4) for r in rng if r in fv}) > 1: issues.append(f'E병합 {mr}이 D경계 넘음') if mr.min_col == 6: if len({(fv.get(r, {}).get(4), fv.get(r, {}).get(5)) for r in rng if r in fv}) > 1: issues.append(f'F병합 {mr}이 D+E경계 넘음') # K열 빈/js/# badk = [r for r in data if (lambda k: k in (None, '') or (isinstance(k, str) and k.strip().startswith(('javascript:', '#'))))(ws.cell(r, 11).value)] if badk: issues.append(f'K열 빈값/js/#: {len(badk)}건 (행 {badk[:8]})') # 콘텐츠 행(순번 또는 URL 또는 메뉴 D~J 보유) — 연속행 포함 content = [r for r in range(3, ws.max_row + 1) if ws.cell(r, 2).value not in (None, '') or ws.cell(r, 11).value not in (None, '') or any(ws.cell(r, c).value not in (None, '') for c in range(4, 11))] # B순번 빈칸(콘텐츠 행인데 B 비어있음) → 자동채움(모든 콘텐츠행 1..N 연속, 빈칸 없이) b_blank = [r for r in content if not isinstance(ws.cell(r, 2).value, int)] if b_blank: issues.append(f'[자동채움] B순번 빈칸(연속행 등): {len(b_blank)}건 (행 {b_blank[:12]})') # 사이트명(C) 빈칸 → 자동채움 대상(워크플로우에서 기관명 기입) c_empty = [r for r in content if ws.cell(r, 3).value in (None, '')] if c_empty: issues.append(f'[자동채움] 사이트명(C) 빈칸: {len(c_empty)}건 (행 {c_empty[:12]})') # 메뉴명/카테고리(D~J) 전무/중간구멍 → 보고(자동수정 X) # D/E/F=병합 상속, G~J=carry-forward(채워내림; G는 병합 안 되므로 첫행에만 값·형제행 None이 정상). mfill = {} for mr in ws.merged_cells.ranges: if mr.min_col in (4, 5, 6) and mr.min_row >= 3: top = ws.cell(mr.min_row, mr.min_col).value for rr in range(mr.min_row, mr.max_row + 1): mfill[(rr, mr.min_col)] = top def effdef(r, c): v = ws.cell(r, c).value return v if v not in (None, '') else mfill.get((r, c)) nomenu, holes = [], [] carry = {7: None, 8: None, 9: None, 10: None} prevdef = object() for r in content: eD, eE, eF = effdef(r, 4), effdef(r, 5), effdef(r, 6) if (eD, eE, eF) != prevdef: carry = {7: None, 8: None, 9: None, 10: None}; prevdef = (eD, eE, eF) res = [eD, eE, eF] for col in (7, 8, 9, 10): # G~J carry-forward own = ws.cell(r, col).value if own not in (None, ''): carry[col] = own for dc in (8, 9, 10): if dc > col: carry[dc] = None res.append(carry[col]) res = [v if v not in (None, '') else None for v in res] filled = [i for i, v in enumerate(res) if v is not None] if not filled: nomenu.append(r) elif any(res[i] is None for i in range(max(filled))): holes.append(r) if nomenu: issues.append(f'[보고] 메뉴명/카테고리 전무: {len(nomenu)}건 (행 {nomenu[:12]})') if holes: issues.append(f'[보고] 카테고리(대/중/소) 중간 빈칸: {len(holes)}건 (행 {holes[:12]})') # 병합 겹침 mranges = [range_boundaries(str(m)) for m in ws.merged_cells.ranges] overlap = 0 seen_cells = {} for (c1, r1, c2, r2) in mranges: for rr in range(r1, r2 + 1): for cc in range(c1, c2 + 1): if (rr, cc) in seen_cells: overlap += 1 seen_cells[(rr, cc)] = 1 if overlap: issues.append(f'병합 겹침 셀 {overlap}개') # 행높이(표본 50) nonstd = [r for r in data[:50] if ws.row_dimensions[r].height not in (15, None)] if len(nonstd) > 5: issues.append(f'행높이≠15 다수(표본50중 {len(nonstd)})') return len(data), issues def main(): if len(sys.argv) < 2: print('사용: python _검수체크.py <기관명>'); return name = sys.argv[1] path = find_file(name) if not path: print(f'FAIL: 파일 못 찾음 ({name})'); return n, issues = inspect(path) rel = os.path.relpath(path, os.path.dirname(os.path.abspath(__file__))) print(f'기관: {name} | 파일: {rel} | 데이터 {n}행') if issues: print(f'FAIL — 구조 이상 {len(issues)}건:') for x in issues: print(f' ✗ {x}') else: print('PASS — 구조/양식 이상 없음 (헤더병합·시트명·B순번연속·D/E/F병합경계·K열·병합겹침 정상)') if __name__ == '__main__': main()