-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathall_course.py
104 lines (98 loc) · 4.38 KB
/
all_course.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
#!/usr/bin/env python
#-*- coding:utf-8 -*-
import xlrd
import xlwt
except_name = ['105220001','111220009','111220070','111220158','111220151','115220001','111220102','111220101','111220025','101220161','091220095','101220049','101220082','101220107']
speical_name = ['111220033','111220112','111220005','111100027','111220018','111220153','111170028','111220095','111220196','111220107','111220073','111250148','111250105','111220172','111220127','111220075','111220171','111220081','111242059','111242033']
num2name = {}
def input_grade(name,dict):
data = xlrd.open_workbook(name)
table = data.sheets()[0]
nrows = table.nrows
for i in range(1, nrows):
if cmp(table.cell(i, 4).value, u'注销') != 0 and table.cell(i,5).value and float(table.cell(i,5).value) >= 60:
st_num = table.cell(i, 0).value
if not dict.get(st_num):
dict[st_num] = {}
num2name[st_num] = table.cell(i,1).value
course_name = table.cell(i, 2).value
if table.cell(i,6).value:
str = table.cell(i,6).value
str_array = str.split()
index = 0
for k in str_array[0]:
if k.isdigit() or k.isalpha():
index += 1
else:
break;
str_array[1] = str_array[0][index+1]
str_array[0] = str_array[0][0:index]
# print str_array[0],str_array[1],type(dict[st_num])
if dict.get(str_array[0]):
dict[st_num][str_array[0]][1] = table.cell(i,5).value
else:
dict[st_num][str_array[0]] = []
if(str_array[1].isdigit()):
dict[st_num][str_array[0]].append(str_array[1])
else:
dict[st_num][str_array[0]].append(table.cell(i,3).value)
dict[st_num][str_array[0]].append(table.cell(i,5).value)
if dict.get(course_name):
dict[st_num][course_name][1] = table.cell(i, 5).value
else:
dict[st_num][course_name] = [table.cell(i, 3).value, table.cell(i, 5).value]
if __name__ == '__main__':
dict = {}
grade_file = xlwt.Workbook()
grade_table = grade_file.add_sheet('sheet0')
# course_file = xlwt.Workbook()
# course_table = course_file.add_sheet('sheet0')
course_table = grade_file.add_sheet('sheet1')
grade_table.write(0,0,u'学号')
grade_table.write(0,1,u'姓名')
grade_table.write(0,2,'GPA')
course_table.write(0,0,u'学号')
course_table.write(0,1,u'课程编号')
course_table.write(0,2,u'学分')
course_table.write(0,3,u'总评')
# input_grade('大零上成绩.xls',dict)
# input_grade('大零下成绩.xls',dict)
input_grade('大一上成绩.xls',dict)
input_grade('大一下成绩.xls',dict)
input_grade('大二上成绩.xls',dict)
input_grade('大二下成绩.xls',dict)
input_grade('大三上成绩.xls',dict)
input_grade('大三下成绩.xls',dict)
input_grade('1.xls',dict)
input_grade('2.xls',dict)
input_grade('3.xls',dict)
input_grade('4.xls',dict)
# input_grade('12.xls',dict)
# input_grade('22.xls',dict)
# input_grade('32.xls',dict)
# input_grade('42.xls',dict)
# input_grade('2011a.xls',dict)
grade_flag = 1
course_flag = 1
for i in dict:
sum = 0.0
credit = 0.0
if i in except_name or i not in speical_name:
continue
for j in dict[i]:
course_table.write(course_flag,0,i.decode('utf-8'))
course_table.write(course_flag,1,j.decode('utf-8'))
course_table.write(course_flag,2,dict[i][j][0])
course_table.write(course_flag,3,dict[i][j][1])
course_flag += 1
# print i,j,dict[i][j][0],dict[i][j][1]
# print dict[i][j][0]," ",dict[i][j][1]
credit += float(dict[i][j][0])
sum += (float(dict[i][j][1]) * float(dict[i][j][0]))
grade_table.write(grade_flag,0,i)
grade_table.write(grade_flag,1,num2name[i])
grade_table.write(grade_flag,2,(sum+0.0)/(credit*20))
grade_flag += 1
grade_file.save('../grade/拔尖班三年综合学分绩.xls')
# course_file.save('../grade/学分绩计算课程.xls')
# print "%s %s %f %f %f" % (i,num2name[i],(sum+0.0)/(credit*20),credit,sum)