神坑：班长实习去了，学委不会用excel……净来坑副班长啊！
我没有教务导出的三年总成绩单，有的只是每年导员发的成绩单。三年三种样式，每年的人还不固定（大二又转来好多人）。
现在任务是，利用已有信息，将所有人的成绩汇总，形成一份三年的总成绩单，然后再算其他的东西。
大一的成绩是这样的（导员自己统计的成绩）：
大二的成绩是这样的（正方教务系统导出的成绩）：
大三的成绩是这样的（青果教务系统导出的成绩）：
所以第一件事是，将大三的成绩“横过来”。第一想法是只利用excel，复制、选择性粘贴，勾选“转置”，这样一个人的成绩就成了以前的样式了。但是还是不方便：全班87人，此操作得手工87遍，并且即使转过来了，也不好进行统计。
然后就想拿编程解决了……
网上找了一下，python有专门的库来读写取excel和csv文件。这就好办了。
然后理一下思路：对于每个人，我需要保存他的全部成绩。不同的两个人之间，科目还不一样。那就这样：用一个set记录下当前所有已知的科目名称，输出的时候，要是某人没有修这门课，就输出个“空”。
然后定义数据结构：
学号是唯一的，在三种样式的成绩单里面都出现了。那就把学号当作key，做成一个字典。
对于每一个人，也再做一个字典吧：key是课程名称，value是分数。因为有些成绩单内看不出学分是多少，就不再定义学分的存储位置了，输出出来之后手动添加学分吧。
class Person:
def __init__(self, id, name):
self.id = id
self.name = name
self.grades = {}
self.courses = set([])
然后是如何实现的事情了：
对于大三的成绩：
打开excel文件，分别读出每一行。
对于每一行，提取出学号、姓名、科目、分数。
如果不存在这个人，那么在名单列表里加入这个人。
找到这个人，如果某个课不在key列表里，说明这个课是第一次出现。第一次出现的都是初修成绩～～存起来。如果前面出现过了，说明现在这个成绩要么是补考成绩，要么是重修成绩，不用保存。
对于大二、大一的成绩单，处理方法类似。不再详述。
输出的时候，因为实在看不懂如何写xls文件，就直接输出成了csv文件了。如果修了某门课，输出成绩，否则输出个“空”。顺带着把科目排了一下序，学号排了一下序。但是！Python里面字符串“小”的定义是什么啊！有电看不懂……
然后又发现，大二的成绩上面，科目后面有个方括号，里面写着学分。因为其他俩单子里面都得不到学分，只好把这个信息舍弃了。用正则表达式替换掉这个方括号即可。
完整代码在此：
__author__ = 'haoyu'
import re
import xlrd
import csv
from PersonInfo import Person
def addToList(id, name, course, grade, studentList):
if (len(str(grade)) == 0):
return studentList
if id not in studentList.keys():
studentList[id] = Person(id, name)
if course not in studentList[id].courses:
studentList[id].grades[course] = grade
studentList[id].courses.add(course)
return studentList
def grade1(fileName, courseNames, studentList):
rawCSVData = xlrd.open_workbook(fileName)
rawCSVTable = rawCSVData.sheets()[0]
nrows = rawCSVTable.nrows # 行数
rowTitle = rawCSVTable.row_values(1)
ncols = len(rowTitle) # 列数
for i in range(3, len(rowTitle) - 3):
rowTitle[i], foo = re.subn("(\s*\[\s*[\d\.]+\s*\])|\s", "", rowTitle[i])
courseNames = set(rowTitle[3:-3])
for rownum in range(2, nrows):
oneRow = rawCSVTable.row_values(rownum)
id = oneRow[1]
name = oneRow[2]
for i in range(3, ncols - 3):
course = rowTitle[i]
grade = oneRow[i]
studentList = addToList(id, name, course, grade, studentList)
return courseNames, studentList
def grade2(fileName, courseNames, studentList):
rawCSVData = xlrd.open_workbook(fileName)
rawCSVTable = rawCSVData.sheets()[0]
nrows = rawCSVTable.nrows # 行数
rowTitle = rawCSVTable.row_values(0)
ncols = len(rowTitle) # 列数
for i in range(2, len(rowTitle)):
rowTitle[i], foo = re.subn("(\s*\[\s*[\d\.]+\s*\])|\s", "", rowTitle[i])
courseNames.add(rowTitle[i])
for rownum in range(1, nrows):
oneRow = rawCSVTable.row_values(rownum)
id = oneRow[0]
name = oneRow[1]
for i in range(2, ncols):
course = rowTitle[i]
grade = oneRow[i]
studentList = addToList(id, name, course, grade, studentList)
return courseNames, studentList
def grade3(fileName, courseNames, studentList):
rawCSVData = xlrd.open_workbook(fileName)
rawCSVTable = rawCSVData.sheets()[0]
nrows = rawCSVTable.nrows # 行数
ncols = rawCSVTable.ncols # 列数
for rownum in range(1, nrows):
oneRow = rawCSVTable.row_values(rownum)
if oneRow:
if oneRow[3] != "计算机科学与技术":
continue
id = oneRow[4]
name = oneRow[5]
course = oneRow[8]
grade = oneRow[9]
courseNames.add(course)
studentList = addToList(id, name, course, grade, studentList)
return courseNames, studentList
def writeCSV(fileName, courseNames, studentList):
writer = csv.writer(open(fileName, "wt"), quoting=csv.QUOTE_ALL)
nameList = list(studentList.keys())
nameList.sort()
courseList = list(courseNames)
courseList.sort()
writer.writerow(["学号", "姓名"] + courseList)
for stu in nameList:
oneData = [studentList[stu].id, studentList[stu].name]
for course in courseList:
if course in studentList[stu].courses:
oneData.append(studentList[stu].grades[course])
else:
oneData.append("")
writer.writerow(oneData)
def main():
courseNames = set([])
studentList = {}
courseNames, studentList = grade1("1.xls", courseNames, studentList)
courseNames, studentList = grade2("2.xls", courseNames, studentList)
courseNames, studentList = grade3("3.xls", courseNames, studentList)
writeCSV("result.csv", courseNames, studentList)
if __name__ == "__main__":
print('处理中……')
main()
print('处理完毕')
grad1函数用于统计导员自己统计的成绩
grad2函数用于统计正方教务系统导出的成绩
grad3函数用于统计青果教务系统导出的成绩
如有需要可以直接拿来用，稍加修改即可
最后费尽周折拿到了三年的导出成绩，都是大三的样式的，于是代码里面就只用了第三种的。
实测速度很可观（1秒就出结果），也能满足我的需要了。
excel有了，开始统计其他信息：
不及格的怎么快速找出来？选中所有数值区域，单元格样式，选“自定义”，输入公式“[红色][<60]"不及格"”，这样所有不及格的都会变为红色的“不及格”。删除掉这些行。或者的，选择“条件格式”-“小于”，输入60，换一个样式，也是OK的。
加权成绩怎么办？有公式：“SUMPRODUCT”，或者直接可以做矩阵的点乘，但好像出错了……我是Office2016。
然后是“vlookup”：第一个参数是“拿谁进行比较”，第二个参数是“哪个范围的第一列里找”，第三个参数是“如果查找到了，返回哪一列上的值”，第四个参数是“是否启用模糊匹配”。注意“只在第一列进行查找”，“去哪里查找”相当于一个小表了。这个函数很方便，但是第一次用的话很纠结，尤其是那个范围的含义。自己体会一下，我说不清楚。哦还有一个他的孪生兄弟“hlookup”我没有用过。
唯一没有实现自动的是，校选课，我输入校选课的名字，后面自动查找这个人的分数，再自动去某个地方把学分取过来。因为校选课的表比较稀疏，就手动来了。人多的话必须得找自动的公式啊啊啊！
其他的就没什么了。
其实想吐槽一下Office2016太智能了……有些东西不应该自增的，它却自作主张来了个自增。还得一个一个手动改回来。
87人，每个人82个数据，整整忙了一天。最后终于出结果了。
写在最后：教务系统害死人，以后绝对不做教育管理的东西！
发表回复