파이썬 자동화 #2 엑셀 자동화: openpyxl로 반복 보고서 끝내기

프로그래밍 언어 파이썬 Automation Excel
6 분 소요

매주 금요일, 지점 다섯 곳에서 같은 양식의 엑셀 파일이 메일로 도착합니다. 하나씩 열어서 복사하고, 본사 양식에 붙여 넣고, 서식을 다시 맞추는 데 한 시간이 걸립니다. 이 작업은 매주 똑같이 반복되고, 손으로 하다 보면 한 줄을 빠뜨리거나 잘못된 시트를 복사하는 실수도 생깁니다. 이번 글에서는 이 취합 작업을 openpyxl 라이브러리로 통째로 자동화해 보겠습니다. #1 스크립트 첫걸음에서 익힌 pathlib도 그대로 다시 씁니다.

  • #1 스크립트 첫걸음
  • #2 엑셀 자동화 ← 이번 글
  • #3 웹 스크래핑(정적 페이지)
  • #4 웹 스크래핑(동적 페이지)
  • #5 메일과 알림
  • #6 스케줄링
  • #7 CLI 도구로 포장

openpyxl 설치 #

openpyxl은 .xlsx 파일을 읽고 쓰는 순수 파이썬 라이브러리입니다. 엑셀 프로그램이 설치돼 있지 않아도 동작하므로 서버에서도 그대로 쓸 수 있습니다. 설치는 한 줄입니다. pip를 쓴다면 pip install openpyxl로 동일합니다.

설치 
uv add openpyxl

파일 읽기: load_workbook #

먼저 파일 하나를 열어 구조를 살펴보겠습니다. 엑셀 파일은 워크북(Workbook) 안에 시트(Worksheet)가 있고, 시트 안에 셀(Cell)이 있는 3단 구조입니다.

read_one.py 
from openpyxl import load_workbook

wb = load_workbook("branch_a.xlsx")

print(wb.sheetnames)        # ['7월 1주', 'Sheet2']
ws = wb["7월 1주"]           # 이름으로 시트 선택
ws = wb.active              # 또는 활성 시트

print(ws["B2"].value)                  # 좌표로 셀 접근
print(ws.cell(row=2, column=2).value)  # 행/열 번호로 접근 (같은 셀)

# 행 단위 순회. min_row=2: 헤더(1행)는 건너뜀
for row in ws.iter_rows(min_row=2, values_only=True):
    print(row)  # ('서울', '키보드', 12, 348000)

좌표 문자열은 사람이 읽기 편하고, 행/열 번호는 반복문에서 편합니다. 표 전체를 행 단위로 순회할 때는 iter_rows를 씁니다. values_only=True를 주면 셀 객체 대신 값 튜플이 바로 나오므로 읽기 전용 코드가 깔끔해집니다.

완성 예제: 폴더의 지점별 엑셀을 한 시트로 취합 #

이제 실전입니다. reports/ 폴더에 branch_a.xlsx, branch_b.xlsx처럼 지점별 파일이 쌓여 있고, 모든 파일의 첫 시트가 같은 양식(지점, 품목, 수량, 매출)이라고 가정하겠습니다. 1편에서 다룬 pathlib의 glob으로 파일 목록을 잡고, 각 파일의 데이터 행을 하나의 새 워크북에 모읍니다.

merge_reports.py 
from pathlib import Path

from openpyxl import Workbook, load_workbook

REPORT_DIR = Path("reports")
OUT_FILE = Path("merged.xlsx")

merged = Workbook()
out = merged.active
out.title = "취합"
out.append(["지점", "품목", "수량", "매출"])  # 헤더는 한 번만

for file in sorted(REPORT_DIR.glob("*.xlsx")):
    wb = load_workbook(file, data_only=True)
    ws = wb.active
    for row in ws.iter_rows(min_row=2, values_only=True):
        if row[0] is None:  # 빈 행은 건너뜀
            continue
        out.append(row)
    print(f"{file.name}: {ws.max_row - 1}행 취합")

merged.save(OUT_FILE)
print(f"완료: {OUT_FILE}")

핵심은 세 가지입니다. glob("*.xlsx")로 폴더의 대상 파일을 전부 잡고, iter_rows(min_row=2)로 각 파일의 헤더를 건너뛰고, out.append(row)로 새 시트 맨 아래에 행을 이어 붙입니다. 파일이 5개든 50개든 코드는 그대로입니다. sorted()를 감싼 이유는 실행할 때마다 파일 순서가 달라지는 일을 막기 위해서입니다.

쓰기와 서식: 보고서답게 다듬기 #

취합만 하면 민짜 표가 나옵니다. 헤더를 볼드로, 열 너비를 내용에 맞게, 매출 열에 천 단위 콤마를 넣어 보고서 모양을 잡아 보겠습니다.

서식 입히기 
from openpyxl.styles import Font

# 1. 헤더 볼드
for cell in out[1]:  # out[1]은 1행 전체
    cell.font = Font(bold=True)

# 2. 열 너비 (단위는 대략 글자 수)
widths = {"A": 12, "B": 18, "C": 8, "D": 14}
for col, width in widths.items():
    out.column_dimensions[col].width = width

# 3. 매출(D열) 숫자 포맷: 1234567 → 1,234,567
for row in out.iter_rows(min_row=2, min_col=4, max_col=4):
    for cell in row:
        cell.number_format = "#,##0"

# 4. 스크롤해도 헤더(1행) 고정
out.freeze_panes = "A2"  # A2 위쪽을 고정한다는 뜻
merged.save(OUT_FILE)

number_format은 엑셀의 셀 서식 코드를 그대로 받습니다. 날짜라면 "yyyy-mm-dd", 백분율이라면 "0.0%"처럼 엑셀에서 쓰던 코드를 그대로 적으면 됩니다.

수식 다루기: openpyxl은 계산하지 않습니다 #

여기서 가장 많이 부딪히는 함정입니다. openpyxl은 수식을 계산하는 엔진이 아닙니다. D10 셀에 =SUM(D2:D9)가 들어 있을 때, 읽는 방식에 따라 보이는 값이 달라집니다.

data_only의 차이 
wb = load_workbook("branch_a.xlsx")
print(wb.active["D10"].value)   # '=SUM(D2:D9)' (수식 문자열)
wb = load_workbook("branch_a.xlsx", data_only=True)
print(wb.active["D10"].value)   # 858000 (마지막 저장 시점의 계산 값)

data_only=True가 돌려주는 값은 openpyxl이 계산한 결과가 아니라, 엑셀이 마지막으로 저장할 때 파일 안에 캐시해 둔 값입니다. 그래서 두 가지를 주의해야 합니다.

  • 파이썬으로만 만들고 엑셀에서 한 번도 열지 않은 파일은 캐시가 없어서 None이 나옵니다.
  • 수식이 있는 파일을 openpyxl로 읽고 다시 저장하면, 읽은 모드의 값(수식 또는 캐시 값)만 남습니다. 원본을 덮어쓰지 말고 결과는 새 파일로 저장하는 습관이 안전합니다.

위 취합 예제에서 data_only=True를 준 이유가 바로 이것입니다. 지점 파일의 매출 열이 수식이어도 수식 문자열이 아니라 숫자를 가져오기 위해서입니다. 합계가 필요하다면 파이썬에서 직접 더하거나, 결과 시트에 =SUM(...) 수식을 적어 두고 받는 사람이 엑셀에서 열 때 계산되게 하면 됩니다.

CSV와 pandas는 어디에 쓰는지 #

데이터에 서식이 필요 없다면 .xlsx 대신 표준 라이브러리 csv 모듈로 충분한 경우도 많습니다. 반대로 취합 후에 그룹별 집계나 피벗 같은 분석이 목적이라면 pandas가 맞는 도구입니다. pandas는 데이터 분석 시리즈에서 따로 다루겠습니다. 이번 시리즈의 초점은 “엑셀 파일 자체를 만들고 꾸미는 자동화"이고, 그 용도에는 openpyxl이 가장 직접적입니다.

흔한 함정 두 가지 #

병합 셀: 병합된 영역에서 값은 좌상단 셀에만 있고 나머지 셀은 None으로 읽힙니다. 지점 파일에 병합 셀이 섞여 있으면 취합 결과에 구멍이 생기므로, ws.merged_cells.ranges로 병합 범위를 확인하고 좌상단 값을 나머지 행에 채워 넣는 처리가 필요합니다. 쓰기 쪽에서도 병합 영역의 좌상단이 아닌 셀에는 값을 넣을 수 없습니다.

큰 파일 성능: 기본 모드는 파일 전체를 메모리에 올립니다. 수십만 행이 넘는 파일은 읽기는 load_workbook(file, read_only=True), 쓰기는 Workbook(write_only=True)로 스트리밍 모드를 쓰는 편이 좋습니다. 대신 read_only 모드에서는 셀 임의 접근이 제한되고, write_only 모드에서는 append로만 행을 추가할 수 있습니다.

정리 #

이번 글에서 만든 흐름을 정리하겠습니다.

  • load_workbook + iter_rows(values_only=True)로 엑셀을 행 단위로 읽기
  • pathlib의 glob + append로 폴더의 지점별 파일을 한 시트로 취합
  • Font(bold=True), column_dimensions, number_format, freeze_panes로 보고서 서식
  • 수식의 함정(openpyxl은 계산하지 않음, data_only=True는 캐시 값), 병합 셀의 None, 대용량 파일의 read_only/write_only 모드

매주 한 시간 걸리던 취합이 스크립트 한 번 실행으로 끝납니다. 다음 글(#3 웹 스크래핑(정적 페이지))에서는 데이터가 엑셀 파일이 아니라 웹 페이지에 있을 때를 다루겠습니다. requests와 BeautifulSoup으로 HTML에서 원하는 표와 목록을 뽑아내는 방법을 정리하겠습니다.

X