Python3 操作Excel - 学生信息管理系统测试数据

Python3 操作Excel - 学生信息管理系统测试数据

测试数据生成

1. 基础学生信息表

from openpyxl import Workbook
from openpyxl.styles import Font, Alignment
import random
from faker import Faker

# 初始化Faker生成随机数据
fake = Faker('zh_CN')

# 创建学生信息表
def create_student_info(file_name="学生信息表.xlsx", num_students=50):
    wb = Workbook()
    ws = wb.active
    ws.title = "学生基本信息"
    
    # 设置表头
    headers = ["学号", "姓名", "性别", "年龄", "班级", "联系电话", "邮箱", "家庭住址"]
    ws.append(headers)
    
    # 设置表头样式
    for cell in ws[1]:
        cell.font = Font(bold=True)
        cell.alignment = Alignment(horizontal='center')
    
    # 生成学生数据
    for i in range(1, num_students+1):
        student_id = f"2023{str(i).zfill(4)}"  # 学号格式：20230001
        name = fake.name()
        gender = random.choice(["男", "女"])
        age = random.randint(16, 22)
        class_name = f"高三({random.randint(1, 12)})班"
        phone = fake.phone_number()
        email = f"{student_id}@school.edu.cn"
        address = fake.address()
        
        ws.append([student_id, name, gender, age, class_name, phone, email, address])
    
    # 自动调整列宽
    for col in ws.columns:
        max_length = 0
        column = col[0].column_letter
        for cell in col:
            try:
                if len(str(cell.value)) > max_length:
                    max_length = len(str(cell.value))
            except:
                pass
        adjusted_width = (max_length + 2) * 1.2
        ws.column_dimensions[column].width = adjusted_width
    
    wb.save(file_name)
    print(f"已生成学生信息表: {file_name}")

2. 学生成绩表

def create_student_scores(file_name="学生成绩表.xlsx", num_students=50):
    wb = Workbook()
    ws = wb.active
    ws.title = "期末考试成绩"
    
    # 设置表头
    headers = ["学号", "姓名", "班级", "语文", "数学", "英语", "物理", "化学", "生物", "总分", "平均分", "排名"]
    ws.append(headers)
    
    # 设置表头样式
    for cell in ws[1]:
        cell.font = Font(bold=True)
        cell.alignment = Alignment(horizontal='center')
    
    # 生成成绩数据
    scores_data = []
    for i in range(1, num_students+1):
        student_id = f"2023{str(i).zfill(4)}"
        name = fake.name()
        class_name = f"高三({random.randint(1, 12)})班"
        
        # 生成各科成绩(0-100分)
        chinese = random.randint(60, 98)
        math = random.randint(50, 100)
        english = random.randint(55, 99)
        physics = random.randint(40, 95) if random.random() > 0.2 else 0  # 20%概率不选物理
        chemistry = random.randint(45, 97) if random.random() > 0.2 else 0
        biology = random.randint(50, 96) if random.random() > 0.2 else 0
        
        total = chinese + math + english + physics + chemistry + biology
        average = round(total / 6, 1)
        
        scores_data.append({
            "学号": student_id,
            "姓名": name,
            "班级": class_name,
            "语文": chinese,
            "数学": math,
            "英语": english,
            "物理": physics,
            "化学": chemistry,
            "生物": biology,
            "总分": total,
            "平均分": average
        })
    
    # 按总分排序
    scores_data.sort(key=lambda x: x["总分"], reverse=True)
    
    # 添加排名并写入工作表
    for rank, student in enumerate(scores_data, start=1):
        row = [
            student["学号"],
            student["姓名"],
            student["班级"],
            student["语文"],
            student["数学"],
            student["英语"],
            student["物理"],
            student["化学"],
            student["生物"],
            student["总分"],
            student["平均分"],
            rank
        ]
        ws.append(row)
    
    # 设置数字格式和样式
    for row in ws.iter_rows(min_row=2, max_row=num_students+1, min_col=4, max_col=12):
        for cell in row:
            cell.alignment = Alignment(horizontal='center')
    
    # 自动调整列宽
    for col in ws.columns:
        max_length = 0
        column = col[0].column_letter
        for cell in col:
            try:
                if len(str(cell.value)) > max_length:
                    max_length = len(str(cell.value))
            except:
                pass
        adjusted_width = (max_length + 2) * 1.2
        ws.column_dimensions[column].width = adjusted_width
    
    wb.save(file_name)
    print(f"已生成学生成绩表: {file_name}")

3. 学生考勤表

def create_attendance_record(file_name="学生考勤表.xlsx", num_students=50, days=30):
    wb = Workbook()
    ws = wb.active
    ws.title = "9月考勤记录"
    
    # 设置表头
    headers = ["学号", "姓名", "班级"] + [f"9月{day}日" for day in range(1, days+1)] + ["出勤天数", "缺勤天数"]
    ws.append(headers)
    
    # 设置表头样式
    for cell in ws[1]:
        cell.font = Font(bold=True)
        cell.alignment = Alignment(horizontal='center')
    
    # 生成考勤数据
    for i in range(1, num_students+1):
        student_id = f"2023{str(i).zfill(4)}"
        name = fake.name()
        class_name = f"高三({random.randint(1, 12)})班"
        
        # 生成每日考勤状态 (1=出勤, 0=缺勤)
        attendance = [random.choices([1, 0], weights=[0.95, 0.05])[0] for _ in range(days)]
        present_days = sum(attendance)
        absent_days = days - present_days
        
        row = [student_id, name, class_name] + attendance + [present_days, absent_days]
        ws.append(row)
    
    # 设置数字格式和样式
    for row in ws.iter_rows(min_row=2, max_row=num_students+1, min_col=4, max_col=days+5):
        for cell in row:
            cell.alignment = Alignment(horizontal='center')
            if cell.value == 0 and cell.column <= days+3:  # 缺勤标记为红色
                cell.font = Font(color="FF0000")
    
    # 自动调整列宽
    for col in ws.columns:
        max_length = 0
        column = col[0].column_letter
        for cell in col:
            try:
                if len(str(cell.value)) > max_length:
                    max_length = len(str(cell.value))
            except:
                pass
        adjusted_width = (max_length + 2) * 1.2
        ws.column_dimensions[column].width = adjusted_width
    
    wb.save(file_name)
    print(f"已生成学生考勤表: {file_name}")

使用示例

if __name__ == "__main__":
    # 生成三种测试表格
    create_student_info(num_students=100)
    create_student_scores(num_students=100)
    create_attendance_record(num_students=100, days=30)
    
    print("所有测试数据已生成完毕！")

生成的数据说明

1. 学生信息表 包含：

   • 学号、姓名、性别、年龄、班级、联系电话、邮箱、家庭住址
   • 50-100条随机学生记录

2. 学生成绩表 包含：

   • 学号、姓名、班级、各科成绩、总分、平均分和排名
   • 成绩数据真实模拟了学生成绩分布
   • 自动计算总分、平均分和排名

3. 学生考勤表 包含：

   • 学号、姓名、班级、每日考勤状态
   • 出勤天数、缺勤天数的统计
   • 缺勤日期会用红色标记

这些测试数据可以用于：

• Excel操作练习
• 数据分析演示
• 学生管理系统开发测试
• 数据可视化项目