반응형
IT·디지털 자동화 블로그 · 엑셀 업무 자동화
이 글은 회사에서 반복 생산되는 엑셀 보고서를 자동으로 업데이트하고 배포하는 시스템을 설계하고 구현하는 방법을 다룬다. 핵심 구성 요소는 데이터 수집, 변환과 검증, 서식 적용, 버전 관리, 스케줄러, 로깅과 알림이다. pandas와 openpyxl을 중심으로 동작하며, Windows 작업 스케줄러 또는 크론과 연동해 무인 운영이 가능하다.
자동 업데이트가 필요한 이유
팀 단위 보고서는 파일을 복사하고 수식 범위를 늘리고 피벗을 새 로고침하는 단순 반복이 많다. 사람의 손을 거칠수록 누락과 지연, 버전 충돌이 발생한다. 자동화는 일정 시간마다 원천 데이터를 읽어 들여 정해진 규칙으로 업데이트하고, 서식이 적용된 결과물을 공유 폴더나 메일로 배포한다. 결과는 일관되고 재현 가능하며 감사를 대비한 로그가 남는다.
전체 아키텍처
흐름
- 입력 폴더에서 최신 원본 파일 감지
- pandas로 읽고 결측 보정, 형 변환
- 검증 규칙에 따른 품질 점검
- 요약 테이블과 지표 산출
- openpyxl로 템플릿에 채워 넣고 서식 반영
- 버전 스탬프 파일명으로 저장 및 백업
- 로그 기록과 알림 발송
기술 스택
- 데이터 처리 pandas
- 엑셀 서식 openpyxl
- 설정 관리 YAML 또는 JSON
- 스케줄 Windows 작업 스케줄러 또는 cron
- 알림 이메일 또는 협업툴 웹훅
환경 준비와 보안 변수
pip install pandas openpyxl pyyaml민감 경로와 토큰은 환경 변수로 관리한다.
Windows PowerShell
setx DATA_ROOT "D:\company\data"
setx REPORT_OUT "D:\company\reports"
setx MAIL_USER "reportbot@example.com"
setx MAIL_PASS "app_password"공유 드라이브 권한과 파일 잠금 상태를 확인하자. 사용 중인 파일을 덮어쓰면 충돌이 난다.
설정 파일 설계
업무마다 컬럼 이름과 규칙이 다른 만큼 설정 파일로 분리해 유지보수를 쉽게 만든다.
# file: config.yaml
paths:
input_dir: "${DATA_ROOT}/incoming"
archive_dir: "${DATA_ROOT}/archive"
template: "${DATA_ROOT}/template/월간보고서_template.xlsx"
output_dir: "${REPORT_OUT}"
rules:
date_column: "날짜"
metrics:
- { name: "매출합계", expr: "매출.sum()" }
- { name: "비용합계", expr: "비용.sum()" }
- { name: "이익합계", expr: "(매출 - 비용).sum()" }
validate:
required_columns: ["날짜","지역","매출","비용"]
non_negative: ["매출","비용"]
format:
number_format: "#,##0"
date_format: "yyyy-mm-dd"
output:
file_prefix: "월간보고서_"
keep_versions: 14
notify:
enable_email: false
to: ["team@example.com"]파이프라인 구현 코드
핵심 단계인 읽기 변환 검증 요약 저장을 한 스크립트로 구성한다.
# file: run_pipeline.py
import os, glob, shutil, logging, json
from datetime import datetime
import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Alignment, Font, PatternFill, Border, Side
import yaml
def load_config(path="config.yaml"):
with open(path, "r", encoding="utf-8") as f:
raw = yaml.safe_load(f)
# 환경변수 치환
def subst(v):
return os.path.expandvars(v) if isinstance(v, str) else v
for k in raw.get("paths", {}):
raw["paths"][k] = subst(raw["paths"][k])
return raw
def find_latest_file(folder, pattern="*.xlsx"):
files = sorted(glob.glob(os.path.join(folder, pattern)))
return files[-1] if files else None
def validate_df(df, cfg):
missing = [c for c in cfg["validate"]["required_columns"] if c not in df.columns]
if missing:
raise ValueError(f"필수 컬럼 누락: {missing}")
for c in cfg["validate"]["non_negative"]:
if (df[c] < 0).any():
raise ValueError(f"음수 값 발견: {c}")
def compute_metrics(df, cfg):
env = {col: df[col] for col in df.columns}
env.update({"pd": pd})
result = {}
for m in cfg["rules"]["metrics"]:
result[m["name"]] = eval(m["expr"], {}, env)
return result
def write_to_template(df, metrics, cfg):
tpl = cfg["paths"]["template"]
wb = load_workbook(tpl)
ws = wb["요약"]
# 표 채우기 예시
ws["B2"] = metrics["매출합계"]
ws["B3"] = metrics["비용합계"]
ws["B4"] = metrics["이익합계"]
# 최근 월별 집계 테이블
df["날짜"] = pd.to_datetime(df[cfg["rules"]["date_column"]])
monthly = df.groupby(pd.Grouper(key="날짜", freq="M")).agg({"매출":"sum","비용":"sum"})
monthly = monthly.reset_index().tail(12)
start_row = 8
ws[f"A{start_row-1}"] = "월"
ws[f"B{start_row-1}"] = "매출"
ws[f"C{start_row-1}"] = "비용"
for i, r in monthly.iterrows():
ws[f"A{start_row+i}"] = r["날짜"].date().strftime("%Y-%m")
ws[f"B{start_row+i}"] = float(r["매출"])
ws[f"C{start_row+i}"] = float(r["비용"])
# 서식 기본값
numfmt = cfg["format"]["number_format"]
for r in range(start_row, start_row+len(monthly)):
ws[f"B{r}"].number_format = numfmt
ws[f"C{r}"].number_format = numfmt
# 파일 저장
stamp = datetime.now().strftime("%Y%m%d")
outdir = cfg["paths"]["output_dir"]
os.makedirs(outdir, exist_ok=True)
outpath = os.path.join(outdir, f"{cfg['output']['file_prefix']}{stamp}.xlsx")
wb.save(outpath)
return outpath
def archive_input(latest_file, cfg):
arc = cfg["paths"]["archive_dir"]
os.makedirs(arc, exist_ok=True)
base = os.path.basename(latest_file)
shutil.copy2(latest_file, os.path.join(arc, f"{datetime.now():%Y%m%d_%H%M%S}_{base}"))
def main():
logging.basicConfig(filename="pipeline.log", level=logging.INFO,
format="%(asctime)s %(levelname)s %(message)s")
cfg = load_config()
latest = find_latest_file(cfg["paths"]["input_dir"], "*.xlsx")
if not latest:
logging.warning("입력 파일 없음")
return
df = pd.read_excel(latest)
validate_df(df, cfg)
metrics = compute_metrics(df, cfg)
out = write_to_template(df, metrics, cfg)
archive_input(latest, cfg)
logging.info("완료 out=%s metrics=%s", out, json.dumps(metrics, ensure_ascii=False))
print("보고서 업데이트 완료 →", out)
if __name__ == "__main__":
main()eval 사용이 부담된다면 파서로 대체하거나, 지표 함수를 코드에 명시적으로 등록하는 전략을 권장한다.
엑셀 서식 적용과 템플릿
서식은 템플릿에 미리 담아두는 것이 가장 빠르다. 필요시 openpyxl로 테두리, 헤더 색상, 정렬을 추가 적용한다.
# 서식 보강 예시
from openpyxl.styles import Alignment, Border, Side, PatternFill, Font
def beautify(ws, start_row, end_row):
thin = Side(style="thin", color="9CA3AF")
border = Border(top=thin, bottom=thin, left=thin, right=thin)
header_fill = PatternFill("solid", fgColor="E2E8F0")
bold = Font(bold=True)
for c in ["A","B","C"]:
ws[f"{c}{start_row-1}"].border = border
ws[f"{c}{start_row-1}"].fill = header_fill
ws[f"{c}{start_row-1}"].font = bold
for r in range(start_row, end_row+1):
for c in ["A","B","C"]:
cell = ws[f"{c}{r}"]
cell.border = border
cell.alignment = Alignment(horizontal="center")템플릿 시트에는 인쇄 영역과 머리말 바닥글을 설정해 PDF 내보내기 시에도 균일한 결과를 얻을 수 있다.
버전 관리와 롤백
결과 파일명에 날짜 스탬프를 포함하고, 보관 주기를 설정한다. keep_versions 일 수를 초과하면 가장 오래된 파일부터 삭제한다.
# 보관 주기 관리 스니펫
import pathlib, time
def rotate_versions(folder, keep=14):
files = sorted(pathlib.Path(folder).glob("월간보고서_*.xlsx"))
while len(files) > keep:
p = files.pop(0)
p.unlink()운영상 문제가 발생했을 때 이전 버전을 그대로 배포할 수 있어야 한다. 아카이브 폴더에서 필요 버전을 복사하는 롤백 스크립트를 준비한다.
스케줄러 등록
Windows 작업 스케줄러
- 작업 만들기에서 트리거를 평일 08시 30분으로 설정
- 동작에 프로그램 시작 입력 후 python 경로와 run_pipeline.py 지정
- 사용자가 로그온하지 않아도 실행 옵션 활성화
macOS 또는 Linux
# 매일 08시 30분에 실행
30 8 * * * /usr/bin/python3 /path/run_pipeline.py >> /path/pipeline_cron.log 2>&1공유 드라이브 마운트 타이밍이 필요한 환경이라면 사전 스크립트로 마운트 확인 후 파이프라인을 실행하자.
로깅과 알림
실패 원인 파악을 위해 파일 경로, 입력 파일명, 산출 지표, 저장 결과를 로그에 남긴다. 필요시 이메일이나 협업툴 웹훅으로 성공 실패를 간단히 통지한다.
# 간단한 이메일 알림 예시
import os, smtplib
from email.message import EmailMessage
def notify(subject, text, to):
if not os.getenv("MAIL_USER"):
return
msg = EmailMessage()
msg["From"] = os.getenv("MAIL_USER")
msg["To"] = ", ".join(to)
msg["Subject"] = subject
msg.set_content(text)
with smtplib.SMTP("smtp.gmail.com", 587, timeout=30) as s:
s.starttls()
s.login(os.getenv("MAIL_USER"), os.getenv("MAIL_PASS"))
s.send_message(msg)실패 시 재시도 로직과 백오프를 더하면 운영 안정성이 높아진다.
운영 체크리스트
- 입력 폴더의 최신 파일 감지 규칙이 명확한가
- 필수 컬럼 검증과 음수 값 탐지가 동작하는가
- 템플릿에 수식, 인쇄 영역, 서식이 안정적으로 반영되는가
- 버전 스탬프와 보관 주기가 설정되어 있는가
- 로그 파일과 알림 통로가 마련되어 있는가
- 스케줄러 등록 후 파일 잠금 충돌이 없는가
다음 글 예고: 자동 업데이트 결과를 PDF로 변환하고 메일 발송
생성된 엑셀 보고서를 PDF로 자동 변환하고, 부서별 수신자 그룹에 맞춰 제목과 본문 템플릿을 적용해 자동 발송하는 파이프라인을 설계한다.
관련 읽을거리: 매일 자동 보고서 만들기 · 엑셀 메일 자동 발송
반응형