import openpyxlimport randomfrom openpyxl.utils import get_column_letter

# 定义文件路径file_path = 'D:\\temp\\Classinfo.xlsx'

# 打开现有的Excel工作簿和工作表try:wb = openpyxl.load_workbook(file_path)ws = wb.activeexcept FileNotFoundError:print(f"File {file_path} not found.")exit()

# 读取第2行样例信息sample_row = 2sample_data = [cell.value for cell in ws[1]]

# 定义姓氏列表surnames = ["李", "张", "刘", "王", "黄", "肖", "姚"]

# 随机生成学生数据for i in range(1, 101):# 从1到100生成100个学生信息# 学号student_id = f"NS{str(i).zfill(4)}"# 姓名surname = random.choice(surnames)name = f"{surname}{random.randint(0, 9)}"# 语文、数学、英语成绩scores = [random.randint(1, 100) for _ in range(3)]total_score = sum(scores)average_score = total_score / len(scores)
# 插入数据到工作表中new_row = [student_id, name] + scores + [total_score, average_score]ws.append(new_row)
# 输出执行信息print(f"Generated data for student {i}: {student_id}, {name}, Scores: {scores}, Total: {total_score}, Average: {average_score}")

# 保存工作簿wb.save(file_path)print("Data generation and file saving completed.")