实验9 处理Excel文件中的成绩数据

目录

实验目的

实验内容

代码

结果

​编辑

代码解析

---

实验目的

（1）了解扩展库openpyxl的安装与使用。

（2）了解使用扩展库openpyxl操作Excel文件的方法。

（3）熟练运用字典结构解决实际问题。

实验内容

假设某学校所有课程每学期允许多次考试，学生可随时参加考试，系统自动将每次成绩添加到Excel文件（包含3列：姓名，课程，成绩）中，现期末要求统计所有学生每门课程的最高成绩。

编写程序，模拟生成若干同学的成绩并写入Excel文件，其中学生姓名和课程名称均可重复，也就是允许出现同一门课程的多次成绩，最后统计所有学生每门课程的最高成绩，并写入新的Excel文件。

代码

from random import choice, randint
from openpyxl import Workbook, load_workbook

# 生成随机数据
def generateRandomInformation(filename):
    workbook = Workbook()
    worksheet = workbook.worksheets[0]
    worksheet.append(['姓名','课程','成绩'])

    # 中文名字中的第一、第二、第三个字
    first = '赵钱孙李'
    middle = '伟昀琛东'
    last = '坤艳志'
    subjects = ('语文','数学','英语')
    for i in range(200):
        name = choice(first)
        # 按一定概率生成只有两个字的中文名字
        if randint(1,100)>50:
            name = name + choice(middle)
        name = name + choice(last)
        # 依次生成姓名、课程名称和成绩
        worksheet.append([name, choice(subjects), randint(0, 100)])
    # 保存数据，生成Excel 2007格式的文件
    workbook.save(filename)

def getResult(oldfile, newfile):
    # 用于存放结果数据的字典
    result = dict()

    # 打开原始数据
    workbook = load_workbook(oldfile)
    worksheet = workbook.worksheets[0]

    # 遍历原始数据
    for row in worksheet.rows:
        if row[0].value == '姓名':
            continue
        # 姓名,课程名称,本次成绩
        name, subject, grade = map(lambda cell:cell.value, row)

        # 获取当前姓名对应的课程名称和成绩信息
        # 如果result字典中不包含，则返回空字典
        t = result.get(name, {})
        # 获取当前学生当前课程的成绩，若不存在，返回0
        f = t.get(subject, 0)
        # 只保留该学生该课程的最高成绩
        if grade > f:
            t[subject] = grade
            result[name] = t

    workbook1 = Workbook()
    worksheet1 = workbook1.worksheets[0]
    worksheet1.append(['姓名','课程','成绩'])

    # 将result字典中的结果数据写入Excel文件
    for name, t in result.items():
        print(name, t)
        for subject, grade in t.items():
            worksheet1.append([name, subject, grade])

    workbook1.save(newfile)

if __name__ == '__main__':
    oldfile = r'd:\test.xlsx'
    newfile = r'd:\result.xlsx'
    generateRandomInformation(oldfile)
    getResult(oldfile, newfile)

结果

赵坤 {'英语': 46, '语文': 99, '数学': 74}
赵昀志 {'数学': 92}
李伟艳 {'英语': 2, '语文': 57}
李坤 {'英语': 96, '数学': 60, '语文': 29}
孙坤 {'语文': 96, '英语': 72, '数学': 75}
赵志 {'数学': 82, '语文': 52, '英语': 77}
孙志 {'英语': 73, '数学': 67}
李昀坤 {'语文': 87}
李艳 {'数学': 78, '英语': 85, '语文': 98}
赵琛志 {'英语': 100}
孙昀艳 {'语文': 56, '数学': 12, '英语': 4}
赵昀艳 {'数学': 58, '语文': 52}
钱昀艳 {'语文': 45}
赵艳 {'语文': 94, '英语': 29}
钱琛艳 {'英语': 93, '数学': 63, '语文': 94}
钱琛坤 {'语文': 11, '英语': 90, '数学': 16}
钱琛志 {'英语': 43, '语文': 10}
钱艳 {'英语': 63, '语文': 56, '数学': 82}
李琛坤 {'语文': 77, '数学': 95}
李东志 {'英语': 32, '数学': 91}
赵琛艳 {'语文': 42, '英语': 72, '数学': 89}
赵伟坤 {'语文': 80, '数学': 69, '英语': 69}
钱坤 {'数学': 57, '语文': 84, '英语': 64}
李昀艳 {'数学': 89, '语文': 54, '英语': 91}
钱东艳 {'语文': 25, '英语': 67}
李东艳 {'英语': 98, '语文': 56}
李琛志 {'语文': 88, '数学': 18}
李志 {'数学': 77, '语文': 47, '英语': 83}
赵琛坤 {'英语': 93}
赵伟志 {'语文': 55, '英语': 8}
钱志 {'数学': 71, '语文': 44, '英语': 18}
孙琛志 {'数学': 49, '英语': 78}
孙伟艳 {'语文': 90, '数学': 95, '英语': 43}
孙东坤 {'英语': 50}
孙艳 {'数学': 97, '英语': 95, '语文': 59}
钱伟艳 {'语文': 11}
孙东艳 {'英语': 15}
赵东艳 {'数学': 59}
李东坤 {'语文': 97}
钱伟坤 {'语文': 60}
钱昀坤 {'语文': 61, '英语': 34}
李伟志 {'数学': 89, '英语': 11, '语文': 39}
孙伟志 {'英语': 74, '数学': 26}
赵东志 {'数学': 34}
赵东坤 {'数学': 38}
李琛艳 {'英语': 60}
钱伟志 {'数学': 46}
李伟坤 {'语文': 100}
孙琛坤 {'数学': 97}
孙昀志 {'语文': 44, '英语': 5}
孙东志 {'数学': 43, '语文': 57}
赵昀坤 {'语文': 84}
孙琛艳 {'语文': 32}
孙昀坤 {'英语': 92}
赵伟艳 {'数学': 31}

 

代码解析

def generateRandomInformation(filename):
    workbook = Workbook()
    worksheet = workbook.worksheets[0]
    worksheet.append(['姓名','课程','成绩'])

1. 使用Workbook()类创建一个新的Excel工作簿实例。Workbook是openpyxl库中的一个类，用于创建和操作Excel文件。
2. 通过workbook.worksheets[0]获取这个新工作簿中的第一个工作表。在新建的工作簿中，默认情况下会有一个工作表，索引为0。
3. 使用worksheet.append()方法向工作表中添加一行数据。这里添加的是标题行，包含三个单元格：‘姓名’、‘课程’和’成绩’。

 # 中文名字中的第一、第二、第三个字
    first = '赵钱孙李'
    middle = '伟昀琛东'
    last = '坤艳志'
    subjects = ('语文','数学','英语')
    for i in range(200):
        name = choice(first)
        # 按一定概率生成只有两个字的中文名字
        if randint(1,100)>50:
            name = name + choice(middle)
        name = name + choice(last)
        # 依次生成姓名、课程名称和成绩
        worksheet.append([name, choice(subjects), randint(0, 100)])
    # 保存数据，生成Excel 2007格式的文件
    workbook.save(filename)

 

1. 使用一个for循环来重复200次以下操作：

   • 使用choice(first)随机选择一个姓氏。
   • 使用randint(1, 100)生成一个1到100之间的随机整数，如果这个数大于50，则使用choice(middle)随机选择一个中间名并添加到姓氏后面。
   • 使用choice(last)随机选择一个名字的最后一个字，并添加到已有的名字后面，形成一个完整的中文姓名。
   • 使用choice(subjects)随机选择一个课程名称。
   • 使用randint(0, 100)随机生成一个0到100之间的整数作为成绩。
   • 使用worksheet.append()方法将生成的姓名、课程和成绩作为一个列表添加到工作表的下一行。

2. 在循环结束后，使用workbook.save(filename)将工作簿保存到指定的文件名filename。这个文件将是Excel 2007格式（.xlsx）。

def getResult(oldfile, newfile):
    # 用于存放结果数据的字典
    result = dict()

    # 打开原始数据
    workbook = load_workbook(oldfile)
    worksheet = workbook.worksheets[0]

1. 初始化一个空的字典result，这个字典将用于存储处理后的结果数据。字典的键可能是学生的姓名，而值可能是与该学生相关的一些信息，例如他们每门课程的最佳成绩。

2. 使用load_workbook函数加载指定路径的Excel工作簿oldfile。这个函数返回一个Workbook对象，它代表了整个Excel文件。

3. 使用workbook.worksheets[0]获取工作簿中的第一个工作表。在Excel工作簿中，工作表可以通过索引来访问，索引从0开始。

# 遍历原始数据
for row in worksheet.rows:
    # 检查当前行的第一个单元格是否包含'姓名'，如果是，则跳过这一行
    if row[0].value == '姓名':
        continue
    name, subject, grade = map(lambda cell: cell.value, row)

1. for row in worksheet.rows:：开始一个循环，worksheet.rows是工作表中的所有行的一个迭代器。

2. if row[0].value == '姓名':：检查当前行的第一个单元格（即标题行）是否包含字符串’姓名’。如果是，这意味着当前行是标题行，所以使用continue语句跳过当前循环的剩余部分，继续下一次循环。

3. name, subject, grade = map(lambda cell: cell.value, row)：使用map函数和lambda表达式来提取每一行中的数据。map函数接收两个参数：一个函数和一个可迭代对象。在这个例子中，lambda cell: cell.value是一个匿名函数，它返回单元格的值，而row是当前行的单元格迭代器。map函数将这个匿名函数应用于row中的每个单元格，并返回一个包含单元格值的迭代器。然后，这个迭代器中的值被解包到name, subject, grade这三个变量中。

 t = result.get(name, {})
        # 获取当前学生当前课程的成绩，若不存在，返回0
        f = t.get(subject, 0)
        # 只保留该学生该课程的最高成绩
        if grade > f:
            t[subject] = grade
            result[name] = t

1. t = result.get(name, {})：

   • result是一个字典，其键是学生的姓名，值是另一个字典，这个内部字典的键是课程名称，值是对应的最高成绩。
   • result.get(name, {})尝试从result字典中获取键为name的值。如果name键存在，则返回对应的值；如果不存在，则返回一个空字典{}。

2. f = t.get(subject, 0)：

   • t是从result字典中获取的与当前学生姓名对应的内部字典。
   • t.get(subject, 0)尝试从内部字典t中获取键为subject的值。如果subject键存在，则返回对应的最高成绩；如果不存在，则返回0。

3. if grade > f:：

   • 这个条件检查当前行中的成绩grade是否高于内部字典t中存储的对应课程的最高成绩f。
   • 如果grade大于f，说明我们找到了一个更高的成绩，需要更新内部字典t。

4. t[subject] = grade：

   • 如果当前成绩grade更高，我们就将内部字典t中对应课程的键subject的值更新为这个更高的成绩grade。

5. result[name] = t：

   • 更新result字典，将当前学生的姓名name作为键，并将更新后的内部字典t作为值。

workbook1 = Workbook()
worksheet1 = workbook1.worksheets[0]
worksheet1.append(['姓名', '课程', '成绩'])

1. workbook1 = Workbook()：

   • 这行代码创建了一个新的Excel工作簿对象，使用openpyxl库。这个工作簿初始时是空的，没有包含任何工作表。

2. worksheet1 = workbook1.worksheets[0]：

   • 当创建一个新的工作簿时，openpyxl会默认创建一个工作表。workbook1.worksheets是一个工作表集合，可以通过索引访问工作表。由于这是新创建的工作簿，所以只有一个工作表，其索引为0。
   • 这行代码获取了工作簿中的第一个工作表，并将其赋值给变量worksheet1。

3. worksheet1.append(['姓名', '课程', '成绩'])：

   • append方法是openpyxl工作表对象的一个功能，允许你向工作表的末尾添加一行数据。
   • 这行代码向worksheet1添加了一个列表['姓名', '课程', '成绩']作为一行，这个列表中的每个元素将分别填充到这一行的各个单元格中。因此，这实际上是为工作表添加了一个标题行，用于说明下面数据的列标题。

 for name, t in result.items():
        print(name, t)
        for subject, grade in t.items():
            worksheet1.append([name, subject, grade])

    workbook1.save(newfile)

1. • 行代码开始遍历result字典。result字典的键是学生的姓名，值是另一个字典，其中包含课程名称和对应的成绩。
   • name变量获取学生的姓名，而t变量获取与该姓名相关联的课程和成绩字典。

2. print(name, t)：

   • 这行代码打印出学生的姓名和他们对应的课程成绩字典。这主要用于调试，确保数据被正确处理。

3. for subject, grade in t.items()：

   • 在外层循环内部，这行代码遍历与特定学生姓名关联的课程和成绩字典。
   • subject变量获取课程名称，而grade变量获取该课程的成绩。

4. worksheet1.append([name, subject, grade])：

   • 这行代码将学生的姓名、课程名称和成绩作为一个列表添加到工作表worksheet1的末尾。每次调用append都会在当前工作表的下一行添加一个新的数据行。

5. workbook1.save(newfile)：

   • 在所有数据都被添加到工作表之后，这行代码将工作簿workbook1保存到指定的文件路径newfile。这个文件将包含所有学生的最高成绩信息。

if __name__ == '__main__':
    oldfile = r'd:\test.xlsx'
    newfile = r'd:\result.xlsx'
    generateRandomInformation(oldfile)
    getResult(oldfile, newfile)

 

1. if __name__ == '__main__':：

   • 这是一个Python idiom，用于检查当前脚本是否作为主程序运行。如果脚本被直接运行，__name__变量会被设置为'__main__'。如果脚本被作为模块导入到另一个脚本中，__name__变量会被设置为模块名。

2. oldfile = r'd:\test.xlsx'：

   • 这里定义了一个变量oldfile，它包含原始Excel文件的路径，即位于D盘根目录下的test.xlsx。

3. newfile = r'd:\result.xlsx'：

   • 这里定义了一个变量newfile，它包含将要生成的包含结果的新Excel文件的路径，即位于D盘根目录下的result.xlsx。

4. generateRandomInformation(oldfile)：

   • 这行代码调用generateRandomInformation函数，并将oldfile作为参数传递。这个函数负责生成包含随机学生成绩信息的Excel文件。

5. getResult(oldfile, newfile)：

   • 这行代码调用getResult函数，并将oldfile和newfile作为参数传递。这个函数读取oldfile中的数据，计算每个学生的每门课程的最高成绩，并将结果保存到newfile中。