西南交大学年成绩统计小程序2.0（python)

import xlrd
import xlwt
import xlutils.copy

class_names = [] #课程类别
id = [] #学号

将一张表划分为不同专业表

def divide_excel(res_path,obj_paths):
# 源文件表
res_file = xlrd.open_workbook(res_path)
res_sheet = res_file.sheet_by_index(0)#按索引获取第一张表

res_rows = res_sheet.nrows
res_cols = res_sheet.ncols

# 获取表格的第一行
res_row0 = res_sheet.row_values(0)

numtables = len(obj_paths)

# 创建目标文件表
obj_files = []
obj_sheets = []
for i in range(numtables):
    obj_files.append(xlwt.Workbook())
    obj_sheets.append(obj_files[i].add_sheet(obj_paths[i], cell_overwrite_ok=True))
    for j in range(res_cols):
        obj_sheets[i].write(0, j, res_row0[j])



#拷贝内容
res_row = 1 #res_sheet的行指针
obj_row = [1]*numtables #table指针list

while res_row<res_rows:
    major = res_sheet.cell_value(res_row, 1)
    for i in range(numtables):
        if obj_paths[i] in major:
            for j in range(res_cols):
                obj_sheets[i].write(obj_row[i], j, res_sheet.cell_value(res_row, j))
            obj_row[i] = obj_row[i] +1
    res_row= res_row+1
for i in range(numtables):
    obj_files[i].save(obj_paths[i] + ".csv")

按统计格式建表复制数据

def copy_data(path):
# 源文件表
global class_names
global id
res_file = xlrd.open_workbook(path + “.csv”)
res_sheet = res_file.sheet_by_index(0) # 按索引获取第一张表
res_rows = res_sheet.nrows
res_row = 1
class_names = []
while res_row < res_rows:
if res_sheet.cell_value(res_row, 5) not in class_names:
class_names.append(res_sheet.cell_value(res_row, 5)) #统计课程名称
res_row = res_row + 1

numclass  = len(class_names)

obj_file = xlwt.Workbook()  # 创建工作簿
obj_sheet = obj_file.add_sheet(path, cell_overwrite_ok=True)  # 创建sheet
# 序号 学号 班级 姓名  课程名称/学分  总分 总学分 平均分 排名 备注

# 写第一行
col = 0
templist = ["序号", "学号", "班级", "姓名"]
for i in range(len(templist)):
    obj_sheet.write(0, col, templist[i])
    col = col + 1
for name in class_names:
    obj_sheet.write(0, col, name)
    col = col + 1
    obj_sheet.write(0, col, "学分")
    col = col + 1
templist = ["总分", "总学分", "平均分", "排名", "备注"]
for i in range(len(templist)):
    obj_sheet.write(0, col, templist[i])
    col = col + 1


#填写数据
id = []
res_row = 1
obj_row = 1
while res_row < res_rows:
    No = res_sheet.cell_value(res_row, 2)
    if No not in id:
        id.append(No)
        obj_sheet.write(obj_row, 0, obj_row)  # 序号
        obj_sheet.write(obj_row, 1, No)  # 学号
        obj_sheet.write(obj_row, 2, res_sheet.cell_value(res_row, 1))  # 班级
        obj_sheet.write(obj_row, 3, res_sheet.cell_value(res_row, 3))  # 姓名
        Type = res_sheet.cell_value(res_row, 9)
        score = res_sheet.cell_value(res_row, 8)
        if Type == "正考":
            if score<60:
                obj_sheet.write(obj_row, 3 + numclass * 2 + 5, "不参加评奖评优")
            class_index = class_names.index(res_sheet.cell_value(res_row, 5))  # 找到课程名字在class_name的位置
            class_index = 3 + class_index * 2 + 1  # 计算课程名字在obj_sheet中的位置
            obj_sheet.write(obj_row, class_index, score)  # 课程分数
            class_index = class_index + 1 #计算学分再obj_sheet中的位置
            obj_sheet.write(obj_row, class_index, res_sheet.cell_value(res_row, 7))  # 课程学分
        else:
            if score >= 60:
                obj_sheet.write(obj_row, 3 + numclass * 2 + 5, "")
        obj_row = obj_row + 1
    else:
        id_index = id.index(No)  # 找到学号在id中的位置
        id_index = id_index + 1  # 计算学号在obj_sheet中的位置
        Type = res_sheet.cell_value(res_row, 9)
        score = res_sheet.cell_value(res_row, 8)
        if (Type == "正考"):
            if score < 60:
                obj_sheet.write(id_index, 3 + numclass * 2 + 5, "不参加评奖评优")
            class_index = class_names.index(res_sheet.cell_value(res_row, 5))  # 找到课程名字在class_name的位置
            class_index = 3 + class_index * 2 + 1  # 计算课程名字在obj_sheet中的位置
            obj_sheet.write(id_index, class_index, res_sheet.cell_value(res_row, 8))  # 课程分数
            class_index = class_index + 1
            obj_sheet.write(id_index, class_index, res_sheet.cell_value(res_row, 7))  # 课程学分
        else:
            if score >= 60:
                obj_sheet.write(id_index, 3 + numclass * 2 + 5, "")
    res_row = res_row + 1
obj_file.save(path + ".csv")  # python‘s shortcomming,can not write file while reading

def statistic(path):
global class_names
res_file = xlrd.open_workbook(path + “.csv”) # 拷贝表格算均分
obj_file = xlutils.copy.copy(res_file)
res_sheet = res_file.sheet_by_index(0)
obj_sheet = obj_file.get_sheet(0)

score = 0
credit = 0
total_credit = 0
total_score = 0
aver_score = 0
numclass  = len(class_names)
res_rows = res_sheet.nrows
res_row = 1
while res_row < res_rows:
    j = 4
    while j<4+numclass*2:
        score = res_sheet.cell_value(res_row, j)
        if type(score) == float:
            credit = res_sheet.cell_value(res_row, j + 1)
            total_score = total_score + credit * score
            total_credit = total_credit + credit
        j = j + 2
    aver_score = total_score / total_credit
    obj_sheet.write(res_row, 3 + numclass * 2 + 1, total_score)
    obj_sheet.write(res_row, 3 + numclass * 2 + 2, total_credit)
    obj_sheet.write(res_row, 3 + numclass * 2 + 3, aver_score)
    total_credit = 0
    total_score = 0
    res_row = res_row + 1
obj_file.save(path + ".csv")

def rank(path):
global class_names
res_file = xlrd.open_workbook(path + “.csv”) # 拷贝文件排名
res_sheet = res_file.sheet_by_index(0)

res_rows = res_sheet.nrows
res_cols = res_sheet.ncols
numclass = len(class_names)

dic = {} #生成字典  序号：成绩
res_row = 1
while res_row < res_rows:
    No = res_sheet.cell_value(res_row, 0)
    aver_score = res_sheet.cell_value(res_row, 3 + numclass * 2 + 3)
    dic[No] = aver_score
    res_row = res_row + 1

dic = sorted(dic.items(), key=lambda x: x[1], reverse=True) #排序

#建表
obj_file = xlwt.Workbook()
obj_sheet = obj_file.add_sheet(path, cell_overwrite_ok=True)

#按排名誊写数据
for j in range(res_cols):
    obj_sheet.write(0, j, res_sheet.cell_value(0, j))
obj_row = 1
r = 1
for d in dic:
    index = int(d[0])
    for j in range(res_cols):
        obj_sheet.write(obj_row, j, res_sheet.cell_value(index, j))
    obj_sheet.write(obj_row, res_cols - 2, r)
    r = r+1
    obj_row = obj_row + 1

#重新写序号
res_row = 1
while res_row < res_rows:
    obj_sheet.write(res_row, 0, res_row)
    res_row = res_row + 1
obj_file.save(path + ".csv")

def test():
l = [3, 5, 9, 4, 6, 1]
print(l.index(3))

if name == ‘main‘:
res_path = “薇姐班2016级学年成绩.xlsx”
obj_paths = [“微电”, “自动”, “物联”]
divide_excel(res_path, obj_paths)
for path in obj_paths:
copy_data(path)
statistic(path)
rank(path)
# test()