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


OUTPUT_SHEET = "案例模板"
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_DIRECT = "直接映射"

COMPUTED_PRIORITY_FIELD = "案例优先级"
INPUT_CONTEXT_FIELDS = (
    "主题名称",
    "数据项分类",
    "数据项名称",
)
DEFAULT_THEME_LIST_SHEET = "主题清单"
DEFAULT_THEME_NAME_COLUMN = 3
DEFAULT_ITEM_CATEGORY_COLUMN = 1
DEFAULT_ITEM_NAME_COLUMN = 2
COMPUTED_FIELD_NAMES = {
    COMPUTED_PRIORITY_FIELD,
}
SKIP_ELEMENT_TYPES = {
    "业务连续数据准确性",
}
DEFAULT_CASE_PRIORITY = "中"
THEME_PRIORITY_MAP = {
    "日期维": "中",
    "高频指标维": "中",
    "基础信息维": "中",
    "估值维": "中",
    "估值信息维": "中",
    "债券信息维": "中",
    "债项维": "中",
    "发行人维": "中",
    "业务连续数据准确性": "高",
    "数据准确性": "高",
    "信息准确性": "高",
}
PHYSICAL_TABLE_PRIORITY_MAP = {
    "债券代码": "中",
    "ISIN码": "中",
    "债券简称": "中",
    "债券全称": "中",
    "债券分类（统计月报）": "高",
    "债券统计分类（一级）": "高",
    "债券统计分类（二级）": "高",
    "债券监测分类（一级）": "高",
    "债券监测分类（二级）": "高",
    "年": "中",
    "季": "中",
    "月": "中",
    "日": "中",
}

# 输出字段的默认生成规则。
# 也可以在模板“配置”页里写同名字段覆盖这些默认规则：
#   写普通文字：每一行都输出固定值，例如“验证：新建数据集”。
#   写占位符：按输入行替换，例如“验证：主题要素_{要素名称}字段展示顺序”。
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 column_index_to_letter(column_index: int) -> str:
    """把 Excel 列号转成列字母，避免依赖某些环境里的 utils 导入。"""
    if column_index < 1:
        raise ValueError(f"列号必须大于 0：{column_index}")

    result = []
    current = column_index
    while current > 0:
        current, remainder = divmod(current - 1, 26)
        result.append(chr(ord("A") + remainder))
    return "".join(reversed(result))


def get_merged_cell_value(sheet, row: int, column: int) -> str:
    """读取单元格值；如果该格属于合并区域，则回退到合并区域左上角的值。"""
    value = clean(sheet.cell(row=row, column=column).value)
    if value:
        return value

    for merged_range in sheet.merged_cells.ranges:
        if (
            merged_range.min_row <= row <= merged_range.max_row
            and merged_range.min_col <= column <= merged_range.max_col
        ):
            return clean(sheet.cell(row=merged_range.min_row, column=merged_range.min_col).value)

    return ""


def parse_input_column_index(value: str, config_field_name: str) -> int:
    text = clean(value)
    if not text.isdigit() or int(text) < 1:
        raise ValueError(f"`{config_field_name}` 的取值内容必须是大于 0 的列号，例如 2、3、5、6。")
    return int(text)


def get_theme_list_sheet_name(workbook) -> str:
    if DEFAULT_THEME_LIST_SHEET not in workbook.sheetnames:
        raise ValueError(f"输入文件缺少主题清单工作表：{DEFAULT_THEME_LIST_SHEET}")
    return DEFAULT_THEME_LIST_SHEET


def get_theme_name_column() -> int:
    return DEFAULT_THEME_NAME_COLUMN


def get_item_columns() -> dict[str, int]:
    return {
        "数据项分类": DEFAULT_ITEM_CATEGORY_COLUMN,
        "数据项名称": DEFAULT_ITEM_NAME_COLUMN,
    }


def load_input_records(workbook, config: dict[str, dict[str, str]]) -> list[dict[str, str]]:
    """按最新格式读取输入：主题清单 C 列主题名 -> 对应主题 sheet 的 A/B 列。"""
    theme_list_sheet_name = get_theme_list_sheet_name(workbook)
    theme_name_column = get_theme_name_column()
    item_columns = get_item_columns()
    theme_list_sheet = workbook[theme_list_sheet_name]
    records: list[dict[str, str]] = []

    for row in range(2, theme_list_sheet.max_row + 1):
        theme_name = get_merged_cell_value(theme_list_sheet, row, theme_name_column)
        if not theme_name:
            continue
        if theme_name not in workbook.sheetnames:
            print(f"跳过主题清单第 {row} 行：主题名称 `{theme_name}` 没有对应工作表。")
            continue

        theme_sheet = workbook[theme_name]
        last_item_category = ""
        for item_row in range(2, theme_sheet.max_row + 1):
            if row_is_empty(theme_sheet, item_row):
                continue

            item_category = get_merged_cell_value(theme_sheet, item_row, item_columns["数据项分类"])
            item_name = get_merged_cell_value(theme_sheet, item_row, item_columns["数据项名称"])
            if item_category:
                last_item_category = item_category
            else:
                item_category = last_item_category

            if not item_category and not item_name:
                continue

            records.append(
                {
                    "主题名称": theme_name,
                    "数据项分类": item_category,
                    "数据项名称": item_name,
                }
            )

    return records


def build_lookup_tokens(value: str) -> list[str]:
    """把路径样式文本拆成可匹配的候选词，便于命中优先级映射。"""
    text = clean(value)
    if not text:
        return []

    tokens = [text]
    for sep in ("\\", "/", "\n"):
        next_tokens: list[str] = []
        for token in tokens:
            next_tokens.extend(part for part in token.split(sep) if part)
        tokens = next_tokens

    seen: set[str] = set()
    result: list[str] = []
    for token in tokens:
        normalized = clean(token)
        if normalized and normalized not in seen:
            seen.add(normalized)
            result.append(normalized)
    return result


def lookup_priority(values: list[str], mapping: dict[str, str]) -> str:
    for value in values:
        for token in build_lookup_tokens(value):
            if token in mapping:
                return mapping[token]
    return ""


def resolve_case_priority(context: dict[str, str], rule: dict[str, str]) -> str:
    """按截图里的“主题/物理表”规则，给当前案例生成优先级。"""
    theme_priority = lookup_priority(
        [
            rule.get("*功能/流程名称", ""),
            context.get("数据项分类", ""),
        ],
        THEME_PRIORITY_MAP,
    )
    if theme_priority:
        return theme_priority

    physical_priority = lookup_priority(
        [
            context.get("数据项名称", ""),
        ],
        PHYSICAL_TABLE_PRIORITY_MAP,
    )
    if physical_priority:
        return physical_priority

    return DEFAULT_CASE_PRIORITY


VALID_DIRECT_INPUT_FIELDS = set(INPUT_CONTEXT_FIELDS)


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_DIRECT}:
            raise ValueError(f"`{CONFIG_SHEET}` 第 {row} 行字段 `{field_name}` 的取值模式无效：{mode}，可选：固定/字段拼接/直接映射")
        if not source:
            raise ValueError(f"`{CONFIG_SHEET}` 第 {row} 行字段 `{field_name}` 的取值内容不能为空。")

        if mode == CONFIG_MODE_DIRECT and source not in VALID_DIRECT_INPUT_FIELDS and source not in COMPUTED_FIELD_NAMES:
            raise ValueError(
                f"`{CONFIG_SHEET}` 第 {row} 行字段 `{field_name}` 的直接映射来源无效：{source}。"
                f" 可选值：{', '.join([*VALID_DIRECT_INPUT_FIELDS, *COMPUTED_FIELD_NAMES])}"
            )

        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_DIRECT:
            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 build_formula_columns(sheet, headers: list[str], template_row: int) -> dict[str, str]:
    """识别模板行里的固定公式列；这些列生成时保留公式，不走配置覆盖。"""
    formula_columns: dict[str, str] = {}
    for col_index, header in enumerate(headers, start=1):
        value = sheet.cell(row=template_row, column=col_index).value
        if isinstance(value, str) and value.startswith("="):
            formula_columns[header] = value
    return formula_columns


def shift_formula_rows(formula: str, row_offset: int) -> str:
    """把模板公式中的相对行号按输出行偏移，保留绝对行号。"""
    if row_offset == 0:
        return formula

    pattern = re.compile(r"(?P<col>\$?[A-Z]{1,3})(?P<row>\$?\d+)")

    def replace(match: re.Match[str]) -> str:
        col_part = match.group("col")
        row_part = match.group("row")
        if row_part.startswith("$"):
            return f"{col_part}{row_part}"
        return f"{col_part}{int(row_part) + row_offset}"

    return pattern.sub(replace, formula)


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:{column_index_to_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_records: list[dict[str, str]],
    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_item_category = ""
    last_item_name = ""
    if not rules:
        # 没有“用例规则”页时保持旧逻辑：一行输入只生成一行案例。
        rules = [{}]

    for input_index, input_record in enumerate(input_records, start=1):
        theme_name = input_record["主题名称"]
        item_category = input_record["数据项分类"]
        item_name = input_record["数据项名称"]
        if item_category in SKIP_ELEMENT_TYPES:
            print(f"跳过主题记录第 {input_index} 行：数据项分类 `{item_category}` 不生成案例。")
            continue
        if item_category:
            last_item_category = item_category
        if item_name:
            last_item_name = item_name

        context = {
            "主题名称": theme_name,
            "数据项分类": item_category,
            "数据项名称": item_name,
            COMPUTED_PRIORITY_FIELD: resolve_case_priority(
                {
                    "主题名称": theme_name,
                    "数据项分类": item_category,
                    "数据项名称": item_name,
                },
                {},
            ),
            # 序号在每条展开规则里再设置，因为它按输出案例行顺延。
            "序号": "",
        }

        for rule_index, rule in enumerate(rules, start=1):
            context[COMPUTED_PRIORITY_FIELD] = resolve_case_priority(context, rule)
            field_templates = build_field_templates(headers, config, rule)
            missing = []
            if not item_category and any(template_needs(value, "数据项分类") for value in field_templates.values()):
                missing.append("A列数据项分类")
            if not item_name and any(template_needs(value, "数据项名称") for value in field_templates.values()):
                missing.append("B列数据项名称")
            if missing:
                # 缺少当前规则需要的关键字段时，只跳过这一条展开规则。
                print(
                    f"跳过主题记录第 {input_index} 行/规则第 {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_item_category,
            "数据项名称": last_item_name,
            COMPUTED_PRIORITY_FIELD: "",
            "序号": f"{serial:04d}",
        }
        context[COMPUTED_PRIORITY_FIELD] = resolve_case_priority(context, rule)
        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 generate_case_rows_by_theme(
    input_records: list[dict[str, str]],
    headers: list[str],
    config: dict[str, str],
    rules: list[dict[str, str]],
    append_rules: list[dict[str, str]],
) -> dict[str, list[list[str]]]:
    grouped: dict[str, list[dict[str, str]]] = {}
    for record in input_records:
        grouped.setdefault(record["主题名称"], []).append(record)

    result: dict[str, list[list[str]]] = {}
    for theme_name, theme_records in grouped.items():
        result[theme_name] = generate_case_rows(theme_records, headers, config, rules, append_rules)
    return result


def populate_output_sheet(
    sheet,
    headers: list[str],
    row_values_list: list[list[str]],
) -> None:
    formula_columns = build_formula_columns(sheet, headers, template_row=2)
    max_col = len(headers)
    trim_output_columns(sheet, max_col)
    clear_output_rows(sheet, max_col)

    for offset, row_values in enumerate(row_values_list, start=2):
        copy_template_row_style(sheet, 2, offset, max_col)
        for col, value in enumerate(row_values, start=1):
            header = headers[col - 1]
            target_cell = sheet.cell(row=offset, column=col)
            if header in formula_columns:
                target_cell.value = shift_formula_rows(formula_columns[header], offset - 2)
            else:
                target_cell.value = value

    last_row = 1 + len(row_values_list)
    trim_output_rows(sheet, max(2, last_row))
    update_tables(sheet, max_col, last_row)


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_records = load_input_records(input_workbook, config)
    case_rows_by_theme = generate_case_rows_by_theme(input_records, headers, config, rules, append_rules)

    for sheet_name in list(template_workbook.sheetnames):
        if sheet_name != OUTPUT_SHEET:
            template_workbook.remove(template_workbook[sheet_name])

    theme_names = list(case_rows_by_theme)
    if not theme_names:
        populate_output_sheet(output_sheet, headers, [])
    else:
        output_sheet.sheet_state = "hidden"

        for theme_name in theme_names:
            theme_sheet = template_workbook.copy_worksheet(output_sheet)
            theme_sheet.title = theme_name
            populate_output_sheet(theme_sheet, headers, case_rows_by_theme[theme_name])

        template_workbook.remove(output_sheet)

    output_path.parent.mkdir(parents=True, exist_ok=True)
    template_workbook.save(output_path)
    return sum(len(rows) for rows in case_rows_by_theme.values())


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