openpyxl 사용법 기초

by

이 문서는 openpyxl 전반에 관하여 간략하게 정리한다. 세부적인 자세한 사용은 이 페이지 곳곳에 있는 내부링크를 이용한다.

파이썬에서 엑셀을 다루기 위한 패키지들은 많이 있다. xlwt, OpenPyXL, XlsxWriter, PyExcelerate 등이 있는데 그 중 가장 많이 쓰이는 것이 XlsxWriter 와 openpyxl 이다. XlsxWriter 는 엑셀의 많은 기능을 지원하고 있음에도 불구하고 커다란 단점이 하나 있으니 바로 기존에 저장해둔 엑셀 파일을 불러들이지 못한다는 것(=file load 불가)이다. 한편 openpyxl 은 충실하게 엑셀 기능을 지원하면서도 이러한 단점이 없어서 많이 쓰이고 있다.

openpyxl 설치

pip install openpyxl

워크북 생성

워크북이라 함은 엑셀파일 하나를 의미한다고 보면 되겠다. 워크북 안에는 워크시트들이 있다.

워크북과 워크시트 설명 예시
위와 같은 엑셀 파일이 있다고 하면 워크북은 목차만들기.xlsx 파일 전체를 의미하고 워크시트는 이 워크북 안에 있는 아래 하단에 보이는 Sheet4 Sheet1 Sheet2… 등과 같은 시트를 의미한다. 워크북 안에 워크시트, 그 안에 셀이 있다고 정리하면 이해가 쉽다. 참고로 위 그림에서는 Sheet4 가 활성화 되어 있다.

새 워크북 생성

import openpyxl

wb = openpyxl.Workbook()
#워크북을 생성하면 그 안에 워크시트 1개가 자동으로 생성
ws = wb.active
# 활성화 된 워크시트를 가리킴

기존 워크북 불러오기

wb = openpyxl.load_workbook(filename='filename.xlsx')
#기존 엑셀 파일 불러오기
ws = wb.active
# 현재 활성화 되어 있는 시트를 가리킴 
ws = wb['Sheet4']
# 시트명 가리킴

워크시트 생성

wb.create_sheet('새시트이름',0)
# 시트명 = '새시트이름', 0번째(=맨 왼쪽을 의미)에 시트를 위치 시키겠다.

엑셀 데이터 사용법

가장 기본적인 셀에 값을 입력하고 값을 읽어오는 내용을 다루겠다. 여기서 중요한 점은 셀 자체를 가리키는 것과 셀의 내용을 읽어오는 함수가 다르기 때문에 이 부분을 햇갈리지 말아야 한다.

셀 접근법

셀 자체를 가리키는 방법과 셀 내용을 읽는 법을 보자

print(ws['A1']) # A1 셀 자체를 가리킴
print(ws['A1'].value)   # A1 셀의 내용을 확인

#또 다른 셀 접근방법
ws.cell(row = 5, column = 2) 
# 세로방향(row)로 5번째, 가로방향(column)으로 2번째 셀을 의미

# 첫째행 타이틀 적기 예제
# 제목 적기
sub = ['번호', '이름', '주소', '이메일']
for kwd, j in zip(sub, list(range(1, len(sub)+1))):
    ws.cell(row=1, column=j).value = kwd

여러 개의 셀 접근

print(ws['A']) 
# A열의 모든 셀을 가져옴

셀 값 얻기

print(ws['A1'].value)   # A1 셀의 내용을 확인

for cell in ws['A']:   # A열의 모든 셀을 확인
    print(cell.value)

셀 가운데 정렬

# 셀 A1 (1열1행) 의 데이터 수평수직 가운데 정렬
row = 1
column = 1
ws.cell(row=row, column=num).alignment = openpyxl.styles.Alignment(horizontal='center', vertical='center')

#D열 전부 가운데 정렬
for cell in range(len(ws['D'])): 
    ws['D'+str(cell+1)].alignment = openpyxl.styles.Alignment(horizontal='center', vertical='center')

#아래는 특정열 지정하여 (3열~15열) 6행 이하부터 전부 가운데 정렬
for num in [3,5,6,7,8,9,10,11,12,14,15]:
    for row in range(6, len(ws['O'])+1):
        ws.cell(row=row, column=num).alignment = openpyxl.styles.Alignment(horizontal='center', vertical='center')

행 삭제

#각 행마다 A열 값이 None 이면 해당 행을 삭제
#시트 전체 행 탐색 및 적용
tmp = 1 
for cell in tuple(ws.columns)[0]:
    if cell.value == None:
        ws.delete_rows(tmp)
        tmp -= 1
    tmp += 1

셀 배경색 변경

from openpyxl.styles import PatternFill

# 셀 1행 7칸 까지만 배경색 노랑색 변경
y_color = PatternFill(start_color='ffff99', end_color='ffff99', fill_type='solid')
for num in range(1, 7):
    ws.cell(1,num).fill = y_color

열 너비 변경

ws.column_dimensions['A'].width = 50 # A열
ws.column_dimensions['B'].width = 15 # B열
ws.column_dimensions['C'].width = 120 # C열
ws.column_dimensions['D'].width = 20
ws.column_dimensions['E'].width = 10
ws.column_dimensions['F'].width = 10

파일 저장

wb.save(filename='filename.xlsx')

닫기

wb.close()

기본코드

# 제목 적기
sub = ['제목1', '제목2', '제목3', '제목4']
for kwd, j in zip(sub, list(range(1, len(sub)+1))):
    ws.cell(row=1, column=j).value = kwd

# 셀너비
ws.column_dimensions['A'].width = 22 # A열
ws.column_dimensions['B'].width = 45 # B열
ws.column_dimensions['C'].width = 55 # C열
ws.column_dimensions['D'].width = 60

from openpyxl.styles import PatternFill

# 셀 1행 4칸 까지만 배경색 노랑색 변경
y_color = PatternFill(start_color='ffff99', end_color='ffff99', fill_type='solid')
for num in range(1, len(sub)+1):
    ws.cell(1,num).fill = y_color

셀에 이미지 삽입

from PIL import Image

img = Image.open(imgPath)
imgPath = imgPath.replace('.jpg', '.png')  # 줄이는 파일은 PNG 로 변환
if img.height > 72:
    transheight = 72
    transwidth = int((img.width * transheight) / img.height)

    img.resize((transwidth, transheight)).save(imgPath, 'PNG', save_all=True)

else:
    img.save(imgPath, 'PNG', save_all=True)

img = openpyxl.drawing.image.Image(imgPath)
img.anchor = 'C' + str(i)
ws.add_image(img)

import openpyxl

wb = openpyxl.Workbook()
ws = wb.worksheets[0]
img = openpyxl.drawing.image.Image('test.jpg')
img.anchor = 'A1'
ws.add_image(img)
wb.save('out.xlsx')

특수문자제거

from openpyxl.cell.cell import ILLEGAL_CHARACTERS_RE

text = ILLEGAL_CHARACTERS_RE.sub(r'',문자열)

필터넣기

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

ws.auto_filter.ref = "A1:B15"
ws.auto_filter.add_filter_column(0, ["Kiwi", "Apple", "Mango"])
ws.auto_filter.add_sort_condition("B2:B15")

wb.save("filtered.xlsx")

하이퍼링크 넣기

cell.value = 내용
cell.hyperlink = 주소
cell.style = "Hyperlink"

ex)
ws['A'+str(i)] = 파이썬독스
ws['A'+str(i)].hyperlink = "https://pythondocs.net"
ws['A'+str(i)].style = "Hyperlink"

2021.05.17. updated

2021.07.26. updated

2021.12.08. updated

2022.02.25. updated

2022.06.04. updated

2022.09.18. updated

10 thoughts on “openpyxl 사용법 기초”

  1. Pingback: 리스트 컴프리헨션(list comprehension)의 이해 - 뻥뚫리는 파이썬 코드 모음

  2. 코드를 짠 뒤 wb.save(results) 라고 친 다음 실행해 봤는데 해당 file 이나 directory가 존재하지 않다는데요, 혹시 액셀 값들을 하위 폴더에 있는 액셀 파일의 워크시트에서 불러온 다음, 해당 값들을 모아 하나의 워크북을 생성하는 것이 가능 할까요? 방법 전해주시면 감사하겠습니다. ㅠㅠ

    응답

    • 물론가능합니다. 하위폴더에 있는 엑셀 파일의 워크시트를 불러오고 새로운 워크북을 하나 생성한훈 불러온 엑셀파일의 워크시트를 복사해서 방금생성한 새워크북에 하나씩 시트를 복사해넣어가서 하나로 모을 수 있겠지요!

      wb.save(results) 라고 쳤는데 파일 디렉토리가 존재하지 않는다는 말은 results 변수가 엑셀파일을 의미하지 않아서 그렇습니다!

      응답

  4. 이 글에서 도움을 받아서 저도 답글을 남깁니다. wb.save(result) 대신 wb.save(‘result.xlsx’)로 하시면 저장될 겁니다.

    응답

  5. 업무를 하고 있는데 openpyxl 가지고 들어는 봤지만 다루는 것이 익숙지 않아서 하면서 공부를 하고 있는데요 엑셀을 업로드를 하면 엑셀데이터에 텍스트형이나 숫자형이 들어가있는데 문자형은 잘들어가있는데 정수형 데이터 들어가 있으면 경고창이 들어가면서 수동으로 변환을 주어야 합니다. 이부분에서 정수형은 정수값으로 받을수 있을까요??

    응답

  6. 안녕하세요 궁금한게 있는데요 openpyxl로 정렬을 하게되는데요 shrink_to_fit=True로 설정하면 칸에 맞게 폰트가 축고 되서 만약에 글자가 10자에서 20자이상 늘어나면 너무 작은글씨로 나와서 이상하게 되는데요 또 False값을 주게 되면 정렬이 안되고 이런 경우에는 어떤 속성으로 줘야 하나요??

    응답

Leave a Comment