df16c98366
공공기관2 작업 중. _temp 몽타주(재생성가능)는 제외. Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
93 lines
4.0 KiB
Python
93 lines
4.0 KiB
Python
import sys, io, shutil, copy, os
|
|
sys.stdout = io.TextIOWrapper(sys.stdout.buffer, encoding='utf-8')
|
|
import openpyxl
|
|
|
|
P = r'D:\01.프로젝트\DB수집\작업파일\광역_사이트맵\충청남도\10.아산시\충청남도_아산시.xlsx'
|
|
BK = P.replace('.xlsx', '_backup_openInfo탭+S삭제전.xlsx')
|
|
if not os.path.exists(BK):
|
|
shutil.copy2(P, BK); print('backup ->', BK)
|
|
|
|
wb = openpyxl.load_workbook(P); ws = wb.active
|
|
ANCHOR = 37 # row 37 = no=55 정보공개제도안내
|
|
N_NEW = 9 # 10 total = 1 existing + 9 inserted
|
|
SHIFT = N_NEW
|
|
|
|
labels = ['행정정보공개란?','행정정보공개제도의 필요성','청구권자와 대상정보',
|
|
'공개대상에서 제외되는 정보','청구 및 처리 절차','행정정보공개는 어떻게 하나요?',
|
|
'불복구제절차란?','공공기관의 의무','정보공개 수수료','비공개대상정보']
|
|
BASE = 'https://www.asan.go.kr/main/cms/?no=55'
|
|
|
|
orig_max = ws.max_row
|
|
|
|
# 1) snapshot merges, then remove all with max_row >= ANCHOR
|
|
removed = [m for m in ws.merged_cells.ranges if m.max_row >= ANCHOR]
|
|
for m in list(removed):
|
|
ws.unmerge_cells(str(m))
|
|
|
|
# 2) insert 9 blank rows at ANCHOR+1 (moves old 38..end down by 9, with styles/values)
|
|
ws.insert_rows(ANCHOR + 1, N_NEW)
|
|
|
|
# 3) rebuild merges
|
|
def addmerge(min_col, min_row, max_col, max_row):
|
|
ws.merge_cells(start_row=min_row, start_column=min_col, end_row=max_row, end_column=max_col)
|
|
for m in removed:
|
|
if m.min_row <= ANCHOR <= m.max_row: # spans anchor -> extend downward
|
|
addmerge(m.min_col, m.min_row, m.max_col, m.max_row + SHIFT)
|
|
else: # entirely below -> shift
|
|
addmerge(m.min_col, m.min_row + SHIFT, m.max_col, m.max_row + SHIFT)
|
|
# parent F merge for the 10 openInfo rows
|
|
addmerge(6, ANCHOR, 6, ANCHOR + 9)
|
|
|
|
# 4) style template = row 37 per-column style; copy to new rows 38..46
|
|
from openpyxl.utils import get_column_letter
|
|
for col in range(1, ws.max_column + 1):
|
|
src = ws.cell(ANCHOR, col)
|
|
for i in range(1, N_NEW + 1):
|
|
dst = ws.cell(ANCHOR + i, col)
|
|
dst._style = copy.copy(src._style)
|
|
|
|
# 5) fill the 10 openInfo rows
|
|
def setrow(r, pg, label, first=False):
|
|
ws.cell(r, 2).value = r - 2 # B 순번 = row-2
|
|
ws.cell(r, 3).value = '충청남도 아산시' # C
|
|
if first:
|
|
ws.cell(r, 4).value = '정보공개' # D (merged anchor)
|
|
ws.cell(r, 5).value = '정보공개' # E (merged anchor)
|
|
ws.cell(r, 6).value = '정보공개제도안내' # F (merged anchor)
|
|
# non-first D/E/F are merged (read-only) -> leave as-is
|
|
ws.cell(r, 7).value = label # G 소분류 = tab label
|
|
for c in (8, 9, 10): # H,I,J
|
|
ws.cell(r, c).value = None
|
|
url = f'{BASE}&pg={pg}'
|
|
kc = ws.cell(r, 11); kc.value = url; kc.hyperlink = url # K
|
|
ws.cell(r, 12).value = '페이지' # L
|
|
ws.cell(r, 13).value = 1 # M
|
|
ws.cell(r, 14).value = '어문' # N
|
|
ws.cell(r, 15).value = '미부착' # O
|
|
for c in (16, 17, 18, 19): # P,Q,R,S
|
|
ws.cell(r, c).value = None
|
|
|
|
for i in range(10):
|
|
setrow(ANCHOR + i, i, labels[i], first=(i == 0))
|
|
|
|
# 6) renumber B for all data rows >= ANCHOR (= row-2); fix all K hyperlinks in shifted region
|
|
for r in range(ANCHOR, ws.max_row + 1):
|
|
ws.cell(r, 2).value = r - 2
|
|
kc = ws.cell(r, 11)
|
|
if kc.value and isinstance(kc.value, str) and kc.value.startswith('http'):
|
|
kc.hyperlink = kc.value
|
|
else:
|
|
kc.hyperlink = None
|
|
|
|
# 7) clear S where value == '외부링크' (whole sheet, position-independent)
|
|
s_cleared = 0
|
|
for r in range(3, ws.max_row + 1):
|
|
sc = ws.cell(r, 19)
|
|
if sc.value is not None and str(sc.value).strip() == '외부링크':
|
|
sc.value = None; s_cleared += 1
|
|
|
|
wb.save(P)
|
|
print(f'orig_max={orig_max} new_max={ws.max_row} (expected {orig_max+SHIFT})')
|
|
print(f'S 외부링크 cleared: {s_cleared}')
|
|
print('saved', P)
|