今天写了一个python代码,需求是这样的:
- 读取一个名为"2209A2361-第一轮面试成绩.xlsx"的Excel文件,获取其中的学生成绩数据。
- 对学生成绩进行统计和分析,包括计算最高分、最低分、平均分、高于平均分和低于平均分的学生,以及前三名学生。
- 将统计和分析结果写入一个名为"dct.csv"的CSV文件。
- 加载一张名为"12.jpg"的图片,并在图片上使用指定字体、字号和颜色绘制学生成绩信息和前三名学生信息。
- 保存修改后的图片为"output_image.jpg"并显示出来。
我自己用笨方法写了一下,是这样写的:
import openpyxl
import csv
from PIL import Image, ImageDraw, ImageFont
aaa = openpyxl.load_workbook('2209A2361-第一轮面试成绩.xlsx')
sheet = aaa.active
cell1 = sheet['A3:A29']
cell2 = sheet['B3:B29']
dic1 = {}
lst10 = []
lst11 = []
for i in cell1:
for j in i:
lst10.append(j.value)
for i in cell2:
for j in i:
lst11.append(j.value)
dic11 = zip(lst10, lst11)
dic = dict(dic11)
xiao = min(lst11[0:-1])
da = max(lst11[0:-1])
# 所有学生分数
print(dic)
# 分数最高
print('最高分:', end="")
for i in dic.items():
if i[1] == da:
print(i, end="")
print()
# 分数最小
print('最低分:', end="")
for i in dic.items():
if i[1] == xiao:
print(i, end="")
print()
# 平均分数
ping = sum(lst11[0:-1]) / len(lst11)
print('平均分:', ping)
# 高于平均分的
gao_fen = []
for i in lst11[0:-1]:
if i > ping:
gao_fen.append(i)
print('高于平均分的:', end='')
for i in dic.items():
if i[1] in gao_fen:
print(i, end='')
print()
print('低于平均分的:', end="")
for i in dic.items():
if i[1] not in gao_fen:
print(i, end='')
print()
del dic['孙梓豪']
dic2 = sorted(dic.items(), key=lambda x: x[1], reverse=True)[:3]
print(dic2)
# 写入CSV文件
with open('dct.csv', 'w', encoding='UTF-8') as f:
writer = csv.writer(f)
for k, v in dic.items():
writer.writerow([k, v])
# 加载图片
image = Image.open('12.jpg')
draw = ImageDraw.Draw(image)
# 设置字体大小和样式
font = ImageFont.truetype('C:/Windows/Fonts/simhei.ttf', size=20)
# 设置文本的起始坐标
x, y = 10, 10
# 遍历字典,逐行打印数据
for key, value in dic.items():
text = f'{key}: {value}'
draw.text((x, y), text, font=font, fill='white')
y += 20
draw.text((200, 10), '前三名:', font=font, fill='white')
a, b = 300, 10
for key, value in dic2:
text = f'{key}: {value}'
draw.text((a, b), text, font=font, fill='white')
b += 20
# 保存修改后的图片
image.save('output_image.jpg')
image.show()
后来用Chat GPT 优化了一下代码
优化期间报了很多错误,经过多次优化得到了以下代码:
import openpyxl
import csv
from PIL import Image, ImageDraw, ImageFont
# 读取Excel文件
wb = openpyxl.load_workbook('2209A2361-第一轮面试成绩.xlsx')
sheet = wb.active
# 获取学生成绩数据
scores = {}
for row in sheet['A3:B29']:
name, score = row
if name.value and isinstance(score.value, (int, float)):
scores[name.value] = int(score.value)
# 统计分析
max_score = max(scores.values())
min_score = min(scores.values())
avg_score = sum(scores.values()) / len(scores)
above_avg = {name: score for name, score in scores.items() if score > avg_score}
below_avg = {name: score for name, score in scores.items() if score <= avg_score}
top_three = sorted(scores.items(), key=lambda x: x[1], reverse=True)[:3]
# 写入CSV文件
with open('dct.csv', 'w', encoding='UTF-8', newline='') as f:
writer = csv.writer(f)
writer.writerows(scores.items())
# 加载图片
image = Image.open('12.jpg')
draw = ImageDraw.Draw(image)
font = ImageFont.truetype('C:/Windows/Fonts/simhei.ttf', size=20)
# 绘制学生成绩信息
x, y = 10, 10
for name, score in scores.items():
text = f'{name}: {score}'
draw.text((x, y), text, font=font, fill='white')
y += 20
draw.text((200, 10), '前三名:', font=font, fill='white')
a, b = 300, 10
for name, score in top_three:
text = f'{name}: {score}'
draw.text((a, b), text, font=font, fill='white')
b += 20
# 保存图片
image.save('output_image.jpg')
image.show()