[Openpyxl] Openpyxl을 이용한 엑셀 자동화

여러 자료들을 단순히 복사하여 붙여 넣는 방식으로 엑셀 보고서를 만드는 것은 시간이 많이 소요되는 단순 반복 작업이다. 이를 피하기 위해 엑셀 파일을 다루는데 최적화되어 있는 모듈인 openpyxl을 사용하여 서식이 있는 엑셀 보고서를 작성하는 방법을 학습하고자 한다.

Openpyxl 기초 문법

: 엑셀의 파일, 시트, 셀을 객체화하여 파이썬에서 엑셀 파일을 효율적으로 다룰 수 있게 하는 모듈

• 시트 구조와 파일 불러오기

: 하나의 엑셀 파일은 하나의 workbook 과 하나의 sheet로 구성됨
: 엑셀 파일은 load_workbook 함수 사용하여 불러오기 가능하며 입력으로 filename(파일 이름)을 받음
: 수식을 보존한 채 가져오기 위해서는 data only 키워드에 false 입력!
: 시트 이름을 key, 시트를 value로 갖는 사전 자료형인 workbook 객체
: 읽고자 하는 시트 이름을 [ ] 안에 넣어 해당 시트를 가져온다!

import openpyxl
wb = openpyxl.loadworkbook(filename = 'file.xlsx', data_only = True)
ws = wb['SheetName']

: 불러온 워크시트에 값이 적힌 범위 확인을 위해서는 dimension 함수 사용

[엑셀 파일 불러오기 실습]

import openpyxl
wb = openpyxl.load_workbook(filename = 'python_7-1.xlsx', data_only= False)
print('포함된 시트 목록:', wb.sheetnames)
ws = wb['Sheet1']
print(ws.dimensions)

#포함된 시트 목록: ['Sheet1', 'Sheet2']
#A1:C5

: .sheetnames 함수는 workbook 객체에서 적용되며 workbook에 포함된 모든 시트 확인시 사용

• 셀에 접근하기

: 셀의 서식 변경 or 값 변경을 위해서는 먼저 셀에 접근!해야 한다.
- 워크시트['B1']
- 워크시트.cell(row = 1, column = 2)
: 접근한 셀에 적힌 값을 확인하려면 .value 함수 사용
- print(cell.value)
: 한 행에 있는 모든 셀에 접근하기 위해서는 워크시트[행 번호]와 같이 입력! => 행에 있는 모든 셀로 구성된 튜플
: 한 열에 있는 모든 셀에 접근하기 위해서는 워크시트[열 번호]와 같이 입력! => 열에 있는 모든 셀로 구성된 튜플

[셀 접근하여 값 확인 실습]

import openpyxl
wb = openpyxl.load_workbook(filename = 'python_7-1.xlsx')
ws = wb['Sheet1']


print('B1 셀에 있는 값:', ws['B1'].vlaue)
print('1행 2열 값:', ws.cell(row = 1, column = 2).value)

print('C열에 있는 값(들)')
for cell in ws['C']:
    print(cell.value)

print('2번째 행에 있는 값(들)')
for cell in ws[2]:
    print(cell.value)


# B1 셀에 있는 값: B1
# 1행 2열 값: B1
# C열에 있는 값(들)
# C1
# C2
# C3
# C4
# C5
# 2번째 행에 있는 값(들)
# A2
# B2
# C2

• 새로운 workbook 만들기

: openpyxl을 이용해 엑셀 파일 만들 수 있음
: 먼저! workbook 객체 생성 .workbook()
: 새로 sheet 추가 위해서는 create_sheet 함수 사용하여 시트 추가하기 => 이 함수는 시트 이름인 title을 입력으로 받음!
: 정의한 시트에 대해 시트[셀 위치]=값 이라는 구조로 셀에 값 입력 가능!
: workbook 저장하기 위해서는 save 함수 사용, 이 함수는 파일명을 입력!

[엑셀에 리스트 저장하기 실습]

import openpyxl
wb = openpyxl.Workbook()
ws = wb.create_Sheet(title = '시트1')

for i in range (1,11): 
    ws['A'+str(i)] = i

wb.save(filename = '엑셀에_리스트_저장하기.xlsx')

엑셀 주요 기능 활용하기

: 함수, 셀 병합, 필터, 차트, 데이터 분석 등을 실현 가능
: merge_cells , unmerge_cells, auto_filter 함수

• merge_cells 와 unmerge_cells 함수

: 보고서를 위한 엑셀에는 셀이 병합되어 있는 경우가 많다! 셀 병합을 위해서는 merge_cells 함수, 병합하고자 하는 셀의 범위를 입력!
: 셀의 범위는 엑셀에서의 셀 이름을 사용해서 설정 가능
: 병합된 셀에 값을 입력하려면 병합 범위 맨 앞에 있는 셀에 값 입력하기
: 병합된 셀은 unmerged_cells 함수 사용하여 병합 해제 가능!

[병합된 셀이 있는 엑셀 파일 생성 실습]

import openpyxl
wb = openpyxl.Workbook()
ws = wb.create_sheet(title = '야구성적')
ws.merge_cells('A1:C1')
ws.merge_cells('D1:F1')
ws['A1'] = 'SK'
ws['D1'] = '두산'
columns = ['승', '패', '무'] *2
values = [84,53,1,83,55,0]
for i in range(6): 
    ws.cell(row = 2, column = i +1).value = columns[i]
    ws.cell(row = 3, column = i +1).value = values[i]
wb.save(filename = '병합된 셀이 있는 파일.xlsx')

: 파이썬은 숫자를 0부터 세지만, 엑셀은 1부터 세므로 i+1!

• 새로운 행과 열 추가 및 삭제하기

: 새로운 행과 열 추가 = insert_cols, insert_rows , 행 혹은 열의 인덱스를 입력 받음!
: 새로운 행과 열 삭제 = delete_cols, delete_rows, 행 혹은 열의 인덱스를 입력 받음!
: 엑셀의 인덱스 이므로 1부터 시작!

[기존 행 삭제 및 새로운 행 추가]

import openpyxl 
wb = openpyxl.load_workbook(filename = 'python_7-2.xlsx', data_only = True)
ws = wb['Sheet1']
print(ws['A4'].value)
ws.delete_rows(4)
print(ws['A4'].value)
ws.insert_rows(4)

ws['A4'] = 4
print(ws.['A4'].value)
wb.save(filename='python수정')

• 필터 부착하기

: 열에 필터 부착하고 정렬하는데 사용할 수 있는 함수 = auto_filter
: auto_filter의 ref 함수를 사용하여 범위 지정시 맨 위 행에 필터 표시가 나타남
: 전체 열을 필터로 지정시 ws.auto_filter.ref = 'A:B' 와 같이 행 번호 생략하면 됨!

[필터 부착하기 실습]

import openpyxl
wb = openpyxl.load_workbook(filename = 'python_7-3.xlsx', data_only = True)
ws = wb['Sheet1']
ws.auto_filter.ref = 'A1:B9'
wb.save(filename = 'python_7-3_수정.xlsx')

서식 변경하기

: styles 모듈 제공하여 서식 수정 가능 - 서식을 객체화하여 여러 셀에 반복적으로 적용할 수 있는 장점
: font, ,alignment, border,patternfill 함수 학습

• 폰트 설정하기와 정렬하기

: styles 모듈에는 font라는 함수가 있고, 폰트에 대한 다양한 옵션 설정하는 객체 생성 가능
: name, size, bold, italic, underline, color
: 셀 정렬을 위해서는 alignment 함수 사용!

import openpyxl
Data = [['지점', '판매량'],['지점1', 100],['지점2',200],['지점3',250]]

header_font_style = openpyxl.styles.Font(size = 14, bold = True, name = 'HY견고딕')
value_font_style = openpyxl.styles.Font(size = 11, bold = False, name = '맑은 고딕')
alignment_Style = openpyxl.styles.Alignment(horizontal = 'center')
wb = openpyxl.Workbook()
ws = wb.create_sheet('지점별_판매량')
for i in range(4):
    for j in range(2):
        ws.cell(row = i +1,column = j+1).value = Data[i][j]
        ws.cell(row = i +1,column = j+1).alignment = alignment_Style
        if i == 0:
            ws.cell(row = i+1, column = j+1).font = header_font_style
        else:
            ws.cell(row = i+1, column = j+1).font = value_font_style

wb.save(filename = '지점별_판매량_폰트설정.xlsx')

• 셀 디자인하기

: border 라는 함수를 사용하여 셀 테두리에 대한 다양한 옵션 설정하는 객체 생성 가능
: left, right, top, bottom, diagonal 입력! , 각 값에 대해서는 side(border_style, color) 와 같은 방식으로 테두리 두께 지정
: styles 모듈에는 PatternFill 함수가 있고, 이 함수를 사용해 셀 배경에 색상 지정 가능 - fgColor 를 입력으로!
: cell.fill = PatternFill(fgColor = 색상) 의 방식으로 테두리 설정 가능

[디자인이 있는 엑셀 파일 실습]

: 헤더의 폰트, 내용의 폰트, 셀 디자인 설정하는 객체 만들고 적용하기!

import openpyxl
from openpyxl.styles import *

header_font_style = Font(size = 14, bold = True, name = "HY 견고딕") 
value_font_style = Font(size = 11, bold = False, name = "맑은 고딕") 
alignment_style = Alignment(horizontal = 'center')

header_border_style = Border(
	left = Side(border_style= "thin", color='000000'),
	right = Side(border_style= "thin", color='000000'),
	top = Side(border_style= "thin", color='000000'),
	bottom = Side(border_style= "thick", color='000000')
)
                               
value_border_style = Border(
  	left = Side(border_style= "thin", color='000000'),
    right = Side(border_style= "thin", color='000000'),
    top = Side(border_style= "thin", color='000000'),
    bottom = Side(border_style= "thin", color='000000')
)

header_color_style = PatternFill("solid", fgColor="DDDDDD")

Data = [["지점", "판매량"], ["지점1", 100], ["지점2", 200], ["지점3", 250]]
wb = openpyxl.Workbook()
ws = wb.create_sheet("지점별_판매량")
for i in range(4):
    for j in range(2):
        ws.cell(row = i + 1, column = j + 1).value = Data[i][j]
        ws.cell(row = i + 1, column = j + 1).alignment = alignment_style
        if i == 0:
            ws.cell(row = i + 1, column = j + 1).font = header_font_style
            ws.cell(row = i + 1, column = j + 1).border = header_border_style
            ws.cell(row = i + 1, column = j + 1).fill = header_color_style
        else:
            ws.cell(row = i + 1, column = j + 1).font = value_font_style
            ws.cell(row = i + 1, column = j + 1).border = value_border_style

wb.save(filename = "지점별_판매량_디자인_완료.xlsx")

: 코드의 상단에서 스타일 지정하는 코드를 만들어 두면, 추후 스타일의 변경이 필요하더라도 해당 스타일을 일일이 변경하기 위해 코드를 뒤질 필요가 없이 상단의 스타일!부분만 변경하면 되어 유지보수 수월!