[openpyxl] 11. 파이썬을 이용한 엑셀 기타 정리본

차트 만들기

from openpyxl.chart import BarChart, LineChart, Reference
from openpyxl import load_workbook
wb = load_workbook("sample.xlsx")
ws = wb.active

# B2:C11 까지의 데이터를 차트로 생성
# min_row가 2면 필드명 정의가 되어 있지 않으므로 계열1,2라 뜬다.
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")  # 차트 넣을 위치 정의
wb.save("sample_bar_chart.xlsx")
wb.close()

wb = load_workbook("sample.xlsx")
ws = wb.active
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 = 15  # 미리 정의된 스타일을 적용, 사용자가 개별 지정도 가능
line_chart.y_axis.title = "점수"  # Y축의 제목
line_chart.x_axis.title = "번호"  # X축의 제목

ws.add_chart(line_chart, "F1")  # 차트 넣을 위치 정의
wb.save("sample_line_chart.xlsx")
wb.close()

 

 

 

셀 스타일 지정해주기

from openpyxl.styles import Font, Border, Side, PatternFill, Alignment
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, size=20)  # 빨간색, 이탤릭, 굵게 적용, 크기 20
b1.font = Font(color="CC33FF", name="Arial",
               strike=True)  # 폰트 Arial, 분홍색, 취소선 적용
# 글자크기 20, 밑줄 1줄짜리 적용
c1.font = Font(color="0000FF", size=15, 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 에 대해 정렬
        # center, left, right, top, bottom 등등 존재
        cell.alignment = Alignment(horizontal="center", vertical="center")

        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 기준으로 틀 고정

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

 

 

함수 만들기

import datetime
from openpyxl import Workbook

wb = Workbook()
ws = wb.active

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

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

 

 

수식 복사가 아닌 값복사 하기

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)


# 수식이 아닌 실제 데이터를 가져오기 위해서는 data_only = True
wb = load_workbook("sample_formula.xlsx", data_only=True)
ws = wb.active

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