higuoxing · October 12, 2020 08:29
diff --git a/homework_summary.py b/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)
	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)