[팀 스파르타-업무자동화] 3주차 - openpyxl 라이브러리

💡엑셀 업무 자동화에 대해서 배워볼 거에요.

• 파이썬에서 엑셀 파일을 다루기 위한 라이브러리인 openpyxl을 소개하고,
  엑셀 자동화의 기초를 이해해 봅니다.
• 코드를 통해 엑셀 파일 내에 새로운 시트를 생성하고 다루는 방법을 익혀 봅니다.
• 마지막 프로젝트로 네이버에서 데이터를 수집해 해당 데이터를 엑셀로 넣어보는 작업까지 해볼 겁니다.

---

💡openpyxl 라이브러리를 다루어 보기전에 엑셀의 구성요소에 대해 알아 봅시다.

 

Workbook

• workbook은 엑셀 파일 자체를 나타내는 개념!
• 엑셀 파일을 여는 것은 책을 열어서 그 안의 페이지(시트)를 읽거나 편집하는 것과 유사합니다.
• 파이썬에서 엑셀 파일을 다루려면 먼저 이 "Workbook"을 열어야 하며,
  이렇게 열린 Workbook을 통해 파일 안의 데이터를 조작하거나 새로운 데이터를 추가할 수 있습니다.

WorkSheet (=Sheet)

• 엑셀 파일은 여러 개의 데이터 세트로 이루어진 책
• 각 데이터 시트는 하나의 작은 데이터 페이지와 같아요.
  이러한 데이터 시트를 엑셀에서는 "Worksheet" 또는 "sheet"라고 부릅니다.
• 엑셀 파일에는 이런 시트가 여러 개 들어갈 수 있으며, 파이썬을 사용하여 엑셀 파일의 특정 시트에 접근하여 데이터를 처리하려면 해당 시트를 선택하는 코드를 사용해야 합니다.

Cell

• 엑셀 시트에서 데이터를 넣거나 읽을 수 있는 작은 사각형 공간을 "Cell"
• 셀은 엑셀에서 데이터를 저장하고 편집하는 기본 단위입니다.
• 각 셀은 위치를 나타내느 행과 열 번호로 식별됩니다.
  • 행은 위에서 아래로 세로로 나열된 줄을 나타내며,
  • 열은 왼쪽에서 오른쪽으로 가로로 나열된 열을 나타냅니다.
  • ex) "A1"은 첫 번째 행과 첫 번째 열의 셀을 나타내며, "B3"은 두 번재 행과  세 번째의 셀을 나타냄
• 이러한 셀을 사용하여 엑셀 시트에서 데이터를 조작하고 참조할 수 있습니다.
  여러 개의 셀을 한꺼번에 선택하여 작업할 수도 있습니다.
  셀을 엑셀에서 데이터를 처리하는 기본 단위이며,
  파이썬 또는 다른 프로그래밍언어를 사용하여 엑셀 파일에서 데이터를 다룰 때 중요한 개념입니다.

---

excel 파일 만들기

from openpyxl import Workbook

# workbook (엑셀) 생성
wb = Workbook()

# 엑셀 파일 기본 Sheet
default_sheet = wb['Sheet']

# Sheet 수정
default_sheet.title = '자동화'

# Sheet 추가
ws = wb.create_sheet('2024.08.18')

# wb(엑셀)에 있는 ws(Sheet) 이름 출력
print("모든 시트 명:", wb.sheetnames)

# 파일명 설정
excel_file_path = "./Python_SpartaCodingClub/excel_openpyxl/my_test.xlsx"

# 엑셀 생성 > 파일명 설정 > 저장
wb.save(excel_file_path)

 

excel 파일 읽기

excel 예시 파일

https://s3.ap-northeast-2.amazonaws.com/materials.spartacodingclub.kr/automation_online/my_excel.xlsx

from openpyxl import load_workbook

save_path = './Python_SpartaCodingClub/excel_openpyxl/my_excel.xlsx'
wb = load_workbook(save_path, data_only=True)

ws = wb['Sheet1']

# row = 가로줄 한 줄 / iter_rows() 한줄 씩 가져오겠다.
for row in ws.iter_rows(min_row=1, max_row=6, min_col=1, max_col=4):
    for cell in row:
        print(cell.value, end=" \n")
print("모든 시트 명:", wb.sheetnames)

 

excel style 변경 코드 (예시)

from openpyxl import load_workbook
from openpyxl.styles import Font, Side, Alignment, PatternFill, Border

save_path = 'my_excel.xlsx'

wb = load_workbook(save_path)

ws = wb['Sheet1']

ws.column_dimensions['B'].width = 20

ws.row_dimensions[1].height = 30

font_style = Font(size=14, italic=True, color="4F81BD", bold=True, name="Calibri")
fill_style = PatternFill(start_color="F0F8FF", end_color="F0F8FF", fill_type="solid")

border_style = Border(
    left=Side(color="4169E1", border_style="double"),
    right=Side(color="4169E1", border_style="dotted"),
    top=Side(color="4169E1", border_style="mediumDashed"),
    bottom=Side(color="4169E1", border_style="thin")
)

alignment_style = Alignment(horizontal="center", vertical="center")

for col in range(1, 5):  
    ws.cell(row=1, column=col).border = border_style
    ws.cell(row=1, column=col).font = font_style
    ws.cell(row=1, column=col).fill = fill_style
    ws.cell(row=1, column=col).alignment = alignment_style

wb.save(save_path)
print("완성!")

 

✅ 셀 배경 채우기 (PatternFill > fill_type) 종류 알아보기

1. solid: 전체 채우기, 단색으로 셀을 채웁니다.
2. darkGrid: 어두운 그리드 패턴.
3. darkHorizontal: 어두운 수평선 패턴.
4. darkTrellis: 어두운 트렐리스(사선 격자) 패턴.
5. darkUp: 어두운 사선 위로 패턴.
6. darkVertical: 어두운 수직선 패턴.
7. gray0625: 회색(6.25%) 채우기.
8. gray125: 회색(12.5%) 채우기.
9. lightGrid: 밝은 그리드 패턴.
10. lightHorizontal: 밝은 수평선 패턴.
11. lightTrellis: 밝은 트렐리스 패턴.
12. lightUp: 밝은 사선 위로 패턴.
13. lightVertical: 밝은 수직선 패턴.
14. mediumGray: 중간 회색 채우기.

excel 파일 합치기

스마트스토어 excel코드

https://s3.ap-northeast-2.amazonaws.com/materials.spartacodingclub.kr/automation_online/%EC%8A%A4%EB%A7%88%ED%8A%B8%EC%8A%A4%ED%86%A0%EC%96%B4.xlsx

# 파이썬 라이브러리를 사용해 엑셀 파일을 다룰 수 있도록 openpyxl 준비
# Workbook: 새로운 엑셀 파일 만들 때 사용
# load_workbook: 이미 있는 엑셀 파일 불러올 때 사용
from openpyxl import Workbook, load_workbook

# 새 엑셀 파일 만들기
wb_new = Workbook() # 엑셀 파일 생성
ws_new = wb_new.active # .active 하면 활성화된 하나 들고 올 수 있다. 새 파일 열면 sheet1 하나 있으니까~ shee1이 된다.
ws_new.title = '통합' # 활성화된 하나 sheet1을 통합으로 이름 변경

# 새 파일에 헤더(머리글) 추가
ws_new.append(['날짜', '번호', '이름', '가격'])

# 합칠 엑셀 파일 리스트
file_list = ['스마트스토어', 'my_excel']

# 각 파일에서 데이터 가져오기
for file in file_list:
    # data_only=True 엑셀 파일을 불러올 때, 수식이 아닌 계산된 값을 가져오도록 하는 옵션 / 사용하지 않으면 수식 자체를 가져오게 됨
    wb = load_workbook(f'./Python_SpartaCodingClub/excel_openpyxl/{file}.xlsx', data_only=True) 
    
    # 파일에서 기본시트를 선택해 (활성화된 시트 가져오기)
    ws = wb.active

    # 시트에서 첫 번째 줄부터 데이터를 한 줄씩 가져와
    # values_only=True 엑셀 파일에서 데이터를 가져올 때, 셀에 들어 있는 값만을 가져오도록 하는 옵션 (글꼴, 색상, 테두리, 서식등 안가져옴)
    for row in ws.iter_rows(min_row=1, values_only=True):
        # 만약에 줄에 데이터가 하나라도 있으면 (없으면 안가져옴)
        if any(row):
            # 그 줄을 새 엑셀 파일 시트에 추가해.
            ws_new.append(row)

# 새로운 엑셀 파일 저장
wb_new.save('./Python_SpartaCodingClub/통합.xlsx')

---

여러 개의 sheet로 excel 파일 분리하기

분리 엑셀 파일

https://s3.ap-northeast-2.amazonaws.com/materials.spartacodingclub.kr/automation_online/%EB%B6%84%EB%A6%AC.xlsx

from openpyxl import load_workbook, Workbook

# '분리.xlsx' 파일을 결고 '분리'sheet 가져오기
wb = load_workbook('./Python_SpartaCodingClub/excel_openpyxl/분리.xlsx')
ws = wb['분리']

# 제품 이름을 저장할 Dictionary 생성
product_data = {}

# 헤더 정보
headers = []
for cell in ws[1]: # ws[1] index 아님! 첫 번째 줄!!
    headers.append(cell.value)
# print(headers) # ['제품 이름', '가격', '재고량']

# '이름' 컬럼의 인덱스 찾기
name_colum_index = headers.index("제품 이름") # 0

# 데이터 읽어오기
for row in ws.iter_rows(min_row=2, values_only=True): # 헤더가 있으니까 min_row는 2부터
    product_name = row[name_colum_index] # row[0] > "제품1"

    # 제품 이름을 키로 딕셔너리에 추가 (ex. "제품1": ('제품1', 10, 100), ('제품1', 12, 80) )
    if product_name not in product_data: # 없다면
        product_data[product_name] = [headers]

    product_data[product_name].append(row)

# print(product_data)

wb.close()

# 결과물 엑셀 파일 생성
output_wb = Workbook()

# 딕셔너리 key, value를 (제품 이름, 값들)
for product_name, data in product_data.items():
    # 결과물 엑셀 파일에 "제품 이름"으로 시트를 생성하고
    ws = output_wb.create_sheet(product_name)

    # key(제품 이름)에 있는 value(data)를 꺼내서 "제품 이름" 시트에 넣어주자
    for row in data:
        ws.append(row)

# 결과물 엑셀 파일의 기본 시트 제거
output_wb.remove(output_wb.active)

# 결과물 엑셀 파일 저장
output_wb.save('./Python_SpartaCodingClub/excel_openpyxl/제품별 현황.xlsx')