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()