60 lines
2.7 KiB
Python
60 lines
2.7 KiB
Python
# -*- coding: utf-8 -*-
|
|
"""검수 마감 서식 정규화 (서식만, 내용/링크/병합/테두리 불변).
|
|
- 전 행 높이 17
|
|
- 3행~끝 채움색 제거(무채색)
|
|
- K열(11) 제외 전 셀 가로·세로 가운데, K열만 가로 좌측(세로 가운데)
|
|
사용: python -X utf8 _fmt_normalize.py <엑셀> [--apply] (기본 dry-run)
|
|
"""
|
|
import openpyxl, os, shutil, sys, copy
|
|
from openpyxl.styles import Alignment, PatternFill
|
|
|
|
NOFILL = PatternFill(fill_type=None)
|
|
CENTER = Alignment(horizontal="center", vertical="center", wrap_text=False)
|
|
LEFT = Alignment(horizontal="left", vertical="center", wrap_text=False)
|
|
KCOL = 11 # K
|
|
|
|
def process(path, apply=False):
|
|
wb = openpyxl.load_workbook(path)
|
|
ws = wb.active
|
|
mx, mc = ws.max_row, ws.max_column
|
|
# 검증용 스냅샷: 값·하이퍼링크
|
|
vals = {(r, c): ws.cell(r, c).value for r in range(1, mx+1) for c in range(1, mc+1)}
|
|
links = {(r, c): (ws.cell(r, c).hyperlink.target if ws.cell(r, c).hyperlink else None)
|
|
for r in range(1, mx+1) for c in range(1, mc+1)}
|
|
merges = sorted(str(m) for m in ws.merged_cells.ranges)
|
|
if not apply:
|
|
return dict(file=path, max_row=mx, max_col=mc, merges=len(merges), dry=True)
|
|
shutil.copy2(path, os.path.splitext(path)[0] + "_backup_서식정규화전.xlsx")
|
|
# 1) 행높이 17
|
|
ws.sheet_format.defaultRowHeight = 17
|
|
ws.sheet_format.customHeight = True
|
|
for r in range(1, mx+1):
|
|
ws.row_dimensions[r].height = 17
|
|
# 2) 3행~끝 채움색 제거 + 3) 정렬(전 범위)
|
|
for r in range(1, mx+1):
|
|
for c in range(1, mc+1):
|
|
cell = ws.cell(r, c)
|
|
if r >= 3:
|
|
cell.fill = copy.copy(NOFILL)
|
|
cell.alignment = copy.copy(LEFT if c == KCOL else CENTER)
|
|
wb.save(path)
|
|
# 검증(재로드): 값·링크·병합·행수 보존
|
|
wb2 = openpyxl.load_workbook(path); ws2 = wb2.active
|
|
fails = []
|
|
if ws2.max_row != mx: fails.append("rowcount")
|
|
for (r, c), v in vals.items():
|
|
if ws2.cell(r, c).value != v: fails.append("val r%d c%d" % (r, c)); break
|
|
for (r, c), t in links.items():
|
|
cur = ws2.cell(r, c).hyperlink.target if ws2.cell(r, c).hyperlink else None
|
|
if cur != t: fails.append("link r%d c%d" % (r, c)); break
|
|
if sorted(str(m) for m in ws2.merged_cells.ranges) != merges: fails.append("merges")
|
|
if fails:
|
|
shutil.copy2(os.path.splitext(path)[0] + "_backup_서식정규화전.xlsx", path)
|
|
return dict(file=path, ok=False, rolledback=True, fails=fails)
|
|
return dict(file=path, ok=True, max_row=mx, merges=len(merges))
|
|
|
|
if __name__ == "__main__":
|
|
a = sys.argv[1:]
|
|
p = a[0]
|
|
print(process(p, apply=("--apply" in a)))
|