openpyxl로 엑셀 데이터 다루기

 

 

Openpyxl

공식 문서 : https://openpyxl.readthedocs.io/en/stable/index.html

openpyxl - A Python library to read/write Excel 2010 xlsx/xlsm files — openpyxl 3.1.2 documentation

openpyxl은 파이썬으로 엑셀 형식의 데이터를 다루는 것을 지원해주는 라이브러리 이다. 해당 라이브러리를 이용한다면 파이썬 코드만으로도 엑셀의 데이터를 조작하여 엑셀에 데이터를 넣거나, 혹은 데이터를 참조하여 파이썬 코드를 사용하여 데이터를 가공 할 수 있다. 

 

이번 게시글에는 몇 가지의 조작 방법만 다룰 예정이니 심화 공부나 원하는 기능이 해당 게시글에 없을 시 위의 공식문서 참고하여 해결하길 바란다. 

 

일단 Openpyxl 라이브러리를 사용하기 위해서는 pip tool을 이용한 설치가 필요하다. 

 

🖥️ Terminal

pip install openpyxl

 

 

 

 

데이터 쓰기

일단 openpyxl에서 제공하는 튜토리얼을 통해서 파이썬으로 엑셀 데이터를 쓰는 방법에 대해서 알아보도록 하자. 아래는 openpyxl에서 제공하는 튜토리얼 코드이다.

from openpyxl import Workbook

# 엑셀파일 쓰기
wb = Workbook()

# 이름이 있는 시트를 생성
ws = wb.create_sheet('생성시트')

# Sheet1에다 입력
ws = wb.active
ws['A1'] = '숫자'

# 행 단위로 추가
ws.append([1,2,3])

# 셀 단위로 추가
ws.cell(5, 5, '5행5열')

# 각 열을 지정하여 추가 
ws.append({'A': 1,'B': 2, 'C': 3, 'E': 4, "F": 5})

# 이미지 추가
from openpyxl.drawing.image import Image
img = Image(f'img/test.jpg')
ws.add_image(img, "G2") # G2에 사진을 첨부한다라는 의미이다. 

# 저장
wb.save("main.xlsx")

코드를 자세하게 보면 다음과 같은 정보를 얻을 수 있다. 

• wb = Workbook() 함수를 통해 워크북을 생성한다. 
• 만들어진 wb을 통해 시트를 생성할 수 있고, 시트를 활성화할 수 있다. 
• 활성화된 시트에 데이터를 넣으면 자동으로 행이 내려가면서 데이터가 추가된다. 

즉 이 순서를 단계적으로 설명하자면, 처음에는 Workbook()을 통해 워크북을 생성하고 시트를 활성화 한 다음,  데이터를 넣으면 알아서 행을 구분하여 데이터를 추가해 준다는 것이다. 

 

 

 

 

데이터 읽기

엑셀을 열어서 데이터를 읽을 수도 있다. 다음은 그 예시의 코드이다. 

from openpyxl import load_workbook


# data_only=True로 해줘야 수식이 아닌 값으로 받아온다. 
wb = load_workbook("output.xlsx", data_only=True)

# 시트 이름으로 불러오기 
ws = wb['Sheet1']

# 셀 주소로 값 출력
print(ws['B2'].value)

# 셀 좌표로 값 출력
print(ws.cell(3, 2).value)

# 지정한 셀의 값 출력
get_cells = ws['B3' : 'B6']
for row in get_cells:
    for cell in row:
         print(cell.value)

# 모든 행 단위로 출력
for row in ws.rows:
    print(row)

# 모든 열 단위로 출력
for column in ws.columns:
    print(column)
    
# 모든 행과 열 출력
all_values = []
    for row in ws.rows:
        row_value = []
        for cell in row:
            row_value.append(cell.value)
        all_values.append(row_value)
    print(all_values)

 

위의 코드를 활용하여 한번 코드를 작성해 보자. 예를 들어서 만약 아래와 같은 데이터가 엑셀에 저장되어 있다고 가정한다. 5 * 5 테이블에 여러 데이터들이 들어가 있는 엑셀이고 main.xlsx 파일에 저장되어 있다. 

이를 행으로 가져와서 데이터를 보고 싶다면 다음과 같이 코드를 작성한다. 그냥 rows에서 row로 꺼낼 경우에는 cell 객체가 나오기 때문에 우리가 원하는 데이터를 받기 위해서는 map() 함수를 이용하여 x.value 값을 붙여 데이터를 가져와야 한다. 

 

from openpyxl import Workbook, load_workbook

wb = load_workbook("main.xlsx")
ws = wb['Sheet']

for row in ws.rows:
    print(list(map(lambda x: x.value, row)))

# 출력
# ['숫자', None, None, None, None]
# [1, 2, 3, 4, None]
# [1, 2, 3, None, None]
# [None, None, None, None, None]
# [None, None, None, None, '5행5열']