Python编程实现学生随机分配导师

 要实现这个操作，主要有三个部分

1、读入表格信息

2、按要求分配导师

3、在结果表格中写入信息

看似简单，实则不难

不过，我在实现这个的时候，采用了边读边写的策略（有点绕）

那我们就分开来吧！

用xlrd包

op1 = xlrd.open_workbook("填写要读取的文件的路径")
sheet1 = op1.sheet_by_name('填写要读取的表格名称')

这样就把要读取的文件打开啦~

读取数据就是一边读一边存入列表，利用列表来存储，用循环来实现

student = []
for i in range(1, sheet1.nrows): 
    cells1 = sheet1.row_values(i)

用openpyxl包

说到这个包，在找到这个包之前，我又踩了很多坑，试了很多次，还是这个最顺手

op = openpyxl.load_workbook("填入被填写的表格的路径")
sheet = op.active

 按行列把值读入

  sheet.cell(i + 1, 1).value = cells1[1]
  sheet.cell(i + 1, 2).value = cells1[0]
  sheet.cell(i + 1, 3).value = cells1[2]

最后不要忘记保存表格并关闭

op.save("填写被写入的表格路径")

这是最主要的部分

大体思路就是存入每个老师辅导的学生数→随机选择老师→选到一次，老师的学生数就减一（若学生数减到0了，就重新选择老师）

i = 1
teacher_num = random.randint(0, 6)  # 随机分配老师
while num_students[teacher_num] >= 1:
    sheet.cell(i + 1, 4).value = teacher[teacher_num]
    num_students[teacher_num] = num_students[teacher_num] - 1
    # print(teacher_num, num_students[teacher_num])
    teacher_num = random.randint(0, 6)
    while num_students[teacher_num] == 0:
        teacher_num = random.randint(0, 6)
    i = i + 1
    if i == sheet1.nrows - 1:
        break

好啦~基本上就写好啦

接下来是最完整的代码，准备好了吗？

import xlrd
import openpyxl
import random

op1 = xlrd.open_workbook("C:\Users\15263\Desktop\待分配学生模板buu.xlsx")
sheet1 = op1.sheet_by_name('待分配学生列表')
op = openpyxl.load_workbook("C:\Users\15263\Desktop\随机分配结果模板buu.xlsx")
sheet = op.active
student = []
for i in range(1, sheet1.nrows):  # 读入学生数据之后写入到表格中
    cells1 = sheet1.row_values(i)
    sheet.cell(i + 1, 1).value = cells1[1]
    sheet.cell(i + 1, 2).value = cells1[0]
    sheet.cell(i + 1, 3).value = cells1[2]

op2 = xlrd.open_workbook("C:\Users\15263\Desktop\1_待分配导师模板buu(1)(1).xlsx")
sheet2 = op2.sheet_by_name('待分配教师列表')
teacher = []
num_students = []
for i in range(1, sheet2.nrows):  # 读入教师数据并对其随机分配
    cells2 = sheet2.row_values(i)
    num_student = int(cells2[1])  # 存入每个教师辅导学生的个数
    num_students.append(num_student)
    teacher.append(cells2[0])  # 存入每个老师

i = 1
teacher_num = random.randint(0, 6)  # 随机分配老师
while num_students[teacher_num] >= 1:
    sheet.cell(i + 1, 4).value = teacher[teacher_num]
    num_students[teacher_num] = num_students[teacher_num] - 1
    # print(teacher_num, num_students[teacher_num])
    teacher_num = random.randint(0, 6)
    while num_students[teacher_num] == 0:
        teacher_num = random.randint(0, 6)
    i = i + 1
    if i == sheet1.nrows - 1:
        break

op.save("C:\Users\15263\Desktop\随机分配结果模板buu.xlsx")

注意：

路径一定要改成自己文件的路径！