WorkBuddy 辅助开发随机试卷生成系统：800 题题库秒出卷，openpyxl 踩坑全记录

| 序号 | 题型     | 题目                    | 选项A    | 选项B    | 选项C    | 选项D    | 答案 |
|------|---------|------------------------|---------|---------|---------|---------|------|
| 1    | 单选题   | 接触网额定电压是？       | 25kV    | 27.5kV  | 35kV    | 10kV    | B    |
| 2    | 多选题   | 接触网由哪些组成？       | 支柱    | 腕臂    | 接触线  | 钢轨    | ABC  |
| 3    | 判断题   | 接触网是高压设备         | —       | —       | —       | —       | √    |

from openpyxl import load_workbook
from pathlib import Path

def load_question_bank(file_path: str) -> dict:
    """读取题库 xlsx，返回按题型分类的题目列表"""
    wb = load_workbook(str(Path(file_path)))
    ws = wb.active
    
    # 读表头，自动建立列名→列号的映射
    headers = {}
    for col_idx, cell in enumerate(ws[1], 1):
        if cell.value:
            headers[cell.value.strip()] = col_idx
    
    # 根据实际列名匹配字段（兼容不同题库的表头叫法）
    col_map = {}
    for field, aliases in [
        ('type', ['题型', '题目类型', '类别']),
        ('question', ['题目', '题干', '问题']),
        ('A', ['选项A', 'A', '选项 A']),
        ('B', ['选项B', 'B', '选项 B']),
        ('C', ['选项C', 'C', '选项 C']),
        ('D', ['选项D', 'D', '选项 D']),
        ('answer', ['答案', '正确答案', '参考答案']),
    ]:
        for alias in aliases:
            if alias in headers:
                col_map[field] = headers[alias]
                break
    
    # 按题型归类
    banks = {'单选题': [], '多选题': [], '判断题': []}
    for row in ws.iter_rows(min_row=2, values_only=False):
        qtype = str(row[col_map.get('type', 1) - 1].value or '').strip()
        if qtype not in banks:
            continue
        
        question = {
            'question': str(row[col_map.get('question', 2) - 1].value or ''),
            'A': str(row[col_map.get('A', 3) - 1].value or ''),
            'B': str(row[col_map.get('B', 4) - 1].value or ''),
            'C': str(row[col_map.get('C', 5) - 1].value or ''),
            'D': str(row[col_map.get('D', 6) - 1].value or ''),
            'answer': str(row[col_map.get('answer', 7) - 1].value or '').strip(),
            'type': qtype,
        }
        banks[qtype].append(question)
    
    return banks

import random

def generate_paper(bank: dict, config: dict) -> list:
    """从题库中按配置随机抽取题目"""
    questions = []
    
    for qtype, count in config.items():
        if qtype not in bank or len(bank[qtype]) < count:
            raise ValueError(f"「{qtype}」题库只有 {len(bank.get(qtype, []))} 题，"}
                             f"要求抽取 {count} 题，不够！")
        selected = random.sample(bank[qtype], count)
        questions.extend(selected)
    
    # 打乱题型顺序，让试卷看起来自然
    random.shuffle(questions)
    return questions

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

def export_paper(questions: list, output_path: str, title: str = "专业培训考试试卷"):
    """将抽取的题目输出为格式化的试卷 xlsx"""
    wb = Workbook()
    ws = wb.active
    ws.title = "试卷"
    
    thin_border = Border(
        left=Side(style='thin'),
        right=Side(style='thin'),
        top=Side(style='thin'),
        bottom=Side(style='thin')
    )
    
    # 标题行
    ws.merge_cells('A1:F1')
    title_cell = ws.cell(row=1, column=1, value=title)
    title_cell.font = Font(size=16, bold=True)
    title_cell.alignment = Alignment(horizontal='center', vertical='center')
    
    # 信息行
    info_row = 2
    ws.cell(row=info_row, column=1, value="姓名：").font = Font(size=11)
    ws.cell(row=info_row, column=3, value="部门：").font = Font(size=11)
    ws.cell(row=info_row, column=5, value="得分：").font = Font(size=11)
    
    question_num = {'单选题': 0, '多选题': 0, '判断题': 0, '总计': 0}
    row = 4  # 从第4行开始输出题目
    
    for q in questions:
        question_num['总计'] += 1
        question_num[q['type']] += 1
        
        # 题目
        ws.merge_cells(start_row=row, start_column=1, end_row=row, end_column=6)
        num = question_num[q['type']]
        if q['type'] == '单选题':
            prefix = f"{question_num['总计']}. 【单选】"
        elif q['type'] == '多选题':
            prefix = f"{question_num['总计']}. 【多选】"
        else:
            prefix = f"{question_num['总计']}. 【判断】"
        
        qcell = ws.cell(row=row, column=1, value=f"{prefix} {q['question']}")
        qcell.font = Font(size=11)
        qcell.alignment = Alignment(wrap_text=True)
        ws.row_dimensions[row].height = 25
        row += 1
        
        # 选项（判断题不显示选项）
        if q['type'] != '判断题':
            for opt in ['A', 'B', 'C', 'D']:
                if q.get(opt):
                    ws.cell(row=row, column=1, value=f"  {opt}. {q[opt]}").font = Font(size=11)
                    row += 1
        
        row += 1  # 题间空行
    
    # 单独输出答案表
    ws_ans = wb.create_sheet("参考答案")
    ws_ans.cell(row=1, column=1, value="题号").font = Font(bold=True)
    ws_ans.cell(row=1, column=2, value="题型").font = Font(bold=True)
    ws_ans.cell(row=1, column=3, value="答案").font = Font(bold=True)
    
    for i, q in enumerate(questions, 1):
        ws_ans.cell(row=i+1, column=1, value=i)
        ws_ans.cell(row=i+1, column=2, value=q['type'])
        ws_ans.cell(row=i+1, column=3, value=q['answer'])
    
    wb.save(output_path)

# ❌ 会报错
wb = load_workbook("题库/接触网题库.xlsx")

# ✅ 用 pathlib.Path 包装一下
from pathlib import Path
wb = load_workbook(str(Path("题库/接触网题库.xlsx")))

def smart_config(bank: dict, desired: dict) -> dict:
    """智能调整抽题配置：如果某题型不够，自动降到题库上限"""
    adjusted = {}
    for qtype, count in desired.items():
        available = len(bank.get(qtype, []))
        adjusted[qtype] = min(count, available)
        if adjusted[qtype] < count:
            print(f"⚠️ {qtype} 题库仅有 {available} 题，已自动调整为 {adjusted[qtype]} 题")
    return adjusted

{
    "题库路径": "题库/接触网题库.xlsx",
    "抽题配置": {
        "单选题": 40,
        "多选题": 20,
        "判断题": 15
    },
    "试卷标题": "接触网专业培训考试试卷",
    "输出目录": "output/"
}

@echo off
cd /d %~dp0
python paper_generator.py
echo.
echo 试卷已生成在 output 目录下
pause