背景：310名學生分組匯報，每組最多8人胆屿。已經(jīng)自由組隊了34組（有的不足8人）奥喻。
已知：全部名單、自由組隊名單
問題：將剩余學生進行分組
輸出：分組名單非迹、dict｛學生:組號｝便于登記分數(shù)
分析：關(guān)鍵--找出未分組的學生环鲤。
顯然是一個集合運算，但未找到excel相關(guān)功能憎兽，因此考慮腳本代碼實現(xiàn)
- 創(chuàng)建二維列表
li = [[] for i in range(40+1)] - python excel讀冷离、寫的操作對象是分開獨立的
- str判斷中文字符
import xlrd
xls = xlrd.open_workbook('21自辨.xls')
sheets = xls.sheets() # [0]打開第一張表
"""
traverse read sheet[1]
output: dict[str: int] + 已有組數(shù)int
"""
def filter_name(name):
if not (u'\u4e00' <=name[-1] <= u'\u9fff'):
name=name[:-1]
return name
def name_to_group_ind(table):
name_dict={}
nrows = table.nrows # 行數(shù)
for i in range(1, nrows):
for cell in table.row_values(i)[1:]:
if cell:
name=filter_name(cell[:3])
if name in name_dict.keys():
print(f"{i}組{cell}增加失敵趁啊：與{name_dict[name]}組{name}重復！")
else:
name_dict[name]=i
print(f"{len(name_dict)}人已完成西剥，{nrows-1}組已分組痹栖，余{(nrows-1)*8-len(name_dict)}空位")
return name_dict, nrows
name_dict, nrows = name_to_group_ind(sheets[1])
"""
traverse write sheet[0]
"""
from xlutils.copy import copy
# xlsnew.save('21自辨-t.xls')
def write_while_group_continue(table, base, name_dict, xls_wt): # rd\wt是兩個對象
groups=[[] for i in range(40+1)]
table_wt = xls_wt.get_sheet(0)
cnt=0
table_wt.write(0,4,"分組")
for r in range(1, table.nrows):
name=table.row_values(r)[2][:3]
if name in name_dict.keys():
# print(f"{name}:{name_dict.get(name)}")
g_id=name_dict.get(name)
table_wt.write(r,4,g_id)
groups[g_id].append(name)
else:
g_id=cnt//8+base
cnt+=1
# print(f"{name}:未分組{g_id}-{cnt}")
table_wt.write(r, 4, g_id)
groups[g_id].append(name)
assert len(name_dict)+cnt== table.nrows-1
print(f"{cnt}人再分組，新增{cnt//8}組")
#####
table2_wt = xls_wt.get_sheet(1)
for r in range(base, len(groups)):
for i in range(len(groups[r])):
print(r,i+1,groups[r][i])
table2_wt.write(r,i+1,groups[r][i])
xls_wt.save('21自辨分組.xls')
return groups
groups=write_while_group_continue(sheets[0], nrows, name_dict, copy(xls))
