Python根据图片生成学生excel成绩表

学习笔记：

上完整代码

import os
import re
from openpyxl import Workbook, load_workbook
from openpyxl.drawing.image import Image as ExcelImage
from PIL import Image as PilImage# 定义图片路径和Excel文件路径
image_dir = './resources/stupics'  # 图片所在的文件夹路径，请根据实际情况修改
excel_path = './result/students_info.xlsx'  # Excel文件路径，请根据实际情况修改
output_folder = './result';
if not os.path.exists(output_folder):os.makedirs(output_folder)# 定义图片名称格式的正则表达式
pattern = r'(?P<grade>\d{2})级(?P<major>[\u4e00-\u9fff]+)(?P<class>\d+班)(?P<name>[\u4e00-\u9fff]+)'# 获取图片文件列表
image_files = [f for f in os.listdir(image_dir) if f.endswith(('.png', '.jpg', '.jpeg'))]# 检查是否存在Excel文件，若不存在则创建新文件
if not os.path.exists(excel_path):wb = Workbook()ws = wb.activews.title = "学生信息"# 写入表头headers = ["年级", "专业", "班级", "姓名", "照片"]ws.append(headers)
else:wb = load_workbook(excel_path)ws = wb.active# 定义各列的固定宽度
column_widths = {'A': 10,  # 年级'B': 25,  # 专业'C': 8,   # 班级'D': 15,  # 姓名'E': 15   # 照片
}# 应用固定列宽
for column, width in column_widths.items():ws.column_dimensions[column].width = width# 遍历图片文件，解析名称并写入Excel
for image_file in image_files:match = re.match(pattern, os.path.splitext(image_file)[0])if match:info = match.groupdict()row = [info['grade'] + '级',info['major'],info['class'],info['name']]ws.append(row)# 确保图片插入到正确的行img_path = os.path.join(image_dir, image_file)img = PilImage.open(img_path)excel_img = ExcelImage(img_path)excel_img.width, excel_img.height = 80, 110  # 根据需要调整图片大小# 计算图片宽度对应的字符数pixel_width = excel_img.widthchar_width = pixel_width / 7  # 假设每个字符约等于7个像素# 设置图片所在列的宽度cell_location = f'E{ws.max_row}'  # 使用当前最大行数作为图片插入位置ws.add_image(excel_img, cell_location)ws.column_dimensions['E'].width = char_width# 计算行高（假设每点约等于1.33像素）pixel_height = excel_img.heightpoint_height = pixel_height / 1.33# 设置行高ws.row_dimensions[ws.max_row].height = point_height# 保存工作簿
wb.save(excel_path)
print("数据已成功写入Excel文件")