엑셀 파일 병합하기 - openpyxl 활용

[openpyxl을 활용한 엑셀 파일 병합] 파이썬 openpyxl과 파일 시스템 관련 함수를 이용하여 여러 엑셀 파일의 시트 내용을 하나의 엑셀 파일의 한 시트로 병합하는 코딩 연습을 해보았다.

 

 

파이썬 openpyxl 라이브러리를 활용한 엑셀 파일 병합

수십 수백 여개의 엑셀 파일을 병합하는 코드를 연습하다가 여러 폴더에 있는 엑셀 파일의 병합은 어떻게 하는지 궁금하여 코드를 짜보았다. (이런 형태로 엑셀 파일이 저장되는 곳도 있었음)  

 

업무 자동화를 해야 하는 환경은 다음과 같다.

• 여러 폴더가 있고,
• 폴더 depth가 동일하나 폴더 이름이 틀리고,
• 폴더 내에 여러 형식의 파일들과 엑셀 파일이 하나 있을 때,
• 엑셀 파일만을 찾아서 시트 내용 전체를 가져오고,
• 해당 내용을 하나의 통합된 엑셀 파일에 그 내용을 모두 복사한다.

 

[파이썬 소스 코드]

import os
import openpyxl
import win32com.client as win32


final_filename = 'excelmerge_final_file.xlsx'
# out_files = open('./' + final_filename, 'w')
f_wb = openpyxl.Workbook()
f_ws = f_wb.active
f_wb.save(final_filename)
xl_row = 2

directory = os.getcwd() + '\excelmerge'  # 현재 디렉토리 + 작업 폴더 추가
names = os.listdir(directory)  # 파일 및 폴더 전체를 리스트 형태로 반환
print(names)

for name in names:
    if len(name.split('.')) == 1:  # 파일이 아닌 폴더만을 대상으로 하기 위해 split함
        sub_dir = f'{directory}\{name}\__\ExcelData'  # 공통 형태의 하위 디렉토리 경로 설정
        print(os.listdir(sub_dir))  # ['wqiWVkud_1649468282']

        final_dir = sub_dir + '\\' + os.listdir(sub_dir)[0]  #  폴더명이 임의적인 경우, 그 폴더를 지정함
        print(final_dir)
        # print(glob.glob(final_dir + '\*.*'))  # 경로(path)까지 추출한 list type

        # print(os.listdir(final_dir))
        for file in os.listdir(final_dir):  # 해당 폴더의 전체 파일 읽기
            if '.xls' in file:  # 파일명에 .xls가 있으면
                print(file)
                xls_file = f'{final_dir}\{file}'  # .xls 파일명 대입

                # xls to xlsx : 이 부분이 에러나서 고생했으나 해결책 찾음
                excel_app = win32.gencache.EnsureDispatch('Excel.Application')
                wb = excel_app.Workbooks.Open(xls_file)
                wb.SaveAs(xls_file + 'x', FileFormat = 51)  # FileFormat = 51은 .xlsx 확장자, 56은 .xls
                wb.Close()
                excel_app.Application.Quit()

                if os.path.isfile(xls_file):  # 기존 .xls 파일 삭제
                    xlsx_file = xls_file + 'x'  # 파일명 .xlxs로 변경
                    print(xlsx_file)
                    os.remove(xls_file)
            
                wb = openpyxl.load_workbook(f'{xlsx_file}', data_only=True)  # data_only= 값만 불러오기

                ws = wb.active
                # for rows in ws.rows:  # 행 단위로 가져오기(객체임)
                #     for cell in rows:
                #         print(cell.value)
                #     break

                for idx in range(1, ws.max_row):  # 엑셀 제목 제외하고 내용만 가져옴
                    col_cnt = 1  # column수 증가
                    for col in ws.iter_cols(min_col=1, max_col=ws.max_column):  # col= A, B, C,...
                        print(col[idx].value)
                        f_ws.cell(xl_row, col_cnt).value = col[idx].value
                        col_cnt += 1
                    xl_row += 1
                    


    f_wb.save(final_filename)

    # break

 

 

 

- openpyxl로 데이터를 옮기는 방법은 다양한데, 위에 쓴 방식은 iter_cols()로 한 행의 모든 열 데이터를 가져오는 것을 사용했다.

- win32com.client 모듈 import와 win32.gencache.EnsureDispatch('Excel.Application')를 사용한 이유는 [openpyxl 엑셀 파일 확장자(.xls) 에러 해결하는 방법] 참고

 

※ 참고 포스팅

 

openpyxl 엑셀 파일 확장자(.xls) 에러 해결하는 방법