반응형
IT·디지털 자동화 블로그 · 파이썬 엑셀 자동화
이 글은 실제 업무에서 바로 쓰는 파이썬 엑셀 자동화 프로젝트의 전체 흐름을 제시한다. 반복 보고서 생성, 여러 파일 병합, 서식/조건부서식, 피벗·차트, PDF 변환, 이메일 배포를 표준 파이프라인으로 통합해 낭비 시간을 제거한다. 운영 단계에서는 예외 처리, 로깅, 재시도, 스케줄링을 통해 안정성을 확보한다.
왜 200% 효율이 가능한가
업무 시간의 상당 부분은 복붙, 정렬, 필터, 서식, 인쇄영역 설정 같은 반복 작업이다. 이를 파이썬으로 자동화하면 작업 시간이 선형 감소하고, 오류는 기하급수적으로 줄어든다. 한 번 만든 파이프라인은 매일/매주 한 번의 실행으로 동일 품질의 산출물을 보장한다.
요구사항 정의와 성공 기준
핵심 요구사항
- 여러 엑셀/CSV 파일을 자동 감지·병합
- 유효성 검증(필수 컬럼, 타입, 음수금지 등)
- 월별/부서별 요약 및 피벗, 차트 생성
- 서식 일괄 적용, 인쇄영역/머리말/바닥글
- PDF 변환 후 메일 자동 발송
성공 기준
- 수작업 대비 처리 시간 50% 이상 단축
- 반복 실행에서 0건 오류, 재현성 보장
- 산출물 구조·디자인의 일관성 유지
- 로그/알림으로 이슈 즉시 파악
폴더 구조와 데이터 표준화
📁 excel_automation_proj/
├─ config.yaml
├─ data/
│ ├─ incoming/ # 원본 수집
│ ├─ archive/ # 원본 보관
│ └─ outputs/ # 결과물
├─ scripts/
│ ├─ 01_ingest_merge.py
│ ├─ 02_transform_pivot_chart.py
│ ├─ 03_format_and_export.py
│ └─ 04_mail_send.py
└─ logs/runner.logconfig.yaml에는 컬럼 표준, 시트 이름, 숫자·날짜 포맷, 차트 종류, 메일 수신자, 스케줄 등을 담아 코드 수정 없이 운영 파라미터만 조정 가능하도록 한다.
# config.yaml 예시
sheets:
detail: "DETAIL"
summary: "SUMMARY"
charts: "CHARTS"
formats:
number: "#,##0"
date: "yyyy-mm-dd"
mail:
enable: true
to: ["team@example.com"]
subject: "[자동] 주간 보고서"파일 수집·병합 자동화
# scripts/01_ingest_merge.py
import os, glob, shutil, pandas as pd
from datetime import datetime as dt
INCOMING = "data/incoming"; ARCHIVE = "data/archive"
os.makedirs(ARCHIVE, exist_ok=True)
files = sorted(glob.glob(f"{INCOMING}/*.xlsx")) + sorted(glob.glob(f"{INCOMING}/*.csv"))
dfs = []
for f in files:
if f.endswith(".xlsx"):
df = pd.read_excel(f)
else:
df = pd.read_csv(f, encoding="utf-8")
df["source_file"] = os.path.basename(f)
dfs.append(df)
if not dfs: raise SystemExit("신규 파일 없음")
all_df = pd.concat(dfs, ignore_index=True)
# 표준 컬럼/타입 보정 예시
required = ["날짜","부서","품목","수량","금액"]
missing = [c for c in required if c not in all_df.columns]
if missing: raise ValueError(f"필수 컬럼 누락: {missing}")
all_df["날짜"] = pd.to_datetime(all_df["날짜"], errors="coerce")
all_df["수량"] = pd.to_numeric(all_df["수량"], errors="coerce").fillna(0).astype(int)
all_df["금액"] = pd.to_numeric(all_df["금액"], errors="coerce").fillna(0)
# 원본 아카이브
for f in files:
shutil.move(f, os.path.join(ARCHIVE, f"{dt.now():%Y%m%d_%H%M%S}_{os.path.basename(f)}"))
all_df.to_parquet("data/outputs/merged.parquet", index=False)
print("병합 완료 rows=", len(all_df))입력 스키마가 제각각인 환경에서는 컬럼 매핑 테이블을 별도로 두어 표준 스키마로 치환 후 병합하는 전략이 안전하다.
정제·집계·피벗·차트
# scripts/02_transform_pivot_chart.py
import pandas as pd, matplotlib.pyplot as plt
df = pd.read_parquet("data/outputs/merged.parquet")
df = df.dropna(subset=["날짜","부서","품목"]) # 기본 정제
df["월"] = df["날짜"].dt.to_period("M").astype(str)
# 요약
summary = df.groupby(["월","부서"], as_index=False).agg(수량합=("수량","sum"), 금액합=("금액","sum"))
summary.to_parquet("data/outputs/summary.parquet", index=False)
# 차트 이미지(월별 금액)
monthly = df.groupby("월")["금액"].sum().sort_index()
plt.figure()
monthly.plot(marker="o")
plt.title("월별 금액 추이")
plt.xlabel("월"); plt.ylabel("금액")
plt.tight_layout(); plt.savefig("data/outputs/chart_monthly.png", dpi=150); plt.close()
print("요약/차트 생성 완료")피벗 테이블이 필요한 경우 pandas.pivot_table을 활용하거나, openpyxl 차트 객체를 통해 엑셀 내에서 차트가 동작하도록 구성할 수 있다.
엑셀 서식·조건부서식·인쇄영역
# scripts/03_format_and_export.py
import pandas as pd
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Alignment, Font, Border, Side, PatternFill
from openpyxl.formatting.rule import CellIsRule
from openpyxl.chart import LineChart, Reference
import datetime as dt
detail = pd.read_parquet("data/outputs/merged.parquet")
summary = pd.read_parquet("data/outputs/summary.parquet")
wb = Workbook()
ws_d = wb.active; ws_d.title = "DETAIL"
ws_s = wb.create_sheet("SUMMARY")
ws_c = wb.create_sheet("CHARTS")
for c in [ws_d, ws_s]:
c.freeze_panes="A2"
# DETAIL 쓰기
ws_d.append(list(detail.columns))
for _, r in detail.iterrows(): ws_d.append(list(r.values))
# SUMMARY 쓰기
ws_s.append(list(summary.columns))
for _, r in summary.iterrows(): ws_s.append(list(r.values))
# 조건부 서식: DETAIL 금액이 0보다 작으면 강조
red_fill = PatternFill("solid", fgColor="FEE2E2")
ws_d.conditional_formatting.add(f"E2:E{ws_d.max_row}",
CellIsRule(operator="lessThan", formula=["0"], stopIfTrue=True, fill=red_fill))
# 숫자 포맷/보더/정렬
thin = Side(style="thin", color="CBD5E1")
border = Border(top=thin, bottom=thin, left=thin, right=thin)
for ws in [ws_d, ws_s]:
for cell in ws[1]:
cell.font = Font(bold=True); cell.alignment = Alignment(horizontal="center")
for row in ws.iter_rows(min_row=1, max_row=ws.max_row, max_col=ws.max_column):
for c in row: c.border = border
# 금액 컬럼 포맷
for r in range(2, ws.max_row+1):
for col in range(1, ws.max_column+1):
if ws.cell(1, col).value and "금액" in str(ws.cell(1, col).value):
ws.cell(r, col).number_format = "#,##0"
# CHARTS: 요약 시트를 기반으로 라인차트 생성(월별 합계)
lc = LineChart(); lc.title = "월별 금액 추이"
values = Reference(ws_s, min_col=3, min_row=1, max_row=ws_s.max_row) # 금액합
cats = Reference(ws_s, min_col=1, min_row=2, max_row=ws_s.max_row) # 월
lc.add_data(values, titles_from_data=True); lc.set_categories(cats)
ws_c.add_chart(lc, "A1")
# 인쇄영역/여백
ws_s.print_area = f"A1:{ws_s.cell(row=ws_s.max_row, column=ws_s.max_column).coordinate}"
for ws in [ws_d, ws_s]:
ws.page_margins.left=0.3; ws.page_margins.right=0.3
ws.page_setup.fitToWidth=1; ws.page_setup.fitToHeight=0
out_xlsx = f"data/outputs/보고서_{dt.datetime.now():%Y%m%d}.xlsx"
wb.save(out_xlsx); print("엑셀 저장:", out_xlsx)서식은 템플릿에 미리 넣어두고 데이터만 교체하는 방식이 가장 효율적이다. 다만 본 예시는 템플릿이 없을 때도 바로 실행 가능한 자가완결형 코드로 구성했다.
PDF 변환과 이메일 자동 발송
# scripts/04_mail_send.py (Windows+Excel COM, 또는 ReportLab 대체 가능)
import os, win32com.client as win32, smtplib
from email.message import EmailMessage
from datetime import datetime as dt
xlsx = f"data/outputs/보고서_{dt.now():%Y%m%d}.xlsx"
pdf = f"data/outputs/보고서_{dt.now():%Y%m%d}.pdf"
excel = win32.gencache.EnsureDispatch("Excel.Application")
excel.Visible = False
wb = excel.Workbooks.Open(os.path.abspath(xlsx))
wb.Worksheets("SUMMARY").ExportAsFixedFormat(Type=0, Filename=os.path.abspath(pdf))
wb.Close(SaveChanges=False); excel.Quit()
msg = EmailMessage()
msg["Subject"] = "[자동] 일간 보고서"
msg["From"] = os.getenv("MAIL_USER"); msg["To"] = "team@example.com"
msg.set_content("자동 생성된 보고서를 첨부합니다.")
with open(pdf, "rb") as f: msg.add_attachment(f.read(), maintype="application", subtype="pdf", filename=os.path.basename(pdf))
with smtplib.SMTP("smtp.gmail.com",587) as s:
s.starttls(); s.login(os.getenv("MAIL_USER"), os.getenv("MAIL_PASS"))
s.send_message(msg)
print("메일 발송 완료")리눅스/서버 환경에서는 LibreOffice CLI로 PDF 변환, EmailMessage를 그대로 사용하면 된다. 민감 정보는 환경 변수로 관리한다.
로깅·에러 처리·스케줄링
# 간단 러너 스크립트 run_all.py
import subprocess, logging, time
logging.basicConfig(filename="logs/runner.log", level=logging.INFO, format="%(asctime)s %(levelname)s %(message)s")
steps = [
["python","scripts/01_ingest_merge.py"],
["python","scripts/02_transform_pivot_chart.py"],
["python","scripts/03_format_and_export.py"],
["python","scripts/04_mail_send.py"]
]
for cmd in steps:
try:
subprocess.run(cmd, check=True)
logging.info("ok %s", " ".join(cmd))
except Exception as e:
logging.exception("fail %s", " ".join(cmd))
time.sleep(3) # 간단 백오프
raiseWindows 작업 스케줄러 또는 cron으로 매일/매주 지정 시간에 run_all.py를 실행하면 무인 운영이 가능하다.
재사용 가능한 코드 템플릿
| 모듈 | 역할 | 확장 포인트 |
|---|---|---|
| ingest | 파일 감지·병합 | 컬럼 매핑·정규화 |
| transform | 정제·집계·피벗 | 지표 추가·결측 처리 |
| format | 서식·차트·인쇄 | 템플릿/테마 적용 |
| export | PDF·메일 | 그룹 발송·암호화 |
| ops | 로깅·재시도 | 알림·대시보드 |
운영 체크리스트
- 입력 스키마 표준화와 필수 컬럼 검증이 적용되었는가
- 숫자·날짜 포맷, 조건부서식, 인쇄영역이 일관적인가
- PDF 변환 결과가 템플릿과 동일하게 보이는가
- 오류 시 재시도/알림 루틴이 동작하는가
- 스케줄 실행 후 로그/아카이브가 축적되는가
다음 단계
구글 시트/공공데이터 API 연동, 사내 DB 연계, 권한 기반 배포(부서별 필터링), PDF 암호화, 실패 재시도 큐 등으로 확장하면 엔터프라이즈급 자동화 플랫폼이 완성된다.
반응형