OpenPyXL

OpenPyXL 공식 문서

파이썬에서 엑셀 다루기 위한 패키지

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

OpenPyXL 설치

pip install openpyxl

워크북 생성

• 워크북은 엑셀 파일 하나를 의미
  • 워크북 안에는 워크시트들이 있음
  • 워크북 안에 워크시트, 그 안에 셀이 있음

새 워크북 생성

import openpyxl

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

기존 워크북 불러오기

from openpyxl import load_workbook

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

• openpyxl의 load_workbook 메서드를 이용해 엑셀 파일을 읽음

def get_excel_data(self, file):
    load_wb = load_workbook(file, data_only=True)
    load_st = load_wb["sheet_name"]
    
    all_values = []
    for row in load_st.rows:
        row_value = []
        for cell in row:
            row_value.append(cell.value)
        all_values.append(row_value)
        
    return all_values

• "sheet_name"은 엑셀의 시트 이름, row별로 for문이 돌아가고 한 row당 존재하는 cell 단위로 for문이 돌아가서 cell.value를 하면 해당 위치의 값이 출력됨
• 출력된 값을 적절한 data_structure로 변환하면 됨
  • 위의 경우에는 2차원 list로 출력 되도록 만들었음

Tip! Http 통신을 통해 Excel File 전달받는 방법

• request.FILES.getlist("key_name")은 여러개의 파일이 올 경우 받는 방법으로 list로 파일이 나열됨
• 한 개의 파일만 받는 경우 request.FILES에 key값으로 excel 파일을 전달
  • 예시 : file = request.FILES["file"]

Tip! Postman으로 파일 전송 테스트하기

워크시트 생성

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()

Tip! 추가 내용

여러개 셀에서 값 취득하기

• 셀에서 값을 취득하기 위해서 행 번호와 열 번호를 지정해 해당 셀에 접근하여 값을 취득할 수 있음

열 기준 모든 값 가져오기

import openpyxl

wb =openpyxl.load_workbook('test.xlsx')
sheet = wb.get_sheet_by_name('Sheet1')

for cell_obj in list(sheet.columns)[1]:
    print(cell_obj.value)

행 기준 모든 값 가져오기

import openpyxl

wb =openpyxl.load_workbook('test.xlsx')
sheet = wb.get_sheet_by_name('Sheet1')

for cell_obj in list(sheet.rows)[1]:
    print(cell_obj.value)