用python计算班级学生年龄_用python3统计数据我们学校的德育加分数据

[Python] 纯文本查看 复制代码while True:# 学院统计

import xlrd

import xlwt

import pandas as pd

from os import remove

college = input('请问你当前要筛选的学院是？')

doc = input('请输入数据库的文件名(带后缀)')

outdoc = input('请输入你要输出的文件名(.xls)')

data = xlrd.open_workbook(doc) # 打开d数据库

dataout = xlwt.Workbook(encoding='utf-8')

dataout_sheet = dataout.add_sheet('result')

line = 0

sheet = data.sheet_by_index(0) # 打开data.xlsx中第一个表

nrows = sheet.nrows # 获取表的行数

Class = ['班级'] # 存放该学院班级的列表

Notclass = [] # 存放不是该学院的班级列表

# 第一步，找出该学院的相关数据

def writein(x, l, s):

fanwei = len(x)

for i in range(fanwei):

s.write(l, i, x[i])

print('-----------------------------------开始从数据中筛选班级------------------------')

for i in range(nrows): # 表中的第i行

idata = sheet.row_values(i) # 获取第i行数据

iclass = idata[0] # 获取班级单元格的值

eleclass = iclass[0:2] # 获取前两个字(也就是班级专业简称)

isclass = False

notclass = False

for y in Class:

if y == eleclass:

isclass = True

for y in Notclass:

if y == eleclass:

notclass = True

if isclass:

writein(idata, line, dataout_sheet)

line += 1

if notclass == False and isclass == False:

ask = input('请问是不是的班级？(y/n):')

if ask == 'y':

Class.append(eleclass)

writein(idata, line, dataout_sheet)

line += 1

elif ask == 'n':

Notclass.append(eleclass)

dataout.save('step.xls')

print('-----------------------------------筛选完毕------------------------')

# 第二步，就当前数据按照姓名，班级，日期进行排序

print('-----------------------------------开始给数据排序------------------------')

data = pd.read_excel('step.xls') # 用pd打开excel

data.sort_values(by=['姓名', '班级', '日期'], inplace=True)

data.to_excel('step1.xlsx') # 输出排序后的结果

print('-----------------------------------计算时长与加分(记得请将加分细则填入表2)------------------------')

# 第三步，准备根据时长总和排序

data = xlrd.open_workbook('step1.xlsx') # 打开数据库

sheet = data.sheet_by_index(0) # 打开第一个表

nrows = sheet.nrows # 读取数据表行数

start = 0

end = 0

timetotal = 0 # 该学生总时长

newdoc = xlwt.Workbook(encoding='utf-8') # 新表(存储总时长和姓名)

newsheet = newdoc.add_sheet('result')

line = 1 # 实时更新新表写入数据的行数

newsheet.write(0, 0, '姓名')

newsheet.write(0, 1, '时长')

for i in range(1, nrows-1): # 因为数据表第一行不是数据，所以从第二行开始

idata = sheet.row_values(i)

iname = idata[2]

y = i + 1

ydata = sheet.row_values(y)

yname = ydata[2]

if i < nrows:

if iname == yname:

timetotal += idata[5]

else:

timetotal += idata[5]

newsheet.write(line, 0, iname)

newsheet.write(line, 1, timetotal) # 纪录这个学生的总时长

timetotal = 0

line += 1

timetotal += idata[5] # 在i=nrows时，没有i+1了，所以独立出来再写一次

newsheet.write(line, 0, iname)

newsheet.write(line, 1, timetotal)

newdoc.save('step2.xls')

data = pd.read_excel('step2.xls') # 用pd打开excel

data.sort_values(by='时长', inplace=True, ascending=False)

data.to_excel('step3.xlsx') # 输出排序后的结果

remove('step.xls')

remove('step2.xls') # 清理一下之前的文件

# 第四步 在原数据表的表2进行时长与分数的换算

print('-----------------------------------准备写入最终数据------------------------')

def score(x):

lable2 = xlrd.open_workbook('data.xlsx')

sheet2 = lable2.sheet_by_index(1)

lines = sheet2.nrows

for i in range(1, lines):

idata = sheet2.row_values(i)

maxtime = float(idata[0])

if maxtime >= x:

return float(idata[1])

# 第五步 以step1.xlsx和step3.xlsx为参考，最终成表

data = xlrd.open_workbook('step3.xlsx') # 打开新表

sheet = data.sheet_by_index(0)

nrows = sheet.nrows # 获取新表行数

data2 = xlrd.open_workbook('step1.xlsx')

sheet2 = data2.sheet_by_index(0)

nrows2 = sheet2.nrows # 旧表行数

result = xlwt.Workbook(encoding='utf-8') # 最终数据库

result_sheet = result.add_sheet('result', cell_overwrite_ok=True) # 结果表

x = sheet2.row_values(0)

style = xlwt.XFStyle()

al = xlwt.Alignment()

al.horz = 0x02

al.vert = 0x01

style.alignment = al

def wrotein(data, line, doc):

lenline = len(data)

for i in range(lenline - 1):

doc.write(line, i, data[i + 1], style)

print('-----------------------------------写入'+data[2]+'的数据------------------------')

wrotein(x, 0, result_sheet) # 写入表头

line = 1 # 从第一行开始写入数据

for i in range(1, nrows): # 新表中遍历

idata = sheet.row_values(i) # 新表第i行

iname = idata[1] # 姓名

iscore = score(idata[2]) # 总时长

place = False # 判断是否找对了地方

for y in range(1, nrows2):

ydata = sheet2.row_values(y)

yname = ydata[2]

if place == False and iname == yname:

wrotein(ydata, line, result_sheet)

place = True

step = 0

start = line

Place = y

line += 1

elif place == True and iname == yname:

wrotein(ydata, line, result_sheet)

step += 1

line += 1

if place == True and iname != yname:

place = False

break

the_data = sheet2.row_values(Place)

theclass = the_data[1]

thename = the_data[2]

thescore = str(iscore)

result_sheet.write_merge(start, start + step, 0, 0, theclass, style)

result_sheet.write_merge(start, start + step, 1, 1, thename, style)

result_sheet.write_merge(start, start + step, 5, 5, thescore, style)

print('-----------------------------------合并相同数据------------------------')

remove('step1.xlsx')

remove('step3.xlsx')

result.save(outdoc + '.xls')

print('--------------------------------------------------------------------------')

print('---------------------------------------------------------------------------请记得修改日期的单元格格式')