#!/usr/bin/env python3 """ Generate seating charts from Excel class data. Required input columns: 班级, 姓名, 性别, 总分 """ import argparse import random import sys from pathlib import Path try: from openpyxl import Workbook from openpyxl.styles import Alignment, Border, Font, PatternFill, Side from openpyxl.utils import get_column_letter except ImportError: print("Error: openpyxl is required. Install with: pip install openpyxl") sys.exit(1) def parse_args(): parser = argparse.ArgumentParser( description="根据Excel学生数据生成座位表" ) parser.add_argument( "excel_file", type=str, help="输入Excel文件路径 (.xlsx)" ) parser.add_argument( "-c", "--class", dest="class_name", type=str, required=True, help="要生成座位表的班级名称" ) parser.add_argument( "-s", "--style", type=str, required=True, help="座位表样式，如 '2222'（4组2人桌）、'3332'（3组3人桌+1组2人桌）" ) parser.add_argument( "-m", "--allow-mixed", action="store_true", help="允许男女生同桌（默认不允许）" ) parser.add_argument( "-t", "--strategy", type=str, default="good-with-good", choices=["good-with-good", "good-with-bad", "random"], help="成绩配对策略：good-with-good（好+好）、good-with-bad（好+差）、random（无关）" ) parser.add_argument( "-o", "--output", type=str, default=None, help="输出Excel文件路径（默认：<输入文件>_<班级>_座位表.xlsx）" ) parser.add_argument( "-n", "--count", type=int, default=1, help="生成座位表的个数（默认1个）" ) parser.add_argument( "--seed", type=int, default=None, help="随机种子（用于 random 策略的可重复性）" ) parser.add_argument( "-S", "--sheet", type=str, default=None, help="指定要读取的工作表名称（默认：第一个工作表）" ) parser.add_argument( "--usage", action="help", help="显示帮助信息" ) return parser.parse_args() def read_students(filepath, class_name, sheet_name=None): """从Excel读取学生数据，按班级筛选。自动查找表头行。""" from openpyxl import load_workbook wb = load_workbook(filepath, data_only=True) if sheet_name: if sheet_name not in wb.sheetnames: print(f"错误：工作表 '{sheet_name}' 不存在。可用工作表：{wb.sheetnames}") sys.exit(1) ws = wb[sheet_name] else: ws = wb[wb.sheetnames[0]] required = ["班级", "姓名", "性别", "总分"] indices = {} header_row = None # 自动查找包含所有必需列的表头行（最多扫描前10行） for r in range(1, min(ws.max_row + 1, 11)): row_vals = [] for col in range(1, ws.max_column + 1): val = ws.cell(row=r, column=col).value row_vals.append(str(val).strip() if val else "") if all(col_name in row_vals for col_name in required): for col_name in required: indices[col_name] = row_vals.index(col_name) header_row = r break if header_row is None: # 输出前几行帮助诊断 print("错误：Excel文件中未找到 '班级'、'姓名'、'性别'、'总分' 列。") for r in range(1, min(ws.max_row + 1, 6)): preview = [str(ws.cell(row=r, column=c).value or "") for c in range(1, ws.max_column + 1)] print(f" 第{r}行：{preview}") sys.exit(1) # 规范化班级名：去除"班"后缀以便灵活匹配 class_name_clean = class_name.rstrip("班") data_start = header_row + 1 students = [] for row in range(data_start, ws.max_row + 1): class_val = str(ws.cell(row=row, column=indices["班级"] + 1).value or "").strip() class_val_clean = class_val.rstrip("班") if class_val_clean != class_name_clean: continue name = str(ws.cell(row=row, column=indices["姓名"] + 1).value or "").strip() gender = str(ws.cell(row=row, column=indices["性别"] + 1).value or "").strip() score_str = str(ws.cell(row=row, column=indices["总分"] + 1).value or "").strip() if not name: continue try: score = float(score_str) except ValueError: score = 0.0 students.append({"name": name, "gender": gender, "score": score}) wb.close() return students def interleave_high_low(items): """将按分数降序排列的列表重新排列为 高-低-高-低 交错顺序。""" result = [] left, right = 0, len(items) - 1 while left <= right: result.append(items[left]) left += 1 if left <= right: result.append(items[right]) right -= 1 return result def build_desk_groups(style_str, num_students): """根据样式字符串构建桌组大小列表。""" sizes = [int(c) for c in style_str] for s in sizes: if s < 1: print(f"错误：样式中的数字必须大于0，发现 '{s}'") sys.exit(1) row_capacity = sum(sizes) num_rows = max(1, (num_students + row_capacity - 1) // row_capacity) desk_groups = [] for _ in range(num_rows): for size in sizes: desk_groups.append(size) return desk_groups def is_male(gender): return gender in ("男", "M", "m", "male", "Male") def is_female(gender): return gender in ("女", "F", "f", "female", "Female") def allocate_students(students, desk_sizes, strategy, allow_mixed, seed): """将学生分配到桌组。""" if seed is not None: random.seed(seed) males = [s for s in students if is_male(s["gender"])] females = [s for s in students if is_female(s["gender"])] others = [s for s in students if not is_male(s["gender"]) and not is_female(s["gender"])] # 无法识别性别的归入人数多的一方 if others: if len(males) >= len(females): males.extend(others) else: females.extend(others) # --- 允许混坐：所有学生一起排 --- if allow_mixed: all_students = males + females if strategy == "good-with-good": all_students.sort(key=lambda s: s["score"], reverse=True) elif strategy == "good-with-bad": all_students.sort(key=lambda s: s["score"], reverse=True) all_students = interleave_high_low(all_students) else: random.shuffle(all_students) groups = [] idx = 0 for size in desk_sizes: group = all_students[idx:idx + size] if group: groups.append(group) idx += size if idx >= len(all_students): break return groups # --- 性别分开排 --- if strategy == "good-with-good": males.sort(key=lambda s: s["score"], reverse=True) females.sort(key=lambda s: s["score"], reverse=True) elif strategy == "good-with-bad": males.sort(key=lambda s: s["score"], reverse=True) males = interleave_high_low(males) females.sort(key=lambda s: s["score"], reverse=True) females = interleave_high_low(females) else: random.shuffle(males) random.shuffle(females) groups = [] m_idx, f_idx = 0, 0 for size in desk_sizes: m_rem = len(males) - m_idx f_rem = len(females) - f_idx if m_rem >= f_rem: take = min(size, m_rem) group = males[m_idx:m_idx + take] m_idx += take else: take = min(size, f_rem) group = females[f_idx:f_idx + take] f_idx += take if group: groups.append(group) return groups def generate_excel(groups, style_str, output_path, class_name): """生成座位表Excel文件。""" wb = Workbook() ws = wb.active ws.title = f"{class_name}座位表" sizes = [int(c) for c in style_str] num_groups_per_row = len(sizes) num_rows = (len(groups) + num_groups_per_row - 1) // num_groups_per_row total_cols = sum(sizes) + (num_groups_per_row - 1) thin_border = Border( left=Side(style="thin"), right=Side(style="thin"), top=Side(style="thin"), bottom=Side(style="thin"), ) center_align = Alignment(horizontal="center", vertical="center", wrap_text=True) male_fill = PatternFill(start_color="D6EAF8", end_color="D6EAF8", fill_type="solid") female_fill = PatternFill(start_color="FADBD8", end_color="FADBD8", fill_type="solid") podium_fill = PatternFill(start_color="D5D8DC", end_color="D5D8DC", fill_type="solid") podium_font = Font(bold=True, size=14) name_font = Font(size=14) # score_font kept for reference but not used separately # --- 讲台行 --- ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=total_cols) podium_cell = ws.cell(row=1, column=1, value="讲 台") podium_cell.font = podium_font podium_cell.alignment = center_align podium_cell.fill = podium_fill for c in range(1, total_cols + 1): ws.cell(row=1, column=c).fill = podium_fill ws.cell(row=1, column=c).border = thin_border ws.row_dimensions[1].height = 35 # --- 空行 --- # (直接将学生放在第3行及以下) # --- 学生座位 --- group_idx = 0 for row_num in range(num_rows): excel_row = row_num + 3 # row 1 = 讲台, row 2 = blank col_offset = 1 for g in range(num_groups_per_row): size = sizes[g] if group_idx < len(groups): group = groups[group_idx] group_idx += 1 for i in range(size): col = col_offset + i cell = ws.cell(row=excel_row, column=col) if i < len(group): student = group[i] cell.value = student['name'] cell.font = name_font cell.alignment = center_align if is_male(student["gender"]): cell.fill = male_fill elif is_female(student["gender"]): cell.fill = female_fill else: cell.value = "" cell.border = thin_border ws.column_dimensions[get_column_letter(col)].width = 13 else: for i in range(size): col = col_offset + i cell = ws.cell(row=excel_row, column=col) cell.border = thin_border ws.column_dimensions[get_column_letter(col)].width = 13 col_offset += size # 过道 if g < num_groups_per_row - 1: aisle_col = col_offset ws.column_dimensions[get_column_letter(aisle_col)].width = 4 col_offset += 1 ws.row_dimensions[excel_row].height = 36 wb.save(output_path) print(f"座位表已保存至：{output_path}") def main(): args = parse_args() # 验证样式 if not args.style.isdigit() or len(args.style) < 2: print("错误：样式必须为纯数字字符串，至少2位，如 '2222' 或 '3332'") sys.exit(1) input_path = Path(args.excel_file) if not input_path.exists(): print(f"错误：文件不存在：{args.excel_file}") sys.exit(1) if args.count < 1: print("错误：生成个数必须大于0") sys.exit(1) # 确定输出路径模板 if args.output: output_base = args.output else: output_base = f"{input_path.stem}_{args.class_name}_座位表.xlsx" # 读取学生（只读一次） print(f"正在读取 {args.excel_file} ...") students = read_students(args.excel_file, args.class_name, args.sheet) if not students: print(f"警告：班级 '{args.class_name}' 中没有找到任何学生，将生成空座位表。") else: m_count = sum(1 for s in students if is_male(s["gender"])) f_count = sum(1 for s in students if is_female(s["gender"])) print(f"班级 '{args.class_name}' 共有 {len(students)} 名学生（男：{m_count}，女：{f_count}）") # 构建桌组（只算一次） desk_sizes = build_desk_groups(args.style, len(students)) # 确定基础种子 base_seed = args.seed if args.seed is not None else random.randint(0, 999999) strategy_names = { "good-with-good": "好+好", "good-with-bad": "好+差", "random": "与成绩无关", } # 生成多个座位表 for i in range(args.count): seed = base_seed + i # 输出文件名：多个时加序号后缀 if args.count > 1: p = Path(output_base) output_path = str(p.parent / f"{p.stem}_{i + 1}{p.suffix}") else: output_path = output_base groups = allocate_students(students, desk_sizes, args.strategy, args.allow_mixed, seed) generate_excel(groups, args.style, output_path, args.class_name) # 摘要 print(f"\n座位表配置：") print(f" 样式：{args.style}（{len(args.style)}组，每组{'-'.join(args.style)}人）") print(f" 策略：{strategy_names[args.strategy]}") print(f" 男女生同桌：{'允许' if args.allow_mixed else '不允许'}") print(f" 生成个数：{args.count}") print(f" 总桌组数：{len(groups)}，总排数：{(len(groups) + len(args.style) - 1) // len(args.style)}") if __name__ == "__main__": main()