009 - EXCEL文件操作练习题

周六作业

1. 随机生成一个有100人班级的语数外三科成绩的文件（使用csv或者xlsx均可）。
2. 初始文件包含四列：学号、语文、数学、英语。
3. 学生姓名使用学号python001、python002、…代替
4. 文件生成完毕以后计算每科最高分、最低分，并找出分值对应学生（允许一个分值有多个学生）
5. 新增一列：总分（每个学生的总分）
6. 找出排名第一的学生和倒数一名的学生。
7. 第四条和第六条找出的结果保存到列表中。

import openpyxl
from random import randint

file = openpyxl.Workbook()
file.create_sheet('学生成绩表', 0)
file.remove(file['Sheet'])

ws = file['学生成绩表']
ws.cell(1, 1).value = '学号'
ws.cell(1, 2).value = '语文'
ws.cell(1, 3).value = '数学'
ws.cell(1, 4).value = '英语'

for i in range(1, 101):
    ws.cell(i + 1, 1).value = 'python{:0>3}'.format(i)
    ws.cell(i + 1, 2).value = randint(0, 100)
    ws.cell(i + 1, 3).value = randint(0, 100)
    ws.cell(i + 1, 4).value = randint(0, 100)

rows = ws.max_row
col = ws.max_column

score_Chinese = [ws.cell(i, 2).value for i in range(2, rows + 1)]
score_Math = [ws.cell(i, 3).value for i in range(2, rows + 1)]
score_English = [ws.cell(i, 4).value for i in range(2, rows + 1)]
score_max_C = max(score_Chinese)
score_min_C = min(score_Chinese)
score_max_M = max(score_Math)
score_min_M = min(score_Math)
score_max_E = max(score_English)
score_min_E = min(score_English)
stu_max_C = [ws.cell(i, 1).value for i in range(2, rows + 1) if ws.cell(i, 2).value == score_max_C]
stu_min_C = [ws.cell(i, 1).value for i in range(2, rows + 1) if ws.cell(i, 2).value == score_min_C]
stu_max_M = [ws.cell(i, 1).value for i in range(2, rows + 1) if ws.cell(i, 3).value == score_max_M]
stu_min_M = [ws.cell(i, 1).value for i in range(2, rows + 1) if ws.cell(i, 3).value == score_min_M]
stu_max_E = [ws.cell(i, 1).value for i in range(2, rows + 1) if ws.cell(i, 4).value == score_max_E]
stu_min_E = [ws.cell(i, 1).value for i in range(2, rows + 1) if ws.cell(i, 4).value == score_min_E]
print(f'语文最高分：{score_max_C}，学生：{stu_max_C}')
print(f'语文最低分：{score_min_C}，学生：{stu_min_C}')
print(f'数学最高分：{score_max_M}，学生：{stu_max_M}')
print(f'数学最低分：{score_min_M}，学生：{stu_min_M}')
print(f'英语最高分：{score_max_E}，学生：{stu_max_E}')
print(f'英语最低分：{score_min_E}，学生：{stu_min_E}')

ws.cell(1, 5).value = '总分'
for row in range(2, rows + 1):
    ws.cell(row, 5).value = ws.cell(row, 2).value + ws.cell(row, 3).value + ws.cell(row, 4).value
scores = [ws.cell(row, 5).value for row in range(2, rows)]
score_highest = max(scores)
score_lowest = min(scores)
stu_first = [ws.cell(i, 1).value for i in range(2, rows + 1) if ws.cell(i, 5).value == score_highest]
stu_last = [ws.cell(i, 1).value for i in range(2, rows + 1) if ws.cell(i, 5).value == score_lowest]
print(f'最高分学生：{stu_first}，分数：{score_highest}')
print(f'最高分学生：{stu_last}，分数：{score_lowest}')

file.save('./学生成绩.xlsx')

"""
输出结果
语文最高分：100，学生：['python048']
语文最低分：2，学生：['python027']
数学最高分：99，学生：['python010']
数学最低分：2，学生：['python051']
英语最高分：100，学生：['python037', 'python096']
英语最低分：0，学生：['python009', 'python040', 'python047', 'python089']
最高分学生：['python096']，分数：294
最高分学生：['python073']，分数：41

"""

讲解

import openpyxl
import random

# 创建文件
wb = openpyxl.Workbook()
# 新建工作表
wb.create_sheet('学生成绩')
# 添加列
cols = ['姓名', '语文', '数学', '英语']
for i in range(len(cols)):
    wb['学生成绩'].cell(1, i + 1).value = cols[i]
# 写入学生学号数据
for i in range(1, 101):
    wb['学生成绩'].cell(i + 1, 1).value = 'python{:0>3}'.format(i)
# 生成成绩写入表格
for i in range(2, 102):
    for j in range(2, 5):
        wb['学生成绩'].cell(i, j).value = random.randint(0, 100)
# 保存文件
wb.save('学生成绩.xlsx')

import openpyxl

# 加载源文件
wb = openpyxl.load_workbook('学生成绩.xlsx')
# 找工作表
# 对行号遍历
list1 = []
list2 = []
list3 = []
for i in range(2, 102):
    list1.append(wb['学生成绩'].cell(i, 2).value)
    list2.append(wb['学生成绩'].cell(i, 3).value)
    list3.append(wb['学生成绩'].cell(i, 4).value)

a = [wb['学生成绩'].cell(i, 1).value for i in range(2, 102) if wb['学生成绩'].cell(i, 2).value == max(list1)]
b = [wb['学生成绩'].cell(i, 1).value for i in range(2, 102) if wb['学生成绩'].cell(i, 2).value == min(list1)]
c = [wb['学生成绩'].cell(i, 1).value for i in range(2, 102) if wb['学生成绩'].cell(i, 3).value == max(list2)]
d = [wb['学生成绩'].cell(i, 1).value for i in range(2, 102) if wb['学生成绩'].cell(i, 3).value == min(list2)]
e = [wb['学生成绩'].cell(i, 1).value for i in range(2, 102) if wb['学生成绩'].cell(i, 4).value == max(list3)]
f = [wb['学生成绩'].cell(i, 1).value for i in range(2, 102) if wb['学生成绩'].cell(i, 4).value == min(list3)]
print(f'语文成绩最高分对应学生：{a},语文成绩最低分对应学生：{b}')
print(f'数学成绩最高分对应学生：{c},数学成绩最低分对应学生：{d}')
print(f'英语成绩最高分对应学生：{e},英语成绩最低分对应学生：{f}')

# 添加总分
wb['学生成绩'].cell(1, 5).value = '总分'
max_ = 0
min_ = 300
# 计算总分
for i in range(2, 102):
    wb['学生成绩'].cell(i, 5).value = \
        wb['学生成绩'].cell(i, 2).value + wb['学生成绩'].cell(i, 3).value + wb['学生成绩'].cell(i, 4).value
    if wb['学生成绩'].cell(i, 5).value > max_:
        max_ = wb['学生成绩'].cell(i, 5).value
    if wb['学生成绩'].cell(i, 5).value < min_:
        min_ = wb['学生成绩'].cell(i, 5).value

# 寻找排名第一和倒数第一的学生
first_stu = []
last_stu = []
for i in range(2, 102):
    if wb['学生成绩'].cell(i, 5).value == max_:
        first_stu.append(wb['学生成绩'].cell(i, 1).value)
    if wb['学生成绩'].cell(i, 5).value == min_:
        last_stu.append(wb['学生成绩'].cell(i, 1).value)

print(f'第一名学生为：{first_stu}')
print(f'倒数第一的学生为：{last_stu}')
# 保存关闭
wb.save('学生成绩.xlsx')