hehihoho3/DB_JOB
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
main
DB_JOB/작업파일/공공기관3/_fis_rebuild.py
hehihoho3 b0acd1e705 백업: 셀병합 정상화(D~J)+KDHC 옵션2/행높이17, 오늘(06-21) 제출 17곳 검수·압축 
- 공공기관2/3 작업본 + 오늘 제출 17곳 D~J 카테고리 셀병합 정상화
- 한국지역난방공사 옵션2(고아셀 F98 수정)+전행 높이17
- 제출_프리랜서2_2026-06-21.zip 생성(17개 xlsx, 2,468행)

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
2026-06-21 13:21:26 +09:00

191 lines
9.7 KiB
Python
Raw Permalink Blame History

# -*- coding: utf-8 -*-
"""한국재정정보원 풀리빌드: junk행 삭제 + 본문탭(tab-menu) F레벨 전개 + 사전정보공표 분류탭 split."""
import sys, io, copy, shutil, re
sys.stdout = io.TextIOWrapper(sys.stdout.buffer, encoding='utf-8')
import openpyxl
from openpyxl.styles import Alignment
from openpyxl.utils import get_column_letter
PATH = '12.한국재정정보원/한국재정정보원.xlsx'
shutil.copy(PATH, '12.한국재정정보원/_backup_탭전개전.xlsx')
wb = openpyxl.load_workbook(PATH)
ws = wb.active
# ---- 1. capture clean data rows 3..35 ----
def rowdata(r):
d = {'cells': {}, 'link': None}
for c in range(2, 28):
d['cells'][c] = ws.cell(r, c).value
k = ws.cell(r, 11)
if k.hyperlink:
d['link'] = k.hyperlink.target
return d
orig = {r: rowdata(r) for r in range(3, 36)}
# capture a style template from an existing data row (row 4) per column
style_tmpl = {}
for c in range(2, 28):
cell = ws.cell(4, c)
style_tmpl[c] = {
'font': copy.copy(cell.font),
'border': copy.copy(cell.border),
'fill': copy.copy(cell.fill),
'number_format': cell.number_format,
'alignment': copy.copy(cell.alignment),
}
# ---- 2. tab expansion data (scanned live) ----
# tabs attach as F-level (col 6) children under the E-leaf row.
# key = orig row number of the E-leaf; value = list of (label, url, L, M, N)
# L/M from live; N placeholder 'TBD' -> montage later. Default page N='어문'.
TABS = {
# 4 기관소개 (E) -> F children
4: [('소개','https://www.fis.kr/ko/intro/intro/intro','페이지',1),
('미션 및 비전','https://www.fis.kr/ko/intro/intro/mission_n_vision','페이지',1),
('연혁','https://www.fis.kr/ko/intro/intro/history','페이지',1),
('오시는길','https://www.fis.kr/ko/intro/intro/way_to_come','페이지',1)],
# 11 dBrain 운영 (E)
11: [('dBrain⁺ 소개','https://www.fis.kr/ko/major_biz/dbrain_oper/intro/what','페이지',1),
('기대효과','https://www.fis.kr/ko/major_biz/dbrain_oper/intro/benefit','페이지',1),
('구축경과','https://www.fis.kr/ko/major_biz/dbrain_oper/intro/progress','페이지',1),
('연계현황','https://www.fis.kr/ko/major_biz/dbrain_oper/intro/status','페이지',1)],
# 12 e나라도움 운영 (E)
12: [('e나라도움 소개','https://www.fis.kr/ko/major_biz/eNara_help_oper/intro/intro','페이지',1),
('주요기능','https://www.fis.kr/ko/major_biz/eNara_help_oper/intro/main_func','페이지',1),
('관련법령','https://www.fis.kr/ko/major_biz/eNara_help_oper/intro/laws','페이지',1),
('e나라도움 현황','https://www.fis.kr/ko/major_biz/eNara_help_oper/intro/new','페이지',1)],
# 19 발간자료 (E) -> 7 board tabs
19: [('분석보고서','https://www.fis.kr/ko/notification/data/report','게시판',46),
('FIS 재정리포트','https://www.fis.kr/ko/notification/data/issue_focus','게시판',17),
('통계자료집','https://www.fis.kr/ko/notification/data/financial_stat','게시판',53),
('월간 나라재정','https://www.fis.kr/ko/notification/data/monthly_country_finance','페이지',1),
('해외재정동향','https://www.fis.kr/ko/notification/data/InternationalFiscalTrends','게시판',1),
('기타 발간물','https://www.fis.kr/ko/notification/data/other_publications','게시판',23),
('(구)발간자료','https://www.fis.kr/ko/notification/data/financial_stat_brief','게시판',49)],
# 23 사회적책임 경영 (E)
23: [('고객만족 경영','https://www.fis.kr/ko/disclosure/manage/customer_satisfy/system','페이지',1),
('고객헌장','https://www.fis.kr/ko/disclosure/manage/customer_satisfy/charter','페이지',1),
('핵심서비스 이행표준','https://www.fis.kr/ko/disclosure/manage/customer_satisfy/standatds','페이지',1),
('고객응대서비스 이행표준','https://www.fis.kr/ko/disclosure/manage/customer_satisfy/customerServices','페이지',1)],
# 26 정보공개 (E)
26: [('신청','https://www.fis.kr/ko/disclosure/disclosure/apply','페이지',1),
('제도안내','https://www.fis.kr/ko/disclosure/disclosure/system_guide','페이지',1),
('업무절차','https://www.fis.kr/ko/disclosure/disclosure/working_process','페이지',1),
('비공개대상정보','https://www.fis.kr/ko/disclosure/disclosure/non-disclosed_info','페이지',1)],
# 27 사전정보공표 (E) -> 6 category board split (no '전체' tab)
27: [('인사 총무·회계','https://www.fis.kr/ko/disclosure/advance_info_disclosure?cateSubCode=0101','게시판',31),
('감사·청렴·윤리','https://www.fis.kr/ko/disclosure/advance_info_disclosure?cateSubCode=0102','게시판',31),
('기획·재정','https://www.fis.kr/ko/disclosure/advance_info_disclosure?cateSubCode=0103','게시판',6),
('재정시스템','https://www.fis.kr/ko/disclosure/advance_info_disclosure?cateSubCode=0104','게시판',73),
('보조금시스템','https://www.fis.kr/ko/disclosure/advance_info_disclosure?cateSubCode=0105','게시판',127),
('정보보안','https://www.fis.kr/ko/disclosure/advance_info_disclosure?cateSubCode=0106','게시판',18)],
# 29 인재채용 (E)
29: [('채용정보','https://www.fis.kr/ko/disclosure/employee/recruit','게시판',95),
('NCS기반 직무기술서','https://www.fis.kr/ko/disclosure/employee/ncs_based','게시판',39),
('인재상','https://www.fis.kr/ko/disclosure/employee/talent','페이지',1)],
}
# 사전정보공표(27) becomes a parent: its own M cleared (becomes category container),
# children carry counts. We turn row 27 into a non-board parent row at E whose F children are boards.
SPLIT_PARENTS = {27}
# ---- 3. build new ordered record list ----
records = [] # each: dict with D..J labels(list len7), K url, link, L,M,N,O, and orig style row ref
def make_rec(dvals, url, link, L, M, N, O, src_row, is_new=False):
return {'d': dvals[:], 'url': url, 'link': link, 'L': L, 'M': M, 'N': N, 'O': O,
'src': src_row, 'new': is_new}
for r in range(3, 36):
od = orig[r]
# D..J = cols 4..10 (7 cols)
dvals = [od['cells'][c] for c in range(4, 11)]
rec = make_rec(dvals, od['cells'][11], od['link'],
od['cells'][12], od['cells'][13], od['cells'][14], od['cells'][15], r)
if r in SPLIT_PARENTS:
# parent becomes category container: clear L/M/N (it's just a board-group landing -> keep page? )
# 사전정보공표 landing itself shows the board; but children are the real boards.
# Make parent a 페이지 (landing) so it isn't double-counted.
rec['L'] = '페이지'; rec['M'] = 1
records.append(rec)
# attach F children (col index 6 -> dvals index 2)
if r in TABS:
# find depth of the E-leaf label: which col has the label
# row r label sits at col E(5) for these -> children at F(6) = dvals idx2
for (lab, url, L, M) in TABS[r]:
cd = [None]*7
cd[2] = lab # F = index 2 (D=0,E=1,F=2)
recc = make_rec(cd, url, url, L, M, '어문', '미부착', None, is_new=True)
records.append(recc)
print('total records:', len(records))
# ---- 4. write to fresh sheet region (rows 3+) ----
# remove ALL merged ranges that start at row >=3 (data area), keep header merges (rows1-2)
for mr in list(ws.merged_cells.ranges):
if mr.min_row >= 3:
ws.unmerge_cells(str(mr))
# clear all data rows
if ws.max_row >= 3:
ws.delete_rows(3, ws.max_row) # remove everything below header
# unmerge any leftover data merges (header merges in rows1-2 preserved by delete_rows? re-add D merges later)
START = 3
n = len(records)
for i, rec in enumerate(records):
rr = START + i
# B 순번
ws.cell(rr, 2, i+1)
ws.cell(rr, 3, '한국재정정보원') # C
for j in range(7): # D..J cols 4..10
ws.cell(rr, 4+j, rec['d'][j])
kc = ws.cell(rr, 11, rec['url'])
if rec['link']:
kc.hyperlink = rec['link']
ws.cell(rr, 12, rec['L'])
ws.cell(rr, 13, rec['M'])
ws.cell(rr, 14, rec['N'])
ws.cell(rr, 15, rec['O'])
# apply style template to all cols
for c in range(2, 28):
st = style_tmpl[c]
cell = ws.cell(rr, c)
cell.font = copy.copy(st['font'])
cell.border = copy.copy(st['border'])
cell.fill = copy.copy(st['fill'])
cell.number_format = st['number_format']
cell.alignment = copy.copy(st['alignment'])
# ---- 5. rebuild D..J vertical merges (same-value runs, blank below) ----
# For each column D(4)..J(10): merge consecutive rows where parent col value present and
# child col empty -> we merge a parent cell down over its descendant rows.
last_row = START + n - 1
# Standard sitemap merge: a value in col c spans until the next non-empty value in col c.
for c in range(4, 11):
r = START
while r <= last_row:
v = ws.cell(r, c).value
if v is None or v == '':
r += 1; continue
# find extent: next row with non-empty in this col, OR a row whose ANY ancestor col(<c) is non-empty (new parent)
end = r
rr = r+1
while rr <= last_row:
if ws.cell(rr, c).value not in (None, ''):
break
# stop if an ancestor column (left of c) starts a new block
anc = any(ws.cell(rr, cc).value not in (None, '') for cc in range(4, c))
if anc:
break
end = rr
rr += 1
if end > r:
ws.merge_cells(start_row=r, start_column=c, end_row=end, end_column=c)
ws.cell(r, c).alignment = Alignment(horizontal='center', vertical='center',
wrap_text=ws.cell(r, c).alignment.wrap_text)
r = end + 1
wb.save(PATH)
print('saved. rows 3..%d' % last_row)
Reference in New Issue View Git Blame Copy Permalink