df16c98366
공공기관2 작업 중. _temp 몽타주(재생성가능)는 제외. Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
77 lines
3.0 KiB
Python
77 lines
3.0 KiB
Python
import sys, io, os, shutil
|
|
sys.stdout = io.TextIOWrapper(sys.stdout.buffer, encoding='utf-8')
|
|
import openpyxl
|
|
from openpyxl.styles import Alignment
|
|
from openpyxl.utils import get_column_letter
|
|
|
|
P = r'D:\01.프로젝트\DB수집\작업파일\광역_사이트맵\충청남도\8.서산시\충청남도_서산시.xlsx'
|
|
BK = P.replace('.xlsx','_backup_검수정렬병합전.xlsx')
|
|
if not os.path.exists(BK): shutil.copy2(P,BK); print('backup ->', os.path.basename(BK))
|
|
|
|
wb=openpyxl.load_workbook(P); ws=wb.active
|
|
|
|
def merges(): return list(ws.merged_cells.ranges)
|
|
def mof(r,c,Ms):
|
|
for m in Ms:
|
|
if m.min_row<=r<=m.max_row and m.min_col<=c<=m.max_col: return m
|
|
return None
|
|
def eff(r,c,Ms):
|
|
m=mof(r,c,Ms); return ws.cell(m.min_row,m.min_col).value if m else ws.cell(r,c).value
|
|
|
|
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))]
|
|
|
|
# (1) B순번 재부여 1..N
|
|
for i,r in enumerate(content,1):
|
|
ws.cell(r,2).value=i
|
|
|
|
# (2) 행높이 15
|
|
fixedh=0
|
|
for r in range(1,ws.max_row+1):
|
|
if ws.row_dimensions[r].height not in (15.0,None):
|
|
ws.row_dimensions[r].height=15.0; fixedh+=1
|
|
|
|
# (3) F 미병합 부모그룹 탐지 후 병합
|
|
Ms=merges()
|
|
data=[r for r in range(3,ws.max_row+1) if ws.cell(r,2).value not in (None,'')]
|
|
def parent_groups(c):
|
|
groups=[]; i=0
|
|
while i<len(data):
|
|
r=data[i]; v=ws.cell(r,c).value
|
|
if v in (None,'') or (mof(r,c,Ms) and mof(r,c,Ms).min_row!=r): i+=1; continue
|
|
parent=tuple(eff(r,cc,Ms) for cc in range(4,c)); rows=[r]; j=i+1
|
|
while j<len(data):
|
|
r2=data[j]
|
|
if tuple(eff(r2,cc,Ms) for cc in range(4,c))!=parent: break
|
|
cv=ws.cell(r2,c).value; m2=mof(r2,c,Ms)
|
|
if (cv in (None,'')) or (m2 and m2.min_row<r2): rows.append(r2); j+=1
|
|
else: break
|
|
if len(rows)>1 and any(ws.cell(rr,c+1).value not in (None,'') for rr in rows):
|
|
m=mof(r,c,Ms); groups.append((rows[0],rows[-1],v,bool(m and m.max_row>m.min_row))); i=j
|
|
else: i+=1
|
|
return groups
|
|
to_merge=[(a,z,v) for a,z,v,mg in parent_groups(6) if not mg]
|
|
for a,z,v in to_merge:
|
|
ws.merge_cells(start_row=a,start_column=6,end_row=z,end_column=6)
|
|
print(f'F 병합 추가: {len(to_merge)}개')
|
|
|
|
# (4) 정렬 통일 — 병합 후 재계산
|
|
Ms=merges()
|
|
nonanchor=set()
|
|
for m in Ms:
|
|
for rr in range(m.min_row,m.max_row+1):
|
|
for cc in range(m.min_col,m.max_col+1):
|
|
if not (rr==m.min_row and cc==m.min_col): nonanchor.add((rr,cc))
|
|
CEN=Alignment(horizontal='center',vertical='center',wrap_text=True)
|
|
LEFT=Alignment(horizontal='left',vertical='center',wrap_text=False)
|
|
ac=0
|
|
for r in content:
|
|
for c in (4,5,6,7): # D E F G
|
|
if (r,c) in nonanchor: continue
|
|
ws.cell(r,c).alignment=CEN; ac+=1
|
|
if (r,11) not in nonanchor: # K
|
|
ws.cell(r,11).alignment=LEFT
|
|
|
|
wb.save(P)
|
|
print(f'B순번 재부여 {len(content)}행 | 행높이정규화 {fixedh}행 | 정렬 D/E/F/G {ac}셀 + K | saved')
|