Python openpyxl로 Excel 파일 읽고 쓰기

Python openpyxl로 Excel 파일 읽고 쓰기

 

앞서 Python pandas로 excel 파일 읽기/쓰기 에서 Python의 Pandas를 이용하여 Excel 파일을 읽고 쓰는 것은 간단히 해봤습니다. 언제나 그렇듯이 제 개인적인 사용범위에서 말씀드리면, 데이터에만 관심이 있다보니 pandas로 읽고 쓰는 정도로도 충분한데, 아주 가끔 엑셀 파일로 보고서 등을 작성할 때 아쉬울 때가 있습니다.

 

업무를 하다보면 아주 정형화된 보고서를 작성하는 일이 반복적으로 이루어질 때가 있습니다. 예를 들어, 학생의 성적표나 개인 건강검진표, 관리비 고지서, 견적서, 거래명세서 등과 같이 특정 셀에 값만 바뀔 뿐 전체적인 형태가 바뀌지 않을 만한 문서를 만들 때, openpyxl 패키지를 이용한 엑셀 수정이 유용할 수 있습니다.

 

openpyxl은 엑셀 파일을 읽는 것 뿐만 아니라 엑셀파일을 작성할때, 셀을 병합하고 폰트, 셀 등의 스타일을 변경하는 등의 많은 기능을 제공합니다. 여기서 제 개인적으로는 셀의 스타일을 변경하면서 아무것도 없는 상태에서 엑세파일을 생성하는 것보다는 미리 작성된 엑셀 파일을 최소로 수정하여 새로운 이름으로 파일을 저장하는 방법을 권해드리며, 간단한 예제를 보이도록 하겠습니다.

 

 

openpyxl로 excel 파일 읽기

 

위의 그림은 학생 20명의 각 과목의 성적, 평균, 등수 등이 기재되어 있는 엑셀파일입니다.

from openpyxl import load_workbook

wb = load_workbook(filename = "2학기중간고사통합.xlsx", data_only=True)

print(wb.sheetnames)

ws = wb[wb.sheetnames[0]]

print(ws)

• 우선 openpyxl에서 load_workbook을 가져옵니다.
• load_workbook함수에 xlsx 파일명 및 data_only=True 옵션을 주었습니다. data_only=True 옵션을 주는 이유는 이 옵션 없이 엑셀파일을 읽어오면, 셀값이 수식으로 되어 있는 경우 (예, "=sum(B4:I4)") 수식의 계산 결과 값이 아닌 수식을 가져오게 됩니다. 수식의 결과 값을 받고 싶다면 data_only=True 옵션을 주어야 합니다. load_workbook은 엑셀 파일을 읽어 workbook으로 리턴합니다.
• wb.sheetnames 를 출력하였는데, 이는 엑셀 파일 내의 시트들의 이름을 리스트로 가지고 있습니다.
• ws = wb[wb.sheetnames[0]] 워크북의 첫번째 worksheet를 ws에 지정하였습니다.

위의 코드의 출력 결과는 다음과 같습니다.

['2학기중간고사']
<Worksheet "2학기중간고사">

 

시트에서 'A' 컬럼에 있는 값들을 출력해보고, 'A'컬럼에 몇개의 로우(row)가 있는지 출력해보겠습니다.

for cell in ws['A']:
    print(cell.value)
    
print(len(ws['A']))
print(ws.cell(row = 3, column = 1).value)

2020년 2학기 중간고사 성적
이름
None
하윤
서윤
...
지호
준우
23
None

• worksheet에서 간단히 ['A']를 한것으로 'A'컬럼을 가져왔습니다. 그 안의 cell을 for루프로 돌리면서 각 cell의 값(cell.value)를 통해 값을 가져왔습니다.
• 출력된 값에서 이름 바로 밑에 None 값이 나왔습니다. 이는 처음 엑셀 파일 그림을 보면 아시겠지만 A2와 A3은 셀이 병합되어 있습니다. 병합된 셀의 경우 그 첫번째 셀(A2)에 값이 존재하고 나머지 셀은 값이 없는 구조입니다. 따라서 None 값이 나왔습니다.
• 이를 다시 확인한 것이 ws.cell(row = 3, column = 1).value 가 None 입니다. worksheet의 cell을 이용하여 각 row와 column 값을 넣어서 값을 가져올 수 있습니다. row나 column의 값은 1부터 시작합니다.
• A컬럼의 로우 개수는 len 함수로 간단히 알 수 있습니다.

 

위에 엑셀 파일을 보시면 4번재 row에서부터 실질적인 학생 이름과 성적, 등수 등이 있습니다. 이를 tmp라는 리스트에 담아 보도록 하겠습니다.

tmp = []
for i in range(4,ws.max_row+1):
    tmp.append([])
    for cell in ws[i]:
        tmp[-1].append(cell.value)
    
print(tmp)

[['하윤', 88, 56, 25, 73, 59, 16, 17, 47.714285714285715, 12, 20], 
['서윤', 13, 36, 76, 43, 59, 18, 53, 42.57142857142857, 18, 20], 
...
['지호', 60, 16, 30, 47, 76, 95, 95, 59.857142857142854, 3, 20], 
['준우', 53, 53, 36, 62, 12, 32, 41, 41.285714285714285, 19, 20]]

• worksheet[row_숫자] 만으로도 특정 row를 가지고 올 수 있습니다.
• worksheet.max_row는 row의 갯수를 알 수 있습니다. 첫 for루프에서 max_row에 +1을 한 것은 max_row 값까지 루프가 돌아야 하기 때문입니다.

 

 

 

openpyxl로 excel 파일 쓰기

 

앞서 잠깐 언급하였듯 openpyxl로 엑셀 파일을 작성할 때 스타일 편집 등이 다양하게 제공되고 있지만, 개인적으로 권하는 것은 이미 엑셀 서식을 만들어놓고 일부 데이터만 수정하는 것이 여러모로 편하긴 합니다.

 

위에서 엑셀파일을 openpyxl로 읽었으니, 개인별 성적표를 만들어보겠습니다. 우선 아래와 같이 개인 성적만 기재되어 있고, 간단한 차트 하나 그려진 서식을 만들어봤습니다.

앞서 20명의 학생이 있었으니, 20개의 성적표 파일("2학기중간고사_이름.xlsx")을 만들겠습니다. VBA를 잘하시는 분들이야 엑셀에서 모든 것을 해결하시겠지만, 저는 엑셀를 못하기에 파이썬을 합니다.

 

from openpyxl.styles import Font

ft_good = Font(color="32CD32")
ft_bad = Font(color="FF0000")

for person in tmp:
    wb2 = load_workbook(filename="2학기중간고사_개인.xlsx")

    ws2 = wb2[wb2.sheetnames[0]]

    for i in range(1,len(person)+1):
        ws2.cell(row = 4, column = i).value = person[i-1]
        if i > 1 and i < 9:
            if person[i-1] <= 50:
                ws2.cell(row = 4, column = i).font = ft_bad
            elif person[i-1] >= 90:
                ws2.cell(row = 4, column = i).font = ft_good

    wb2.save("2학기중간고사_"+str(person[0])+".xlsx")

• openpyxl.styles에서 Font를 가져왔습니다. 이는 성적 값에 따라 글씨 색을 변경하는 예제를 보이고자 합니다.
• 간단하게 Font(color="31CD32")로 폰트 색을 초록색과 빨간색을 두개(ft_good,ft_bad) 지정했습니다.
• 우선 서식 파일("2학기중간고사_개인xlsx")을 읽어와서 워크북, 워크시트로 작업을 하되, 저장을 덮어쓰는게 아니라 새로운 파일로 써서 저장을 합니다.
• 특정 셀의 값을 변경하는 것은 worksheet.cell(row=숫자, colum=숫자).value의 값에 대입하는 것으로도 간단히 수정 가능합니다.
• 성적이 50점 이하일때는 폰트를 ft_bad로, 90점 이상일 때에는 ft_good으로 설정하였습니다.

 

위의 코드를 실행했더니 서식파일을 기반으로 각 학생들의 성적표가 각각의 파일로 생성되었습니다.

 

이제 성적표의 점수에 따라 폰트 색깔이 바뀌었는지 확인해보면 아래와 같습니다.

 

반복적으로 정형화된 문서를 작성해야 할 때 시간을 내어 코드를 하나 만들어두면, 업무 자동화가 되어 시간을 상당히 단축시킬 수 있습니다.

 

openpyxl에 대한 예제는 다음 링크에 가면 더 많이 확인 가능합니다.

openpyxl.readthedocs.io/en/stable/tutorial.html