[Python] Xlwings 사용법

Introduction

Python에서 엑셀을 쉽게 다룰 수 있도록 돕는 다양한 라이브러리가 존재하는데, 그 중에 하나인 xlwings를 소개하고자 한다.
(참고로 Jupyter Notebook에서 진행을 했습니다.)

• Xlwings는 Python에서 엑셀을 제어할 수 있는 라이브러리다.
• VBA와 연계된 Python script가 필요하다면 addin을 통해 사용할 수 있다.
• UDF (User Defined Function)을 사용해 VBA를 알지 못해도 엑셀에서 사용자 함수를 사용할 수 있다.
• 엑셀 프로그램 자체를 동작시키는 원리로, DRM이 걸린 파일도 작업할 수 있다.
• openpyxl 등의 다른 라이브러리로 엑셀을 조작할 수 있지만, 이런 라이브러리는 DRM에 대응 할 수 없다.

설치 방법은 다음 명령으로 쉽게 가능하다.

pip install xlwings

Python에서 사용할 때 보통 xw로 바꿔서 사용한다.

import xlwings as xw

Excel File Hierarchy

엑셀 파일은 다음과 같은 계층 구조로 되어있다.

1. Excel
2. Workbook: 엑셀 파일은 workbook이라는 해당 통합문서를 의미하는 구조를 포함한다.
3. Sheet: 엑셀을 열먼 하단에 sheet tab이 있는 것으로 다수의 sheet이 존재하는 것을 알 수 있다.
4. Cell: Sheet 안에는 마치 표처럼 다수의 cell들로 구성되어있다.

Excel File 생성 / 열기

# Create new 
wb = wx.Book()

xlwings의 Book() 함수를 통해 새로운 엑셀 파일이 열린다.

# Open excel file
wb = wx.Book("./src/my_excel.xlsx")

Book() 함수에 위치를 가리키는 문자열을 넣어주면 해당 경로의 파일을 불러온다.

# Execute excel app
app = xw.App()
# Excel PID
app_pid = xw.apps
print(app_pid)

참고로, 위와 같이 excel application의 PID를 얻을 수있다.

Excel File 저장 / 종료

filename = "xlwings_file.xlsx"

wb.save(filename)
wb.close()

Sheet 관련 기능

Sheet 선택

엑셀에서 다수의 sheet 중 선택을 하는 기능을 다음과 같은 방법으로 수행 가능하다.

wb.sheets
list(wb.sheets)

[<Sheet [workbook.xlsx]Sheet1>,
 <Sheet [workbook.xlsx]Sheet2>,
 <Sheet [workbook.xlsx]Sheet3>]

sheet_name = "Sheet1"
ws = wb.sheets[0]
# ws = wb.sheets[sheet_name] # Same with the above

위 코드를 수행하면 특정 sheet의 object를 가져올 수 있다. 참고로 접근하는 방법은 1) index를 통한 접근과 2) sheet의 이름을 통한 접근 방법 두 가지가 존재한다.

wb.sheets[sheet_name].activate()

만약 엑셀에서 특정 sheet을 클릭해 보여주는 기능을 수행하고 싶은 경우 activate()을 통해서 수행 가능하다.

ws = wb.sheets.active
print(ws)

그리고 현재 active한 sheet를 얻는 방법은 active property를 통해 접근 가능하다.

Sheet 추가 / 수정 / 삭제

new_sheet_title1 = "xlwings_sheet1"
new_sheet_title2 = "xlwings_sheet2"
new_sheet_title3 = "xlwings_sheet3"

wb.sheets.add(new_sheet_title1)
wb.sheets.add(new_sheet_title2, before="Sheet2")
wb.sheets.add(new_sheet_title3, after=wb.sheets[-1])

list(wb.sheets)

[<Sheet [workbook.xlsx]xlwings_sheet1>,
 <Sheet [workbook.xlsx]Sheet1>,
 <Sheet [workbook.xlsx]xlwings_sheet2>,
 <Sheet [workbook.xlsx]Sheet2>,
 <Sheet [workbook.xlsx]Sheet3>,
 <Sheet [workbook.xlsx]xlwings_sheet3>]

add() 함수를 통해 원하는 위치에 sheet를 추가 할 수 있다.

new_sheet_name = "new_sheet"
wb.sheets[0].name = new_sheet_name
list(wb.sheets)

[<Sheet [workbook.xlsx]new_sheet>,
 <Sheet [workbook.xlsx]Sheet1>,
 <Sheet [workbook.xlsx]xlwings_sheet2>,
 <Sheet [workbook.xlsx]Sheet2>,
 <Sheet [workbook.xlsx]Sheet3>,
 <Sheet [workbook.xlsx]xlwings_sheet3>]

name property를 통해 sheet 이름을 바꿀 수 있다.

target_sheet = "new_sheet"
wb.sheets[target_sheet].delete()
list(wb.sheets)

[<Sheet [workbook.xlsx]Sheet1>,
 <Sheet [workbook.xlsx]xlwings_sheet2>,
 <Sheet [workbook.xlsx]Sheet2>,
 <Sheet [workbook.xlsx]Sheet3>,
 <Sheet [workbook.xlsx]xlwings_sheet3>]

해당 sheet에 접근해 delete() 함수를 통해 제거를 할 수 있다.

Sheet의 Cell 선택

# range("[Column Row]")
cell1 = ws.range("A1")
cell2 = ws.range("B4")

print(cell1.value)

100

sheet object에서 range()를 통해 cell에 접근해서 해당 값을 얻기 위해선 value property를 읽으면 된다.

cells = ws.range("A1:G1")

print(cells.value)

[100, 200, 300, 400, 500, 600, 700]

만약 다수의 cell들을 접근하기 위해선 slicing 연산자 :를 사용하면 된다.

cell = ws.range("A1")
cells = cell.expand("table")

만약 sheet에 입력된 데이터들의 정확한 cell의 위치를 정할 필요가 없고 전체를 선택하고자 할 때는, expand("table")를 통해서 선택이 가능하다.

특정 Cell 삭제

cells = ws.range("A1:G1")
print(cells.value)

cells.clear()
print(cells.value)

[100, 200, 300, 400, 500, 600, 700]
[None, None, None, None, None, None, None]

clear()를 통해 데이터를 삭제하면, 해당 cell들은 형태는 남아있고 값만 삭제가 된다. (마치 엑셀에서 delete으로 삭제한 것 처럼 동작)

ws.range("A:A").api.Delete()

만약 우리가 엑셀에서 특정 행 또는 열을 삭제하면서 shift 시키는 동작을 하고자 할 때는 api.Delete()를 통해서 수행 할 수 있다.

DataFrame 변환

Sheet -> DataFrame

import pandas as pd

df_from_xlwings = ws.range("A1").options(
    pd.DataFrame,
    index=False,        # 몇 개의 index를 사용할지
    header=True,        # header (column)을 사용할지 말지
    expand="table",
).value

print(type(df_from_xlwings))
df_from_xlwings

<class 'pandas.core.frame.DataFrame'>
상품명	발급년도	발급월	주택유형	지역	건수	보증금액
0	전세보증금반환보증	2016.0	1.0	다세대주택	인천광역시	1.0	9.000000e+07
1	전세보증금반환보증	2016.0	1.0	아파트	인천광역시	138.0	2.994885e+10
2	전세보증금반환보증	2016.0	1.0	연립주택	인천광역시	2.0	1.500000e+08
3	전세보증금반환보증	2016.0	1.0	오피스텔	인천광역시	1.0	1.280000e+08
4	전세보증금반환보증	2016.0	2.0	다가구주택	인천광역시	2.0	3.100000e+08
...	...	...	...	...	...	...	...
376	전세보증금반환보증	2021.0	6.0	연립주택	인천광역시	70.0	1.249020e+10
377	전세보증금반환보증	2021.0	6.0	다세대주택	인천광역시	700.0	8.296775e+10
378	전세보증금반환보증	2021.0	6.0	단독주택	인천광역시	25.0	4.219000e+09
379	전세보증금반환보증	2021.0	6.0	다가구주택	인천광역시	47.0	6.775000e+09
380	전세보증금반환보증	2021.0	6.0	다중주택	인천광역시	1.0	3.000000e+07
381 rows × 7 columns

예를 들면, 위와 같이 특정 엑셀 파일을 읽어 DataFrame 형태로 바꾸고자 할 때는 읽고자 하는 cell 최상단 좌측의 cell을 선택하고 (위 예시는 A1이 되겠다.), options에 필요한 정보를 입력해주어 DataFrame으로 변환이 가능하다.

DataFrame -> Sheet

new_wb = wx.Book()
new_ws = new_wb.sheets["Sheet1"]
new_ws.activate()

new_ws.range("A1").value = df_from_xlwings

DataFrame에서 엑셀 sheet로 전환하는 방법은, sheet의 특정 cell의 value property에 DataFrame을 그대로 저장하면 쉽게 전환이 된다.