[엑셀이 편해지는 파이썬] 9장. 엑셀 자동화 (245~275p)

9장. 엑셀 자동화¶

9.1 엑셀윙스 시작하기¶

9.1.1 엑셀을 데이터 뷰어로 사용하기¶

매번 데이터프레임에서 작업하고 엑셀이 어떻게 변했는지 확인하기 귀찮고 힘들다.
to_clipboard()를 사용해 엑셀에 붙여 넣을 수도 있지만,
엑셀윙스의 view 함수를 써보자.

In [ ]:

import datetime as dt
import xlwings as xw
import pandas as pd
import numpy as np

df = pd.DataFrame(data=np.random.randn(100, 5),
                  columns=[f"Trial {i}" for i in range(1, 6)])
df

Out[ ]:

	Trial 1	Trial 2	Trial 3	Trial 4	Trial 5
0	0.700193	0.618968	0.481117	-1.781480	0.196548
1	-0.386819	0.627257	-0.111451	-0.058724	1.172992
2	-0.229460	-0.039730	-0.122273	-0.584582	-1.075843
3	0.993330	-0.117246	-1.127032	1.021788	-1.518022
4	1.192030	1.707856	-1.351326	1.295865	-1.031627
...	...	...	...	...	...
95	0.730070	1.307933	0.129068	1.650098	-0.333116
96	2.291650	0.518444	-1.714803	-0.904069	0.414425
97	-0.394114	0.461352	-0.941090	1.128649	1.294691
98	-0.510230	-0.270064	1.503375	-0.983186	-1.387183
99	-0.030095	0.146202	-0.122732	0.476420	-0.140183

100 rows × 5 columns

In [ ]:

xw.view(df)

9.1.2 엑셀 객체 모델¶

• Refer: xw.load 함수
• Refer: 엑셀 인스턴스가 무엇이고 왜 중요한가? Link

가장 포괄적인 구조로 Apps가 있고,
books 컬렉션을 포함하는 app
sheets 컬렉션을 포함하는 book
range 객체와 charts 같은 컬렉션에 접근할 수 있는 sheet
하나 이상의 연속적인 셀을 요소로 포함하는 range

위와 같은 객체를 포함하는 계층으로 표현할 수 있다.
range 객체가 그러므로 가장 아래에 있으며, value 속성으로 값을 쓰고 읽을 수 있다.

Warning 1에서 시작하는 인데스 - range 객체, 그 외 다른 엑셀윙스 객체는 0으로 시작하는 인덱스를 사용

같은 워크북을 두 개의 엑셀 인스턴스에서 열었거나, 워크북을 열 엑셀 인스턴스를 지정하고 싶다면 - books 컬렉션을 사용

9.1.3 VBA 코드 실행¶

xlsm에 저장된 매크로를 macro 객체 인스턴스를 만들어 호출하면
마치 네이티브 파이썬 함수인 것처럼 사용할 수 있다.

9.2 변환기, 옵션, 컬렉션¶

9.2.1 데이터프레임 사용¶

판다스를 데이터프레임을 이용해 값을 읽고 쓰기
엑셀에서 Shift + Command + 아래쪽 화살표와 오른쪽 화살표를 누른 것과 같은, 범위로 선택을 확장하기
이를 파이썬에서 구현한 것이 expand() 메서드이다.
이 메서드에 options(pd.DataFrame)을 전달하면 데이터프레임 전체에 값이 있는 범위를 선택한 것과 같고 그 값을 추출할 수 있다.

9.2.2 변환기와 옵션¶

options 메서드는 엑셀에 값을 읽고 쓰는 방법을 조정
range 객체의 value 속성을 호출할 때만 평가된다.

엑셀윙스의 convert 인자가 받는 값을 변경/추가해서 변환기를 직접 만들 수도 있다.
Refer https://oreil.ly/Ruw8v

9.2.3 차트, 그림, 정의된 이름¶

엑셀 차트¶

charts 컬렉션의 add 메서드를 사용, 차트 타입과 데이터를 지정하여 엑셀 파일에 차트를 추가
사용할 수 있는 그래프 타입 Link

그림: 맷플롯립 그래프¶

figure 객체를 가져온 다음, 이를 pictures.add의 인자로 전달해 그래프를 그림으로 변환하여 엑셀에 전달
그림을 새로운 그래프로 업데이트 하려면 - update 메서드를 호출 (그림만 교체된다.)

• Refer: pillow를 설치하자 - 그림이 엑셀에 정확한 크기와 비율로 삽입되게 할 수 있다.

정의된 이름¶

엑셀에서 정의된 이름은 범위, 공식, 상수에 이름을 할당해 만듦
가장 흔한 경우는 범위에 이름을 붙이는 것으로 이름 붙은 범위라 부름
기본 스코프는 워크북이며, 로컬인 시트 스코프를 사용할 수도 있다.

9.2.4 또 다시 살펴보는 케이스 스터디: 엑셀 보고서¶

엑셀윙스는 꼭 엑셀이 설치되어 있어야 작동하므로, 서버에서 작업하거나 라이센스가 없는 경우 문제가 될 수 있다.
서버에서 작업 시 엑셀이 깔려 있더라도 안정성이 문제가 될 수 있는데, apps = xw.App()와 같이 각 스크립트를 새 엑셀 인스턴스에서 실행하기만 해도 일반적인 안정성 문제는 해결할 수 있다.

9.3 엑셀윙스 고급 주제¶

9.3.1 엑셀윙스 기초¶

macOS에서는 애플스크립트에 의존한다 - appscript https://oreil.ly/tIsDd

9.3.2 성능 향상¶

애플리케이션 간 호출을 최소화하라 - 가장 쉬운 방법은 개별 셀을 순회하지 말고 엑셀 범위를 읽고 쓰는 것
값 원형 - 윈도우나 macOS의 데이터 타입을 맞추기 위해 값 전체를 순회하며 작업을 하는 데 이를 스킵하기 위해 options('raw')를 전달
앱 프로퍼티 - app 객체의 프로퍼티를 바꿔서 코드가 더 빨리 실행될 수도 있다.

• 스크립트 마지막에 원래대로 돌려 놓아야 한다.
• 윈도우에서 숨은 엑셀 인스턴스에서 실행하면 조금 더 빨라 질 수 있다.

9.3.3 필요한 기능이 없을 때 대처하는 법¶

1) 일단, 메서드가 있는지부터 확인

• 주피터 노트북에서 객체에 점을 찍은 후 탭 키로 나오는 것 확인
• dir(sheet["A1"])과 같이 객체에서 사용 가능한 메서드와 속성 출력
• 엑셀윙스 API 문서 https://oreil.ly/EiXBc
  2) `sheet["A1"].api 호출 - 윈도우는 pywin32, macOS는 애플스크립트 객체에 접근
  3) 엑셀 VBA 문서에서 엑셀 객체 모델의 관련 기능을 검색
  4) 엑셀윙스 개발자 가이드 https://oreil.ly/YSS0Y를 검색

9.4 요약¶

빠진 기능이 무엇이든, 판다스와 엑셀윙스를 조합하면 쉽게 대응할 수 있다.