12.6 精选练习-[Python]

实验步骤：

1、在命令提示符环境使用 pip install openpyxl 命令安装扩展库 openpyxl。

2、编写代码。

参考代码：

import openpyxl
import random
def autoGet(fileName):
    wb=openpyxl.Workbook()
    sheet=wb.active
    sheet.append([‘姓名’,’课程’,’成绩’])
    first=tuple('赵明张王')
    middle=tuple('天宇茜一')
    last=tuple('如天佳')

    subjects=('语文','数学','英语')
    for i in range(2000):
        line=[]
        r=random.randint(1,100)
        name=random.choice(first)
        if r>50:
            name=name+random.choice(middle)
        name=name+random.choice(last)
        line.append(name)
        line.append(random.choice(subjects))
        line.append(random.randint(0,100))
        sheet.append(line)
    wb.save(fileName)
def getMaxScore(oldfileName,newfilrName):
    wb=openpyxl.load_workbook(oldfileName)
    final_wb=openpyxl.Workbook()
    start_sheet=wb.active
    final_sheet=final_wb.active
    final_sheet.title='学生的各科成绩'
    final_sheet.append([‘姓名’,’课程’,’最高分’])

    stuGrade={}

    for i in range(2,start_sheet.max_row+1):
        stuName=start_sheet[‘A’+str(i)].value
        lesName=start_sheet[‘B’+str(i)].value
        score=start_sheet[‘C’+str(i)].value

        if stuName in stuGrade:
            stuGrade[stuName]
        else:
            stuGrade[stuName]={}

        if lesName in stuGrade[stuName]:
            if score>stuGrade[stuName][lesName]:
                stuGrade[stuName][lesName]=score
        else:
            stuGrade[stuName][lesName]=score
    for student in stuGrade:
        for lesson,score in stuGrade[student].items():
            final_sheet.append([student,lesson,score])
    final_wb.save(newfilrName)
oldfile='全校学生成绩.xlsx'
newfile='学生最终成绩表.xlsx'
autoGet(oldfile)
getMaxScore(oldfile,newfile)