import os
import xlwt, xlrd
from xlutils.copy import copy
import basics as ba
def newold_show():
"""用于new和old的显示"""
print("****************************")
print("\t 1:导出全部信息")
print("\t 2:导出学生信息")
print("\t 3:导出课程信息")
print("\t 4:导出成绩信息")
print("\t 5:导出排序信息")
print("\t 0:返回导出菜单")
print("****************************\n")
def write_all( cn, file, count):
"""导出全部信息到xls文件中"""
write_students( cn, file, count)
write_courses( cn, file, 1)
write_reports( cn, file, 1)
print("全部信息到入%s完毕" % file)
def write_students( cn, file, count):
"""导出学生信息到xls文件中"""
if count == 0:
new_workbook = xlwt.Workbook()
new_sheet = new_workbook.add_sheet("Students")
else:
wb = xlrd.open_workbook( file,formatting_info=True)
newb = copy(wb)
try:
new_sheet = newb.add_sheet("Students")
except Exception:
print("已经存在Students表，请重新输入")
return
# 写名称，在表格的第一行
new_sheet.write(0,0,"Sno")
new_sheet.write(0,1,"Sname")
new_sheet.write(0,2,"Sgender")
new_sheet.write(0,3,"Sbirth")
new_sheet.write(0,4,"Sclass")
new_sheet.write(0,5,"Snative")
sql = '''SELECT DISTINCT Sno, Sname, Sgender,
Sbirth, Sclass, Snative
FROM Students
ORDER BY Sno ASC
'''
information = ba.common( cn, sql)
for i, item in enumerate( information, start=1):
for j in range( 0, 6):
new_sheet.write( i, j, item[j])
if count == 0:
new_workbook.save( file)
else:
newb.save( file)
print("学生信息到入%s完毕" % file)
def write_courses( cn, file, count):
"""导出课程信息到xls文件中"""
if count == 0:
new_workbook = xlwt.Workbook()
new_sheet = new_workbook.add_sheet("Courses")
else:
wb = xlrd.open_workbook( file,formatting_info=True)
newb = copy(wb)
try:
new_sheet = newb.add_sheet("Courses")
except Exception:
print("已经存在Courses表，请重新输入")
return
# 写名称，在表格的第一行
new_sheet.write(0,0,"Cno")
new_sheet.write(0,1,"Cname")
new_sheet.write(0,2,"Chours")
new_sheet.write(0,3,"Ccredit")
sql = '''SELECT DISTINCT Cno, Cname,
Chours, Ccredit
FROM Courses
ORDER BY Cno ASC
'''
information = ba.common( cn, sql)
for i, item in enumerate( information, start=1):
for j in range( 0, 4):
new_sheet.write( i, j, item[j])
if count == 0:
new_workbook.save( file)
else:
newb.save( file)
print("课程信息到入%s完毕" % file)
def write_reports( cn, file, count):
"""导出成绩信息到xls文件中"""
if count == 0:
new_workbook = xlwt.Workbook()
new_sheet = new_workbook.add_sheet("Reports")
else:
wb = xlrd.open_workbook( file,formatting_info=True)
newb = copy(wb)
try:
new_sheet = newb.add_sheet("Reports")
except Exception:
print("已经存在Reports表，请重新输入")
return
# 写名称，在表格的第一行
new_sheet.write(0,0,"Sno")
new_sheet.write(0,1,"Cno")
new_sheet.write(0,2,"Racademicyear")
new_sheet.write(0,3,"Rterm")
new_sheet.write(0,4,"Grade")
sql = '''SELECT DISTINCT Sno, Cno,
Racademicyear, Rterm, Grade
FROM Reports
ORDER BY Sno ASC, Cno ASC
'''
information = ba.common( cn, sql)
for i, item in enumerate( information, start=1):
for j in range( 0, 5):
new_sheet.write( i, j, item[j])
if count == 0:
new_workbook.save( file)
else:
newb.save( file)
print("成绩信息到入%s完毕" % file)
# 导出排序信息
def write_total( cn, file, count):
"""按总分排序查看"""
if count == 0:
new_workbook = xlwt.Workbook()
new_sheet = new_workbook.add_sheet("Total")
else:
wb = xlrd.open_workbook( file,formatting_info=True)
newb = copy(wb)
try:
new_sheet = newb.add_sheet("Total")
except Exception:
print("已经存在Total表，请重新输入")
return
sql = '''SELECT DISTINCT Racademicyear,
Rterm, Sno, SUM(Grade)
FROM Reports
GROUP BY Sno, Racademicyear, Rterm
ORDER BY Racademicyear DESC, Rterm DESC,
SUM(Grade) DESC
'''
information = ba.common( cn, sql)
new_sheet.write(0,1,"Racademicyear")
new_sheet.write(0,2,"Rterm")
new_sheet.write(0,3,"Sno")
new_sheet.write(0,4,"Total")
i = 1
y = 0
for item in information:
if y != item[0]:
y = item[0]
i = i + 1
new_sheet.write( i, 0, str(item[0]) + '年度')
i = i + 1
m = 0
for j in range( 1, 5):
new_sheet.write( i, j, item[m])
m = m + 1
if m == 4:
m = 0
i = i + 1
if count == 0:
new_workbook.save( file)
else:
newb.save( file)
print("成绩信息到入%s完毕" % file)
def cname_to_cno( cn, cname):
"""将cname转换成cno"""
sql = '''SELECT DISTINCT Cno
FROM Courses
WHERE Cname = "%s"
''' % cname
cursor = cn.execute( sql)
ccno = []
for row in cursor:
ccno.append( row)
cno = ccno[0]
return cno
def single( cn, file, cname, count):
"""按write_single的选择导入数据"""
if count == 0:
new_workbook = xlwt.Workbook()
new_sheet = new_workbook.add_sheet(cname)
else:
wb = xlrd.open_workbook( file,formatting_info=True)
newb = copy(wb)
try:
new_sheet = newb.add_sheet( cname)
except Exception:
print("已经存在%s表，请重新输入" %
cname)
return
cno = cname_to_cno( cn, cname)
sql = '''SELECT DISTINCT Racademicyear,
Rterm, Sno, Grade
FROM Reports WHERE Cno = "%s"
ORDER BY Racademicyear DESC, Rterm DESC,
Grade DESC
''' % cno
information = ba.common( cn, sql)
new_sheet.write(0,1,"Racademicyear")
new_sheet.write(0,2,"Rterm")
new_sheet.write(0,3,"Sno")
new_sheet.write(0,4,"Grade")
i = 1
y = 0
for item in information:
if y != item[0]:
y = item[0]
i = i + 1
new_sheet.write( i, 0, str(item[0]) + '年度')
i = i + 1
m = 0
for j in range( 1, 5):
new_sheet.write( i, j, item[m])
m = m + 1
if m == 4:
m = 0
i = i + 1
if count == 0:
new_workbook.save( file)
else:
newb.save( file)
print("成绩信息到入%s完毕" % file)
def write_single( cn, file, count):
"""按单科成绩排序"""
sql = '''SELECT Cname
FROM Courses
'''
total_cname = ba.common( cn, sql)
print("存在以下课程：")
i = 0
for row in total_cname:
print( row[0], end = " ")
i = i + 1
if i % 5 == 0:
print("")
print("")
cname = input("\n请输入课程名:")
judge = ba.judge_comment\
( cn, 'Courses', 'Cno', 'Cname', cname)
if judge == 0:
print("课程不存在，请重新输
结合sqlite3的学生信息成绩管理系统.zip
需积分: 50 90 浏览量
2020-06-21
22:24:04
上传
评论 3
收藏 59KB ZIP 举报
学生信息管理系统.zip （15个子文件） 
学生信息管理系统 
xin.xls 40KB
manage.py 12KB 语文成绩.xls 27KB Data.xls 34KB import_data.py 12KB main.py 3KB __pycache__ basics.cpython-37.pyc 6KB import_data.cpython-37.pyc 8KB query.cpython-37.pyc 10KB export_data.cpython-37.pyc 10KB manage.cpython-37.pyc 10KB basics.py 6KB export_data.py 13KB stu.db 28KB xin.xl query.py 11KB资源评论
