[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('请问<' + eleclass + '>是不是<' + college + '>的班级？(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('---------------------------------------------------------------------------请记得修改日期的单元格格式')
发表于 2020-3-9 12:07
|
发表于 2020-3-10 11:12
