[#1 python excel] 엑셀 다루기 기초

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

ws.append(("학번", "출석", "퀴즈1", "퀴즈2", "중간고사", "기말고사", "프로젝트"))

scores = [
    (1, 10, 8, 5, 14, 26, 12), (2, 7, 3, 7, 15, 24, 18), (3, 9, 5, 8, 8, 12, 4), (4, 7, 8, 7, 17, 21, 18), (5, 7, 8, 7, 16, 25, 15), (6,
                                                                                                                                      3, 5, 8, 8, 17, 0), (7, 4, 9, 10, 16, 27, 18), (8, 6, 6, 6, 15, 19, 17), (9, 10, 10, 9, 19, 30, 19), (10, 9, 8, 8, 20, 25, 20)
]

for s in scores:
    ws.append(s)

# 1. 퀴즈2 점수를 10으로 수정
for idx, cell in enumerate(ws["D"]):
    if idx == 0:  # 제목인 경우 skip
        continue
    cell.value = 10


# 2. H열에 총점(SUM 이용), I열에 성적 정보 추가
ws["H1"] = "총점"
ws["I1"] = "성적"

for idx, score in enumerate(scores, start=2):
    sum_val = sum(score[1:]) - score[3] + 10  # 총점
    ws.cell(row=idx, column=8).value = f"=SUM(B{idx}:G{idx})"

    grade = None  # 성적
    if sum_val >= 90:
        grade = "A"
    elif sum_val >= 80:
        grade = "B"
    elif sum_val >= 70:
        grade = "C"
    else:
        grade = "D"
    if score[1] < 5:
        grade = "F"

    ws.cell(row=idx, column=9).value = grade

wb.save("quiz_score.xlsx")

1. Create_file

엑셀을 다루기 위해서는 openpyxl을 설치해야한다.

 

터미널에서

pip install openpyxl

후 아래의 파일을 저장한다.

 

1._create_file.py

from openpyxl import Workbook
wb = Workbook()  # 새 워크북 생성
ws = wb.active  # 현재 활성화된 sheet 가져옴
ws.title = "NadoSheet"
wb.save("sample.xlsx")
wb.close()

1_create_file.py

0.00MB

2. Sheet

엑셀내의 시트를 복사하거나 이동한다.

 

[시트생성]

wb.create_sheet

 

[시트복사]

wb.copy_worksheet

from openpyxl import Workbook
wb = Workbook()
ws = wb.create_sheet()  # 새로운 Sheet 기본 이름으로 생성
ws.title = "MySheet"
ws.sheet_properties.tabColor = "ff66ff"

ws1 = wb.create_sheet("YourSheet")  # 주어진 이름으로 Sheet 생성
ws2 = wb.create_sheet("NewSheet", 2)  # 2번째 index에 Sheet 생성

new_ws = wb["NewSheet"]  # Dict 형태로 Sheet에 접근
print(wb.sheetnames)  # 모든 Sheet 이름 확인

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

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

2_sheet.py

0.00MB

3. cell

엑셀 내의 셀정보를 가져오거나 입력한다.

from random import *
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
ws.title = "NadoSheet"

# A1셀에 1이라는 값 입력

ws["A1"] = 1
ws["A2"] = 2
ws["A3"] = 3
ws["B1"] = 4
ws["B2"] = 5
ws["B3"] = 6

print(ws["A1"].value)  # 셀 A1의 value 출력
print(ws["A10"].value)  # 값이 없을 떈 'None' 출력
print(ws.cell(column=1, row=1).value)  # ws["A1"].value 와 같은 값이다.
print(ws.cell(column=2, row=1).value)  # ws["B1"].value 와 같은 값이다.

for x in range(1, 11):
    for y in range(1, 11):
        ws.cell(row=x, column=y, value=randint(0, 100))


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

3_cell.py

0.00MB

4. open_file

엑셀 내의 셀 정보 가져오기

from openpyxl import load_workbook  # 파일 불러오기

wb = load_workbook("cell.xlsx")
ws = wb.active

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


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

4_open_file.py

0.00MB

5. cell_range

엑셀 내의 특정 영역(주소)의 값을 읽어온다.

from openpyxl.utils.cell import coordinate_from_string
from openpyxl import Workbook
from random import *

wb = Workbook()
ws = wb.active

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


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)6

row_range = ws[2:6]  # 1번째 줄인 title을 제외하고 2번째 줄에서 6번째 줄까지 가지고 오기

# for rows in row_range:
#     for cell in rows:
#         print(cell.value, end=" ")
#     print()


row_range = ws[2:ws.max_row]  # 2번째 줄 부터 마지막 줄까지 가지고오기
# for rows in row_range:
#     for cell in rows:
#         print(cell.value, end=" ")
#     print()

row_range = ws[2:ws.max_row]  # 셀 주소 기반으로 가지고 오기
# for rows in row_range:
#     for cell in rows:
#         # print(cell.value, end=" ")
#         xy = coordinate_from_string(cell.coordinate)
#         print(xy[0], end=" ")
#         print(xy[1], end=" ")
#     print()

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

# 전체 columns
# print(tuple(ws.columns))
# for column in tuple(ws.columns):
#     print(column[0].value)

# for row in ws.iter_rows():
#     print(row[2].value)


# 범위를 제한해서 가져오기
# 2번째 줄에서 11번째 줄까지, 2번쨰 열부터 3번쨰 열까지

for row in ws.iter_rows(min_row=2, max_row=11, min_col=2, max_col=3):
    print(row[0].value, row[1].value)
wb.save("sample.xlsx")

5_cell_range.py

0.00MB

6. 엑셀 내의 특정 값을 찾는다.

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_modifed.xlsx")

6_search.py

0.00MB

7. insert

엑셀에 행이나 열을 추가한다.

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_cols.xlsx")

7_insert.py

0.00MB

8. Delete

엑셀 내의 특정 행이나 컬럼을 삭제한다.

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)  # B열 삭제
ws.delete_cols(2, 2)  # B열 부터 2개의 열 삭제

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

8_delete.py

0.00MB

9. Move

엑셀 내의 특정 주소의 값을 다른 주소로 옮긴다. (잘라내기&붙여넣기)

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

# 번호 영어 수학
# 번호 (국어) 영어 수학

# ws.move_range("B1:C11", rows=0, cols=2)  # 이동할 범위를 지정하고 어디로 옮길 것인지 알려줌
# ws["B1"].value = "국어"  # B1 셀에 '국어' 입력

# 번호 영어 수학

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

wb.save("sample_korean.xlsx")

9_move.py

0.00MB

10. chart

엑셀 내에 특정 영역의 값을 차트로 삽입한다.

from openpyxl import load_workbook
from openpyxl.chart import BarChart, LineChart, Reference


wb = load_workbook("sample.xlsx")
ws = wb.active

# B1:C11 까지의 데이터
# Bar 차트 추가
# 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")  # 차트를 넣을 위치 정의

# 라인 차트 추가
line_value = Reference(ws, min_row=1, max_row=11, min_col=2, max_col=3)
line_chart = LineChart()  # 차트 종류 설정 (Bar, Line, Pie, ...)
# 계열을 영어, 수학과 같은 첫째 행에서 가져옴
line_chart.add_data(line_value, titles_from_data=True)
line_chart.title = "성적표"
line_chart.style = 10  # 미리 정의된 스타일을 적용, 사용자가 개별 지정도 가능
line_chart.y_axis.title = "점수"  # Y축의 제목
line_chart.x_axis.title = "번호"  # X축의 제목
ws.add_chart(line_chart, "E1")  # 차트를 넣을 위치 정의

wb.save("sample_chart.xlsx")

10_chart.py

0.00MB

11. cell style

엑셀 내의 특정 주소의 셀의 스타일을 수정한다.

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

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)  # 글자색, 글꼴, 취소선
c1.font = Font(color="0000FF", size=20, underline="single")  # 글자색, 글씨크기, 밑줄

# 테두리 적용
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

# 90점 넘는 셀에 대해서 초록색으로 적용
for row in ws.rows:
    for cell in row:
        cell.alignment = Alignment(horizontal="center", vertical="center")

        if cell.column == 1:  # A번호열은 제외
            continue

        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를 기준으로 틀 고정
wb.save("sample_style.xlsx")

11_cell_style.py

0.00MB

12. formular

엑셀에 수식을 입력하고 불러온다. 

from openpyxl import Workbook
import datetime
wb = Workbook()
ws = wb.active
ws["A1"] = datetime.datetime.today()
ws["A2"] = "=SUM(1,2,3)"
ws["A3"] = "=AVERAGE(1,2,3)"

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

wb.save("sample_formula.xlsx")

12_formular.py

0.00MB

13. formular dataonly

엑셀 내의 수식이 있더라도 수식이 적용된 값을 불러온다.

주의: dataonly로 가져오려면 일단 엑셀 파일을 한번 열어서 사람이 직접 저장을 해주어야 가져올 수 있다.

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


#  수식 그대로 가져오고 있음
# evaluate 되지 않은 상태에서의 데이터에는 None이라고 표시 >> 사람이 직접열어서 한번 저장해주어야 수식이 적용된 값 출력 가능
for row in ws.values:
    for cell in row:
        print(cell)

13_formula_dataonly.py

0.00MB

14. merge

엑셀 주소의 영역을 병합한다.

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

# 병합하기

ws.merge_cells("B2:D2")  # B2부터 D2까지 합치겠음
ws["B2"].value = "Merged Cell"

wb.save("sample_merge.xlsx")

14_merge.py

0.00MB

15. unmerge

엑셀의 병합된 셀을 병합해제한다.

from openpyxl import load_workbook

wb = load_workbook("sample_merge.xlsx")
ws = wb.active

# 병합하기

ws.unmerge_cells("B2:D2")  # B2부터 D2까지 합치겠음
wb.save("sample_unmerge.xlsx")

15_unmerge.py

0.00MB

16. image

엑셀 내의 특정 주소에 이미지 삽입

from openpyxl import Workbook
from openpyxl.drawing.image import Image


wb = Workbook()
ws = wb.active

img = Image("img.png")


ws.add_image(img, "C3")

wb.save("sample_image.xlsx")

16_image.py

0.00MB

17. insert 

엑셀 내에 값 입력 및 수식 입력 고급

17_quiz_socres.py

0.00MB