[pywin32]파이썬코드 한 줄로 엑셀 표 자동생성

들어가며,

파이썬에는 엑셀파일이나 엑셀프로그램을 다루는 다양한 모듈이 있습니다.

엑셀파일을 다루는 모듈 중에는 pandas나 openpyxl이 많이 쓰이고

마이크로소프트 엑셀 프로그램을 직접 다루는 프로그램으로는 xlwings나 pywin32가 많이 쓰이죠.

저는 개인적으로 pywin32 모듈을 가장 선호합니다. 그 이유는 여러 가지가 있겠지만,

우선 DRM보안에 걸리지 않고, 다른 모듈에 비해 가장 VBA와 비슷한 명령어로 사용할 수 있어서입니다.

엑셀의 매크로 녹화를 통해 VBA코드를 복사한 후 파이썬으로 옮기면 대부분 큰 수정 없이 사용할 수 있거든요.

일례로 이번 시간에는,

파이썬으로 빈 엑셀파일을 열고 1행에 칼럼제목을 적은 후에,

빈 표를 생성하는 코드를 파이썬으로 작성하는 과정을 보여드리려고 합니다.

참고로 제가 말씀드리는 표는,

이런 표 말고,

위와 같은 그냥 줄 그어놓은 표 말고, Ctrl-T를 눌러서 생성하는 표(ListObject)를 말씀드리는 겁니다.

이렇게 범위선택한 후에

위와 같이 범위를 선택한 후에 Ctrl-T를 누르면

표 만들기 팝업

머리글 포함(선택범위 첫 번째 행을 제목 행으로 지정)에 체크를 하고 확인 버튼을 누르면

"표1"이 생성되었습니다.

엑셀에서 "표"를 활용하면 데이터입출력도 DB와 유사하게 다룰 수 있고, 피벗테이블, 피벗차트 생성도 간단하며,

칼럼별 필터링이나 정렬, 요약 등 아주 유용한 기능이 많습니다.

표에 관련된 상세한 내용은 다른 포스팅이나 영상을 참고하시기 바랍니다.

 

---

 

본론 : 파이썬으로 새 엑셀파일 만들고 표 생성하기

이 포스팅을 마칠 때 작성되어 있을 파이썬 코드는 10줄도 되지 않습니다.

완성된 파이썬 코드를 먼저 보여드린 후, 라인별로 추가설명을 드리겠습니다.

참고로 아래의 코드를 실행하기 위해서는 파이썬과 pywin32모듈,

그리고 마이크로소프트 오피스가 설치되어 있어야 합니다.

import win32com.client as win32  # 모듈 임포트


excel = win32.gencache.EnsureDispatch("Excel.Application")  # 엑셀 실행
wb = excel.Workbooks.Add()  # 워크북 생성
ws = wb.Worksheets(1)  # 워크시트 지정
excel.Visible = True  # 백그라운드 해제

ws.Range("A1:G1").Value = ("기관명", "부서명", "담당자", "직위", "전화번호1", "전화번호2", "이메일주소")  # 제목 입력
excel.ActiveSheet.ListObjects.Add(1, ws.Range("A1:G1"), None, 1).Name = "표1"  # 범위 표 지정

위 코드를 파이썬으로 실행하면 엑셀파일이 실행되고, 아래 이미지처럼 표가 만들어집니다.

표 생성 완료

라인별로 상세히 설명을 보태고 싶지만, 잠이 오는 관계로ㅜ

아홉번째 라인까지는 다 이해하실 거라고 생각이 됩니다.

그래서 마지막 10번 라인만 설명드리겠습니다.

excel.ActiveSheet.ListObjects.Add(1, ws.Range("A1:G1"), None, 1).Name = "표1"  # 범위 표 지정

이 라인은 VBA로 녹화 후 거의 그대로 가져온 코드입니다.

VBA코드 녹화를 한 번 같이 해보십시다.

파이썬 콘솔에 9번 라인까지만 실행해봅니다.

저는 파이참 기준으로만 설명드리니, VSCode나 다른 IDE를 사용하는 분들은... 잘 해주시기 바랍니다.

9번 라인까지만 선택한 후에 Shift-Alt-E

 

1. 1~9번라인까지 선택한 후에 Shift-Alt-E를 누르면 선택한 범위의 코드만 파이썬콘솔로 옮겨 실행해줍니다.

잠시 후 아래와 같이 엑셀이 실행되고 워크시트 1행에 제목문자열이 입력된 상태가 됩니다.

파이썬으로 엑셀을 실행한 상태

---

2. 우리는 10번째 라인(ListObjects.Add)을 모르는 셈 치고,

VBA를 녹화해봅시다. 보기-매크로-매크로 기록(R)을 클릭합니다.

매크로 기록(R)

 

그러면 아래와 같은 팝업이 화면 가운데에 나타납니다.

매크로 기록 옵션. 확인만 눌러도 됨.

---

3. 그냥 "확인"을 눌러준 후에,

1행 아무 셀에나 캐럿을 가져다놓고 Ctrl-T를 누릅니다.

"머리글 포함(M)" 체크한 후 "확인"

---

4. 머리글 포함에 체크한 후 확인을 누릅니다.

표가 생성된 상태.

---

5. 표가 생성되었으니, 매크로 녹화를 종료합시다. 보기-매크로-기록 중지(R)를 클릭합니다.

기록 중지(R) 클릭

---

6. 이제 녹화한 매크로를 확인해봅시다. 보기-매크로-매크로 보기(V)를 클릭합니다.

---

매크로 선택창에서 "편집(E)" 클릭

---

7. 방금 녹화한 매크로1이 선택된 상태로, "편집(E)"을 클릭하시면 아래와 같이 코드창이 나타납니다.

Module1 (코드) 창

방금 녹화된 VBA코드입니다.

여기서 필요한 코드는 딱 한 줄입니다. 바로 ListObjects.Add 메서드가 있는 가장 긴 라인인데요.

필요한 코드만 정리해보면

ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$G$1"), , xlYes).Name = "표1"

위와 같습니다.

---

8. 이제 이 라인을 파이썬 코드로 바꿉니다. 중요하고 유용한 팁을 알려드리겠습니다.

 

1. xlSrcRange, xlYes 등 xl로 시작하는 상수값들은 일반적으로 정수이며, win32.constants.xlSrcRange 식으로 불러올 수 있습니다. 예를 들어 xlSrcRange == 1, xlYes == 1입니다. 메서드 실행시 해당 정수를 입력해도 무방합니다.
2. ActiveSheet은 excel.ActiveSheet로 변경해주면 됩니다.
3. VBA는 특정 파라미터를 지정하지 않을 때 그냥 빈 칸으로 두고 콤마를 연속으로 사용하는 경우가 있는데 파이썬에서는 문법오류가 발생하기 때문에, 콤마 사이에 None이나 '' 등을 넣어서 패스해줍시다.
4. 마지막으로 xlSrcRange가 1이고 xlYes가 1인 건 파이썬 콘솔에서도 확인 가능하지만, 엑셀에서 미리 확인할 수 있는 간편한 방법이 있는데 바로 "개체 찾아보기(F2)"를 사용하는 겁니다.

VBA창에서 F2를 누르면 개체 찾아보기 창이 뜨는데,

실시간으로 확인 가능

좌측 상단 검색어 입력란에 원하는 상수명을 입력하고 엔터를 누르면

하단에 "Const xlSrcRange = 1" 이라는 값이 출력됩니다.

이를 통해 엑셀에서 VBA constants의 값을 하나씩 알아볼 수 있습니다.

---

9. 위의 과정을 거쳐서 만들어낸 파이썬 코드는 아래와 같습니다. (포스팅 시작 부분에 보여드렸죠.)

excel.ActiveSheet.ListObjects.Add(1, ws.Range("A1:G1"), None, 1).Name = "표1"

입니다.

설명은 여기까지~입니다.

다음 시간에는 여기에 데이터를 입력한 후, 기관명으로 오름차순 정렬하는 파이썬코드를 알려드리겠습니다.

끝.

---

 

부록1. FAQ

---

FAQ1. 그냥 로그파일처럼 한 행씩 추가하는 방법도 있는데, 굳이 이렇게 표까지 만들어야 할 필요가 있는지?

만들어주는 게 좋다고 생각합니다. 우선 디자인이 예뻐지고, 특정 칼럼을 기준으로 정렬하는 과정이 훨씬 간편해집니다. 표에서 정렬이나 필터링, 피벗테이블 또는 차트를 생성하는 파이썬 코드는 이어지는 포스팅에서 조만간 알려드리겠습니다. 

---

FAQ2. 보여주신 대로 했는데, 4번라인에서 엑셀실행시 오류가 납니다. 해결방법은?

win32.gencache.EnsureDispatch 로 엑셀을 실행할 때 오류가 나는 경우가 있습니다.

1분이면 해결할 수 있는 간단한 방법 하나를 알려드리겠습니다.

 

1. pythonwin.exe를 실행합니다. 제 PC의 경우는 해당 파일의 경로가

    "C:\Python\Python39-64\Lib\site-packages\pythonwin\Pythonwin.exe" 네요.

2. Tools - COM Makepy utility 메뉴를 클릭합니다.

COM Makepy utility 실행

3. 라이브러리 중 "Microsoft Office 16.0 Object Library (2.8)"과 유사한 이름을 찾아 선택하고 OK 클릭

    (더 낮은 버전에서는 Excel Application Object Library... 식으로 엑셀이 따로 분리되어 있기도 합니다.)

오피스365 기준입니다;

이 화면이 나오면 끝난 거니, PythonWin을 종료합니다.

4. 파이참을 재시작한 후 다시 코드를 실행해봅니다. 대부분 이 과정으로 오류가 해결됩니다. 끝.

 

---

국내 유일의 파이썬+한컴오피스 업무자동화 입문강의

움짤로 빠르게 배우는 파이썬-아래아한글 자동화 레시피 - 인프런 | 강의