엑셀과 파이썬 결합을 통한 강력한 엑셀 활용법 How to Supercharge Excel With Python

Photo by Lukas from Pexels

How to integrate Python and Excel with xlwings

엑셀과 파이썬을 결합하여 사용할 수 있다면, 엑셀에 날개를 다는 것이나 마찬가지다. xlwings 라고 불리는 파이썬 라이브러리는 이를 가능하게 해주는데, VBA 를 통해 파이썬 스크립트를 호출하여 데이터를 전달할 수 있게 한다. 그럼 아래에서 이에 대해 알아보자.

파이썬과 엑셀 VBA 통합 사용의 장점

VBA 로도 충분히 상당한 작업을 할 수 있다. 하지만 파이썬을 엑셀에 통합해서 사용해야하는 이유는 무엇일까?

1. VBA 에 대한 지식 없이도 엑셀에서 커스텀 함수를 만들수 있다
2. 아무래도 사용자들은 엑셀이 친숙하다
3. 파이썬을 사용함으로써 확실히 데이터 작업 속도가 빨라진다
4. 파이썬에는 수많은 라이브러리가 있다 (Machine Learning, Data Science 등)
5. 마지막으로 여러분은 할 수 있기 때문이다

xlwings 설치하기

가장 먼저 진행해야하는 것은, 다른 새로운 라이브러리를 사용할 때와 마찬가지로 해당 라이브러리를 설치하는 일이다. 터미널에서 다음과 같이 입력해보자. 물론 여러분의 OS 에는 파이썬이 설치되어 있다는 가정하에 진행된다. 

pip install xlwings

이제 엑셀 통합 파트를 설치해보자. 열려있는 엑셀 인스턴스를 모두 종료한 후 터미널에서 다음과 같이 입력한다. 

윈도우 10 기반의 엑셀 2016의 경우 종종 아래와 같은 오류가 발생한다고 한다. 이 경우, 누락된 디렉토리를 mkdir 을 이용하여 다음과 같이 생성하면 된다. 

xlwings 0.17.0
[Errno 2] No such file or directory: 'C:\\Users\\alicia\\AppData\\Roaming\\Microsoft\\Excel\\XLSTART\\xlwings.xlam'

mkdir C:\\Users\\alicia\\AppData\\Roaming\\Microsoft\\Excel\\XLSTART

이상의 엑셀과 파이썬 라이브러리간의 통합작업이 별 이상없이 진행되었다면, 다음과 같은 차이점을 엑셀에서 발견할 수 있을 것이다. 최상단에 xlwings 라는 탭이 생겼다.

 

xlwings 사용자 정의 함수 활성화

먼저 엑셀 애드인을 로드해야한다. 다음 위치에서 로드가 가능하다.

Excel 옵션 > 추가기능 > 관리 Excel 추가 기능 이동 

마지막으로 VBA 프로젝트 개체 모델을 대한 접근을 허용해야 한다. Office 단추 > Excel 옵션 > 보안센터 > 보안 센터 설정 > 매크로 설정 에서 진행할 수 있다. 다음과 같이 설정한다. 

 

xlwings 시작하기

엑셀과 파이썬을 넘나드는 방법에는 2가지가 있다. 하나는 VBA 에서 파이썬 스크립트를 직접 호출하는 것이고, 다른 하나는 사용자 정의 함수를 통하는 것이다. 둘 다 살펴보자. 

적당한 폴더로 이동하여 터미널에서 다음과 같이 입력한다. 다음 위치에 PythonXL 이라는 폴더를 하나 만들고 해당 위치에서 다음 명령을 실행했다.

위의 명령은 프로젝트 이름 PythonXL01 과 동일한 새로운 엑셀 시트와 파이썬 파일을 아래와 같이 해당 위치에 생성한다.

.xlsm 파일을 열게되면, 아래와 같이 _xlwings.conf 라는 새로운 시트가 생성된 것을 볼 수 있다. xlwings 의 기본 설정값을 변경하고 싶다면, 이 시트의 이름을 _ 를 제외한 xlwings.conf 로 새로 매기면 된다. 

 

VBA to Python

VBA 편집기를 열기 위해 Alt + F11 을 치면, 다음과 같은 화면을 볼 수 있다.

위의 코드는 다음과 같은 작업을 수행하게 된다.

1. 스프레드시트와 같은 위치에 있는 파이썬 스크립트를 찾는다.
2. 스프레드시트와 같은 이름을 가진 파이썬 스크립트를 찾는다.
3. “main()” 파이썬 스크립트로부터 "main()" 함수를 호출한다.

몇 가지 샘플을 통해 좀 더 자세히 살펴보자.

 

예제 1: 엑셀 외부에서 조작하여 엑셀로 결과 반환

본 예제에서는 엑셀 외부에서 작업하여, 그 결과를 스프레드 시트로 보여주는 것을 살펴본다. 샘플 csv 파일을 만들어 적당한 위치에 저장한 후 다음과 같이 진행한다.

VBA code : 앞선 코드를 변경없이 그대로 사용한다.

Python code : 다음과 같다. csv 파일이 저장된 위치는 각자 맞게 수정한다.

import xlwings as xw
import pandas as pd
def main():
    wb = xw.Book.caller()
    df = pd.read_csv(r'C:\Python\PythonXL\temp\TestData.csv')
    df['total_length'] =  df['result1'] + df['result2']
    wb.sheets[0].range('A1').value = df

예제용 csv 파일이다.

# TestData.csv

result1	result2
1.22	1.83
15.55	23.325
874.1	1311.15
44.1	66.15
849.4	1274.1
99.8	149.7
87.7	131.55
889	1333.5
112.4	168.6
156.8	235.2

실행결과는 다음과 같다. Run main 을 클릭하면 다음과 같이 result1, result2 를 합산 total_length 를 포함하여 스프레드시트로 반환해준다.

 

예제 2: 엑셀 입력값 사용하기

엑셀에서 입력값을 받아와, 파이썬에서 작업을 진행한 후 다시 엑셀로 결과값을 전달하는 예제를 진행해보자. 좀 더 구체적으로 이야기하면, 인사, 이름, 그리고 뮨제가 포함된 파일 위치를 읽어와, 해당 파일의 랜덤 라인을 읽어와서 문제를 반환한다.

VBA code: 수정없이 기본값으로 둔다.

Python code 는 다음과 같다.

import xlwings as xw
import random

def random_line(afile):
    line = next(afile)
    for num, aline in enumerate(afile, 2):
      if random.randrange(num): continue
      line = aline
    return line

def main():
    wb = xw.Book.caller()
    listloc = str(wb.sheets[0].range('B3').value)
    fhandle = open(listloc, encoding = 'utf-8')
    
wb.sheets[0].range('A5').value = wb.sheets[0].range('B2').value + ' ' + wb.sheets[0].range('B1').value + ' here is a question for you'
    wb.sheets[0].range('A6').value = random_line(fhandle)

예제 csv 파일은 다음가 같다.

# list.csv

How do I explain the long life spans of Elves?
My friend listed my name as a co-author on two journal papers without my permission
Best way to pass a 2d array to functions which size is unknown at compile time in pure C
What was so different about “Night of the Living Dead” from previous zombie movies?
Calculate the Progressive Mean™
Is it neccessary to clear browser cache periodically?
Are bread improvers really needed?
How to make set of equations appear side by side using empheq package?
Can open-source software be peer-reviewed and published?
Why wouldn't dragon-hide make me fire-resistant?
How to write sign like that pic
Can a deterministic finite automaton ever go into an infinite loop?
Pros and cons of mixed hydraulic/mechanical brakes vs pure hydraulics
Should I cite an article that I can't find?
Is the mortality rate of 2019-nCoV 41%?
Concentric rings on a snub square tiling
Way to prove you are human when the Turing test is not sufficient
Crossed & nested random effects model
Circular diagram in LaTeX
How does the math for the Iowa Democratic Caucuses work?
Unlocked packages vs Unmanaged packages
Showing 3d spikes in QGIS?
Is the Senate Compelled to Acquit Trump?
Are postdocs in your own institution frowned upon?

실행결과는 다음과 같다.

만약 실행시 다음과 같은 에러가 발생하는 경우 다음 링크를 참조한다. 

UnicodeDecodeError: 'utf-8' codec can't decode byte 0xa1 in position 251: invalid start byte 

https://stackoverflow.com/questions/12468179/unicodedecodeerror-utf8-codec-cant-decode-byte-0x9c

UnicodeDecodeError: 'utf8' codec can't decode byte 0x9c

아래와 같이 errors = 'ignore' 를 추가한다.

import xlwings as xw
import random

def random_line(afile):
    line = next(afile)
    for num, aline in enumerate(afile, 2):
      if random.randrange(num): continue
      line = aline
    return line

def main():
    wb = xw.Book.caller()
    listloc = str(wb.sheets[0].range('B3').value)
    fhandle = open(listloc, encoding = 'utf-8', errors='ignore')
    
    wb.sheets[0].range('A5').value = wb.sheets[0].range('B2').value + ' ' + wb.sheets[0].range('B1').value + ' here is a question for you'
    wb.sheets[0].range('A6').value = random_line(fhandle)

 

xlwings 를 사용한 사용자 정의 함수

엑셀 사용자 정의 함수로 변환하기 위해서는 함수가 시작되기 전 라인에 @xw.func 을 다음과 같이 추가하면 된다. 파이썬 코드는 다음과 같다.

import xlwings as xw

@xw.func
def double_sum(x, y):
    """Returns twice the sum of the two arguments"""
    return 2 * (x + y)

UDF Modules 란에 가져올 모듈이 포함된 파이썬 파일이름을 입력하고 Import Functions 을 누른다. 그러면 해당 함수를 다른 엑셀 함수처럼 사용할 수 있다.

그러면 여려 개의 모듈을 가져와야하는 경우에는 어떻게 해야하는가? 라는 의문이 생길 수 있다. 이 부분에 대해서는 아래 도움말을 참조한다. 여기에 따르면 다음과 같이 ; 을 기준으로 입력하면 된다고 한다. 

Example: UDF_MODULES = "common_udfs;myproject"  

http://docs.xlwings.org/en/stable/addin.html#global-settings

xlwings 를 활용한 더욱 다양한 예제는 하단 링크를 참조한다.

https://www.xlwings.org/examples

Examples

원문소스

How to Supercharge Excel With Python