Skip to content

Instantly share code, notes, and snippets.

@higuoxing
Last active October 12, 2020 08:29
Show Gist options
  • Save higuoxing/bb4d21a31f117f4733c7bc0f66199a23 to your computer and use it in GitHub Desktop.
Save higuoxing/bb4d21a31f117f4733c7bc0f66199a23 to your computer and use it in GitHub Desktop.
QQ 群作业汇总
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)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment