파이썬으로 엑셀 업무자동화하기

인프런 강의 파이썬 무료 강의 (활용편4) - 업무자동화 (RPA) 중 파트1 엑셀 부분을 수강하고 정리해보았다.

파일 생성

# pip install openpyxl

from openpyxl import Workbook

# 엑셀 파일 생성
wb = Workbook() # 새 엑셀 워크북 생성
ws = wb.active # 현재 활성화된 sheet 가져옴

ws.title = "yesolSheet" # 시트 제목 변경

wb.save("sample.xlsx")
wb.close()

시트 생성 및 조작

# 시트
from openpyxl import Workbook
wb = Workbook()
ws = wb.create_sheet() # 새로운 sheet 기본이름으로 생성
ws.title = "mySheet"

ws.sheet_properties.tabColor = "ff66ff" # HEX컬러 6자리 형태로 입력하면 시트 탭 색상 변경

ws1 = wb.create_sheet("yourSheet") # 주어진 이름으로 시트 생성
ww2 = wb.create_sheet("newSheet", 2) # index 줘서 시트 순서 변경

new_ws = wb["newSheet"] # Dict 형태로 sheet에 접근 가능

#Sheet 복사
new_ws["A1"] = "Test"
target = wb.copy_worksheet(new_ws)
target.title = "copied sheet"

print(wb.sheetnames) # 모든 시트 이름 확인
wb.save("sample.xlsx")

셀에 값 입력

# 셀에 값 입력
ws["A1"] = 1
ws["A2"] = 2
ws["A3"] = 3

# 반복문을 이용해서 랜덤 숫자 채우기
ws.cell(row=1, column=1, value="번호")
ws.cell(row=1, column=2, value="랜덤 숫자")
for x in range(2, 12):
    # 첫 번째 열에는 번호
    ws.cell(row=x, column=1, value=x-1)
    # 두 번째 열에는 랜덤 숫자
    ws.cell(row=x, column=2, value=randint(0, 100)) # 0~100 사이 랜덤 숫자

# 입력 순서 확인
index = 1
for a in range(1, 11):
    for b in range(1, 11):
        ws.cell(row=a, column=b, value=index)
        index += 1

wb.save("sample.xlsx")

셀의 값 읽어오기

# 셀 값 읽어보기
print(ws["A1"]) # 셀 정보 출력
print(ws["A1"].value) # 셀의 값 출력. 값 없을 땐 'None' 출력

print(ws.cell(row=1, column=1).value) # == 'A1'셀과 동일

for i in range(1, 4):
    print(ws.cell(row=i, column=1).value)
    ws.cell(row=i, column=2).value = "test"
    # ws.cell(row=i, column=2, value = "test")

기존 파일 불러오기

# cell 데이터 불러와서 출력
for a in range(1, 11):
    for b in range(1, 11):
        print(ws.cell(row=a, column=b).value, end=" ")
    print()

# cell의 row, column 개수 모를 때
for x in range(1, ws.max_row+1):
    for y in range(1, ws.max_column+1):
        print(ws.cell(row=x, column=y).value, end=" ")
    print()

행, 열 단위로 가져오기

# 1줄씩 데이터 넣기
ws.append(["번호", "영어", "수학"])
for i in range(1, 11):
    ws.append([i, randint(0, 100), randint(0, 100)])

# 영어 점수만 가져오기
col_eng = ws["B"]
print(col_eng) #  col의 정보만 가져옴

for cell in col_eng[1:]:
    print(cell.value, end=", ")

# 영어, 수학 col 두개 가져오기
col_range = ws["B:C"] # excel slicing에선 B:c ->B, C포함
for col in col_range:
    for cell in col[1:]:
        print(cell.value, end=" ")
    print()

# # 각 셀의 (이름, 좌표)정보를 가져오는 모듈
from openpyxl.utils.cell import coordinate_from_string

# row_range = ws[2:6]
row_range = ws[2:ws.max_row]
for row in row_range:
    for cell in row:
        # print(cell.value, end=" ")
        # print(cell.coordinate, end=" ") # A1
        xy = coordinate_from_string(cell.coordinate)
        print(xy, end=" ") # ('A', 2)
        print(xy[0], end=" ")
        print(xy[1], end=" ")
    print()

# 전체 rows
print(ws.rows)
print(tuple(ws.rows)) # (<Cell 'Sheet'.A1>, <Cell 'Sheet'.B1>, <Cell 'Sheet'.C1>)
print(tuple(ws.columns)) # (<Cell 'Sheet'.A1>, <Cell 'Sheet'.A2>, <Cell 'Sheet'.A3>, <Cell 'Sheet'.A4>, <Cell 'Sheet'.A5>, <Cell 'Sheet'.A6>, <Cell 'Sheet'.A7>, <Cell 'Sheet'.A8>, <Cell 'Sheet'.A9>, <Cell 'Sheet'.A10>, <Cell 'Sheet'.A11>)

for row in tuple(ws.rows):
    print(row[1].value)
for col in tuple(ws.columns):
    print(col[0].value)

# ws.iter_rows(min_row, min_col, max_row, max_col)
for row in ws.iter_rows(min_row=1, max_row=5, min_col=2, max_col=3) : # 전체 row
    print(row[0].value, row[1].value)

for col in ws.iter_cols(): # 전체 row
    print(col[0].value)

wb.save("sample.xlsx")

검색하기

for row in ws.iter_rows(min_row=2): # 제목 스킵
    # 번호, 영어, 수학
    if row[1].value > 80:
        print(type(row[1].value))
        print(row[0].value, "번 학생은 영어 천재 [영어 점수: {0}]".format(row[1].value))

for row in ws.iter_rows(max_row=1):
    # 영어 -> 컴퓨터
    for cell in row:
        if cell.value == "영어":
            cell.value = "컴퓨터"

wb.save("sample_modified.xlsx")

행, 열 삽입하기

# 새로운 시트 복사
target = wb.copy_worksheet(ws)
target.title = "insert_sheet"

target = wb['insert_sheet']

# 새로운 행 삽입 : n번째 줄이 비워짐
target.insert_rows(8)

# 8번째부터 5줄 삽입
target.insert_rows(8, 5)

# 새로운 열 삽입
ws_cols = wb.copy_worksheet(ws)
ws_cols.title = "insert_cols"
ws_cols.insert_cols(2, 4)

삭제하기

# delete 연습용 새 sheet 생성
ws_del_rows = wb.copy_worksheet(ws)
ws_del_rows.title = "del_rows"

ws_del_rows.delete_rows(8, 2) # 8번째 줄부터 2명 삭제

ws_del_rows.delete_cols(2, 2)

내용 이동시키기

move_cols = wb.copy_worksheet(ws)
move_cols.title = "sample_korean"
move_cols = ws

# 번호 (국어) 영어 수학
move_cols.move_range("B1:C11", rows=0, cols=1) # 범위, 이동할 행, 이동할 열
move_cols["B1"].value = "국어"

for row in move_cols.iter_rows(min_row=2):
    row[1].value = randint(0, 100)

차트 그리기

# 어떤 데이터를 chart로 만들지 정의
bar_value = Reference(ws, min_row=2, min_col=2, max_row=ws.max_row, max_col=ws.max_column)
bar_chart = BarChart() # 차트 종류 설정 (Bar, Line, Pie...)
bar_chart.add_data(bar_value) # 차트 데이터 추가
ws.add_chart(bar_chart, "G1") # 차트 넣을 위치 정의

# 선 차트
line_value = Reference(ws, min_row=1, min_col=2, max_row=11, max_col=4)
line_chart = BarChart()
line_chart.add_data(line_value, titles_from_data=True)
line_chart.title = "성적표"
line_chart.style = 20
line_chart.y_axis.title = "점수"
line_chart.x_axis.title = "번호"

ws.add_chart(line_chart, "G1")

셀 스타일 적용하기

# 번호, 영어, 수학
a1 = ws["A1"] # 번호
b1 = ws["B1"] # 영어
c1 = ws["C1"] # 수학

# 열 너비, 높이 설정
ws.column_dimensions["A"].width = 5
ws.row_dimensions[1].height = 50

# 스타일 적용 : font 적용
a1.font = Font(color="FF0000", italic=True, bold=True)
b1.font = Font(color="CC33FF", name="Arial", strike=True) # strike: 취소선 -> excel 셀 서식-글꼴-효과-취소선
c1.font = Font(color="0011FF", size=20, underline="single") # underline = {'double', 'doubleAccounting', 'single', 'singleAccounting'}

# 셀 테두리 적용
thin_border = Border(left=Side(border_style="thin"), right=Side(border_style="thin"), top=Side(border_style="thin"), bottom=Side(border_style="thin"))

a1.border = thin_border
b1.border = thin_border
c1.border = thin_border

# 조건에 따라 셀 배경색 변경: 80점 넘는 셀에 대해 초룍색 배경
for row in ws.rows:
    for cell in row:
        # 설 정렬하기
        cell.alignment = Alignment(horizontal="center", vertical="center")

        if cell.column == 1: # 번호열 제외
            continue
        if isinstance(cell.value, int) and cell.value > 80:
            cell.fill = PatternFill(fgColor="00FF00", fill_type="solid")
            cell.font = Font(color="FF0000")

# 틀 고정
ws.freeze_panes = "B2" # 셀 주소 전달하면 해당 셀 기준 행, 열 고정

엑셀 수식 입력하기

ws["A1"] = datetime.datetime.today() # 오늘 날짜 정보
ws["A2"] = "=SUM(1, 2, 3)"
ws["A3"] = "=AVERAGE(1, 2, 3)"

ws["A4"] = 10
ws["A5"] = 20
ws["A6"] = "=SUM(A4:A5)"

엑셀 수식 결과값만 가져오기

# # 모든 값 가져오기: 수식 그대로 가져옴
for row in ws.values:
    for cell in row:
        print(cell)

# 모든 값 가져오기: 수식 그대로 가져옴
# evaluate(계산)되지 않은 상태의 데이터는 None으로 표시
wb = load_workbook("sample_formula.xlsx", data_only=True)
ws = wb.active

for row in ws.values:
    for cell in row:
        print(cell)

병합 및 가운데정렬

# 병합하기
ws.merge_cells("B2:D2")
ws["B2"].value = "Merged Cell"

# 가운데정렬
ws["B2"].alignment = Alignment(horizontal="center") # horizontal = 가로 정렬

# 새 시트
ws_new = wb.copy_worksheet(ws)
ws_new.title = "unmerge"
# 병합 해제
ws_new.unmerge_cells("B2:D2")

이미지 추가하기

from openpyxl.drawing.image import Image
from openpyxl import Workbook
wb = Workbook()
ws = wb.active

# ImportError: You must install Pillow to fetch image objects -> pip install pillow
img = Image("img.png")
ws.add_image(img, "C3")

wb.save("sample_image.xlsx")

ImportError: You must install Pillow to fetch image objects

• pip install pillow로 pillow 설치 후 진행하기

엑셀 퀴즈