QQ 群作业汇总

homework_summary.py

from openpyxl import Workbook
from openpyxl.styles import Color, PatternFill, Font, Border, colors, fills
from openpyxl import load_workbook
from datetime import datetime

def parse_sheet(wb):
    students = []
    for i, r in enumerate(wb[wb.sheetnames[0]].rows):
        if i <= 1 or r[0].value == None or '助教' in r[0].value:
            continue

        # 学生姓名       r[0]
        # 作业评分和状态  r[1]
        # 作业提交时间   r[2]
        # 评语          r[3]
        name_id_str = r[0].value
        name_id_str = name_id_str.replace(' ', '').replace('+', '').replace('-', '')

        id_str = name_id_str[:3]
        name_str = name_id_str[3:]

        review_state_str = r[1].value

        #if review_state_str == '待批改' or review_state_str == '未提交':
        #    print(name_id_str)

        submit_time_str = '2020-09-09 00:00:00'
        if r[2].value != None:
            submit_time_str = str(r[2].value)
        submit_time = datetime.fromisoformat(submit_time_str)

        note_str = ''
        if r[3].value != None:
            note_str = r[3].value

        students.append((id_str, name_str, review_state_str, submit_time, note_str))
    return students

names = []

with open('names.csv', 'rt') as f:
    for i, l in enumerate(f):
        if i == 0:
            continue
        info = l.split(',')
        names.append((int(info[0]), info[2]))

def check(info_tuples, students):
    info_set = set(info_tuples)
    for s in students:
        t = (int(s[0]), s[1])
        if t in info_set:
            info_set.remove(t)
        else:
            print("{} is not on the list".format(t))

    for i in info_set:
        print("{} is not covered".format(i))
        
from os import listdir
from os.path import isfile, join
fs = [f for f in listdir('./') if isfile(join('./', f)) and '2020' in f]

summary = {}

for n in names:
    summary[n[0]] = {
        'name': n[1],
        'assignments': [],
    }

for f in fs:
    print("begin processing {}".format(f))
    wb = load_workbook(f)
    students = parse_sheet(wb)
    check(names, students)
    
    for s in students:
        summary[int(s[0])].get('assignments').append((f[:9], s[2], s[3], s[4]))

# for k in summary:
#     print("{} {}".format(k, summary[k]))
    
wb = Workbook()
summary_name = 'summary.xlsx'

ws = wb.active
ws.title = '作业提交情况'

for row, k in enumerate(summary):
    _ = ws.cell(column=1, row=row+1, value="{}".format(k))
    _ = ws.cell(column=2, row=row+1, value="{}".format(summary[k].get('name')))
    col = 3
    for c, a in enumerate(summary[k].get('assignments')):
        ## ('20200921.', 'B', datetime.datetime(2020, 9, 21, 22, 1), '照片还需逆时针旋转90度')
        ## ft = Font(color="FF0000")
        c = ws.cell(column=col + c, row=row+1, value="提交时间 {}\n\n批改情况 {}\n\n{}".format(a[2], a[1], a[3]))
        ## c.font = ft
        red = colors.Color(rgb='FF0000')
        red_fill = fills.PatternFill(patternType='solid', fgColor=my_red)
        if a[1] == '待批改' or a[1] == '未提交':
            c.fill = red_fill
#     for col in range(0, 3):
#         _ = ws.cell(column=col+1, row=row+1, value="{}".format(summary[k].get('name')))

# for row in range(1, len(summary) + 1):
#     for col in range(1, 3):
#         _ = ws.cell(column=col, row=row, value="{}".format(summary[row].get('name')))

wb.save(filename=summary_name)
# wb = load_workbook('20200921.xlsx')
# students = parse_sheet(wb)
# check(names, students)


# for i, s in enumerate(students):
#     print("{} {}".format(i, s))

# s = set(range(1, 126))

# for n in s:
#     print(n)