[파이썬 엑셀/PyWin32] 개별 폴더에 위치하는 엑셀 파일의 특정 시트들을 새로운 엑셀 파일에 모두 합치기 (ft. Tkinter를 이용한 폴더 선택 기능 추가)

안녕하세요, 왕초보 코린이를 위한 코딩유치원에 오신 것을 환영합니다.

 

코딩유치원에서는 파이썬 기초부터 사무자동화, 웹크롤링, 데이터 분석 등의 다양한 패키지까지 초보자도 알기 쉽도록 내용을 정리해 놓았습니다.

 

업무는 물론 투자에도 도움이 될만한 전자공시시스템(DART)나 텔레그램(Telegram) 관련 패키지도 배울 수 있으니 많은 관심 부탁드립니다.

 

 

---

오늘은 지난 시간의 엑셀 시트 취합 프로젝트의 업그레이드 버전을 가져와봤습니다.

 

이번 프로그램의 컨셉은 아래와 같습니다. 참고로 해당 프로젝트는 제품 모델당 자동으로 생성되는 Report들을 한번에 취합해서 프린팅 하기 위한 프로그램입니다.

 

수 십, 수 백개의 엑셀 파일을 하나씩 열어가며 인쇄 버튼을 누르는 것보다 훨씬 시간을 절약할 수 있겠죠?

 

1. 전체 코드

 

코드에 대한 설명은 주석으로 대체하도록 하겠습니다. 최대한 자세히 달아두었으나 이해가지 않으시는 부분은 댓글로 질문해주세요!

 

# step1.관련 모듈 및 패키지 import
import glob
import win32com.client
import tkinter
from tkinter import filedialog
import os

# step2.폴더 선택 (Tkinter)
root = tkinter.Tk()
root.withdraw()
path = filedialog.askdirectory(parent=root, initialdir="./", title="폴더를 선택 해 주세요")

# step3.glob 모듈로 원하는 폴더 내의 모든 xlsx 파일의 경로를 리스트로 반환
list_filepath = glob.glob(path + '/**/*.xlsx', recursive=True)

# step4.역슬레시로 출력되는 부분 슬레시로 변환
list_filepath_slash = [i.replace("\\", "/", 5) for i in list_filepath]

# step5.복사한 시트의 이름을 각 Report가 존재하는 폴더 이름으로 변경해주기 위한 폴더명 리스트
list_folder_name = os.listdir(path)

# step6.win32com(pywin32)를 이용해서 엑셀 어플리케이션 열기
excel = win32com.client.Dispatch("Excel.Application")
excel.Visible = True #실제 작동하는 것을 보고 싶을 때 사용

# step7.엑셀 어플리케이션에 새로운 Workbook 추가
wb_new = excel.Workbooks.Add()

# step8.엑셀 시트를 추출하고 새로운 엑셀에 붙여넣는 반복문
for i, filepath in enumerate(list_filepath_slash):

    # 받아온 엑셀 파일의 경로를 이용해 엑셀 파일 열기
    wb = excel.Workbooks.Open(filepath)

    # 새로 만든 엑셀 파일에 추가
    # 추출할wb.Worksheets("추출할 시트명").Copy(Before=붙여넣을 wb.Worksheets("기준 시트명")
    wb.Worksheets("1번 문서").Copy(Before=wb_new.Worksheets("Sheet1"))
    
    # 시트명을 원래 Report 파일이 존재하던 폴더명으로 변경 
    ws = wb_new.Worksheets('1번 문서')
    ws.Name = list_folder_name[i]
    
    # copy 작업이 끝난 Workbook 종료
    wb.Close()

# 처음 새로운 엑셀파일이 생성될 때 존재하는 "Sheet1" 시트 삭제
wb_new.Worksheets("Sheet1").Delete()

# step6. 취합한 엑셀 파일을 "통합 문서"라는 이름으로 저장
wb_new.SaveAs(r"C:\Users\SANGWOO\Desktop\VSCODE\통합 문서.xlsx")

# step7. 켜져있는 엑셀 및 어플리케이션 모두 종료
excel.Quit()

 

2. 새롭게 알게 된 점

 

1)  윈도우의 기준, 파일 혹은 폴더 경로에 /(슬레시)와, \(역슬레시)가 섞여서 들어가면 에러가 난다

 

아래의 코드와 같이 Tkinter로 폴더 경로를 선택한 후, glob 모듈로 하위 폴더 내의 모든 xlsx파일의 경로를 리스트로 받아서 출력해보니 다음과 같이 출력되었습니다. 

 

해당 문제는 전체 코드에서는 개선하여 반영하였습니다.

 

import glob
import tkinter
from tkinter import filedialog

root = tkinter.Tk()
root.withdraw()
path = filedialog.askdirectory(parent=root, initialdir="./", title="폴더를 선택 해 주세요")
print("path : ", path)

list_filepath = glob.glob(path + '/**/*.xlsx', recursive=True)

for i in list_filepath:
    print(i)

 

<출력 결과>

path :  C:/Users/SANGWOO/Desktop/VSCODE/엑셀 시트 취합
C:/Users/SANGWOO/Desktop/VSCODE/엑셀 시트 취합\1번 제품\Report.xlsx
C:/Users/SANGWOO/Desktop/VSCODE/엑셀 시트 취합\2번 제품\Report.xlsx
C:/Users/SANGWOO/Desktop/VSCODE/엑셀 시트 취합\3번 제품\Report.xlsx
C:/Users/SANGWOO/Desktop/VSCODE/엑셀 시트 취합\4번 제품\Report.xlsx
C:/Users/SANGWOO/Desktop/VSCODE/엑셀 시트 취합\5번 제품\Report.xlsx

 

 

2) win32com으로 엑셀을 다룰 때, 같은 이름의 엑셀 파일이 열리면 아래와 같은 오류가 발생한다

 

아래와 같은 에러를 띄우면서 시트를 복사하는 부분에서 걸리는 문제가 발생해서 원인을 찾다보니, Report라는 엑셀 파일을 열고 닫지 않고 그다음 폴더의 Report 파일을 열어서 문제가 발생하는 것을 파악하였습니다.

 

이를 해결하기 위해서는 wb.Close( ) 명령을 사용해야합니다.

 

---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
<ipython-input-42-3f47d6541631> in <module>
     39     # 새로 만든 엑셀 파일에 추가
     40     # 추출할wb.Worksheets("추출할 시트명").Copy(Before=붙여넣을 wb.Worksheets("기준 시트명")
---> 41     wb.Worksheets("1번 문서").Copy(Before=wb_new.Worksheets("Sheet1"))
     42 
     43 # 처음 새로운 엑셀파일이 생성될 때 존재하는 "Sheet1" 시트 삭제

AttributeError: 'NoneType' object has no attribute 'Worksheets'

 

직접 동일명 엑셀 파일을 열었을 때 출력되는 메시지

 

 

3) win32com으로 엑셀 시트 이름을 바꾸는 방법

 

ws = wb.Worksheets('변경 전 시트 이름')

ws.Name = '변경 후 시트명'

 

 

---

 

<참고 자료>

 

https://url.kr/jqxl4b

파이썬 Replace, 폴더 디렉토리 경로 "\"→"/" 자동 변경 방법 (Python)

 

https://stackoverflow.com/questions/31330941/python-win32-excel-copy-worksheet-and-change-name-of-copied-worksheet

python win32 excel copy worksheet and change name of copied worksheet