openpyxl 파이썬에서 엑셀 업무 자동화 사용법

 

 

파이썬에서 엑셀 업무 자동화에 자주 쓰이는 openpyxl 라이브러리에 대해 알아봅시다. openpyxl은 엑셀 2010 xlsx, xlsm, xltx, xltm 파일 데이터를 읽거나 쓰고 제어할 수 있는 대표적인 Python 라이브러리입니다. 자세한 내용은 openpyxl 공식 홈페이지를 참고하시기 바랍니다. 

 

Openpyxl 공식 튜토리얼

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

 

Openpyxl 설치는 아래 명령어로 합니다.

 

$ pip install openpyxl

 

아래 샘플 코드를 VS code나 Pycharm에서 실행하면 sample.xlsx 파일이 생성되고 파일을 열면 아래 이미지와 같다.

 

from openpyxl import Workbook
wb = Workbook()

# grab the active worksheet
ws = wb.active

# Data can be assigned directly to cells
ws['A1'] = 42

# Rows can also be appended
ws.append([1, 2, 3])

# Python types will automatically be converted
import datetime
ws['A2'] = datetime.datetime.now()

# Save the file
wb.save("sample.xlsx")

 

생성된 엑셀 파일은 아래와 같다.

 

생성한 엑셀 파일

 

 

기본 사용법은 아래와 같다.

 

openpyxl.load_workbook('파일명')을 통하여 엑셀 문서를 열 수 있습니다.
이 때, open한 엑셀 파일을 객체로 받습니다.
ex) excelFile = openpyxl.load_workbook('example.xlsx')


현재 활성 중인 워크시트를 선택하는 방법은 다음과 같습니다.
sheet = wb.active


또는 sheet의 이름을 직접 입력하여 sheet를 선택할 수 있습니다.
sheet = wb["SheetName"]


get_sheet_names()를 이용하여 시트의 목록을 볼 수 있습니다.
excelFile.get_sheet_names()


get_sheet_by_name('시트명')으로 특정 시트를 불러올 수 있습니다.
이 때 open한 시트를 객체로 받습니다.
ex) sheet1 = excelFile.get_sheet_by_name('Sheet1')


get_active_sheet()로 활성화된 시트를 불러올 수도 있습니다.
ex) sheet2 = excelFile.get_active_sheet()


워크시트[‘열행’]으로 특정 셀을 불러올 수 있습니다.
참고로 엑셀에서는 열/행 순서의 좌표를 가지고 있습니다.(A1은 A열 1행)
ex) B1 = sheet[B1]


어떤 sheet의 이름을 변경하고 싶으면 다음과 같이 바꿀 수 있습니다.
sheet.title = new title


셀 객체를 접근하면 row/column 또는 좌표자체 그리고 셀에 저장된 값을 얻을 수 있습니다.
ex) B1.row, B1.column, B1.coordinate, B1.value


셀에 데이터를 입력하는 방법에 대하여 알아보겠습니다.
sheet.cell(row=row_index, column=column_index).value = 값
ex) sheet.cell(row=1, column=1).value = 10 : (1,1) 즉, A1에 10을 대입합니다.

 

엑셀 파일 새로 만들어서 저장하는 방법 

 

filepath = "/test.xlsx"
wb = openpyxl.Workbook()
wb.save(filepath)

 

sheet.iter_rows()와 sheet.iter_cols()를 이용하여 특정 범위의 셀에 접근할 수 있습니다. 

 

allList = []
for row in sheet.iter_rows(min_row=1, max_row=10, min_col=2, max_col=5):
    a = []
    for cell in row:
        a.append(cell.value)
    allList.append(a)

 

sheet.iter_rows()/iter_cols()를 선언하면 generator가 생성됩니다.

생성된 generator를 이용하여 row와 col의 min/max 범위에 맞게 접근합니다. 

 

엑셀 함수를 사용하다보면 열/행조합(ex. A1:A7)으로 범위를 접근하는 경우가 있습니다.
openpyxl에서도 다음과 같이 범위를 이용하여 셀에 접근할 수 있습니다.

특정 범위 접근
cell_range = sheet[‘A1’:’C2’]
특정 row 접근
row10 = sheet[10]
특정 row 범위
row_range = sheet[5:10]
특정 Column
colC = sheet[‘C’]
특정 Column 범위
col_range = sheet[‘C:D’]

 

엑셀의 행 또는 열을 추가하려면 다음과 같이 추가합니다. 함수의 인자로 입력된 숫자 앞에 행 또는 열이 추가됩니다. 

 

sheet.insert_cols(숫자)
sheet.insert_rows(숫자)

 

셀에 음영 색깔을 칠하려면 색 정보를 PatternFill을 통해 생성한 다음에 셀에 fill 해주면 됩니다. 

 

from openpyxl.styles import PatternFill

# 음영 색 지정
yellowFill = PatternFill(start_color='FFFFFF00',
                   end_color='FFFFFF00',
                   fill_type='solid')

# 지정된 음영 색으로 음역 색칠하기
sheet["A1"].fill = yellowFill

 

새로운 sheet를 추가하려면 workbook 객체에서 .create_sheet(title=None, index=None) 함수를 사용하면 됩니다. wb.create_sheet(title=None, index=None)와 같은 형태이며 예를 들어 wb.create_sheet(index = 1 , title = sheetname)와 같이 사용할 수 있습니다. 두 인자 모두 선택적으로 사용가능하며 모두 입력하지 않으면 기본값으로 입력됩니다. 

 

 

openpyxl을 이용하여 python으로 엑셀 다루기

 

 

pandas to openpyxl 

 

파이썬으로 structured data를 다룰 때 가장 많이 사용하는 자료형이 pandas 입니다. pandas를 사용하면 굉장히 편리하게 structured data를 다룰 수 있기 때문입니다. 따라서 openpyxl에서 모든 작업을 다 처리하는 것보다 pandas에서 작업을 하고 마지막에 openpyxl을 이용하여 xlsx 포맷으로 변경하는 것이 편리합니다.

 

따라서 아래에서 pandas를 openpyxl로 변경하는 방법을 살펴보겠습니다. 먼저 가장 간단하게 변환할 수 있는 방법은 아래와 같습니다. active된 worksheet에 행 방향으로 데이터가 쌓입니다.  

 

from openpyxl.utils.dataframe import dataframe_to_rows
wb = Workbook()
ws = wb.active

for r in dataframe_to_rows(df, index=True, header=True):
    ws.append(r)

 

만약 header와 index를 pandas 타입과 같이 강조하려면 다음과 같이 사용하시면 됩니다. 한번 출력해 보시면 내용을 이해하실 수 있습니다. 

 

wb = Workbook()
ws = wb.active

for r in dataframe_to_rows(df, index=True, header=True):
    ws.append(r)

for cell in ws['A'] + ws[1]:
    cell.style = 'Pandas'

wb.save("pandas_openpyxl.xlsx")

 

openpyxl to pandas 

 

이번에는 앞의 예제와 반대로 기존의 엑셀 파일을 pandas로 변경하는 방법에 대하여 살펴보겠습니다. 물론 엑셀 파일의 내용은 pandas로 변경이 가능한 내용이 저장되어 있어야 합니다.  

 

import openpyxl
from itertools import islice
from pandas import DataFrame

wb = openpyxl.load_workbook('example.xlsx')
ws = wb.active

# 모든 값을 DataFrame에 포함하고 index와 comlumn 이름은 0, 1, 2, ...의 숫자 사용
df = DataFrame(ws.values)

data = ws.values
cols = next(data)[1:]
data = list(data)
idx = [r[0] for r in data]
data = (islice(r, 1, None) for r in data)

# 첫 행이 index이고 첫 열이 column 명일 경우에 사용
df = DataFrame(data, index=idx, columns=cols)

 

비밀번호로 시트 보호 

 

엑셀 파일을 보호하고 수정을 방지하기 위하여 protection 기능을 사용할 수 있습니다. 엑셀 파일 중 보호하는 대상은 workbook과 worksheet 입니다.

 

workbook에 protection 기능을 적용하면 엑셀 시트 이외의 전체 구조에 대한 protection을 적용할 수 있습니다. 반면 worksheet에 protection 기능을 적용하면 워크시트에 입력된 값 및 서식들에 대하여 protection 기능을 적용할 수 있습니다.

 

아래 코드에서 ... 라고 되어 있는 패스워드 란을 실제 사용할 패스워드로 바꿔서 사용하면 됩니다. 

 

# workbook
wb.security.workbookPassword = '...'  #실제 패스워드 입력
wb.security.lockStructure = True


ws = wb.active
ws.protection.sheet = True
ws.protection.enable()
ws.protection.password = '...'     #실제 패스워드 입력
# ws.protection.disable()

 

열 너비 자동 맞춤 

 

엑셀에서 열 너비 자동 맞춤 기능은 일괄적으로 시트를 보기 좋게 만들기 위하여 종종 사용합니다.

 

아래 AutoFitColumnSize 함수를 사용하면 원하는 열 또는 모든 열에 대하여 열 너비를 자동 맞춤하는 기능을 적용할 수 있습니다. 이때, 적용되는 열의 너비는 열의 각 셀 중 가장 긴 문자를 포함하는 셀의 너비에 margin을 더한 값입니다. 1 이상의 margin을 가져야 빽빽하지 않게 자동 맞춤이 됩니다.

 

아래 함수에서 columns는 리스트를 받고 따로 None 또는 입력을 하지 않으면 전체 열을 대상으로 자동 맞춤을 적용하여 [1, 2, 3]과 같이 적용하면 1열, 2열, 3열만 자동 맞춤을 적용하게 됩니다. 

 

# culumns is passed by list and element of columns means column index in worksheet.
# if culumns = [1, 3, 4] then, 1st, 3th, 4th columns are applied autofit culumn.
# margin is additional space of autofit column. 

def AutoFitColumnSize(worksheet, columns=None, margin=2):
    for i, column_cells in enumerate(worksheet.columns):
        is_ok = False
        if columns == None:
            is_ok = True
        elif isinstance(columns, list) and i in columns:
            is_ok = True
            
        if is_ok:
            length = max(len(str(cell.value)) for cell in column_cells)
            worksheet.column_dimensions[column_cells[0].column_letter].width = length + margin

    return worksheet

 

참고

openpyxl을 이용하여 python으로 엑셀 다루기 

사장님 몰래 하는 파이썬 업무 자동화

 

 

참고: openPyxl 관련 포스팅

 

[파이썬/업무자동화] Openpyxl로 엑셀 함수 사용하기, 엑셀 수식 넣기

[파이썬/업무자동화] Openpyxl 통해 엑셀 함수 사용하기 2편

[파이썬/업무자동화] 엑셀에 입력 된 파일명 따라 이미지 삽입하기 - openpyxl

[파이썬/업무자동화] openpyxl.style 사용법 정리

[파이썬/업무자동화] openypyxl 통해 엑셀 조건부 서식 적용하기

[파이썬/업무자동화] openpyxl 시트 조작 방법 4가지

 

 

openpyxl을 이용하여 python으로 엑셀 다루기