[나도 코딩] 엑셀 자동화 코드 정리

[나도 코딩] 엑셀 자동화 코드 정리

01. 파일 만들기

from openpyxl import Workbook
wb = Workbook() # 새 워크북 생성
ws = wb.active # 현재 활성화된 sheet를 가져옴
ws.title = "Sheet1" # sheet의 이름을 변경
wb.save("sample.xlsx") # 파일 sample라는 이름으로 저장
wb.close()

02. 시트

from openpyxl import Workbook
wb = Workbook()
ws = wb.create_sheet() # 새로운 sheet를 기본 이름으로 생성
ws.title = "MySheet" # sheet의 이름 변경
ws.sheet_properties.tabColor = "ff66ff" #RGB형태로 값을 넣어주면 텝 색상 변경

ws1 = wb.create_sheet("YourSheet") # 주어진 이름으로 시트 생성
ws2 = wb.create_sheet("NewSheet",2) #2번째 index에 sheet 생성
 
new_ws = wb["NewSheet"] # Dict 형태로 Sheet에 접근

print(wb.sheetnames) # 모든 Sheet내용 확인

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

wb.save("sample.xlsx")

03. 셀 기본

from openpyxl import Workbook
wb = Workbook()
ws = wb.active
ws.title = "MySheet"

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

ws["B1"] =4
ws["B2"] =5
ws["B3"] =6

print(ws["A1"]) # A1셀의 정보를 출력
print(ws["A1"].value) # A1 셀의 값을 출력
print(ws["A10"].value) # A10의 값을 출력 / 값이 없으면 None을 출력

print(ws.cell(row=1,column=1).value) # A1의 값
print(ws.cell(row=1,column=2).value) # B1의 값

c = ws.cell(column=3, row=1, value=10) # C1의 값을 10으로 설정 
print(c.value)

# A~J까지 1~10까지의 행과 열의 랜덤 값을 입력
from random import *
for x in range(1,11):
    for y in range(1,11):
        ws.cell(row=x, column=y, value=randint(0,100))


wb.save("sample.xlsx")

04. 파일 열기

from openpyxl import load_workbook
wb = load_workbook("sample.xlsx") # sample.xlsx 파일에서 wb를 불러옴
ws = wb.active # 활성화된 Sheet

# cell 데이터 불러오기
for x in range(1,11):
    for y in range(1,11):
        print(ws.cell(row=x,column=y).value, end=" ") # cell의 데이터 읽기
    print()

# cell의 갯수를 모를때
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=" ") # cell의 데이터 읽기
    print()

05 - 1. 셀 영역 1

from random import randint
from openpyxl import Workbook
wb = Workbook()
ws = wb.active

# 한줄씩 데이터 넣기
ws.append(["번호","영어","수학"])
for i in range(1,11):
    ws.append([i,randint(0,100),randint(0,100)]) # 번호 밑에는 i 값 영어와 수학 밑에는 랜덤 값을 입력

col_b = ws["B"] # 영어 column만 가지고 오기
print(col_b)

for cell in col_b:
    print(cell.value)

col_range = ws["B:C"] # 영어, 수학 column 함께 가지고오기
for cols in col_range:
    for cell in cols:
        print(cell.value)

row_title = ws[1] # 1번째 row만 가지고오기
for cell in row_title:
    print(cell.value)

row_range = ws[2:6] # 2번째 줄에서 6번째 줄까지 가지고오기
for rows in row_range:
    for cell in rows:
        print(cell.value, end=" ")
    print()

from openpyxl.utils.cell import coordinate_from_string
row_range = ws[2:ws.max_row] # 2번째 줄부터 마지막 줄까지
for rows in row_range:
    for cell in rows:
        # print(cell.value, end=" ")
        # print(cell.coordinate, end=" ") # 데이터 셀의 위치 정보 표시
        xy = coordinate_from_string(cell.coordinate) # 데이터 셀의 위치정보를 튜플형태로 나누어줌
        # print(xy, end=" ")
        print(xy[0], end="") # A
        print(xy[1], end=" ") # 1
    print()

wb.save("sample.xlsx")

05 - 2. 셀 영역 2

from random import randint
from openpyxl import Workbook
wb = Workbook()
ws = wb.active

ws.append(["번호","영어","수학"])
for i in range(1,11):
    ws.append([i,randint(0,100),randint(0,100)])

# 전체 rows
print(tuple(ws.rows)) # 가로 한 줄씩 가지고 오기

# 전체 columns 
print(tuple(ws.columns)) # 세로 한 줄씩 가지고오기

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

for column in tuple(ws.columns):
    print(column[1].value)

for rows in ws.iter_rows(): # 전체 row
    print(row[1].value)

for column in ws.iter_cols(): # ()값을 넣어서 범위 지정 가능
    print(column[1].value)

wb.save("sample.xlsx")

06. 찾기

from openpyxl import load_workbook
wb = load_workbook("sample.xlsx")
ws = wb.active

for row in ws.iter_rows(min_row=2):
    # 번호, 영어, 수학
    if int(row[1].value) > 80:
        print(row[0].value,"번 학생은 영어 천재")

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

wb.save("sample.xlsx")

07. 삽입

from openpyxl import load_workbook
wb = load_workbook("sample.xlsx")
ws = wb.active

# ws.insert_rows(8) # 8 번재 줄이 추가됨
# ws.insert_rows(8,5) # 8줄 위치에 5줄이 추가됨
# ws.insert_cols(2) # B번째 열이 추가됨
ws.insert_cols(2,3) # B번째 열로부터 3열추가됨

wb.save("sample_insert_rows.xlsx")
wb.save("sample_insert_cols.xlsx")

08. 삭제

from openpyxl import load_workbook
wb = load_workbook("sample.xlsx")
ws = wb.active

# ws.delete_rows(8) # 8번째 줄에 있는 7번 학생 데이터 삭제
# ws.delete_rows(8,3) # 8번째 줄부터 3줄의 데이터 삭제
# ws.delete_cols(2) # 2번째 열 (B) 삭제
ws.delete_cols(2,2) # 2번째 열부터 2개의 열 삭제

# wb.save("sample_delete_row.xlsx")
wb.save("sample_delete_col.xlsx")

09. 이동

from openpyxl import load_workbook
wb = load_workbook("sample.xlsx")
ws = wb.active

# 번호 영어 수학 -> 번호 국어 영어 수학 으로 변경
ws.move_range("B1:C11", rows=0, cols=1)
ws["B1"].value = "국어" # B1셀에 국어 입력

ws.move_range("C1:C11",rows=5,cols=-1)
wb.save("sample_korea.xlsx")

10. 차트

from openpyxl import load_workbook
wb = load_workbook("sample.xlsx")
ws = wb.active

from openpyxl.chart import BarChart,Reference,LineChart
# bar_value = Reference(ws, min_row=2, max_row=11, min_col=2, max_col=3)
# bar_chart = BarChart() # 차트의 종류를 설정 (Bar,Line,Pie, ..)
# bar_chart.add_data(bar_value)
# ws.add_chart(bar_chart, "E1") # 차트 넣을 위치 정의

#B11:C11 까지의 데이터
line_value = Reference(ws, min_row=1, max_row=11, min_col=2, max_col=3)
line_chart = LineChart()
line_chart.add_data(line_value, titles_from_data = True) # 계열 -> 수학,영어처엄 제목에서 가지고 오게됨
line_chart.title = "성적표" # 제목
line_chart.style = 20 # 미래 정의 된 스타일을 적용, 사용자가 개별 지정도 가능
line_chart.y_axis.title = "점수" #y축의 제목
line_chart.x_axis.title = "점수" #x축의 제목
ws.add_chart(line_chart, "E1")

wb.save("sample_chart.xlsx")

11 - 1. 셀 스타일 1

from openpyxl.styles import Font, Border,Side
from openpyxl import load_workbook
wb = load_workbook("sample.xlsx")
ws = wb.active

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

# A열의 너비를 5로 설정
ws.column_dimensions["A"].width =5 

# 1행의 높이를 50으로 설정
ws.row_dimensions[1].height = 50

# 스타일 적용
a1.font = Font(color= "FF0000", italic=True, bold=True) # 글자색 : 빨강, 이탤릭 , 두껍게
b1.font = Font(color="CC33FF", name="Arial", strike=True) # 폰트를 Arial로 설정, 취소선
c1.font = Font(color="0000FF", size=20,underline="single") # 글자 크기를 20 밑줄 적용

# 테두리 적용
thin_border = Border(left=Side(style="thin"),right=Side(style="thin"),top=Side(style="thin"),bottom=Side(style="thin"))
a1.border = thin_border
b1.border = thin_border
c1.border = thin_border
wb.save("sample_style.xlsx")

11 - 2. 셀 스타일 2

#90점 넘는 셀에 대해서 초록색으로 적용
for row in ws.rows:
    for cell in row:
        # 각 cell에 대해서 정렬
        cell.alignment = Alignment(horizontal="center", vertical="center")
        # center, left, right, top, bottom
        if cell.column ==1: # A 번호열은 제외
            continue
        # cell 이 정수형 데이터이고 90점 이상이면
        if isinstance(cell.value, int) and cell.value > 90:
            cell.fill = PatternFill(fgColor="00FF00", fill_type="solid") # 배경을 초록색으로
            cell.font = Font(color="FF0000") # 폰트 색상 변경


# 틀 고정
ws.freeze_panes = "B2"  #B2 기준으로 틀 고정

12. 수식

from datetime import datetime
from openpyxl import Workbook
wb = Workbook()
ws = wb.active

ws["A1"] = datetime.today() # 오늘 날짜 정도
ws["A2"] = "=SUM(1,2,3)" # 1+2+3 이 더해진 6값이 들어감
ws["A3"] = "=AVERAGE(1,2,3)" # 1,2,3의 평균인 6이 들어감

ws["A4"] = 10 
ws["A5"] = 20
ws["A6"] = "=SUM(A4:A5)" #30
wb.save("sample_formula.xlsx")

13. 수식 (데이터 전용)

from openpyxl import load_workbook
# wb = load_workbook("sample_formula.xlsx")
# ws = wb.active

# # 수식 그대로 가져오고 있음
# for row in ws.values:
#     for cell in row:
#         print(cell)

wb = load_workbook("sample_formula.xlsx", data_only=True)
ws = wb.active

# 수식이 아닌 실제 데이터를 가지고옴
#evaluate 되지 않은 상태의 데이터는 None 이라고 표시 / exel 을 한번 열어두고 저장하면 정상적으로 작동됨
for row in ws.values:
    for cell in row:
        print(cell)

---

깃 허브 : https://github.com/jong-seoung/Python/tree/main/RPA/Exel

GitHub - jong-seoung/Python: Python study

 

---

[출처]나도코딩 강의 : https://www.youtube.com/watch?v=exgO1LFl9x8