본문 바로가기
업무자동화/파이썬-엑셀 자동화 기초

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

by Martinii의 회사원코딩 2021. 6. 24.

들어가며,

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

엑셀파일을 다루는 모듈 중에는 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. 파이참을 재시작한 후 다시 코드를 실행해봅니다. 대부분 이 과정으로 오류가 해결됩니다. 끝.


 

부록2. 업무자동화 및 엑셀 관련 교재 추천


시중에 나왔던 파이썬-엑셀 서적 중 대부분은 은근히 "엑셀을 버리고 파이썬과 판다스로~" 이런 느낌이 있었는데, 아래의 책, "일 잘하는 직장인을 위한 엑셀 자동화 with 파이썬"은 엑셀과 파이썬의 비중을 적절히 잘 잡았다고 생각합니다. 엑셀 차트 시각화를 자동화하거나, 상당한 수준의 통계데이터 분석을 따라해볼 수 있는 점도 여타의 책과 차별화되는 부분인 것 같네요. 특히 파이썬 파트가 상당히 꼼꼼(철저?)한데, 개인적으로는 이 분의 커리큘럼이나 난이도가 파이썬-엑셀을 배우기에 입문자에게 가장 적절한 과정인 것 같습니다. 너무 쉽고 간단하지도 않으면서, 그렇다고 읽기를 포기할 만큼 막막하거나 어렵지 않은 수준에서, 고민을 많이 하신 흔적이 보입니다. (목차를 아래 붙여두었으니 구매하실 분들은 참고하시기 바랍니다.)

 

좋은 책 써주셔서 감사합니다.

 

일 잘하는 직장인을 위한 엑셀 자동화 with 파이썬 : 복잡하고 지루한 반복 업무를 쉽고 빠르게 해

COUPANG

www.coupang.com

 

최은석

GIST에서 박사학위를 받고 삼성전자에서 센서 기반 인터랙션 설계, 센서 신호 처리, 임베디드 시스템 S/W 관련 업무를 하고 있다. 항상 좀 더 좋은 방법이 없을까 고민하며 새로운 아이디어를 제시하고 구현하는 과정을 즐긴다. 파이썬을 이용한 업무 자동화, 데이터 처리 및 분석, 마이크로컨트롤러 응용에 관심이 많으며 주변 사람들에게 파이썬의 편리함을 알리고 있다. 저서로는 『데이터 분석을 위한 파이썬 철저 입문』이 있다.

★ 01장: 프로그래밍 언어 파이썬 시작하기
1.1 시작하기 전에 알아보기
____엑셀과 VBA의 한계
____파이썬의 특징
1.2 파이썬 시작하기
____파이썬 개발 환경 설치
____파이썬 실행하기
____통합 개발 환경에서 파이썬 코드 작성
1.3 주피터 노트북 활용
____주피터 노트북 실행과 노트북 생성
____주피터 노트북 주요 기능 둘러보기
____주피터 노트북에서 코드 작성
____주피터 노트북에서 문서 작성
____그 밖에 할 수 있는 작업
1.4 정리

★ 02장: 파이썬 기본 문법
2.1 변수와 자료형
____변수
____숫자(int, float)
____문자열(str)
____불(bool)
____리스트(list)
____튜플(tuple)
____세트(set)
____딕셔너리(dict)
2.2 제어문
____조건문
____반복문
2.3 데이터의 출력
____기본 출력
____출력 형식 지정
2.4 정리

★ 03장: 함수, 클래스, 모듈
3.1 함수
____함수의 정의와 호출
____내장 함수
3.2 클래스
____클래스와 객체
____클래스의 상속
3.3 모듈
____모듈 만들고 불러오기
____내장 모듈
____패키지
3.4 정리

★ 04장: 파일 읽고 쓰기와 문자열 처리
4.1 파일 읽고 쓰기
____파일 읽고 쓰기 위한 기본 구조
____파일 읽기
____파일을 한 줄씩 읽어 처리하기
____파일 쓰기
____with 문으로 파일 읽고 쓰기
4.2 문자열 처리
____문자열 분리하기: split()
____불필요한 문자열 삭제하기: strip()
____문자열 연결하기: join()
____문자열 찾기: find(), count(), startswith(), endswith()
____문자열 바꾸기: replace()
____대소문자 변경하기: lower(), upper()
4.3 정리

★ 05장: 데이터 처리와 분석을 위한 라이브러리
5.1 배열 데이터 연산에 효율적인 넘파이(NumPy)
____배열 데이터 생성
____배열 데이터 연산
____배열 데이터 선택
5.2 표 데이터 처리에 강한 판다스(pandas)
____데이터 구조와 생성
____표 형식의 데이터 파일 읽고 쓰기
____표 데이터 연산
____표 데이터 선택
____표 데이터 통합
5.3 정리

★ 06장: 엑셀 파일을 다루는 라이브러리
6.1 XlsxWriter로 엑셀 파일 생성하기
____XlsxWriter 기본 사용법
____XlsxWriter로 다양한 자료형 데이터 쓰기
____XlsxWriter로 셀 서식 지정
____XlsxWriter로 그림과 텍스트 상자 삽입
6.2 파이썬으로 엑셀과 상호 작용할 수 있는 xlwings
____xlwings의 기본 사용법
____xlwings로 다양한 자료형 데이터 쓰고 읽기
____xlwings로 엑셀 파일 출력하기
6.3 정리

★ 07장: 엑셀 파일과 데이터 다루기
7.1 파이썬을 이용한 엑셀 파일 처리 과정
7.2 엑셀 파일 통합
____효율적인 데이터 처리를 위한 엑셀 데이터 구조
____여러 엑셀 파일을 하나로 통합하기
7.3 엑셀 데이터 필터링과 계산
____데이터 필터링
____데이터 계산
____여러 엑셀 파일에 적용하기
7.4 알아두면 유용한 엑셀 함수를 파이썬으로 처리하기
____지정한 범위에서 데이터 찾아서 가져오기
____조건에 따라 결과 입력하기
____조건에 따라 다른 서식 적용하기
7.5 엑셀 데이터 정제
____누락 데이터 확인과 처리
____데이터 추출과 정리
7.6 엑셀 데이터 요약과 집계
____피벗 테이블 만들기 기본
____피벗 테이블 만들기 심화
7.7 웹 페이지에서 데이터 가져오기
____표 데이터 가져오기 기본
____표 데이터 가져오기 심화
7.8 정리

★ 08장: 엑셀 데이터 시각화
8.1 엑셀 차트
____엑셀 차트를 생성하는 코드의 기본 구조
____막대형 차트
____꺾은선형 차트
____영역형 차트
____원형 차트
____분산형 차트
8.2 엑셀 스파크라인
____스파크라인의 종류와 활용 예
____스파크라인을 생성하는 코드의 기본 구조
8.3 판다스로 그래프 그리기
____그래프를 위한 기본 구조
____선 그래프(꺾은선형 차트)
____막대 그래프(막대형 차트)
____산점도(분산형 차트)
____파이 그래프(원형 차트)
____면적 그래프(영역형 차트)
____히스토그램
____박스 그래프(상자 수염 차트)
____그래프를 저장하고 엑셀 파일에 추가하기
8.4 정리

★ 09장: 엑셀과 파이썬을 이용한 통계 데이터 분석
9.1 통계 데이터 분석 기본
____기본 통계량 이해
____기본 통계량 구하기
9.2 통계 데이터 분석 심화
____상관 분석
____회귀 분석
9.3 정리

- 아나콘다(Anaconda)를 활용한 파이썬 개발 환경 설치, 주피터 노트북 사용법
- 파이썬 기본 문법, 문자열 데이터 처리(분리, 삭제, 연결, 찾기, 바꾸기)
- 넘파이와 판다스를 이용한 배열과 표 데이터 처리(연산, 집계, 데이터 선택과 삭제, 통합)
- XlsxWriter로 서식을 지정해 엑셀 파일 쓰기, 엑셀 파일에 그림과 텍스트 상자 삽입
- xlwings로 문서 보안이 적용된 엑셀 파일 읽고 쓰기, 프린트나 PDF 파일로 출력
- 엑셀 파일 다루기(데이터 읽기, 쓰기, 통합, 계산, 집계, 누락 데이터 확인과 처리, 피벗 테이블)
- 판다스에서 xlsxwriter 엔진을 이용해 엑셀 차트와 스파크라인 그리기
- 판다스와 matplotlib으로 데이터 시각화(선/막대/파이/면적/박스 그래프, 산점도, 히스토그램)
- 엑셀과 파이썬을 활용한 통계 데이터 분석(기본 통계량 분석, 상관 분석, 회귀 분석)

 

 

 


donaricano-btn

댓글0