[파이썬/업무자동화] openypyxl 통해 엑셀 조건부 서식 적용하기

※ OpenPyxl 관련 포스팅 모음

2021.03.19 - [코딩/업무자동화] - [파이썬/업무자동화] Openpyxl로 엑셀 함수 사용하기, 엑셀 수식 넣기

2021.08.21 - [코딩/업무자동화] - [파이썬/업무자동화] Openpyxl 통해 엑셀 함수 사용하기 2편

2021.08.30 - [코딩/업무자동화] - [파이썬/업무자동화] 엑셀에 입력 된 파일명 따라 이미지 삽입하기 - openpyxl

2021.10.04 - [코딩/업무자동화] - [파이썬/업무자동화] openpyxl.style 사용법 정리

2021.10.16 - [코딩/업무자동화] - [파이썬/업무자동화] openypyxl 통해 엑셀 조건부 서식 적용하기

2021.11.09 - [코딩/업무자동화] - [파이썬/업무자동화] openpyxl 시트 조작 방법 4가지

---

 

  엑셀에는 '조건부서식'이라는 기능이 있다. 조건부 서식은 말 그대로 특정 조건에 따라 서식을 다르게 적용한다는 뜻이다. 아래 test data를 보자.

 

파일명 : "조건부서식 test.xlsx"

 

  위 양식은 학교에서 흔하게 볼 수 있는 학급별 성적표이다. 평균이 70점 이상이면 합격, 불합격이다. 조건부서식을 적용하기 전에 합격/불합격을 표시할 수 있는 코드를 작성해보도록 하겠다.

 

 

1. 합격/불합격 표시하기

 

<코드>

import openpyxl as op

#test 엑셀 파일이 있는 경로 지정
path = r"C:\Users\Desktop\VS CODE\Project"

wb = op.load_workbook(path + "/" + "조건부서식 test.xlsx", data_only=True) #Workbook 객체 생성
ws = wb.active #Worksheet 객체 생성

#합/불 판정해주는 함수
def passfail():
    max_row = ws.max_row
    for row_index in range(2, max_row+1):
        average = ws.cell(row = row_index, column=5).value
        if average >= 70:
            ws.cell(row=row_index, column=6).value = "합격"
        else:
            ws.cell(row=row_index, column=6).value = "불합격"         

#실행부
if __name__ == "__main__":
    passfail()
    wb.save(r"C:\Users\Yongbeom Jeong\Desktop\VS CODE\Project\result.xlsx")

 

-. load_workbook 함수의 파라미터 중 data_only=True는 함수, 수식에 대해 계산 된 결과값을 가져오는 설정값이다. 만만약 False로 지정할 경우, 함수나 수식에 대한 문자열을 그대로 가져온다. 예를 들어,=AVERAGE("B2:D2")라는 함수가 적용되어 있는 셀을 읽어올 경우 False로 지정되어있으면 함수 문자열 =AVERAGE("B2:D2") 그대로 읽어온다. 

 

참고링크 : 2021.08.21 - [코딩/업무자동화] - [파이썬/업무자동화] Openpyxl 통해 엑셀 함수 사용하기 2편

 

-. 70점 이상일 경우 합/불 판단은 if ~ else 조건문을 활용하여 적용했다.

 

-. 아래 결과 파일을 보면 합격/불합격이 표시된 것을 확인할 수 있다.

 

<결과>

 

 

2. 조건부 서식 적용하기

 

  1번에서 합격/불합격을 먼저 다룬 이유는 openpyxl을 통해 조건부서식을 적용하는 것이 크게 다르지 않기 때문이다. 아래와 같이 코드를 적용하면 된다.

 

-. 각 조건에 따른 셀서식 format을 변수로 설정하기

-. if ~ else 등 조건문을 사용하여 값에 따라 다른 셀서식 format 적용하기

 

  위 결과 기준으로 합격일 경우 파란색 글씨, 불합격일 경우 빨간색을 설정해보도록 하자. 코드는하나 더 위 1번 코드에서 함수를 하나 더 만들 것이다. 함수명은 conditionFormat 이다.

 

<코드>

#조건부 서식 적용 함수
def conditionFormat():
    pass_format =  Font(size=12, name='굴림', color = '000000FF') #	000000FF은 파란색
    fail_format =  Font(size=12, name='굴림', color = '00FF0000') #00ff000은 빨간색 
    max_row  = ws.max_row
    for row_index in range(2, max_row+1):
        result_str = ws.cell(row = row_index, column=6).value
        if result_str == "합격":
            ws.cell(row=row_index, column=6).font = pass_format
        else:
            ws.cell(row=row_index, column=6).font = fail_format

 

<결과>

조건부 서식 적용

 

 

3. 전체 코드

 

import openpyxl as op
from openpyxl.styles.fonts import Font

path = r"C:\Users\Desktop\VS CODE\Project"

wb = op.load_workbook(path + "/" + "조건부서식 test.xlsx", data_only=True) #Workbook 객체 생성
ws = wb.active #Worksheet 객체 생성

#합/불 판정해주는 함수
def passfail():
    max_row = ws.max_row
    for row_index in range(2, max_row+1):
        average = ws.cell(row = row_index, column=5).value
        if average >= 70:
            ws.cell(row=row_index, column=6).value = "합격"
        else:
            ws.cell(row=row_index, column=6).value = "불합격" 

#조건부 서식 적용 함수
def conditionFormat():
    pass_format =  Font(size=12, name='굴림', color = '000000FF') #	000000FF은 파란색
    fail_format =  Font(size=12, name='굴림', color = '00FF0000') #00ff000은 빨간색 
    max_row  = ws.max_row
    for row_index in range(2, max_row+1):
        result_str = ws.cell(row = row_index, column=6).value
        if result_str == "합격":
            ws.cell(row=row_index, column=6).font = pass_format
        else:
            ws.cell(row=row_index, column=6).font = fail_format


#실행부
if __name__ == "__main__":
    passfail()
    conditionFormat()
    wb.save(r"result.xlsx")