파이썬 openpyxl로 엑셀(Excel) 파일 읽기/쓰기

파이썬에는 엑셀(Excel) 파일을 다루기 위한 다양한 라이브러리가 존재합니다.

openpyxl은 가장 많이 쓰이는 엑셀 라이브러리 중 하나입니다.

파이썬에서 openpyxl을 활용해 엑셀 파일을 읽고 쓰는 법을 알아보겠습니다.

 

1. 새로운 엑셀 파일 생성

먼저 새로운 엑셀 파일을 생성하고 셀에 텍스트를 입력하는 방법입니다.

openpyxl을 사용하려면 먼저 패키지를 추가해야 합니다.

PyCarm을 기준으로 File > Settings 메뉴(Ctrl + Alt + S)로 이동합니다.

패키지 리스트

패키지 리스트의 우측에 보이는 '+' 버튼을 클릭해서 openpyxl을 추가합니다.

openpyxl

상단의 검색창을 이용해서 openpyxl을 찾고 Install Package를 눌러서 설치합니다.

엑셀 파일을 작성하고 간단한 내용을 입력하는 코드는 다음과 같습니다.

먼저 엑셀 파일 생성에 필요한 기본적인 데이터를 sample_items에 입력했습니다.

다음으로 엑셀 파일을 생성하고 데이터를 입력하기 위한 메서드를 작성했습니다.

아래 코드로 새로운 엑셀 파일을 생성할 수 있습니다.

wb = openpyxl.Workbook()

기본적으로 빈 시트가 생성되는데 기본 생성 시트들을 삭제하기 위해서 wb.remove()를 호출했습니다.

그리고 wb.create_sheet()를 호출해서 새로운 워크시트를 2020이라는 이름으로 생성했습니다.

이 메서드는 index를 받기 때문에 원하는 위치를 지정할 수 있습니다.

여러 개의 셀을 하나로 합치려면 ws.merge_cells('시작 셀:종료 셀') 형태로 입력하면 됩니다.

하나의 셀을 선택해서 변수에 저장하려면 []를 사용하면 됩니다.

Python의 딕셔너리 데이터 타입을 사용하는 것처럼 셀의 번호를 입력하면 됩니다.

아래 코드와 같이 선택된 셀의 폰트를 바꾸거나 배경색을 변경하는 것도 가능합니다.

    cell = ws['A1']
    cell.font = openpyxl.styles.Font(color='FF00FF', size=20)
    cell.fill = openpyxl.styles.fills.PatternFill(patternType='solid',
                                                  fgColor=openpyxl.styles.colors.Color(rgb='00FF00'))

엑셀 파일은 각 셀을 위해 다양한 표시 포맷을 지원합니다.

각 셀에 데이터를 입력하는 방법은 아래 두 가지 방법 중 하나를 사용하면 됩니다.

ws['A2'] = 'ID'
ws.cell(row=row, column=3, value=item[1])

ws.cell()을 호출할 때 row나 column의 인덱스는 1부터 시작합니다.

그래서 A1 셀의 내용을 변경하려면 row=1, column=1이 되어야 합니다.

openpyxl 역시 여러 가지 포맷을 지원하는데 전체적인 목록은 아래 링크에서 확인할 수 있습니다.

https://openpyxl.readthedocs.io/en/stable/_modules/openpyxl/styles/numbers.html

openpyxl.styles.numbers — openpyxl 3.0.4 documentation

BUILTIN_FORMATS 내부의 포맷은 아래와 같이 설정할 수 있습니다.

        cell.number_format = openpyxl.styles.numbers.builtin_format_code(3)  # '#,##0'

3번은 #,##0으로 3자리마다 쉼표를 찍어서 표시하는 방법입니다.

BUILTIN_FORMATS 외에도 추가로 정의된 포맷들이 있기 때문에 역시 사용 가능합니다.

        cell.number_format = openpyxl.styles.numbers.FORMAT_DATE_YYYYMMDD2  # 'yyyy-mm-dd'

원하는 포맷을 지정해서 사용하면 됩니다.

그리고 openpyxl은 엑셀 파일 작성 시 다양한 함수 역시 지원합니다.

셀에 함수를 작성해서 입력해주면 정상적으로 계산이 돼서 결과를 표시해주는 것을 확인할 수 있습니다.

SUM이나 COUNT 등의 다양한 함수를 사용할 수 있습니다.

마지막으로 wb.save(파일 경로)로 파일을 생성할 수 있습니다.

생성된 엑셀 파일

코드가 길지 않고 메서드 이름 등이 직관적이기 때문에 쉽게 이해가 가능할 것입니다.

간단한 코드로 쉽게 재고 목록 내용이 반영된 엑셀 파일이 생성되었습니다.

 

2. 엑셀 파일 읽기

생성된 파일을 읽는 것은 쓰는 것보다 간단합니다.

엑셀 파일을 읽는 코드는 다음과 같습니다.

이미 존재하는 엑셀 파일을 열려면 아래 코드를 실행하면 됩니다.

    wb = openpyxl.load_workbook(filename=filepath)

그리고 현재 워크시트를 가져오기 위해 다음과 같이 실행하면 됩니다.

    ws = wb.active

위에서 생성한 엑셀 파일은 2020이라는 워크시트 하나만 존재하기 때문에 바로 가져올 수 있습니다.

여러 개의 워크시트가 존재하는 경우 다음과 같이 이름으로 찾을 수 있습니다.

    ws = wb['2020']

셀이나 워크시트에 접근할 때 [] 연산자를 활용하면 쉽게 접근할 수 있습니다.

모든 워크시트의 이름은 wb.sheetnames를 통해 확인할 수 있습니다.

보통 엑셀 파일을 읽을 때는 각 행 단위로 읽습니다.

엑셀 행(Row)

아래와 같이 for문으로 모든 행을 순차적으로 접근할 수 있습니다.

    for row in ws.iter_rows(min_row=3, max_row=ws.max_row):

위에서 생성한 파일은 세 번째 라인부터 실제 데이터가 존재하기 때문에 min_row를 3으로 설정했습니다.

엑셀 파일을 기록할 때와 마찬가지로 인덱스는 1부터 시작합니다.

각 행을 다시 for 문을 적용하면 각 셀을 접근할 수 있습니다.

날짜 타입은 날짜만 입력해도 가져올 때는 '2020-07-25 00:00:00'과 같이 시간이 포함된 값을 받습니다.

그렇기 때문에 원하는 포맷으로 수동으로 고쳐줘야만 합니다.

그리고 openpyxl의 한 가지 단점은 함수가 함수문 그대로 표시가 되는 점입니다.

함수 표시 문제

출력의 끝 부분을 보면 실제 D3와 E3를 곱한 결과가 아닌 함수 표현식이 그대로 출력됩니다.

해당 함수의 로직을 알면 직접 D3와 E3를 곱해서 값을 가져오는 방식으로 값을 가져올 수 있습니다.

아니면 파일을 생성할 때 함수 대신 Python에서 계산하고 값을 직접 입력하는 방법을 고려해볼 수도 있습니다.

마지막으로 엑셀 함수를 지원하는 다른 라이브러리를 활용할 수도 있습니다.

이것으로 간단하게 엑셀 파일을 읽을 수 있게 되었습니다.

 

파이썬의 openpyxl은 편리하게 엑셀 파일을 생성하고 다룰 수 있게 해줍니다.

날짜 표기나 함수 미지원 등의 문제가 있지만 강력한 기능을 제공하는 패키지입니다.

파이썬에서 엑셀 파일을 다룰 때 openpyxl을 고려해보는 것을 추천합니다.