내용 : 엑셀 파이썬 연동
참조 : https://wikidocs.net/135789 (사장님 몰래 하는 파이썬 업무자동화)
xlwings 공식 홈페이지 : https://www.xlwings.org/
0.시작하며
지난 강좌에서 pandas를 다루었다. dataframe은 excel과 유사하면서 database처럼 사용할 수 있는 장점이 있었다. pandas는 테이블 형태의 정형화된 데이터를 처리할 때 유리하다. 우리는 여전히 exce로 정형화되지 않은 계산들을 한다. 구조계산서를 엑셀로 만드는 경우가 많은데 엑셀을 워드+MathCAD처럼 쓰고 있는 것이 엔지니어링 업계의 현실이다. 그래서 업무자동화에서 Excel 사용은 빼놓을 수 없다. 그래서 이번 강좌에서는 python으로 excel을 직접 다루는 방법에 대해서 알아보고자 한다.
1. xlwings설치 확인
python으로 excel을 통제할 수 있는 여러가지 모듈이 있다. xlwings, openpyxl, xlsxwriter (xlsx), xlrd, xlwt(xls) 등이 그것이다. 이중에서 xlwings를 다루도록 하겠다. 다른 모듈들은 읽거나 쓰기만 가능하다. 하지만 xlwings는 엑셀에서 python으로 만든 사용자 함수를 사용할 수 있다. 대신 xlwings는 excel이 설치되어 있어야 한다. 다른 모듈들은 excel이 설치되어 있지 안아도 excel파일을 생성하고 읽을 수 있다. 용도에 따라 적당한 것을 고르면 된다.
anaconda를 설치했다면 xlwings도 같이 설치되어 있을 것이다. 확인해보기 위해서 anaconda prompt를 띄우고 pip show xlwings라고 입력해보자.
|
(base) C:\Users\ysj>pip show xlwings
Name: xlwings
Version: 0.24.9
Summary: Make Excel fly: Interact with Excel from Python and vice versa.
Home-page: https://www.xlwings.org
Author: Zoomer Analytics LLC
Author-email: felix.zumstein@zoomeranalytics.com
License: BSD 3-clause
Location: c:\programdata\anaconda3\lib\site-packages
Requires: pywin32
Required-by:
|
xlwings에 관한 정보들을 볼 수 있다. 버전은 0.24.9이고 홈페이지는 https://www.xlwings.org 그리고 설치되어 있는 디렉토리는 c:\programdata\anaconda3\lib\site-package라는 것을 알 수 있다.
만일 anaconda를 설치하지 않아 xlwings가 설치되어 있지 않다면 명령창에서 pip install xlwings라고 입력해서 xlwings를 설치하면 된다.
2. 엑셀파일 열기
일단 기존에 있는 엑셀파일을 여는 것부터 해보자. 지난번 강좌에서 썼던 중간고사점수.xlsx를 열어보겠다. xlwings 모듈을 xw라는 이름으로 import한다. 그리고 xw.Book(엑셀파일명)을 써주면 엑셀파일을 연다. 그리고 wb에 할당했다. wb는 workbook을 의미한다. 앞으로 이 워크북에 접근할 때는 wb를 사용하면된다. 실습은 주피터노트북을 이용해서 하겠다. d:\dev\xlwings 디렉토리에서 주피터 노트북을 실행하고 xw라는 이름으로 노트북 이름을 변경해서 저장했다. 중간고사점수.xlsx파일도 같은 디렉토리에 있다.
|
import xlwings as xw
wb = xw.Book('중간고사점수.xlsx')
|
이 셀을 실행하면 실행시키면 아무 일도 일어나지 않는다. 만일 중간고사점수.xlsx파일이 이미 열려 있었다면 연결이 되고, 엑셀파일이 열려있지 않았다면 해당 파일이 열린다. 해당 파일의 내용은 아래와 같다.
3. xlwings 여러가지 기능들
- sheet 접근하기
이제 워크북에 접근했으니 시트에 접근할 차례다. sheets(시트이름)으로 원하는 시트에 접근했다.
|
sheet=wb.sheets('sheet1')
|
이제 sheet.name이라고 하면 sheet의 이름을 얻을 수 있다.
|
sheet.name
'Sheet1'
|
- cell에 접근하기(value, formula)
셀의 내용에 접근하기 위해서 sheet[ ] 안에 주소를 문자열로 써주고 value속성을 써주면 된다.
|
sheet['B4'].value
'홍길동'
|
셀의 value 외에 formula속성도 있다. 값이 아닌 수식을 읽거나 쓰는 것이다. F4셀 주소에 =sum(C4:E4)라는 수식을 입력해보자. 엑셀에서 입력하는 것이 아니라 python 코드로 입력하는 것이다. 엑셀의 F4 셀에 우리가 원하는 수식이 입력된 것을 확인할 수 있다.
|
sheet['F4'].formula='=sum(C4:E4)'
|
수식이 입력된 F4 셀의 값을 알고 싶으면 value속성을 이용하면 된다.
|
sheet['F4'].value
250.0
|
- 셀에 리스트 넣기
파이썬의 list형식의 데이터를 엑셀의 셀에 넣을 수 있다. 첫번째 리스트는 과목명들로 이루어져 있고 두번째 리스트는 점수로 이루어진 리스트를 묶어서 큰 리스트로 만들어서 셀의 value로 할당하면 된다. 큰 리스트 안의 리스트는 행으로 보면 된다.
|
sheet['B10'].value = [['철근콘크리트', '상하수도', '수문학'], [10.0, 20.0, 30.0]]
|
- 엑셀 범위 값 파이썬으로 가져오기
이번에는 위에서 입력한 엑셀의 범위값을 파이썬의 데이터로 가져와보자. 원하는 구역을 설정하기 위해서는 range속성을 이용해야 한다.
|
sheet.range('B10','D11').value
[['철근콘크리트', '상하수도', '수문학'], [10.0, 20.0, 30.0]]
|
만일 셀 B10부터 D11이 연속되어 있다면 expand()를 이용할 수 있다. B10으로부터 확장하라는 뜻이다. 위의 결과와 동일하다.
|
sheet['B10'].expand().value
[['철근콘크리트', '상하수도', '수문학'], [10.0, 20.0, 30.0]]
|
expand 에는 옵션이 있다. ‘down’, ‘right’, ‘table’이다. ()만 쓰면 ‘table’이 적용된다. table을 ‘down’과 ‘right’가 같이 적용된 것으로 보면 된다. 엑셀에서 shift-Control키를 누르고 아래 방향 키나 오른쪽 방향 키를 누르면 선택영역이 확장되는 개념과 같다.
|
sheet['B10'].expand('right').value
['철근콘크리트', '상하수도', '수문학']
|
B10으로부터 오른쪽으로 확장되어 선택된 것을 알 수 있다.
확장된 주소를 알고 싶다면 address속성을 이용하면 된다. ‘:’로 구분된 주소를 얻을 수 있다.
|
sheet['B10'].expand().address
'$B$10:$D$11'
|
- 엑셀과 dataframe
전 강좌에서 pandas dataframe을 다룬 바 있다. dataframe의 값을 엑셀로 넣거나 엑셀의 값들을 dataframe으로 가져와서 작업하면 편할 것이다.
pandas를 pd라는 이름으로 import하고 토목시공학과 교량공학 과목명과 점수로 dataframe을 만들어 df라는 이름의 변수에 할당한다. index와 columns를 지정하지 않았기 때문에 0과 1의 index가 만들어졌다.
|
import pandas as pd
df=pd.DataFrame([['토목시공학','교량공학'],[80,90]])
df
|
이 데이터 프레임을 엑셀에다 넣어보자. 주소 B14에 넣으려면 아래와 같이 하면 된다. 단순하게 df를 range의 value에 할당했다.
|
sheet['B14'].value=df
|
index와 columns까지 포함되어 엑셀에 반영되었다. index와 columns를 빼고 순수한 값만 엑셀에 반영하기 위해서는 options를 이용한다. options(index=False)를 추가하면 index 없이 엑셀에 반영된다.
|
sheet['B18'].options(index=False).value=df
|
마찬가지로 header를 없애기 위해서는 header=False를 추가하면 된다. index와 header 없이 데이터만 엑셀에 반영된 것을 확인할 수 있다.
|
sheet['B23'].options(index=False,header=False).value=df
|
이번에는 엑셀의 내용을 데이터프레임을 가져와 보자. 맨 처음 데이터인 B3:E6의 데이터를 dataframe으로 가져온다고 해보자. index와 columns(header)가 있다는 것을 기억하자.
|
df1=sheet['B3'].options(pd.DataFrame, expand='table').value
df1
|
options의 pd.DataFrame은 dataframe형태로 변환하라는 뜻이고 expand=’table’은 B3셀로부터 오른쪽, 아래쪽으로 확장해서 선택하라는 뜻이다. index와 columns(header)가 제대로 인식되어 dataframe이 만들어진 것을 확인할 수 있다. dataframe 옵션 없이 같은 명령을 실행하면 리스트로 만들어지는 것을 확인할 수 있다.
|
df2=sheet['B3'].options(expand='table').value
df2
[['이름', '구조역학', '토질역학', '수리학'],
['홍길동', 84.0, 80.0, 86.0],
['정약용', 99.0, 95.0, 90.0],
['이몽룡', 80.0, 85.0, 89.0]]
|
- 셀에 음영 넣기
셀에 음영을 넣기 위해서 color를 속성을 이용한다. B3셀에서부터 오른쪽으로 확장해서 선택하고 color를 (255,100,100)으로 설정했다.
|
sheet['B3'].expand('right').color=(255,100,100)
|
- 셀 테두리 설정
셀에 테두리를 그리고 싶을 때는 Borders속성과 Weight속성을 이용할 수 있다.
|
sheet.range('B10:D11').api.Borders.Weight = 2
|
4. 맷음말
xlwings를 이용해서 파이썬의 값을 엑셀에 쓰고, 엑셀의 값을 파이썬으로 가져오고, 음영을 설정하거나 테두리를 설정하는 것을 해봤다. 이 외에도 열의 폭을 설정한다든가 행의 높이를 설정하는 등의 설정도 가능하고 그래프를 그리거나 선을 그리는 등의 작업도 파이썬으로 가능하다. 엑셀에서 하는 모든 작업들을 파이썬으로 할 수 있다고 보면 된다. 이번 시간에는 기본적인 것만 소개했다. 필요한 기능은 구글링으로 찾아보는 것을 추천한다. 모든 것을 다 알고 있을 필요는 없다. 중요한 개념만 알고 있으면 된다. 엑셀에서는 range개념이 중요하다. 다음 시간에는 엑셀 사용자함수를 파이썬으로 만드는 것을 알아보겠다.