[OpenPyXL] 파이썬으로 엑셀 다루기 - 셀 서식, 셀 병합, 필터

from openpyxl import Workbook

# Workbook: 엑셀 파일
wb = Workbook()

# Worksheet: 엑셀 시트 (Sheet1, Sheet2 등)
ws = wb.active  # (1) 현재 활성화된 시트
ws = wb["Sheet1"]  # (2) 또는 시트명
ws = wb.create_sheet("새 시트")  # (3) 새 시트 생성

# Cell: 개별 셀 (A1, B2 등)
cell = ws['A1']  # 셀 직접 접근
cell.value = "Hello"  # 값 설정

from openpyxl import Workbook

# 엑셀 파일 생성
wb = Workbook()
ws = wb.active
ws.title = "데이터"

# 데이터 추가
data = [
    ["이름", "나이", "직업"],   # 헤더
    ["홍길동", 30, "개발자"],   # 1행
    ["김철수", 25, "마케터"],   # 2행
    ["이영희", 28, "기획자"],   # 3행
]
for row in data:
    ws.append(row)

# 파일 저장
wb.save("sample.xlsx")

from openpyxl import load_workbook, Workbook
from openpyxl.worksheet.worksheet import Worksheet
from openpyxl.cell.cell import Cell

wb: Workbook = load_workbook("data.xlsx")
ws: Worksheet = wb.active
cell: Cell = ws["A1"]

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

# 1. 정렬 (https://openpyxl.readthedocs.io/en/stable/api/openpyxl.styles.alignment.html)
cell.alignment = Alignment(
    horizontal = "center",      # 왼쪽(left), 가운데(center), 오른쪽(right) 등
    vertical = "center",        # 상단(top), 중앙(center), 하단(bottom) 등
    wrap_text = True,           # 자동 줄바꿈 허용
)

# 2. 테두리 (https://openpyxl.readthedocs.io/en/stable/api/openpyxl.styles.borders.html)
cell.border = Border(
    left = Side(style="thin"),  # 상하좌우 외에 대각선(diagonal) 등 테두리 지원
    right = Side(style="thin"), # Side: 점선(dotted), 이중선(double) 등 테두리 옵션
    top = Side(style="thin"),
    bottom = Side(style="thin"),
)

# 3. 채우기 (https://openpyxl.readthedocs.io/en/stable/api/openpyxl.styles.fills.html)
cell.fill = PatternFill(
    start_color = "FFFF00",     # RGB 색상 코드 (노란색)
    end_color = "00FFFF00",     # 또는 알파 값을 추가한 aRGB 색상 코드 (노란색)
    fill_type = "solid",
)

# 4. 폰트 (https://openpyxl.readthedocs.io/en/stable/api/openpyxl.styles.fonts.html)
cell.font = Font(
    name = "Arial",
    size = 11,
    bold = True,
    color = "000000",           # RGB 색상 코드 (검은색)
)

# 5. 숫자 서식
cell.number_format = "#,##0"    # 천 단위 구분
cell.number_format = "0.00%"    # 백분율
cell.number_format = "yy-mm-dd" # 날짜

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

def style_cell(
        cell: Cell,
        alignment: dict | None = None,      # 정렬
        border: dict | None = None,         # 테두리
        fill: dict | None = None,           # 채우기
        font: dict | None = None,           # 폰트
        number_format: str | None = None,   # 숫자 서식
    ):
    if alignment:
        cell.alignment = Alignment(**alignment)
    if border:
        cell.border = create_border(**border)   # 주의) 테두리 옵션은 Side 객체로 전달해야 함
    if fill:
        cell.fill = PatternFill(**fill)
    if font:
        cell.font = Font(**font)
    if number_format:
        cell.number_format = number_format

def create_border(**kwargs: dict) -> Border:
    return Border(**{side: Side(**options) for side, options in kwargs.items()})

header_style = {
    "alignment": {"horizontal": "center", "vertical": "center"},                        # 가운데 정렬
    "border": {side: {"style": "thin"} for side in ["left","right","top","bottom"]},    # 바깥 테두리
    "fill": {"start_color": "FFFF00", "end_color": "FFFF00", "fill_type": "solid"},     # 노란색 배경
    "font": {"bold": True, "color": "000000"},                                          # 볼드체
}

# 헤더(1행, 2행)에 서식 적용합니다.
for header_row in [1, 2]:
    for cell in ws[header_row]:
        style_cell(cell, **header_style)

# 추가로, 숫자 열에 1,000 단위 구분 서식을 추가합니다.
for numeric_column in ['C', 'D']:
    for cell in ws[numeric_column]:
        style_cell(cell, number_format = "#,##0")

ws.column_dimensions['A'].width = 8.43  # A열 너비 지정
ws.row_dimensions[1].height = 15.0      # 1행 높이 지정

def get_cell_width(value: str) -> float:
    try:
        # 한글: 1.8배, 공백: 1.2배, 영문/숫자: 1배
        return sum(1.8 if ord(c) > 12799 else 1.2 if c.isspace() else 1. for c in value)
    except:
        return 0.

column = 'A'
max_width = 8.43    # 최소 너비 (초깃값)

for cell in ws[column]:
    if cell.value:
        max_width = max(max_width, get_cell_width(str(cell.value)))

ws.column_dimensions[column].width = min(max_width + 2., 25.)   # 좌우 1씩 여백, 최대 너비 25 제한

from openpyxl.formatting.rule import CellIsRule
from openpyxl.styles import PatternFill

# 범위 내 값이 1000 이상이면 빨간 배경색 서식을 표시하는 조건부 서식
rule_gte = CellIsRule(
    operator = "greaterThanOrEqual",
    formula = ["1000"],
    stopIfTrue = False,
    fill = PatternFill(start_color="FF0000", end_color="FF0000", fill_type="solid"),
)
ws.conditional_formatting.add("C3:C10", rule_gte)

from openpyxl.formatting.rule import Rule, CellIsRule, FormulaRule
from typing import Literal, Sequence

def conditional_rule(
        operator: Literal[
            "endsWith", "containsText", "beginsWith", "lessThan", "notBetween", "lessThanOrEqual",
            "notEqual", "notContains", "between", "equal", "greaterThanOrEqual", "greaterThan", "formula"],
        formula: Sequence | None = None,    # 수식 또는 피연산자
        stop_if_true: bool | None = None,   # 우선순위 규칙
        border: dict | None = None,         # 스타일 - 테두리
        fill: dict | None = None,           # 스타일 - 채우기
        font: dict | None = None,           # 스타일 - 폰트
    ) -> Rule:
    if operator == "formula":
        return formula_rule(formula, stop_if_true, border, fill, font)

    styles = dict()
    if border:
        styles["border"] = create_border(**border)
    if fill:
        styles["fill"] = PatternFill(**fill)
    if font:
        styles["font"] = Font(**font)
    return CellIsRule(operator=operator, formula=formula, stopIfTrue=stop_if_true, **styles)

def formula_rule(
        formula: Sequence | None = None,    # 수식
        stop_if_true: bool | None = None,   # 우선순위 규칙
        border: dict | None = None,         # 스타일 - 테두리
        fill: dict | None = None,           # 스타일 - 채우기
        font: dict | None = None,           # 스타일 - 폰트
    ) -> Rule:
    styles = dict()
    if border:
        styles["border"] = create_border(**border)
    if fill:
        styles["fill"] = PatternFill(**fill)
    if font:
        styles["font"] = Font(**font)
    return FormulaRule(formula=formula, stopIfTrue=stop_if_true, **styles)

# 값이 3,000 이상인 경우의 조건부 서식
rule_gte = dict(
    operator = "greaterThanOrEqual",
    formula = ["3000"],
    stop_if_true = True,
    fill = dict(start_color="FE968D", end_color="FE968D", fill_type="solid"),
)

# 값이 1,000 이하인 경우의 조건부 서식
rule_lte = dict(
    operator = "lessThanOrEqual",
    formula = ["1000"],
    stop_if_true = True,
    fill = dict(start_color="41B0F6", end_color="41B0F6", fill_type="solid"),
)

ws.conditional_formatting.add("C3:C10", conditional_rule(**rule_gte))
ws.conditional_formatting.add("C3:C10", conditional_rule(**rule_lte))

ws.merge_cells("A1:B1")  # A1부터 B1까지 병합
ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=2)  # 또는 행/열 번호로 지정

from collections import deque
from openpyxl.utils import range_boundaries

range_string: str = "A1:E10"
mode: Literal["blank", "same_value"] = "same_value"

# --- [0] --- 함수의 첫 번째 부분: 2차원 배열 초기화
min_col, min_row, max_col, max_row = range_boundaries(range_string)
num_rows, num_cols = (max_row - min_row + 1), (max_col - min_col + 1)

rows = [list(row) for row in ws.iter_rows(min_row, max_row, min_col, max_col, values_only=True)]
visited = [[False for _ in range(num_cols)] for _ in range(num_rows)]

# --- [1] --- 함수의 두 번째 부분: BFS 구현
def _bfs(row_seq: int, col_seq: int) -> list[tuple[int,int]]:
    queue, cells = deque(), [(row_seq, col_seq)]
    queue.append((row_seq, col_seq))
    visited[row_seq][col_seq] = True
    value = rows[row_seq][col_seq]

    while queue:
        r, c = queue.popleft()
        for nr, nc in [(r, c+1), (r+1, c)]: # [(오른쪽), (아래쪽)]
            if (0 <= nr < num_rows) and (0 <= nc < num_cols) and (not visited[nr][nc]):
                if (rows[nr][nc] == value) if mode == "same_value" else (rows[nr][nc] is None):
                    visited[nr][nc] = True
                    queue.append((nr, nc))
                    cells.append((nr, nc))
    return cells

from openpyxl.utils import get_column_letter as colstr

merge_ranges: list = list()
priority: Literal["width", "height"] = "width"

# --- [2] --- 함수의 세 번째 부분: 병합 범위 선택
for row_seq in range(num_rows):
    for col_seq in range(num_cols):
        if (not visited[row_seq][col_seq]) and (rows[row_seq][col_seq] is not None):
            cells = _bfs(row_seq, col_seq)
            if len(cells) < 2:
                continue

            # 최적의 직사각형 범위 구하기
            best_corners = get_largest_rectangle(cells, top_left=cells[0], priority=priority)
            (row_start, col_start) = best_corners[0][0] # 좌상단 모서리 좌표
            (row_end, col_end) = best_corners[1][1] # 우하단 모서리 좌표

            for r, c in cells:
                # 직사각형 범위에 선택되지 않은 셀은 방문 기록 제거
                if not ((row_start <= r <= row_end) and (col_start <= c <= col_end)):
                    visited[r][c] = False

            if (row_start != row_end) or (col_start != col_end):
                # 범위 문자열로 치환하여 병합 대상 범위에 추가
                merge_ranges.append(f"{colstr(col_start+min_col)}{row_start+min_row}:{colstr(col_end+min_col)}{row_end+min_row}")
            else:
                # 직사각형 범위가 1x1 크기일 경우 인접한 셀에 대한 방문 기록 제거
                for r, c in cells[1:]:
                    visited[r][c] = False

from typing import TypeVar

Node = TypeVar("Node", bound=tuple[int, int])
TopLeft = TypeVar("TopLeft", bound=Node)
TopRight = TypeVar("TopRight", bound=Node)
BottomLeft = TypeVar("BottomLeft", bound=Node)
BottomRight = TypeVar("BottomRight", bound=Node)

def get_largest_rectangle(
        nodes: list[Node],
        top_left: Node,
        priority: Literal["width","height"] = "width",
    ) -> tuple[tuple[TopLeft, TopRight], tuple[BottomLeft, BottomRight]]:
    node_set = set(nodes)
    y0, x0 = top_left

    best_score = (0, 0)
    best_corners = (
        ((top_left, top_left), (top_left, top_left)),
        ((top_left, top_left), (top_left, top_left)),
    )

    max_width = 0
    while (y0, x0 + max_width) in node_set:
        max_width += 1

    min_height = float("inf")
    for width in range(1, max_width + 1):
        height = 0
        while (y0 + height, x0 + width - 1) in node_set:
            height += 1

        min_height = min(min_height, height)
        area = width * min_height
        score = (area, min_height if priority == "height" else width)

        if score > best_score:
            best_score = score
            y_max, x_max = (y0 + int(min_height) - 1), (x0 + width - 1)
            best_corners = (
                ((y0, x0), (y0, x_max)),
                ((y_max, x0), (y_max, x_max))
            )

    return best_corners

>>> find_merge_ranges(ws, range_string = "A1:E1", mode = "same_value")
["A1:B1", "C1:D1"]

for range_string in find_merge_ranges(ws, range_string = "A1:E1", mode = "same_value"):
    ws.merge_cells(range_string)

from openpyxl.utils import get_column_letter

ws.auto_filter.ref = f"A1:{get_column_letter(ws.max_column)}{ws.max_row}"

from openpyxl.worksheet.filters import FilterColumn, Filters, DateGroupItem
from openpyxl.worksheet.filters import Top10, DynamicFilter, BlankFilter, ColorFilter, IconFilter
from openpyxl.worksheet.filters import CustomFilters, CustomFilter

filter_column = FilterColumn(colId = 0)

# 1. 값 필터
filter_column.filters = Filters(
    blank = True,                   # 빈 값 허용
    calendarType = "gregorian",     # 달력 유형 (양력 등)
    filter = ["값1", "값2"],         # 필터할 값
    dateGroupItem = [DateGroupItem(year=2026, month=2, day=20)],    # 필터할 날짜
)

# 2. 상위/하위 필터
filter_column.top10 = Top10(
    top = True,         # True면 상위, False면 하위
    percent = True,     # True면 백분율(%), False면 항목 수
    val = 10.0,         # 항목 수 또는 백분율(%) <-- 필수
    filterVal = 50.0,   # 실제 기준값 <-- 선택
)

# 3. 평균/날짜 필터
filter_column.dynamicFilter = DynamicFilter(
    # "평균 이상", "오늘", "이번 달" 등 평균/날짜 관련 필터 타입 지원
    type: Literal["aboveAverage", "today", "thisMonth", ...] = "null",
    val: Float = None,          # 기준값 (숫자)
    valIso: DateTime = None,    # 기준값 (날짜)
    maxVal: Float = None,       # 최대 기준값 (숫자)
    maxValIso: DateTime = None, # 최대 기준값 (날짜)
)

# 4. 연산자 필터
filter_column.customFilters = CustomFilters(
    # 여러 개 연산 조건 허용
    customFilter = [
        CustomFilter(
            # "같음", "초과", "미만" 등 연산자 필터 타입 지원
            operator: Literal["equal", "lessThan", "greaterThan", ...] = "equal"
            val: String = "",   # 기준값 (텍스트)
        ),
        ...
    ]
)

# 5. 공백 필터, 색상 필터, 아이콘 필터 (설명 생략)
filter_column.filters = Filters(blank=True, filter=[BlankFilter()])
filter_column.colorFilter = ColorFilter(...)
filter_column.iconFilter = IconFilter(...)

# 0. 필터 항목 적용 (항목만 선택, 실제 적용 X)
ws.auto_filter.filterColumn = filter_column

ws.row_dimensions[1].hidden = True

import xml.etree.ElementTree as ET
import tempfile
import os

Sheet = TypeVar("Sheet", bound=int)
Row = TypeVar("Row", bound=int)

def apply_filters(wb: Workbook, filtered_rows: dict[Sheet, set[Row]]) -> Workbook:
    for index, rows in filtered_rows.items():
        ws = wb.worksheets[index-1]
        if not (ws.auto_filter and ws.auto_filter.filterColumn):
            continue

        ns = {"main": "http://schemas.openxmlformats.org/spreadsheetml/2006/main"}
        ET.register_namespace('', ns["main"])

        # Workbook 객체를 직접 다룰 수 없으므로 임시 파일로 저장
        with tempfile.NamedTemporaryFile(suffix=".xlsx", delete=False) as tmp_file:
            input_path = tmp_file.name
            wb.save(input_path)

        try:
            # 실제로 행을 숨기는 동작은 이어서 설명
            wb = _apply_filter(input_path, index, rows, ns)
        finally:
            os.remove(input_path)

    return wb

def _apply_filter(input_path: str, sheet_index: Sheet, filtered_rows: set[Row], ns: dict) -> Workbook:
    from openpyxl import load_workbook
    from tempfile import NamedTemporaryFile
    import xml.etree.ElementTree as ET
    import zipfile
    import os

    with zipfile.ZipFile(input_path, 'r') as zip_in:
        sheet_xml = f"xl/worksheets/sheet{sheet_index}.xml"
        with zip_in.open(sheet_xml) as zip_file:
            tree = ET.parse(zip_file)
            root = tree.getroot()

        sheet_data = root.find(".//main:sheetData", ns)
        if sheet_data is not None:
            for row in sheet_data.findall(".//main:row", ns):
                row_idx = int(row.get('r', 0))
                if row_idx in filtered_rows:
                    row.set("hidden", '1')      # 필터 대상 행 숨기기
                elif row.get("hidden"):
                    del row.attrib["hidden"]    # 필터 대상이 아닌 행 펼치기

        # 또 다른 임시 파일 생성
        with NamedTemporaryFile(delete=False, suffix=".xlsx") as file:
            output_path = file.name

        try:
            # 필터 적용된 임시 파일을 저장하고,
            with zipfile.ZipFile(output_path, 'w', zipfile.ZIP_DEFLATED) as zip_out:
                for zip_info in zip_in.infolist():
                    if zip_info.filename == sheet_xml:
                        xml_bytes = ET.tostring(root, encoding="utf-8", xml_declaration=True)
                        zip_out.writestr(zip_info, xml_bytes)
                    else:
                        zip_out.writestr(zip_info, zip_in.read(zip_info.filename))
            # 임시 파일을 `load_workbook()` 함수로 다시 `Workbook` 객체로 불러오기
            return load_workbook(output_path)
        finally:
            os.remove(output_path)

ws.auto_filter.ref = "A2:E10"

cell.hyperlink = url

for col_idx, column in enumerate(ws.columns, start=1):
    for row_idx, cell in enumerate(column, start=1):
        if hyperlink and text.startswith("https://"):
            cell.hyperlink = text
            cell.font = _font(color="#0000FF", underline="single")

# 첫 행 고정 (A2부터 스크롤)
ws.freeze_panes = "A2"

# 첫 열도 함께 고정 (B2부터 스크롤)
ws.freeze_panes = "B2"

# 여러 행/열 고정 (C3부터 스크롤 = 2행 2열 고정)
ws.freeze_panes = "C3"

ws.sheet_view.zoomScale = 85    # 85% 크기로 표시