#python #파이썬 #업무자동화 #openpyxl
엑셀은 많은 직장인의 필수 프로그램 중 하나다. python으로 엑셀 작업을 자동화할 수 있다면 반복적이고 많은 양의 작업도 빠르고 정확하게 처리할 수 있다. 데이터 분석가 입장에서도 현업 부서에서 엑셀 결과물이 필요한 경우 유용하게 활용할 수 있는 부분이다.
대표적인 라이브러리로는 openpyxl 패키지가 있는데, 이번 포스팅에서 우선 기본적인 기능에 대한 내용 - 엑셀 파일을 읽고, 데이터를 불러오거나 내보내는 - 을 정리해본다. 서식 지정 등의 추가적인 내용은 또 다른 포스팅에서 정리를 이어가는 것으로.
늘 그렇듯 가장 정확한 최신의 내용은 공식문서에서 확인할 수 있다. OpenPyXL 공식 document는 아래의 링크를 참고하자.
목차
1. 기본 이용 방법
1.1 openpyxl 패키지 설치 및 불러오기
•
패키지 설치
◦
터미널 창에서 아래의 명령어로 openpyxl 패키지를 설치할 수 있다.
$ pip install openpyxl
Shell
복사
◦
아나콘다 사용자의 경우 이미 설치되어 있으므로 다음 단계로 넘어가자.
•
패키지 불러오기
◦
이제 파이썬 스크립트 파일에서 아래와 같이 패키지를 불러온다. alias로는 op를 활용해보자.
import openpyxl as op
Python
복사
1.2 Workbook & Worksheet 개념
•
우리가 엑셀 프로그램으로 열어보는 하나의 엑셀 파일(.xlsx)은 하나의 workbook이며, 하나의 workbook을 구성하는 각 시트를 worksheet 라고 한다.
•
행과 열로 이루어진 셀(cell)들이 하나의 worksheet를 구성한다.
•
프로그래밍적으로 엑셀을 다루기 위해서는 workbook(파일)에 접근하고 → 그 중 원하는 worksheet(시트, 탭)에 접근하고 → 해당 worksheet에서 원하는 cell에 접근해서 특정 작업을 한다고 생각하면 된다.
1.3 Workbook과 엑셀 파일 다루기
•
workbook 생성하기
◦
통상 wb라는 이름으로 Workbook() 클래스의 인스턴스를 생성한다.
◦
처음 workbook을 생성하면 default로 Sheet가 하나 만들어진다.
from openpyxl import Workbook
# workbook 생성
wb = Workbook()
Python
복사
•
엑셀 파일에서 workbook 불러오기
wb = op.load_workbook(filename = "{file_name}.xlsx")
Python
복사
•
엑셀 파일로 저장하기
◦
생성하거나 수정한 workbook은 save 메서드로 엑셀 파일로 저장해야 반영이 되고, 엑셀 프로그램을 열어 확인할 수 있다.
wb.save("{filename}.xlsx")
Python
복사
1.4 Worksheet 다루기
1) 시트에 접근(선택)하기
•
첫 시트(활성화된 시트)에 접근하기
sh = wb.active
Python
복사
•
workbook의 worksheet 리스트 확인하기
wb.sheetnames
# wb.get_sheet_names() # deprecated
Python
복사
test_excel.xlsx 로 만든 파일의 기본 시트명인 Sheet를 확인할 수 있다.
•
특정 시트 접근하기
sh = wb["{sheet_name}"]
# sh = wb.get_sheet_by_name("{sheet_name}") # deprecated
Python
복사
2) 새로운 시트 만들기 & 삭제하기
•
새로운 worksheet를 추가하려면 create_sheet() 메서드를 사용한다. 시트의 위치는 기본적으로 맨 마지막에, 시트의 이름은 Sheet{n}으로 생성된다.
sh_new = wb.create_sheet()
Python
복사
•
시트의 이름이나 위치를 지정하여 생성할 수도 있다. worksheet의 위치 index는 0부터 시작한다
sh_new = wb.create_sheet("sheet_name", 1)
Python
복사
•
시트 삭제하기
wb.remove(sh_new)
Python
복사
sh_new로 생성했던 시트 ‘Sheet1’이 삭제되었다.
3) 시트의 속성 지정하기
•
시트 이름 지정하기
sh.title = "sheet_name"
Python
복사
•
시트 탭 색상 지정하기
sh.sheet_properties.tabColor = "{hex_code}"
Python
복사
시트의 이름과 탭 색상이 지정되었다.
1.4 Cell 다루기
1) Basic
•
셀 객체: 특정 셀을 객체로 받기. 아래의 두 가지 방법 중 하나로 인덱싱
a = sh.cell(row=1, column=2) # cell 속성 사용
a = sh["B1"] # 시트의 인덱스 사용
Python
복사
•
셀의 여러가지 속성
# cell 객체 a에 대해 다양한 속성을 확인
a.value # 저장된 데이터 값
a.row # 행 (숫자값, 1부터 시작)
a.column # 열 (숫자값, 1부터 시작)
a.coordinate # 좌표 (셀주소. ex: 'B1')
Python
복사
•
셀에 데이터 입력하기
# cell 객체 a의 value 속성에 10을 할당
a.value = 10
# cell의 데이터를 지우려면 ""(공백)을 입력
a.value = ""
Python
복사
B1 셀에 10을 입력했다.
2) 특정 범위의 셀에 접근
•
셀주소 사용
rng = sh["A1":"C3"] # 범위 저장 -> 2차원 튜플
for row in rng: # 튜플의 첫번째 차원: 행 (범위 내의 각 행)
for cell in row: # 튜플의 두번째 차원: 열 (행 내의 각 셀)
print(cell.value)
Python
복사
엑셀 파일에 스크린샷처럼 값을 입력하고 저장한 뒤 그 내용을 읽어왔다.
•
.rows, .colomns: 시트에서 데이터가 입력된 범위의 row, column을 반환하는 iterator
# 시트의 각 row의 셀 객체 튜플 출력
for row in sh.rows:
print(row)
Python
복사
(<Cell '첫시트'.A1>, <Cell '첫시트'.B1>, <Cell '첫시트'.C1>)
(<Cell '첫시트'.A2>, <Cell '첫시트'.B2>, <Cell '첫시트'.C2>)
(<Cell '첫시트'.A3>, <Cell '첫시트'.B3>, <Cell '첫시트'.C3>)
# 시트의 각 column의 셀 객체 튜플 출력
for col in sh.columns:
print(col)
Python
복사
(<Cell '첫시트'.A1>, <Cell '첫시트'.A2>, <Cell '첫시트'.A3>)
(<Cell '첫시트'.B1>, <Cell '첫시트'.B2>, <Cell '첫시트'.B3>)
(<Cell '첫시트'.C1>, <Cell '첫시트'.C2>, <Cell '첫시트'.C3>)
•
.max_row, .max_column: 시트에서 데이터가 입력된 최대 행, 열의 값을 정수로 반환
sh.max_row
sh.max_column
Python
복사
•
iter_rows()와 iter_cols()를 이용
◦
iter_rows(), iter_cols()를 선언하면 generator가 생성
◦
생성된 generator를 이용하여 row와 col의 범위(min - max)에 순서대로 접근
# 지정한 범위의 셀에서 셀 주소를 리스트로 받아오기
ls = []
for row in sh.iter_rows(min_row=1, max_row=10, min_col=2, max_col=5):
a = []
for cell in row:
a.append(cell.coordinate)
ls.append(a)
ls
Python
복사
>>>
[['B1', 'C1', 'D1', 'E1'],
['B2', 'C2', 'D2', 'E2'],
['B3', 'C3', 'D3', 'E3'],
['B4', 'C4', 'D4', 'E4'],
['B5', 'C5', 'D5', 'E5'],
['B6', 'C6', 'D6', 'E6'],
['B7', 'C7', 'D7', 'E7'],
['B8', 'C8', 'D8', 'E8'],
['B9', 'C9', 'D9', 'E9'],
['B10', 'C10', 'D10', 'E10']]
•
행 삭제, 열 삭제 (데이터 삭제가 아닌 행, 열 자체를 제거함)
sh.delete_rows(1, 2) # 1행부터 2개 행을 삭제
sh.delete_cols(2, 1) # 2열(B열)부터 1개 열을 삭제
Python
복사
2. DataFrame 
엑셀
데이터 관련 업무에서 python으로 엑셀을 잘 다루는데 있어 핵심은 결국 pandas의 dataframe으로 만들어진 테이블 형태의 데이터를 엑셀 파일로 잘 내보내거나, 반대로 엑셀에서 데이터를 읽어와서 dataframe으로 잘 받아오는 데에 있을 것이다.
openpyxl의 경우 구글 시트를 다루는 pygsheet 패키지보다는 dataframe과의 호환성은 좀 떨어지는 것으로 보인다. pygsheet의 경우 간편하게 dataframe을 영역을 지정해서 바로 내보내거나 가져오는 메서드가 있는 반면, openpyxl에서는 반복문 등을 이용해서 데이터를 내보내야 한다.
2.1 DataFrame → 엑셀
1) dataframe_to_rows 활용
•
모듈을 활용해 가장 간단한 방법이지만, 시트에서 입력할 셀의 영역을 지정할 수 없다.
시트상 마지막 row 다음부터 행 방향으로 dataframe이 한 줄씩 append된다.
from openpyxl.utils.dataframe import dataframe_to_rows
for r in dataframe_to_rows(df, index=True, header=True):
sh.append(r)
Python
복사
index와 header를 True로 넣어주어서 컬럼명과 인덱스명이 함께 입력되었다.
2) 데이터를 입력할 셀 영역을 지정할 수 있는 방법
•
iter_rows() 이용
min_row = 10 # 데이터 입력을 시작할 row index
max_row = min_row + len(df) - 1
min_col = 3 # 데이터 입력을 시작할 column index
max_col = min_col + len(df.columns.tolist()) - 1
i = 0
for row in sh.iter_rows(min_row=min_row, max_row=max_row, min_col=min_col, max_col=max_col):
j = 0
for cell in row:
cell.value = df.iloc[i, j]
j += 1
i += 1
Python
복사
C10부터 시작하는 4*3 데이터가 입력되었다.
•
행, 열 index를 for문에 직접 활용
◦
시트와 DataFrame의 행, 열 index를 활용하면 이중 for문을 사용하지 않아도 된다.
idx = [(i, j) for i in range(len(df)) for j in range(len(df.columns.tolist()))]
min_row = 14 # 데이터 입력을 시작할 row index
min_col = 2 # 데이터 입력을 시작할 column index
for (i, j) in idx:
cell = sh.cell(row=i+min_row, column=j+min_col)
# 셀 데이터 입력
cell.value = df.iloc[i, j]
Python
복사
B14부터 데이터가 입력되었다.
2.2 엑셀 → DataFrame
1) 전체 데이터를 dataframe으로 받아오기
# 워크시트의 전체 데이터를 dataframe으로 받아온다
df = pd.DataFrame(sh.values)
Python
복사
# 데이터에 header가 포함된 경우, 첫줄을 header로 지정할 수 있다.
df = df.rename(columns=df.iloc[0])
df = df.iloc[1:].reset_index(drop=True)
Python
복사
2) 특정 영역의 데이터를 dataframe으로 받아오기
이제 엑셀에 영역을 지정해 아래와 같이 내보냈던 데이터를 거꾸로 받아와보자.
•
iter_rows() 이용
min_row = 10 # 데이터를 가져올 시작 위치 row index
rows = 3 # 가져올 행 수
max_row = min_row + rows - 1
min_col = 3 # 데이터를 가져올 시작 위치 column index
cols = 4 # 가져올 열 수
max_col = min_col + cols - 1
df = pd.DataFrame() # 빈 DataFrame 생성
i = 0
for row in sh.iter_rows(min_row=min_row, max_row=max_row, min_col=min_col, max_col=max_col):
j = 0
for cell in row:
df.loc[i, j] = cell.value # 셀에 데이터 입력
j += 1
i += 1
Python
복사
가져온 결과 데이터
•
행, 열 index를 for문에 직접 활용
◦
시트와 DataFrame의 행, 열 index를 활용하면 이중 for문을 사용하지 않아도 된다.
min_row = 14 # 데이터를 가져올 시작 위치 row index
rows = 3 # 가져올 행 수
min_col = 2 # 데이터를 가져올 시작 위치 column index
cols = 4 # 가져올 열 수
idx = [(i, j) for i in range(rows) for j in range(cols)]
df = pd.DataFrame() # 빈 DataFrame 생성
for (i, j) in idx:
# 셀 접근
cell = sh.cell(row=i+min_row, column=j+min_col)
# 셀 데이터 df로 가져오기
df.loc[i, j] = cell.value
Python
복사
가져온 결과 데이터
이상, python으로 엑셀 파일을 다루는 기본적인 방법에 대해 알아보았다.
위에서 얘기한 것처럼 엑셀 서식 지정 등 추가적인 내용은 추후 또 다른 포스팅으로 정리를 해보기로 하자.