기타

openpyxl 사용법 정리

kkam99 2023. 4. 27. 20:43
반응형

프로젝트를 하면서 다량의 엑셀 파일을 만들 일이 있어 편하게 만들 수 있는 방법이 없을까 고민하다가 openpyxl 이라는 라이브러리를 알게 되었다.

데이터 분석을 하면서 의사소통을 위해서 엑셀이나 파워포인트를 활용하는 경우가 많은데 이런 보고용 자료를 만드는 일이 시간을 많이 잡아먹는다. 같은 포맷에 데이터만 바꿔야 하는 경우라면 이런 자동화 라이브러리를 적극 활용하자!

공식 레퍼런스: https://openpyxl.readthedocs.io/en/stable/#

기본 사용법

from openpyxl import Workbook

wb = Workbook()
sheet=wb.active
sheet.title='test'

wb.save('test.xlsx')

기본적인 사용법은 위 코드와 같다.

Workbook()으로 생성된 객체는 하나의 엑셀 파일을 만들고 그 아래 sheet를 만들 수 있다.

sheet의 이름은 sheet.title 로 설정이 가능하다.

(엑셀 파일을 처음 켜면 sheet1, sheet2, .. 이렇게 되어 있는 부분)

만약 여러 sheet를 만들고 싶다면 아래 코드를 적용하면 된다.

from openpyxl import Workbook

wb=Workbook()
sheet=wb.active
sheets=[sheet]
name = # 적용할 시트 이름들
for sheet_name in name:
	sheets.append(wb.create_sheet(title=sheet_name)

엑셀 파일을 만들고 첫 번째 시트를 만든 후에 그걸 배열 등에 저장하고 그 배열 뒤에다 새로운 시트들을 추가해 나가면 된다.

특정 조건에 따른 데이터 분석 결과 등을 조건에 따라 시트를 만든 후 엑셀 파일 하나로 작업하기 유용하다.

단순히 시트 한 개만 더 추가하고 싶으면 sheet2=wb.create_sheet() 로 생성하면 된다.

셀 접근은 sheet['A1'] 과 같은 형태로 가능하다. (기존 엑셀 함수식에 넣는 표현식과 동일)

데이터 프레임 엑셀로 변환하기

판다스 데이터프레임을 엑셀로 변환하는 방법은 라이브러리에서 제공하는 to_excel() 로도 가능하지만 엑셀로 ‘저장’만 가능하고 폰트변경이나 테두리 설정 등은 불가능하다.

결국 이런 작업을 위해선 저장한 엑셀 파일을 열어서 수작업으로 변경해줘야 하기 때문에 이런 부분에서 매우 유용하다.

특히 데이터프레임의 피벗테이블 함수는 엑셀의 피벗테이블과 똑같은 기능이면서 훨씬 많은 양의 데이터에 대해 피벗테이블을 만들 수 있어서 이걸 다시 엑셀로 변환할 때 매우 유용한 것 같다.

from openpyxl import Workbook

wb=Workbook()
sheet=wb.active

df = # 엑셀파일로 변환할 데이터 프레임
row_len=len(df.index)
col_len=len(df.columns)
start_row=5 # 데이터프레임의 시작 위치 (5행부터 데이터프레임 입력)

# 데이터프레임 인덱스 이름 입력
for row in range(row_len): 
	sheet.cell(column=1, row=start_row + row, value=df.index[row])

# 데이터프레임 컬럼 이름 입력
for col in range(col_len):
	sheet.cell(column=1+col, row=start_row, value=df.columns[col])

# 데이터프레임 값 입력
for row in range(row_len):
	for col in range(col_len):
		sheet.cell(column=1+col, row=start_row+row+1, value=df.iloc[row, col])

인덱스가 필요없거나 컬럼명이 필요없다면 값 입력 부분만 사용하면 된다.

이중 for문 형태라 데이터프레임이 크다면 속도 이슈가 있겠지만 속도 이슈가 있을만큼 큰 데이터프레임이라면 엑셀로 안 바꾸는게 나을 듯 하다.

💡
주의해야할 점은 셀의 처음 인덱스가 1이라는 점이다. 파이썬처럼 0으로 시작하면 오류가 난다.

폰트, 테두리, 정렬 설정

폰트, 테두리, 정렬 등의 설정은 styles 패키지 안의 모듈들을 불러와야 한다.

from openpyxl import Workbook
from openpyxl.styles import Font, Border, Side, Alignment

wb=Workbook()
sheet=wb.active

# 테두리 설정
border_type=Side(border_style='thin', color='FF000000')

# 폰트 설정
font_type=Font(nae='궁서체', bold=True, size=8, italic=False)
align=Alignment(horizontal='center', vertical='center')

# 폰트, 테두리 적용할 범위
border_cells=sheet['A1':'J10']

# 폰트, 테두리 그리기
for rows in border_cells:
	for cell in rows:
		cell.border=Border(top=border_type, bottom=border_type, 
											 left=border_type, right=border_type)
		cell.font=font_type
		cell.alignment=align

		# 숫자 표시 형식 지정
		cell.number_format='#,##0'

색상은 8자리 헥스코드로 지정이 가능하다.

헥스코드는 RGB값을 6자리 숫자로 표현한거고 뒤에 두 자리는 투명도값이라고 한다.

숫자 표시 형식 지정도 역시나 가능하다.

컬럼 위치 찾아가기

특정 컬럼만 볼드체로 표시하고 싶은데 컬럼이 어디에 위치해 있는지를 모른다면 아래와 같은 코드를 적용할 수 있다.

데이터프레임을 엑셀로 변환한다고 할 때, 특정 컬럼 위치를 외우고 있기는 힘들고 컬럼명으로 접근하는 것이 훨씬 편하므로 이런 방법이 더욱 유용할 거 같다.

from openpyxl import Workbook
from openpyxl.utils import get_column_letter

wb=Workbook()
sheet=wb.active

df = # 데이터 프레임
col_name = # 특정 컬럼명

letter=get_column_letter(df.columns.get_loc(col_name))

df.columns.get_loc(col_name) 은 col_name이 몇 번째인지를 가져오는 함수이고 이를 A,B,C,..와 같은 엑셀 컬럼 문자로 바꿔주는 코드이다.

셀은 숫자로도 접근이 가능하지만 border_cells=sheet['A1':'J10'] 와 같이 ‘컬럼문자행번호’ 형태로 접근할 일도 많기 때문에 매우 유용한 함수이다.

조건부 서식

수식을 사용하여 서식을 지정할 셀 결정 예시

from openpyxl import Workbook
from openpyxl.styles import PatternFill
from openpyxl.utils import get_column_letter
from openpyxl.formatting.rule import FormulaRule

wb=Workbook()
sheet=wb.active

# 특정 컬럼의 아래값이 위값 보다 크면 노란색으로 칠해주는 조건부 서식

col_name=# 원하는 컬럼명
start_row=5 # 기준 위치 (해당 컬럼의 제일 첫 값이 위치해 있는 곳)
final_row=10 # 해당 컬럼의 마지막 위치

letter=get_column_letter(df.columns.get_loc(col_name))
format_range=f'{letter}{start_row}:{letter}{final_row}'
rule=f'${letter}{start_row+1}>${letter}{start_row}'

sheet.conditinal_formatting.add(format_range, FormulaRule(formula=[rule],
																fill=PatternFill('solid', bgColor='00FFFF00')

수식의 동작은 엑셀을 켜서 꼭 확인해보자.

PatternFill 은 이름 그대로 여러가지 옵션으로 셀 채우기를 지원해준다.

마무리

이번 프로젝트에서는 데이터프레임으로 계산을 하고 결과파일만 엑셀로 만드는 형태라 주로 데이터프레임을 엑셀로 변환하는 작업만 했다.

하지만 다량의 엑셀 파일 (예를 들어 지점별 통계 등..) 을 하나의 집계파일로 만드는 데도 매우 유용하게 사용할 수 있을 것 같다.

엑셀파일을 열 때는 아래의 코드를 쓰면 된다고 한다.

from openpyxl import load_workbook
wb = load_workbook(filename = # 대상 엑셀 파일 이름)
sheet_ranges = wb['range names']
print(sheet_ranges['D18'].value)

파일을 여는 건 특정 조건일 때만 가능하다고 하니 이건 나중에 확인해봐야겠다.


Uploaded by N2T

반응형