Python으로 Excel 파일 병합하기
매일 Excel과 함께 업무를 하는 직장인으로서, 가장 괴로운 점은 바로 흩어진 수많은 Excel 파일을 처리해야 하는 경우입니다. 예를 들어 매주 각 매장에서 제출하는 판매표、각 부서가 제출한 비용 명세서 등을 하나의 총괄 표로 병합해야 할 때, 수동으로 파일을 열어 복사·붙여넣기하는 것은 시간이 많이 소요될 뿐만 아니라, 행 누락이나 행 오류가 발생하기 쉽습니다. 특히 병합 셀、공식이 포함된 표를 다룰 때는 정말 악몽과 같습니다.
예전에는 pandas로 처리했지만, pandas는 Excel 형식에 대한 지원이 탁월하지 않아 병합 후 형식이 손실되거나 공식이 작동하지 않는 경우가 자주 발생했습니다. 이후 Free Spire.XLS for Python 무료 라이브러리를 시도해보니, Excel 원본 형식에 대한 지원이 매우 우수하여 병합 후의 표가 원래의 스타일、공식 및 병합 셀을 완전히 유지할 수 있었습니다. 오늘은 이 매우 실용적인 방법을 모두에게 공유하겠습니다.
방안 개요
Free Spire.XLS for Python은 Microsoft Excel을 설치하지 않고도 Excel 파일을 읽고 쓸 수 있는 순수 Python 라이브러리로, 무료 버전으로 대부분의 일상적인 병합 요구를 충족할 수 있습니다. 본 문서에서는 두 가지 일반적인 경우에 대해 구체적인 구현 방법을 제시합니다:
독립 워크시트 모드: 각 원본 파일의 모든 워크시트를 병합된 워크북에 독립적인 워크시트로 추가하는 방식으로, 원본 파일을 구분해야 하는 경우에 적합합니다.
추가 모드: 모든 원본 파일의 데이터를 병합된 워크북의 동일한 워크시트에 차례로 추가하는 방식으로, 동종 데이터를 집계해야 하는 경우에 적합합니다.
환경 준비
Python 3.7 이상 버전이 설치되어 있는지 확인하고, 다음 명령어를 실행하여 의존 라이브러리를 설치합니다:
pip install Spire.Xls.Free
참고: 무료 버전은 .xls 형식 Excel 문서의 읽기/쓰기 시, 각 문서당 워크시트 5개、각 워크시트당 행 200개 제한이 적용됩니다.
모드 1: 각 파일을 독립적인 시트로 생성
이 모드는 각 파일의 원본 구조를 유지하고 워크시트 간에 간섭이 없어야 하는 경우에 적합합니다. 예를 들어, 다른 부서나 지역의 보고서를 병합하여 원본에 따라 조회하기 쉽도록 할 때 사용합니다.
코드 구현
import os
from spire.xls import *
# 병합할 파일이 있는 폴더 경로
input_folder = './sample_files'
# 병합 후 출력 파일명
output_file = 'merged_workbook.xlsx'
# 병합 워크북 변수 초기화
merged_workbook = None
# 폴더 내 모든 파일 순회
for filename in os.listdir(input_folder):
# Excel 파일만 처리 (.xlsx 또는 .xls)
if filename.endswith('.xlsx') or filename.endswith('.xls'):
file_path = os.path.join(input_folder, filename)
# 현재 파일 로드
source_workbook = Workbook()
source_workbook.LoadFromFile(file_path)
if merged_workbook is None:
# 첫 번째 파일을 병합 워크북의 기반으로 사용
merged_workbook = source_workbook
else:
# 이후 파일의 각 워크시트를 병합 워크북에 복사
for i in range(source_workbook.Worksheets.Count):
sheet = source_workbook.Worksheets.get_Item(i)
merged_workbook.Worksheets.AddCopy(sheet, WorksheetCopyType.CopyAll)
# 병합된 워크북 저장
merged_workbook.SaveToFile(output_file, ExcelVersion.Version2016)몇 가지 설명:
첫 번째 파일을 직접 "모판"으로 사용하여, 첫 번째 파일의 원본 워크시트 순서를 유지할 수 있습니다.
AddCopy(sheet, WorksheetCopyType.CopyAll)을 사용하여 워크시트를 복사하면, 형식、병합 셀、차트까지 함께 복사되므로 매우 편리합니다.워크시트 이름은 자동으로 "원본 파일명_원본 워크시트명"으로 생성되어 이름 중복을 방지합니다. 이름이 너무 길다고 느끼시면, 자체적으로 이름 결합 규칙을 수정하실 수 있습니다.
모드 2: 모든 데이터를 동일한 워크시트에 병합
이 모드는 구조가 동일한 여러 데이터 표를 하나의 총괄 표로 집계해야 하는 경우에 적합합니다. 예를 들어, 월별 판매 상세를 병합할 때 사용합니다. 각 파일의 첫 번째 행이 헤더 행이고, 이후 파일에서는 데이터 행만 추가한다고 가정합니다.
코드 구현
import os
from spire.xls import *
# 병합할 파일이 있는 폴더 경로
input_folder = './excel_worksheets'
# 병합 후 출력 파일명
output_file = 'merged_into_one_sheet.xlsx'
# 병합 대상으로 새 워크북 생성
merged_workbook = Workbook()
# 첫 번째 워크시트를 데이터 집계 표로 사용
merged_sheet = merged_workbook.Worksheets[0]
# 현재 쓰기 행 번호 (1부터 시작)
current_row = 1
# 폴더 내 모든 파일 순회
for filename in os.listdir(input_folder):
if filename.endswith('.xlsx') or filename.endswith('.xls'):
file_path = os.path.join(input_folder, filename)
# 현재 파일 로드
workbook = Workbook()
workbook.LoadFromFile(file_path)
# 첫 번째 워크시트 가져오기
sheet = workbook.Worksheets[0]
# 사용된 영역 가져오기
source_range = sheet.Range
# 병합 표에서 목표 시작 셀 위치 지정
dest_range = merged_sheet.Range[current_row, 1]
# 데이터 복사
source_range.Copy(dest_range)
# 현재 행 번호 업데이트 (다음 데이터 블록의 시작 위치로 지정)
current_row += sheet.LastRow
# 병합된 워크북 저장
merged_workbook.SaveToFile(output_file, ExcelVersion.Version2016)
주의사항:
데이터만 복사:
Range.Copy메서드는 주로 값과 공식을 복사합니다.행 번호 관리:
sheet.LastRow를 누적하여 다음 데이터 블록의 시작 행을 확인함으로써, 데이터가 연속적으로 이어지도록 보장합니다.헤더 행 처리: 위 코드에서는 첫 번째 파일의 헤더 행이 유지되지만, 이후 파일은 1행부터 복사하기 때문에（원본 시트 첫 번째 행）헤더 행도 함께 복사되어 중복 헤더가 발생합니다. 하나의 헤더 행만 유지하고 싶으시다면, 이후 파일 복사 시 시작 행을 2행으로 수정하면 됩니다（즉
source_range = sheet.Range[2, 1]）.
본 문서에서는 두 가지 일반적인 Excel 병합 경우에 대한 완전한 Python 구현 방법을 제공하며, Excel 애플리케이션을 설치하지 않고도 실행할 수 있습니다. 반복적인 작업을 자동화함으로써 데이터 처리 효율성을 효과적으로 높이고 인위적 오류를 줄일 수 있습니다. 사용 시 실제 요구에 따라 코드를 선택하거나 조정하여 일상 업무에 적용할 수 있습니다.
