openpyxl로 엑셀파일 다루기

엑셀 데이터를 관리하거나 json 등의 다른 타입으로 변경할 때 openpyxl을 사용합니다.

pip을 이용해 openpyxl을 설치해 봅시다.

1
pip3 install openpyxl
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

학습을 위해 적당한 엑셀 파일을 다운받아 봅시다.

e-나라지표의 자치단체 행정구역 및 인구현황 데이터를 다운받아 봅시다.

아래 링크로 가서 엑셀 파일을 다운받아 봅시다.

http://www.index.go.kr/potal/main/EachDtlPageDetail.do?idx_cd=1041

* 참고: openpyxl은 Excel 2010 이상의 xlsx, xlsm, xltx, xltm 파일을 처리할 수 있습니다.

따라서 다운받은 파일이 xls라면 xlsx로 변환해 줘야 합니다.

openpyxl에서는 엑셀 파일을 book이라고 합니다. book하나에는 여러개의 sheet가 있습니다. 각 sheet에는 row와 column을 가진 2차원 cell로 구성됩니다.

sublime등 에디터를 열고 아래와 같이 작성해 줍니다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
# -*- coding:utf-8 -*-
import openpyxl
# 엑셀파일 열기
filename = "stats_104102.xlsx"
book = openpyxl.load_workbook(filename)
# 맨 앞의 시트 추출하기
sheet = book.worksheets[0]
# 시트의 각 행을 순서대로 추출하기
data = []
for row in sheet.rows:
    data.append([row[0].value, row[9].value])
# 필요없는 줄(헤더, 연도, 계) 제거하기
del data[0]
del data[1]
del data[2]
# 데이터를 인구 순서로 정렬합니다.
data = sorted(data, key=lambda x:x[1])
# 하위 5위를 출력합니다.
for i, a in enumerate(data):
    if (i >= 5): break
    print(i+1, a[0], int(a[1]))
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

실행하면 아래와 같이 출력된다.

1
2
3
4
5
1 세종 210
2 제주 624
3 울산 1173
4 광주 1472
5 대전 1518
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

이번엔 엑셀 파일 쓰기를 해봅시다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
# -*- coding:utf-8 -*-
import openpyxl
# 엑셀파일 열기
filename = "stats_104102.xlsx"
book = openpyxl.load_workbook(filename)
# 활성화된 시트 추출하기
sheet = book.active
# 서울을 제외한 인구를 구해서 쓰기
for i in range(0, 9):
    total = int(sheet[str(chr(i + 66)) + "3"].value)
    seoul = int(sheet[str(chr(i + 66)) + "4"].value)
    output = total - seoul
    print("서울 제외 인구 = ", output)
    # 쓰기
    sheet[str(chr(i + 66)) + "21"] = output
    cell = sheet[str(chr(i + 66)) + "21"]
    # 폰트와 색상 변경해보기
    cell.font = openpyxl.styles.Font(size=14, color="FF0000")
    cell.number_format = cell.number_format
# 엑셀 파일 저장하기
filename = "population.xlsx"
book.save(filename)
print("OK")
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

활성화된 sheet는 row와 column으로 구성된엑셀의 Cell을 표시합니다.

따라서 i가 0일때 sheet[str(chr(i + 66)) + "3"]은 B3 Cell을 의미하며, int(sheet[str(chr(i + 66)) + "3"].value)는 숫자형의 B3 Cell의 값을 나타냅니다.

chr(65)는 대문자 A, chr(66)은 대분자 B 입니다. chr(90)은 대문자 Z 입니다.

소문자 a부터 z까지는 chr(97)부터 chr(122)까지 입니다.

각 Cell의 값을 불러와 처리할 수 있고 임의의 Cell에 값을 넣고 저장할 수 있습니다.

위 코드를 실행하면 아래와 같이 엑셀 파일이 저장됩니다.

이상 openpyxl의 기본 사용 설명이었습니다.

상세한 기능 설명은 아래 링크에서 확인하세요.

https://openpyxl.readthedocs.io/en/stable/