IT·디지털 자동화 블로그 · 파이썬 엑셀 자동화
1) 파일 잠금·경로 권한
공유 드라이브에서 엑셀을 누군가 열어두면 저장/덮어쓰기 오류가 난다. 윈도에서는 COM(Excel)로 열어도 잠금 충돌이 빈번하다.
import os, time
def wait_for_unlock(path, timeout=60):
start = time.time()
while time.time() - start < timeout:
try:
with open(path, "ab"):
return True
except PermissionError:
time.sleep(2)
raise TimeoutError("파일 잠금 유지: " + path)저장 전 wait_for_unlock을 호출하고, 실패 시 임시 파일로 저장 후 알림을 보낸다. 네이밍은 _pending_YYYYMMDD_HHMMSS 규칙으로 관리한다.
2) 타입 붕괴: 날짜·숫자·문자
문제
- 날짜가 문자열로 들어와 정렬/리샘플이 오작동
- 우편번호/제품코드가 숫자로 바뀌며 선행 0 소실
- 소수점 금액이 부정확한 반올림으로 표시
해결
import pandas as pd
df = pd.read_excel("in.xlsx", dtype={"우편번호":"string","제품코드":"string"})
df["날짜"] = pd.to_datetime(df["날짜"], errors="coerce", format="mixed")
# 표시 포맷은 쓰기 단계에서 number_format으로 통제Excel의 1900 윤년 버그로 1900-02-29가 유효 날짜처럼 보일 수 있다. 역사 데이터면 datetime64[ns] 검증 로직을 업무 규칙으로 포함하자.
3) 병합셀과 헤더 파싱 실패
보고서형 엑셀은 헤더가 2~3행 병합으로 구성되어 read_excel이 바로 읽기 어렵다.
import pandas as pd
raw = pd.read_excel("report.xlsx", header=None)
# 0~1행을 결합해 다중 컬럼 생성
top = raw.iloc[0].ffill()
mid = raw.iloc[1].ffill()
cols = [f"{a}.{b}" for a,b in zip(top, mid)]
df = raw.iloc[2:].set_axis(cols, axis=1)템플릿을 통제할 수 있다면 병합 대신 단일 헤더+설명행(주석)을 권장한다.
4) 수식 미계산·data_only 오해
openpyxl로 수식을 쓴 직후 값을 읽으면 계산되지 않은 상태다. data_only=True는 “마지막으로 Excel이 계산해 저장한 값”을 읽는 옵션일 뿐, 계산을 수행하지 않는다.
from openpyxl import load_workbook
wb = load_workbook("file.xlsx", data_only=True)
# 값이 0 또는 None이면 Excel에서 재계산된 적이 없다는 뜻해결책은 두 가지다. 1) Excel(COM)으로 CalculateFullRebuild() 후 저장. 2) 수식이 필요한 지표는 파이썬에서 계산하고 값으로 기록.
5) 서식 유실·숫자 포맷 깨짐
데이터프레임을 매번 새로 쓰면 기존 서식이 사라진다. 템플릿을 로드해 필드만 교체하거나, NamedStyle을 정의해 재사용하자.
from openpyxl import load_workbook
from openpyxl.styles import NamedStyle
wb = load_workbook("template.xlsx")
ws = wb["SUMMARY"]
if "KR_CURRENCY" not in wb.named_styles:
cur = NamedStyle(name="KR_CURRENCY", number_format="#,##0")
wb.add_named_style(cur)
ws["B2"].value = 1234567
ws["B2"].style = "KR_CURRENCY"
wb.save("out.xlsx")6) 피벗/차트/인쇄영역 호환성
openpyxl은 피벗 테이블 작성 기능이 제한적이다. 피벗·차트·페이지 설정을 유지하려면 템플릿+Excel(COM) 내보내기를 고려한다.
# Windows 전용 예시 (pywin32)
import win32com.client as win32, os
excel = win32.gencache.EnsureDispatch("Excel.Application")
excel.Visible = False
wb = excel.Workbooks.Open(os.path.abspath("template_with_pivot.xlsx"))
wb.RefreshAll()
excel.CalculateUntilAsyncQueriesDone()
for sht in wb.Worksheets:
sht.ExportAsFixedFormat(Type=0, Filename=os.path.abspath(f"out_{sht.Name}.pdf"))
wb.Close(SaveChanges=True); excel.Quit()인쇄영역은 템플릿에서 고정하고, 데이터 범위만 표(Excel Table)로 관리하면 자동 확장에 유리하다.
7) 성능/메모리 폭증
- 거대 시트 반복 쓰기 →
xlsxwriter엔진을 사용하거나, CSV/파케이로 중간 저장 - 수십만 행 스타일링 → 최소 컬럼만 서식 적용, 조건부서식을 활용
- 대규모 병합 → 청크 처리
import pandas as pd
# 청크 로드/저장 예시
chunks = pd.read_csv("big.csv", chunksize=200_000)
for i, ch in enumerate(chunks, 1):
ch.to_parquet(f"tmp/ch_{i}.parquet", index=False)8) 엔진 선택 가이드
| 시나리오 | 권장 | 비고 |
|---|---|---|
| 데이터 쓰기 속도 | xlsxwriter | 빠름, 서식 풍부, 수식 계산은 Excel 필요 |
| 기존 파일 읽기/수정 | openpyxl | 표·스타일 유지, 피벗 생성 한계 |
| 템플릿 피벗/차트 재계산 | Excel(COM) | Windows 전용, 신뢰성 높음 |
| 서버/리눅스 PDF | LibreOffice CLI | 서식 호환성 테스트 필수 |
9) 로케일/구분자/인코딩
CSV 입력의 구분자와 인코딩이 섞이면 데이터가 어긋난다. 메타데이터가 없다면 탐지기를 쓰거나, 업무 표준을 강제하자.
import pandas as pd
df = pd.read_csv("in.csv", encoding="utf-8-sig") # BOM 대응
# 세미콜론 구분자
df2 = pd.read_csv("in_semicolon.csv", sep=";")엑셀에서 개행 포함 셀은 CSV화 시 줄 바꿈이 깨진다. 보고서 배포는 가능하면 xlsx 본문+PDF 요약의 이중 채널을 유지한다.
10) 사전 예방 체크리스트
- 입력 스키마: 필수 컬럼/타입/날짜 포맷 검증
- 파일 상태: 잠금 감지, 백업/아카이브, 덮어쓰기 금지 규칙
- 서식 정책: 템플릿 우선, NamedStyle/조건부서식 재사용
- 수식 정책: 가능하면 파이썬 계산, Excel 계산 시 COM 재계산
- 성능: 청크 처리, 파케이 중간 저장, 최소 서식
- 배포: 인쇄영역/머리말/바닥글 템플릿, PDF 변환 경로 이중화
- 로그/에러: 단계별 로그, 재시도/백오프, 실패 알림
자주 묻는 질문
수식이 많은 통합 문서를 서버에서 자동 변환하려면?
피벗/수식이 핵심이면 Windows 서버에서 Excel(COM)로 재계산 후 PDF 내보내는 파이프라인이 안정적이다. 리눅스라면 수식은 파이썬에서 선계산하고 값으로 박는 전략을 사용한다.
서식과 속도를 모두 잡을 수 있을까?
대용량 데이터는 xlsxwriter로 빠르게 쓰고, 최종 서식을 입히는 부분만 openpyxl로 후처리 하거나 템플릿을 병행한다.
동시 실행 충돌을 줄이는 방법은?
프로세스 락 파일을 사용하고, 출력 파일명에 타임스탬프를 붙인다. 예약 작업은 단일 러너 스크립트에서 직렬로 실행한다.
엑셀 자동화는 “코드가 돌아가는가”보다 “매일 실수 없이 운영되는가”가 중요하다. 위의 함정들을 체크리스트에 반영하고, 템플릿·로깅·재시도·알림으로 운영 신뢰성을 끌어올리자.
보안 환경에서는 경로·권한·매크로 차단 정책이 자동화 실패의 핵심 원인이다. IT 정책과 협의하여 서비스 계정·서명된 스크립트·허용 경로를 마련하라.