我是这样统计成绩的 系列之二

这一次算的是单年的成绩。

我不“如果没选这门课，就不统计这门课的学分”，所以只好再写程序来解决了。

这回顺带着连加权成绩一起算了。

先读一个Excel，把所有科目的学分搜读出来。然后就是各种算各种输出了。

__author__ = 'haoyu'

import re
import xlrd
import csv


class Person:
    def __init__(self, id, name):
        self.id = id
        self.name = name
        self.grades = {}
        self.grades2 = {}
        self.grade2sum = 0
        self.courses = set([])
        self.courseGradeSum = 0


def addToList(id, name, course, grade, classGrade, studentList):
    if id not in studentList.keys():
        studentList[id] = Person(id, name)

    if course not in studentList[id].courses:
        studentList[id].courses.add(course)
        studentList[id].grades[course] = grade
        studentList[id].courseGradeSum += classGrade
        studentList[id].grades2[course] = float(grade) * classGrade
        studentList[id].grade2sum += studentList[id].grades2[course]
    return studentList


def readGrade(filename, courseNames):
    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)
        name = oneRow[0]
        grade = oneRow[1]
        courseNames[name] = grade
    return courseNames


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]
            classGrade = courseNames[course]
            studentList = addToList(id, name, course, grade, classGrade, studentList)
    return 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,
                   len(studentList[stu].courses), studentList[stu].grade2sum, studentList[stu].courseGradeSum, ""]
        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 = {}
    studentList = {}
    courseNames = readGrade("学分.xls", courseNames)
    studentList = grade3("only3.xls", courseNames, studentList)
    writeCSV("grade3.csv", courseNames, studentList)


if __name__ == "__main__":
    print('处理中……')
    main()
    print('处理完毕')

速度还好，效果还好。

有需要的可以拿去用。

PS：针对的是青果教务系统的成绩导出表。