파이썬 엑셀 파일 합치기는 수작업 복사 붙여넣기를 가장 빨리 줄여주는 작은 자동화입니다. 특히 폴더 안에 파일이 여러 개 있고, 그 안에 시트도 여러 장씩 들어 있으면 사람 손으로 정리할수록 실수가 늘어나기 쉽습니다.
이번 글에서는 pandas와 openpyxl 조합으로 폴더 안의 xlsx 파일을 읽고, 각 파일의 모든 시트를 새 엑셀 파일 하나로 모으는 흐름을 단계별로 정리하겠습니다. 목표는 거창한 시스템이 아니라, 바로 실행해서 결과 파일을 확인할 수 있는 실전형 미니 도구입니다.
왜 이 방식이 실용적인가
핵심은 두 가지입니다. pandas는 엑셀 시트를 DataFrame으로 읽고 다루기 편하고, ExcelWriter를 쓰면 여러 DataFrame을 한 결과 파일의 여러 시트에 정리해서 저장할 수 있습니다.
- 바깥 반복은 파일 단위로 돈다
- 안쪽 반복은 시트 단위로 돈다
- 각 시트를 새 결과 파일의 개별 시트로 저장한다
여기에 sheet_name=None을 더하면 한 파일 안의 여러 시트를 한 번에 읽을 수 있습니다. 즉, 파일 반복과 시트 반복을 분리해서 생각하면 전체 구조가 훨씬 단순해집니다.
이 글의 핵심은 파일 반복과 시트 반복을 분리해서 생각하는 것입니다.
준비
먼저 필요한 패키지를 설치합니다.
pip install pandas openpyxl공식 문서는 pandas의 read_excel, ExcelFile.sheet_names, ExcelWriter 문서와 openpyxl 튜토리얼을 기준으로 참고했습니다.
참고 링크는 pandas.read_excel 문서, pandas.ExcelFile.sheet_names 문서, pandas.ExcelWriter 문서, openpyxl 튜토리얼입니다.
파이썬 엑셀 파일 합치기 전체 코드
아래 코드는 폴더 안의 모든 xlsx 파일을 찾아서, 각 파일의 모든 시트를 새 엑셀 파일 하나에 모읍니다.
from __future__ import annotations
from pathlib import Path
import re
import pandas as pd
def make_sheet_name(file_stem: str, original_sheet: str, used_names: set[str]) -> str:
raw_name = f"{file_stem}_{original_sheet}"
cleaned = re.sub(r"[\\/*?:\[\]]", "_", raw_name).strip()
if not cleaned:
cleaned = "sheet"
base = cleaned[:31]
candidate = base
counter = 2
while candidate in used_names:
suffix = f"_{counter}"
candidate = f"{base[:31 - len(suffix)]}{suffix}"
counter += 1
used_names.add(candidate)
return candidate
def merge_excel_folder(input_dir: str, output_file: str) -> None:
folder = Path(input_dir)
output_path = Path(output_file)
excel_files = sorted(
path for path in folder.glob("*.xlsx")
if path.name != output_path.name
)
if not excel_files:
raise FileNotFoundError("합칠 xlsx 파일을 찾지 못했습니다.")
used_sheet_names: set[str] = set()
with pd.ExcelWriter(output_path, engine="openpyxl") as writer:
for excel_path in excel_files:
sheets = pd.read_excel(excel_path, sheet_name=None)
for sheet_name, df in sheets.items():
merged_sheet_name = make_sheet_name(
excel_path.stem,
sheet_name,
used_sheet_names,
)
df.to_excel(writer, sheet_name=merged_sheet_name, index=False)
print(f"저장 완료: {output_path}")
if __name__ == "__main__":
merge_excel_folder("excels", "merged_all.xlsx")이 코드는 파일 목록을 모으고, 각 파일에서 모든 시트를 읽고, 결과 파일에 시트별로 다시 저장하는 세 단계로 이해하면 편합니다.
sheet_name=None이 중요한 이유
pandas.read_excel 문서에서 중요한 부분은 sheet_name에 None을 주면 시트별 DataFrame 딕셔너리를 돌려준다는 점입니다. 그래서 시트 이름을 미리 하나씩 적지 않아도 한 파일 안의 여러 시트를 한 번에 받을 수 있습니다.
import pandas as pd
sheets = pd.read_excel("sales.xlsx", sheet_name=None)
for sheet_name, df in sheets.items():
print(sheet_name, df.shape)처음에는 이 한 줄이 가장 큰 편의점입니다. 한 파일 안 시트 개수가 매번 달라도 같은 흐름으로 처리할 수 있기 때문입니다.
왜 결과 시트명에 파일명까지 붙이는가
여러 파일을 모을 때는 같은 이름의 시트가 반복될 가능성이 높습니다. 그래서 결과 파일에서는 원본 파일 stem과 시트명을 붙여 새 시트명을 만드는 편이 안전합니다.
- january_매출
- february_매출
- march_매출
이렇게 해두면 결과 파일을 열었을 때 시트의 출처를 바로 알 수 있습니다. 단순히 Sheet1, Sheet2만 남기면 합친 다음에 다시 헷갈리기 쉽습니다.
실행 방법
폴더 구조를 예로 들면 project 폴더 아래에 merge_excels.py 파일과 excels 폴더를 두고, 그 안에 branch_a.xlsx, branch_b.xlsx, branch_c.xlsx 같은 입력 파일을 넣으면 됩니다.
그다음 실행은 아래처럼 하면 됩니다. 성공하면 현재 작업 폴더에 merged_all.xlsx가 생깁니다.
python merge_excels.py조금 더 실전답게 바꾼 버전
입력 폴더와 출력 파일명을 바깥에서 받고 싶다면 argparse를 붙여 재사용성을 높일 수 있습니다.
from __future__ import annotations
import argparse
from pathlib import Path
import re
import pandas as pd
def make_sheet_name(file_stem: str, original_sheet: str, used_names: set[str]) -> str:
raw_name = f"{file_stem}_{original_sheet}"
cleaned = re.sub(r"[\\/*?:\[\]]", "_", raw_name).strip()
if not cleaned:
cleaned = "sheet"
base = cleaned[:31]
candidate = base
counter = 2
while candidate in used_names:
suffix = f"_{counter}"
candidate = f"{base[:31 - len(suffix)]}{suffix}"
counter += 1
used_names.add(candidate)
return candidate
def merge_excel_folder(input_dir: str, output_file: str) -> None:
folder = Path(input_dir)
output_path = Path(output_file)
excel_files = sorted(
path for path in folder.glob("*.xlsx")
if path.name != output_path.name
)
if not excel_files:
raise FileNotFoundError(f"{folder} 안에 합칠 xlsx 파일이 없습니다.")
used_sheet_names: set[str] = set()
with pd.ExcelWriter(output_path, engine="openpyxl") as writer:
for excel_path in excel_files:
sheets = pd.read_excel(excel_path, sheet_name=None)
for sheet_name, df in sheets.items():
target_name = make_sheet_name(excel_path.stem, sheet_name, used_sheet_names)
df.to_excel(writer, sheet_name=target_name, index=False)
print(f"저장 완료: {output_path}")
def main() -> None:
parser = argparse.ArgumentParser(description="폴더 안 여러 엑셀 파일의 시트를 한 파일로 모읍니다.")
parser.add_argument("input_dir", help="입력 폴더")
parser.add_argument("-o", "--output", default="merged_all.xlsx", help="결과 파일명")
args = parser.parse_args()
merge_excel_folder(args.input_dir, args.output)
if __name__ == "__main__":
main()python merge_excels.py excels -o report.xlsx이 버전부터는 폴더만 바꿔가며 재사용하기가 훨씬 편해집니다.
자주 하는 실수
출력 파일을 다시 입력으로 읽는 경우
결과 파일을 같은 폴더에 두면 다음 실행 때 그것까지 다시 읽을 수 있습니다. 그래서 예제 코드에서는 output 파일명을 입력 목록에서 제외했습니다.
시트 이름 충돌을 그대로 두는 경우
서로 다른 파일에 같은 시트명이 많으면 결과 파일에서 구분이 어려워집니다. 출처를 알아보기 쉬운 이름 규칙을 먼저 정하는 편이 낫습니다.
서식까지 그대로 합쳐질 거라고 기대하는 경우
이 방식은 데이터 모으기에 초점이 있습니다. 셀 색, 병합 상태, 차트, 복잡한 꾸밈까지 원본 그대로 재현하는 용도라고 기대하면 방향이 조금 다를 수 있습니다.
이 스크립트는 보기 좋은 보고서 복제가 아니라, 여러 시트의 데이터를 한데 모으는 자동화에 더 가깝습니다.
openpyxl은 언제 떠올리면 좋은가
openpyxl 튜토리얼을 보면 Workbook 생성, load_workbook, iter_rows 같은 기본 도구가 잘 정리돼 있습니다. 그래서 데이터프레임보다 엑셀 파일 구조를 조금 더 직접 다루고 싶을 때 떠올리기 좋습니다.
예를 들어 값을 직접 순회하면서 복사하는 흐름은 아래처럼 잡을 수 있습니다. 다만 여러 파일과 여러 시트를 한 번에 모으는 입문 자동화라면, 시작점은 pandas 쪽이 더 짧고 읽기 쉬운 편입니다.
from openpyxl import Workbook, load_workbook
source = load_workbook("source.xlsx")
result = Workbook()
result_ws = result.active
result_ws.title = "collected"
row_index = 1
for ws in source.worksheets:
for row in ws.iter_rows(values_only=True):
for col_index, value in enumerate(row, start=1):
result_ws.cell(row=row_index, column=col_index, value=value)
row_index += 1
result.save("collected.xlsx")같이 보면 좋은 글
작은 자동화 흐름을 더 넓혀 보고 싶다면 파이썬 QR 코드 생성기 만들기와 파이썬 이미지 워터마크 붙이기도 이어서 보기 좋습니다. 둘 다 입력을 받고 결과 파일을 만드는 감각을 같이 익히기 좋습니다.
기본 문법 감각을 같이 다지고 싶다면 파이썬 리스트와 튜플 차이도 함께 보면 도움이 됩니다.
마무리
파이썬 엑셀 파일 합치기는 생각보다 어렵지 않습니다. 파일 반복, 시트 반복, 결과 저장이라는 세 덩어리만 나눠서 보면 금방 이해할 수 있습니다.
처음에는 폴더 안 xlsx 파일을 모두 읽어 새 결과 파일에 시트별로 모으는 버전부터 성공해보세요. 그다음에 입력 폴더 인자 받기, 시트명 규칙 다듬기, 특정 파일만 필터링하기를 붙이면 금방 실무형 도구로 커집니다.
