使用python操作execl按照学生成绩进行蛇形分班

目录

• 说明
• 代码

说明

这几天担任助教工作，需要将270多名学生按照绩点蛇形分成24个小班，确保每个班男女人数、平均成绩基本相同。写了好几版，其他忘记保存了，基本功能用如下代码都可以完成。需要事先在execl中按照绩点排好序，当然也可以在代码中实现排序，不过要更麻烦一些。

代码

from openpyxl import load_workbook
#蛇形分班函数
def class_trans(row, class_num):
    if row < class_num:
        return row
    else:
        return class_num*2 - row - 1

wb = load_workbook(filename = 'grade.xlsx',data_only=True)
ws = wb['classone']

# class_num = input('请输入小班数量')
class_num = 24

nograde_male = 0 #没有绩点的男生
nograde_female = 0  #没有绩点的女生
grade_male = 0  #有绩点的男生
grade_female = 0 #有绩点的女生

#为每个小班创建一个sheet
for i in range(int(class_num)):
    wb.create_sheet('class'+str(i))

#为每一个小班创建一个记录人数的数组
list = [1 for col in range(int(class_num))]
print(list)

max_row = ws.max_row
max_column = ws.max_column


#男女独立、蛇形分班
#有绩点的女生
for i in range(2,max_row+1):
    cell = ws.cell(row = i,column = 10)
    print('DEBUG: iterating: %s ' % (ws.cell(row = i,column = 2).value))
    if(cell.value != '#N/A' and ws.cell(row = i,column =3).value == '女'):
        class_val = class_trans(grade_female, class_num)
        ws1 = wb['class'+str(class_val)]
        row = ws[i]
        temp = 1
        for cell in row:
            ce = ws1.cell(row = list[class_val],column = temp)
            ce.value = cell.value
            temp = temp + 1
        list[class_val] = list[class_val] + 1   
        grade_female = (grade_female + 1) % int(class_num*2)
#           print('DEBUG: copy %s to class%d(%d)' % (ws.cell(row = i,column =2).value, grade_male, list[grade_male]))
           
#没有绩点的女生
ungrade_female = grade_female#接着上次的队尾

for i in range(2,max_row+1):
    cell = ws.cell(row = i,column = 10)
    if(cell.value == '#N/A' and ws.cell(row = i,column =3).value == '女'):
        class_val = class_trans(nograde_female, class_num)
        ws1 = wb['class'+str(class_val)]
        row = ws[i]
        temp = 1
        for cell in row:
            ce = ws1.cell(row =  list[class_val],column = temp)
            ce.value = cell.value
            temp = temp + 1
        list[class_val] = list[class_val] + 1      
        nograde_female = (nograde_female + 1) % int(class_num*2)
    #             print('DEBUG: copy %s to class%d(%d)' % (ws.cell(row = i,column =2).value, grade_female, list[grade_female]))
            
#有绩点的男生
grade_male = 24

for i in range(2,max_row+1):
    cell = ws.cell(row = i,column = 10)
    if(cell.value != '#N/A' and ws.cell(row = i,column =3).value == '男'):   
        class_val = class_trans(grade_male, class_num)
        ws1 = wb['class'+str(class_val)]
        row = ws[i]
        temp = 1
        for cell in row:
            ce = ws1.cell(row = list[class_val],column = temp)
            ce.value = cell.value
            temp = temp + 1
        list[class_val] = list[class_val] + 1    
        grade_male = (grade_male + 1) % int(class_num*2)

#没有绩点的男生

nograde_male = grade_male#接上次队尾

for i in range(2,max_row+1):
    cell = ws.cell(row = i,column = 10)
    if(cell.value == '#N/A' and ws.cell(row = i,column =3).value == '男'): 
        class_val = class_trans(nograde_male, class_num)
        print('DEBUG: copy %s to class%d(%d)' % (ws.cell(row = i,column =2).value, class_val,list[class_val]))
        ws1 = wb['class'+str(class_val)]
        row = ws[i]
        temp = 1
        for cell in row:
            ce = ws1.cell(row = list[class_val],column = temp)
            ce.value = cell.value
            temp = temp + 1
        list[class_val] = list[class_val] + 1 
        nograde_male = (nograde_male + 1) % int(class_num*2)
    
    
    
   #
print(nograde_male, nograde_female, grade_male, grade_female)
print(list)

wb.save('res.xlsx')