shanxia2.0/backend/app/utils/excel_list_validation.py

from __future__ import annotations

from typing import Sequence

from openpyxl.utils import get_column_letter
from openpyxl.workbook.workbook import Workbook
from openpyxl.worksheet.datavalidation import DataValidation
from openpyxl.worksheet.worksheet import Worksheet


def write_option_sheet(
    wb: Workbook,
    sheet_title: str,
    labels: Sequence[str],
    ids: Sequence[str] | None = None,
    *,
    hidden: bool = True,
) -> str:
    """写入选项表：A=label，可选 B=id。返回 sheet_title。"""
    if sheet_title in wb.sheetnames:
        ws = wb[sheet_title]
        wb.remove(ws)
    ws = wb.create_sheet(sheet_title)
    ws.append(["label", "id"] if ids is not None else ["label"])
    for i, lab in enumerate(labels):
        if ids is not None:
            ws.append([lab, ids[i] if i < len(ids) else ""])
        else:
            ws.append([lab])
    if hidden:
        ws.sheet_state = "hidden"
    return sheet_title


def add_list_validation(
    ws: Worksheet,
    *,
    option_sheet: str,
    option_rows: int,
    target_col: int,
    start_row: int,
    end_row: int,
    allow_blank: bool = True,
) -> None:
    """option_rows 为选项数据行数（不含表头）。option 在 A2:A{1+option_rows}。"""
    if option_rows <= 0:
        return
    last = 1 + option_rows
    formula = f"='{option_sheet}'!$A$2:$A${last}"
    col = get_column_letter(target_col)
    dv = DataValidation(
        type="list",
        formula1=formula,
        allow_blank=allow_blank,
        showErrorMessage=True,
        showInputMessage=True,
    )
    dv.error = "请从下拉列表选择"
    dv.errorTitle = "无效值"
    dv.add(f"{col}{start_row}:{col}{end_row}")
    ws.add_data_validation(dv)