반응형
IT·디지털 생활형 블로그 · 파이썬 엑셀 자동화
왜 시트 자동 생성인가
월별 실적표, 부서별 점검표, 프로젝트별 보고서를 매번 복사해 파일명을 바꾸는 일은 시간이 오래 걸리고 실수가 잦다. 템플릿 하나를 기준으로 시트를 자동 생성하면 일관성, 재현성, 속도를 동시에 얻을 수 있다. 또한 코드로 생성한 구조는 다른 시스템과 연동하기 쉬워 데이터 파이프라인으로 확장할 수 있다.
환경 준비와 템플릿 설계
가상환경을 활성화하고 openpyxl을 설치한다. 템플릿 시트에는 고정 머리글, 기본 서식, 계산 수식, 안내 문구를 포함해 둔다.
pip install --upgrade pip
pip install openpyxl템플릿 시트 생성
# file: 00_make_template.py
from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, PatternFill, Border, Side
from openpyxl.utils import get_column_letter
wb = Workbook()
ws = wb.active
ws.title = "_TEMPLATE" # 언더스코어로 숨김 취지 표시
# 머리글
headers = ["일자","항목","수량","단가","금액","메모"]
ws.append(headers)
thin = Side(style="thin", color="9CA3AF")
for cell in ws[1]:
cell.font = Font(bold=True)
cell.fill = PatternFill("solid", fgColor="E2E8F0")
cell.alignment = Alignment(horizontal="center")
cell.border = Border(top=thin,bottom=thin,left=thin,right=thin)
# 열 너비
widths = [12,22,10,12,14,24]
for i, w in enumerate(widths, start=1):
ws.column_dimensions[get_column_letter(i)].width = w
# 합계 라벨과 수식 자리
ws["A30"] = "합계"
ws["E30"] = "=SUM(E2:E29)"
wb.save("시트자동_템플릿.xlsx")
print("템플릿 저장 완료")팁: 템플릿 시트명 앞에 언더스코어를 붙여 사용자가 실수로 수정하지 않도록 구분한다.
월별 시트 자동 생성
템플릿을 복제해 1월부터 12월까지 시트를 만든다. 생성과 동시에 머리글에 월 정보를 기록한다.
# file: 01_clone_months.py
from openpyxl import load_workbook
from datetime import datetime
wb = load_workbook("시트자동_템플릿.xlsx")
tpl = wb["_TEMPLATE"]
months = ["1월","2월","3월","4월","5월","6월","7월","8월","9월","10월","11월","12월"]
for m in months:
ws = wb.copy_worksheet(tpl)
ws.title = m
ws["F1"] = f"{m} 입력 가이드"
ws["A2"] = f"{datetime.now():%Y}-{m[:-1]}-01" # 예시 날짜
# 템플릿 시트는 맨 뒤로 이동 또는 숨김
wb.move_sheet(tpl, offset=wb.index(wb.worksheets[-1]) - wb.index(tpl))
wb.save("시트자동_월별.xlsx")
print("월별 시트 생성 완료")부서별 시트 자동 생성
같은 템플릿을 부서 이름 리스트로 복제한다. 부서별 의견 칼럼을 추가하는 등 커스터마이즈도 가능하다.
# file: 02_clone_departments.py
from openpyxl import load_workbook
wb = load_workbook("시트자동_템플릿.xlsx")
tpl = wb["_TEMPLATE"]
departments = ["영업팀","기획팀","개발팀","CS팀","관리팀"]
for d in departments:
ws = wb.copy_worksheet(tpl)
ws.title = d
ws["F1"] = f"{d} 점검표"
# 필요시 추가 컬럼 제목
ws["G1"] = "검토자"
ws["H1"] = "확정여부"
wb.save("시트자동_부서별.xlsx")
print("부서별 시트 생성 완료")서식·수식·머리글 일괄 배포
이미 템플릿에 들어 있는 서식과 수식은 복제 시 자동으로 따라간다. 추가로 공통 머리글, 로고, 고정 범위 이름 등을 일괄 적용할 수 있다.
# file: 03_distribute_common.py
from openpyxl import load_workbook
from openpyxl.styles import Alignment
wb = load_workbook("시트자동_월별.xlsx")
sheets = [ws for ws in wb.worksheets if ws.title != "_TEMPLATE"]
for ws in sheets:
ws["A1"] = f"{ws.title} 지출 보고서"
ws.merge_cells("A1:E1")
ws["A1"].alignment = Alignment(horizontal="center")
# 이름 정의 예시
for ws in sheets:
wb.create_named_range(f"{ws.title}_합계", ws, "E30")
wb.save("시트자동_월별_배포.xlsx")
print("공통 설정 배포 완료")요약 시트 인덱스와 하이퍼링크
첫 시트에 모든 시트로 이동하는 링크와 합계 집계를 제공한다. 여러 시트를 관리할 때 탐색 효율이 크게 올라간다.
# file: 04_make_index.py
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter
wb = load_workbook("시트자동_월별_배포.xlsx")
# 인덱스 시트가 있으면 삭제 후 새로 생성
if "INDEX" in wb.sheetnames:
idx = wb["INDEX"]
wb.remove(idx)
idx = wb.create_sheet("INDEX", 0)
idx.append(["시트명","이동","합계"])
row = 2
for ws in wb.worksheets:
if ws.title in ["INDEX","_TEMPLATE"]:
continue
idx[f"A{row}"] = ws.title
idx[f"B{row}"] = f"#{ws.title}!A1" # 표시는 링크 텍스트로 바꿈
idx[f"B{row}"].hyperlink = f"#{ws.title}!A1"
idx[f"B{row}"].style = "Hyperlink"
idx[f"C{row}"] = f"='{ws.title}'!E30" # 각 시트 합계 참조
row += 1
# 열 너비
for i, w in enumerate([18,18,14], start=1):
idx.column_dimensions[get_column_letter(i)].width = w
wb.save("시트자동_월별_인덱스.xlsx")
print("인덱스 생성 완료")시트 보호와 입력 영역
계산식과 머리글은 잠그고, 데이터 입력 칸만 열어둔다. 템플릿 단계에서 설정하면 복제본에도 동일하게 적용된다.
# file: 05_protect_sheets.py
from openpyxl import load_workbook
from openpyxl.styles import Protection
wb = load_workbook("시트자동_월별_인덱스.xlsx")
targets = [ws for ws in wb.worksheets if ws.title not in ["INDEX","_TEMPLATE"]]
for ws in targets:
# 우선 전체 잠금
for row in ws.iter_rows(min_row=1, max_row=30, min_col=1, max_col=6):
for c in row:
c.protection = Protection(locked=True)
# 입력 열만 해제
for r in range(2, 30):
for col in ["A","B","C","D","F"]:
ws[f"{col}{r}"].protection = Protection(locked=False)
ws.protection.sheet = True
ws.protection.password = "1234" # 필요 시 정책에 맞춰 변경
wb.save("시트자동_월별_보호.xlsx")
print("보호 설정 완료")성능 최적화 팁
- 대용량에서는 셀 단위 서식 적용을 최소화하고, 템플릿에 서식을 담아 복제한다.
- 반복 쓰기 대신 ws.append 사용을 기본으로 한다.
- 여러 파일을 생성할 때는 출력 폴더를 고정하고 날짜 스탬프로 버전 관리한다.
- 대량 데이터 계산은 pandas에서 처리 후 결과만 openpyxl로 전달한다.
체크리스트
- 템플릿 시트에 머리글·수식·서식을 설계했는가
- 월별 또는 부서별 리스트로 자동 생성했는가
- 공통 머리글, 이름 정의, 병합 등 일괄 배포가 되었는가
- INDEX 시트에서 링크와 합계를 제공하는가
- 입력 칸과 보호 영역을 구분했는가
- 출력 규칙과 버전 관리가 정해졌는가
다음 글 예고: 자동 보고서 PDF 변환과 메일 발송
다음 편에서는 생성된 월별 시트를 PDF로 일괄 변환하고, 파일을 이메일로 자동 발송하는 워크플로를 구성한다.
관련 읽을거리: openpyxl 완벽 정리 · 셀 값 수정과 데이터 입력 자동화
반응형