xlrd模块、xlwt模块综合练习

xlrd模块、xlwt模块综合练习
实验内容：
1.创建名称为“成绩单.xls”工作簿,新建一张工作表，表的名称为”01班成绩表”。
2.将学生的成绩写入到“01班成绩表”中。
3.读出每个学生的成绩，并计算每个学生成绩的总分。
4.读出单科的成绩，并计算单科成绩的平均分。

import xlwt
workbook = xlwt.Workbook()
mySheet = workbook.add_sheet("01班成绩表")
headers=['姓名','语文','数学','英语']
scores=[('李思聪',56,99,96),
                ('李建林',98,100,93),
                ('马风',94,95,95),
                ('张小某',90,98,92),
                ('陈开歌',95,91,96),
                ('冯大刚',99,99,95.3),
                ('赵小薇',96,100,91)
                ]
for cols, header in enumerate(headers):
    mySheet.write(0, cols, header)
for row, scores in enumerate(scores):
    for cols, score in enumerate(scores):
        mySheet.write(row+1, cols, score)
workbook.save("成绩单.xls")
#总分，平均分
import xlrd
import xlwt
myWorkbook = xlrd.open_workbook("成绩单.xls")
rsheet = myWorkbook.sheet_by_name('01班成绩表')
r_nrows = rsheet.nrows
r_ncols = rsheet.ncols
rsheet.put_cell(0, r_ncols,xlrd.XL_CELL_TEXT,'总分',None)
for row in range(1, r_nrows):
    grades = rsheet.row_values(row, 1, r_ncols)
    total = sum(grades)
    rsheet.put_cell(row, 4, xlrd.XL_CELL_NUMBER,total,None)
rsheet.put_cell(rsheet.nrows, 0, xlrd.XL_CELL_TEXT,'平均分',None)
for col in range(1, r_ncols+1):
    grades = rsheet.col_values(col, 1, r_nrows)
    avg = sum(grades) / len(grades)
    rsheet.put_cell(r_nrows, col, xlrd.XL_CELL_NUMBER, avg, None)
wwb = xlwt.Workbook()
wSheet = wwb.add_sheet("班级1",)
w_nrows = rsheet.nrows
w_ncols = rsheet.ncols
for row in range(0,w_nrows):
    for cols in range(0,w_ncols):
        wSheet.write(row, cols, rsheet.cell_value(row,cols))  # 从内存中获取成绩并写入wSheet中
wwb.save('abc.xls')