自动生成荣誉证书

ookk

需求很简单：自动读取XLS表格中姓名、编号、日期列，叠加在背景图片上，可设置字体、字号、颜色。网上找了几个工具，都不是很好用，借助AI工具完成编码。

[Python] 纯文本查看 复制代码

import os
import pandas as pd
from PIL import Image, ImageDraw, ImageFont

# ==================== 配置参数 ====================
EXCEL_FILE = "名单.xlsx"          # 输入的 Excel 文件路径
BACKGROUND_IMG = "background.png" # 背景图片路径
OUTPUT_DIR = "证书输出"             # 输出文件夹

# ---- A 列 ----
A_FONT_PATH = "C:/Windows/Fonts/simhei.ttf"          
A_FONT_SIZE = 40
A_COLOR = (0, 0, 0)               # RGB 黑色
A_POSITION = (265, 270)           # (x, y) 左上角坐标

# ---- B 列 ----
B_FONT_PATH = "C:/Windows/Fonts/simhei.ttf"                  
B_FONT_SIZE = 46
B_COLOR = (0, 0, 0) 
B_POSITION = (340, 840)

# ---- C 列 ----
C_FONT_PATH = "C:/Windows/Fonts/simhei.ttf"
C_FONT_SIZE = 34
C_COLOR = (0, 0, 0) 
C_POSITION = (940, 1682)

def load_font(font_path, size):

    if font_path and os.path.isfile(font_path):
        return ImageFont.truetype(font_path, size)
    else:
        return ImageFont.load_default()

def sanitize_filename(text):
illegal = r'<>:"/\|?*'
    for ch in illegal:
        text = text.replace(ch, '_')
    return text.strip() or "unnamed"

def generate_certificate(row):
text_a = str(row['编号']).zfill(8) if pd.notna(row.get('编号')) else ''
    print(row['编号'])
    text_b = str(row['姓名']) if pd.notna(row.get('姓名')) else ''
    text_c = str(row['日期']) if pd.notna(row.get('日期')) else ''
img = Image.open(BACKGROUND_IMG).convert("RGBA")
    draw = ImageDraw.Draw(img)
font_a = load_font(A_FONT_PATH, A_FONT_SIZE)
    draw.text(A_POSITION, text_a, font=font_a, fill=A_COLOR)
font_b = load_font(B_FONT_PATH, B_FONT_SIZE)
    draw.text(B_POSITION, text_b, font=font_b, fill=B_COLOR)
font_c = load_font(C_FONT_PATH, C_FONT_SIZE)
    draw.text(C_POSITION, text_c, font=font_c, fill=C_COLOR)

    safe_name = sanitize_filename(text_b)
    output_path = os.path.join(OUTPUT_DIR, f"{safe_name}.png")
    img.save(output_path)
    print(f"&#9989; 已生成: {output_path}")

def main():
try:
        df = pd.read_excel(EXCEL_FILE)
    except FileNotFoundError:
        print(f"&#10060; 未找到 Excel 文件: {EXCEL_FILE}")
        return

    required_cols = ['编号', '姓名', '日期']
    if not all(col in df.columns for col in required_cols):
        print(f"&#10060; Excel 中必须包含列: 编号,姓名,日期，当前列名: {list(df.columns)}")
        return

    os.makedirs(OUTPUT_DIR, exist_ok=True)

    # 逐行处理
    for idx, row in df.iterrows():
        generate_certificate(row)

if __name__ == "__main__":
    main()