Case Generator：Python 脚本展示

from __future__ import annotations

import argparse
import re
import sys
from copy import copy
from pathlib import Path

from openpyxl import load_workbook
from openpyxl.utils import get_column_letter


OUTPUT_SHEET = "Sheet1"
CONFIG_SHEET = "配置"
RULES_SHEET = "用例规则"
VALID_STATUS_HEADER = "*有效状态"
DEFAULT_INPUT_PATH = Path("inputs/需求输入.xlsx")
DEFAULT_TEMPLATE_PATH = Path("templates/案例输出模板.xlsx")
DEFAULT_OUTPUT_PATH = Path("outputs/generated/生成案例.xlsx")
CONFIG_FIELD_HEADER = "字段名"
CONFIG_NOTE_HEADER = "说明"
CONFIG_MODE_HEADER = "取值模式"
CONFIG_SOURCE_HEADER = "取值内容"
CONFIG_MODE_FIXED = "固定"
CONFIG_MODE_TEMPLATE = "模板"
CONFIG_MODE_INPUT = "输入"

# 输入需求表固定按列读取：C=主题名称，E=要素类型，F=要素名称。
# 后续如果输入表列位置变了，只改这里的 4 个数字。
INPUT_THEME_CATEGORY_COL = 2
INPUT_THEME_COL = 3
INPUT_ELEMENT_TYPE_COL = 5
INPUT_ELEMENT_NAME_COL = 6
INPUT_FIELD_MAP = {
    "主题分类": INPUT_THEME_CATEGORY_COL,
    "主题名称": INPUT_THEME_COL,
    "要素类型": INPUT_ELEMENT_TYPE_COL,
    "要素名称": INPUT_ELEMENT_NAME_COL,
}
SKIP_ELEMENT_TYPES = {
    "业务连续数据准确性",
}

# 输出字段的默认生成规则。
# 也可以在模板“配置”页里写同名字段覆盖这些默认规则：
#   写普通文字：每一行都输出固定值，例如“验证：新建数据集”。
#   写占位符：按输入行替换，例如“验证：主题要素_{要素名称}字段展示顺序”。
DEFAULT_FIELD_TEMPLATES = {
    "*需求-业务名称": "{主题名称}",
    "*功能/流程名称": "{要素类型}",
    "*案例编号": "SIT-YONGHONG_{主题名称}_{序号}",
    "*测试意图": "验证：主题要素_{要素名称}字段展示顺序",
    "*测试步骤": "登录债券数据门户\n进入：{主题分类}",
}
RULE_SCOPE_HEADER = "规则类型"
RULE_SCOPE_NORMAL = "输入展开"
RULE_SCOPE_APPEND = "末尾追加"

PLACEHOLDER_PATTERN = re.compile(r"{([^{}]+)}")


def clean(value) -> str:
    """把 Excel 单元格值统一转成去掉首尾空格的字符串。"""
    if value is None:
        return ""
    return str(value).strip()


def is_blank(value) -> bool:
    return clean(value) == ""


def require_xlsx(path: Path, label: str) -> None:
    if path.suffix.lower() != ".xlsx":
        raise ValueError(f"{label} 只支持 .xlsx 文件：{path}")


def load_fixed_config(workbook) -> dict[str, dict[str, str]]:
    """读取模板里的“配置”页，显式支持固定/模板/输入三种模式。"""
    if CONFIG_SHEET not in workbook.sheetnames:
        raise ValueError(f"模板缺少 `{CONFIG_SHEET}` 工作表，请先在模板中维护固定字段配置。")

    sheet = workbook[CONFIG_SHEET]
    config: dict[str, dict[str, str]] = {}
    headers = {clean(sheet.cell(row=1, column=col).value): col for col in range(1, sheet.max_column + 1)}
    field_col = headers.get(CONFIG_FIELD_HEADER)
    note_col = headers.get(CONFIG_NOTE_HEADER)
    mode_col = headers.get(CONFIG_MODE_HEADER)
    source_col = headers.get(CONFIG_SOURCE_HEADER)

    required = {
        CONFIG_FIELD_HEADER: field_col,
        CONFIG_MODE_HEADER: mode_col,
        CONFIG_SOURCE_HEADER: source_col,
    }
    missing_headers = [name for name, col in required.items() if col is None]
    if missing_headers:
        raise ValueError(f"`{CONFIG_SHEET}` 缺少必需列：{', '.join(missing_headers)}")

    for row in range(2, sheet.max_row + 1):
        field_name = clean(sheet.cell(row=row, column=field_col).value)
        if not field_name:
            continue

        mode = clean(sheet.cell(row=row, column=mode_col).value)
        source = clean(sheet.cell(row=row, column=source_col).value)

        if mode not in {CONFIG_MODE_FIXED, CONFIG_MODE_TEMPLATE, CONFIG_MODE_INPUT}:
            raise ValueError(f"`{CONFIG_SHEET}` 第 {row} 行字段 `{field_name}` 的取值模式无效：{mode}")
        if not source:
            raise ValueError(f"`{CONFIG_SHEET}` 第 {row} 行字段 `{field_name}` 的取值内容不能为空。")

        if mode == CONFIG_MODE_INPUT and source not in INPUT_FIELD_MAP:
            raise ValueError(
                f"`{CONFIG_SHEET}` 第 {row} 行字段 `{field_name}` 的输入来源无效：{source}。"
                f" 可选值：{', '.join(INPUT_FIELD_MAP)}"
            )

        config[field_name] = {
            "mode": mode,
            "source": source,
        }

    return config


def load_case_rules(workbook, headers: list[str]) -> tuple[list[dict[str, str]], list[dict[str, str]]]:
    """读取“用例规则”页，拆成“输入展开”和“末尾追加”两类规则。"""
    if RULES_SHEET not in workbook.sheetnames:
        return [], []

    sheet = workbook[RULES_SHEET]
    rule_headers = [clean(sheet.cell(row=1, column=col).value) for col in range(1, sheet.max_column + 1)]
    input_rules: list[dict[str, str]] = []
    append_rules: list[dict[str, str]] = []

    for row in range(2, sheet.max_row + 1):
        if all(is_blank(sheet.cell(row=row, column=col).value) for col in range(1, sheet.max_column + 1)):
            continue

        rule: dict[str, str] = {}
        for col, header in enumerate(rule_headers, start=1):
            if not header:
                continue
            value = clean(sheet.cell(row=row, column=col).value)
            if value:
                rule[header] = value

        scope = rule.pop(RULE_SCOPE_HEADER, RULE_SCOPE_NORMAL) or RULE_SCOPE_NORMAL

        unknown_headers = [header for header in rule if header not in headers]
        if unknown_headers:
            raise ValueError(
                f"`{RULES_SHEET}` 第 {row} 行存在输出模板没有的字段：{', '.join(unknown_headers)}"
            )
        if scope == RULE_SCOPE_APPEND:
            append_rules.append(rule)
        else:
            input_rules.append(rule)

    return input_rules, append_rules


def get_output_headers(sheet) -> list[str]:
    """读取输出表头，只保留到 W 列附近的“*有效状态”为止。"""
    headers = [clean(sheet.cell(row=1, column=col).value) for col in range(1, sheet.max_column + 1)]

    try:
        last_col = headers.index(VALID_STATUS_HEADER) + 1
    except ValueError as exc:
        raise ValueError(f"模板 `{OUTPUT_SHEET}` 首行缺少 `{VALID_STATUS_HEADER}` 表头。") from exc

    return headers[:last_col]


def get_field_template(header: str, config: dict[str, dict[str, str]]) -> str:
    """字段值优先读“配置”页；没配置时再使用代码里的少量默认规则。"""
    if header in config:
        entry = config[header]
        # 配置页三种模式：
        #   固定：直接返回固定文本
        #   模板：返回带占位符的模板文本，例如“验证：主题要素_{要素名称}字段展示顺序”
        #   输入：把“主题名称/要素类型/要素名称”直接映射成占位符模板
        if entry["mode"] == CONFIG_MODE_INPUT:
            return "{" + entry["source"] + "}"
        return entry["source"]
    return DEFAULT_FIELD_TEMPLATES.get(header, "")


def render_field(field_name: str, template: str, context: dict[str, str]) -> str:
    """把配置文本里的占位符替换成当前输入行的值；没有占位符就是固定值。"""
    result = template
    for placeholder in PLACEHOLDER_PATTERN.findall(template):
        if placeholder not in context:
            raise ValueError(f"配置字段 `{field_name}` 使用了未知占位符：{{{placeholder}}}")
        result = result.replace(f"{{{placeholder}}}", context[placeholder])
    return result


def template_needs(template: str, placeholder: str) -> bool:
    return f"{{{placeholder}}}" in template


def build_field_templates(headers: list[str], config: dict[str, dict[str, str]], rule: dict[str, str]) -> dict[str, str]:
    """合并字段生成规则：用例规则页优先，其次配置页，最后代码默认值。"""
    return {
        header: rule.get(header, get_field_template(header, config))
        for header in headers
    }


def build_record_from_templates(
    headers: list[str],
    field_templates: dict[str, str],
    context: dict[str, str],
) -> dict[str, str]:
    """根据字段模板和上下文生成一整行输出记录。"""
    return {
        header: render_field(header, field_templates[header], context)
        for header in headers
    }


def copy_cell_style(source, target) -> None:
    """复制单元格样式，保证生成行看起来和模板行一致。"""
    if source.has_style:
        target.font = copy(source.font)
        target.fill = copy(source.fill)
        target.border = copy(source.border)
        target.alignment = copy(source.alignment)
        target.number_format = source.number_format
        target.protection = copy(source.protection)


def copy_template_row_style(sheet, source_row: int, target_row: int, max_col: int) -> None:
    for col in range(1, max_col + 1):
        copy_cell_style(sheet.cell(row=source_row, column=col), sheet.cell(row=target_row, column=col))

    source_dim = sheet.row_dimensions[source_row]
    target_dim = sheet.row_dimensions[target_row]
    target_dim.height = source_dim.height
    target_dim.hidden = source_dim.hidden


def clear_output_rows(sheet, max_col: int) -> None:
    """清空模板中已有的数据行，但保留行样式给后面复用。"""
    for row in range(2, sheet.max_row + 1):
        for col in range(1, max_col + 1):
            sheet.cell(row=row, column=col).value = None


def trim_output_rows(sheet, last_row: int) -> None:
    """删除多余空白行，让输出文件只留下表头和实际案例行。"""
    if sheet.max_row > last_row:
        sheet.delete_rows(last_row + 1, sheet.max_row - last_row)


def trim_output_columns(sheet, max_col: int) -> None:
    """删除 W 列后面的执行结果/备注等列，第一版不输出这些字段。"""
    if sheet.max_column > max_col:
        sheet.delete_cols(max_col + 1, sheet.max_column - max_col)


def update_tables(sheet, max_col: int, last_row: int) -> None:
    """更新 Excel 表格范围，否则筛选区域可能还停留在模板原始行数。"""
    table_last_row = max(2, last_row)
    table_ref = f"A1:{get_column_letter(max_col)}{table_last_row}"
    for table in sheet.tables.values():
        table.ref = table_ref


def get_input_sheet(workbook, sheet_name: str | None):
    if sheet_name:
        if sheet_name not in workbook.sheetnames:
            raise ValueError(f"输入文件缺少工作表：{sheet_name}")
        return workbook[sheet_name]
    return workbook.active


def row_is_empty(sheet, row: int) -> bool:
    return all(is_blank(sheet.cell(row=row, column=col).value) for col in range(1, sheet.max_column + 1))


def generate_case_rows(
    input_sheet,
    headers: list[str],
    config: dict[str, str],
    rules: list[dict[str, str]],
    append_rules: list[dict[str, str]],
) -> list[list[str]]:
    """把输入需求表转换为输出案例表，并在最后追加模板里写死的固定案例。"""
    rows: list[list[str]] = []
    last_theme_category = ""
    last_theme = ""
    last_element_type = ""
    last_element_name = ""
    if not rules:
        # 没有“用例规则”页时保持旧逻辑：一行输入只生成一行案例。
        rules = [{}]

    for input_row in range(2, input_sheet.max_row + 1):
        if row_is_empty(input_sheet, input_row):
            continue

        theme = clean(input_sheet.cell(row=input_row, column=INPUT_THEME_COL).value)
        theme_category = clean(input_sheet.cell(row=input_row, column=INPUT_THEME_CATEGORY_COL).value)
        element_type = clean(input_sheet.cell(row=input_row, column=INPUT_ELEMENT_TYPE_COL).value)
        element_name = clean(input_sheet.cell(row=input_row, column=INPUT_ELEMENT_NAME_COL).value)
        if element_type in SKIP_ELEMENT_TYPES:
            print(f"跳过输入第 {input_row} 行：要素类型 `{element_type}` 不生成案例。")
            continue
        if theme_category:
            last_theme_category = theme_category
        if theme:
            last_theme = theme
        if element_type:
            last_element_type = element_type
        if element_name:
            last_element_name = element_name

        context = {
            "主题分类": theme_category,
            "主题名称": theme,
            "要素类型": element_type,
            "要素名称": element_name,
            # 序号在每条展开规则里再设置，因为它按输出案例行顺延。
            "序号": "",
        }

        for rule_index, rule in enumerate(rules, start=1):
            field_templates = build_field_templates(headers, config, rule)
            missing = []
            if not theme and any(template_needs(value, "主题名称") for value in field_templates.values()):
                missing.append("C列主题名称")
            if not theme_category and any(template_needs(value, "主题分类") for value in field_templates.values()):
                missing.append("B列主题分类")
            if not element_type and any(template_needs(value, "要素类型") for value in field_templates.values()):
                missing.append("E列要素类型")
            if not element_name and any(template_needs(value, "要素名称") for value in field_templates.values()):
                missing.append("F列要素名称")
            if missing:
                # 缺少当前规则需要的关键字段时，只跳过这一条展开规则。
                print(
                    f"跳过输入第 {input_row} 行/规则第 {rule_index} 行："
                    f"缺少 {', '.join(missing)}。"
                )
                continue

            serial = len(rows) + 1
            context["序号"] = f"{serial:04d}"

            # 每个字段都走同一套规则：
            #   配置值没有占位符 -> 固定值
            #   配置值有占位符   -> 用当前输入行替换
            record = build_record_from_templates(headers, field_templates, context)

            rows.append([record.get(header, "") for header in headers])

    # 末尾固定案例不依赖输入表，只要模板写了就顺延追加。
    for rule_index, rule in enumerate(append_rules, start=1):
        serial = len(rows) + 1
        context = {
            # 末尾追加案例如果没有显式写主题/要素，默认沿用最后一条有效输入。
            "主题分类": last_theme_category,
            "主题名称": last_theme,
            "要素类型": last_element_type,
            "要素名称": last_element_name,
            "序号": f"{serial:04d}",
        }
        field_templates = build_field_templates(headers, config, rule)
        record = build_record_from_templates(headers, field_templates, context)
        rows.append([record.get(header, "") for header in headers])

    return rows


def write_output(template_path: Path, input_path: Path, output_path: Path, input_sheet_name: str | None) -> int:
    require_xlsx(template_path, "模板")
    require_xlsx(input_path, "输入")
    require_xlsx(output_path, "输出")

    if not template_path.exists():
        raise FileNotFoundError(f"模板文件不存在：{template_path}")
    if not input_path.exists():
        raise FileNotFoundError(f"输入文件不存在：{input_path}")
    if template_path.resolve() == output_path.resolve():
        raise ValueError("输出文件不能覆盖模板文件，请换一个 --output 路径。")

    template_workbook = load_workbook(template_path)
    if OUTPUT_SHEET not in template_workbook.sheetnames:
        raise ValueError(f"模板缺少 `{OUTPUT_SHEET}` 工作表。")

    output_sheet = template_workbook[OUTPUT_SHEET]
    headers = get_output_headers(output_sheet)
    config = load_fixed_config(template_workbook)
    rules, append_rules = load_case_rules(template_workbook, headers)

    input_workbook = load_workbook(input_path, data_only=True)
    input_sheet = get_input_sheet(input_workbook, input_sheet_name)
    case_rows = generate_case_rows(input_sheet, headers, config, rules, append_rules)

    max_col = len(headers)
    trim_output_columns(output_sheet, max_col)
    clear_output_rows(output_sheet, max_col)

    for offset, row_values in enumerate(case_rows, start=2):
        copy_template_row_style(output_sheet, 2, offset, max_col)
        for col, value in enumerate(row_values, start=1):
            output_sheet.cell(row=offset, column=col).value = value

    last_row = 1 + len(case_rows)
    trim_output_rows(output_sheet, max(2, last_row))
    update_tables(output_sheet, max_col, last_row)

    output_path.parent.mkdir(parents=True, exist_ok=True)
    template_workbook.save(output_path)
    return len(case_rows)


def parse_args(argv: list[str]) -> argparse.Namespace:
    parser = argparse.ArgumentParser(description="根据需求 Excel 和输出模板生成案例 Excel。")
    parser.add_argument(
        "--input",
        default=DEFAULT_INPUT_PATH,
        type=Path,
        help=f"输入需求 .xlsx 文件路径；默认 {DEFAULT_INPUT_PATH}",
    )
    parser.add_argument(
        "--template",
        default=DEFAULT_TEMPLATE_PATH,
        type=Path,
        help=f"输出模板 .xlsx 文件路径；默认 {DEFAULT_TEMPLATE_PATH}",
    )
    parser.add_argument(
        "--output",
        default=DEFAULT_OUTPUT_PATH,
        type=Path,
        help=f"生成结果 .xlsx 文件路径；默认 {DEFAULT_OUTPUT_PATH}",
    )
    parser.add_argument("--input-sheet", help="输入需求工作表名称；不填则读取活动工作表")
    return parser.parse_args(argv)


def main(argv: list[str] | None = None) -> int:
    args = parse_args(argv or sys.argv[1:])
    try:
        count = write_output(args.template, args.input, args.output, args.input_sheet)
    except Exception as exc:
        print(f"生成失败：{exc}", file=sys.stderr)
        return 1

    print(f"生成完成：{args.output}")
    print(f"有效案例行数：{count}")
    return 0


if __name__ == "__main__":
    raise SystemExit(main())