背景需求
之前做了与卡套相同大小的名字信息卡。
【办公类-109-04】20250807托小班新生的卡套长方形纸牌制作Python3.11.5 32（85*54mm横板和54*85mm竖版，一页9张）-CSDN博客文章浏览阅读996次，点赞31次，收藏12次。【办公类-109-04】20250807托小班新生的卡套长方形纸牌制作（85*54mm横板和54*85mm竖版，一页9张）
今天上班了，有组长来要点名册
我就把“点名册”“长方形卡套横竖卡”“圆形被子挂牌”都群发了
10点开园区小会议，组长把资料群发了，看到手机的截图，老师们很惊讶：
”这个真厉害，名字都有的！“
“为什么做拼音？”“啊呀，就是方便行政老师送被子、送小孩，能叫得出名字！”
“你太厉害了！托小班孩子有的名字真不认识！”
随后组长讨论了每个班级用什么颜色（四种浅色A4纸，小2分到浅蓝色A4）。她随后建议：“这卡片都是字，大人看没问题，小孩子不认识字，最好贴个照片！”
”照片贴哪里？“一位同事问，”这一面都是字，贴照片就把文字挡住了”
我心里咯噔一下——我没有预留“贴照片”的位置，卡套里只能显示一面文字，不可能把照片贴在反面
“大家先不要打印，我再做一份有照片位置的！”
改进过程：
1、照片的大小：卡套只有85*54mm，只能用1寸照片。查询大小
2、预设做一张2.5*3.5CM的空白图片，中间写上“贴照片”
横版：照片插入左侧
竖版，照片插入园区下方
主要就是调整文本位置+是否换行
'''
1.制作20250901托小班长方形卡片，横版85*54mm和竖版54*85mm，一页9张单面
2.添加姓名拼音显示功能（拼音在姓名上方，带声调）
3.python 3.7.8
3. 横版布局：单列显示园区和班级，双列显示拼音、姓名和学号
4. 竖版布局：单列显示所有信息
5. 添加照片占位符：2.5cm宽*3.5cm高，黑色框线，中间写"贴照片"
豆包,deepseek,阿夏
20250826
'''
# ========== 记录程序开始时间 ==========
import time
start_time = time.time()
# ========== 创建照片占位符图片 ==========
import os
from PIL import Image, ImageDraw, ImageFont
import shutil
import pandas as pd
from pypinyin import pinyin, Style
from docx import Document
from docx.shared import Cm, Pt
from docx.enum.text import WD_PARAGRAPH_ALIGNMENT
from docx.oxml.ns import qn
from docx.oxml import OxmlElement
import win32com.client
import glob
from PyPDF2 import PdfMerger
def create_photo_placeholder(image_path):
"""创建照片占位符图片"""
try:
# 确保目录存在
os.makedirs(os.path.dirname(image_path), exist_ok=True)
# 设置图片尺寸 (2.5cm x 3.5cm)
width, height = int(2.5/2.54*300), int(3.5/2.54*300) # 转换为300dpi的像素
# 创建白色背景图片
img = Image.new('RGB', (width, height), 'white')
draw = ImageDraw.Draw(img)
# 绘制黑色边框
draw.rectangle([(10, 10), (width-10, height-10)], outline='black', width=2)
# 添加文字
try:
# 尝试使用微软雅黑字体
font = ImageFont.truetype("msyh.ttc", 20)
except:
# 如果找不到字体，使用默认字体
font = ImageFont.load_default()
text = "贴照片"
text_bbox = draw.textbbox((0, 0), text, font=font)
text_width = text_bbox[2] - text_bbox[0]
text_height = text_bbox[3] - text_bbox[1]
text_x = (width - text_width) // 2
text_y = (height - text_height) // 2
draw.text((text_x, text_y), text, fill='black', font=font)
# 保存图片
img.save(image_path)
print(f"已创建照片占位符: {image_path}")
return True
except Exception as e:
print(f"创建照片占位符失败: {str(e)}")
return False
# ========== 读取Excel数据 ==========
def read_excel_data(file_path):
"""读取Excel文件所有工作表数据，返回字典{班级名: 数据列表}"""
# 特殊姓氏读音字典
SPECIAL_SURNAMES = {
"乐": "Yuè", "单": "Shàn", "解": "Xiè", "查": "Zhā",
"盖": "Gě", "仇": "Qiú", "种": "Chóng", "朴": "Piáo",
"翟": "Zhái", "区": "Ōu", "繁": "Pó", "覃": "Qín",
"召": "Shào", "华": "Huà", "纪": "Jǐ", "曾": "Zēng",
"缪": "Miào", "员": "Yùn", "车": "Chē", "过": "Guō",
"尉": "Yù", "万": "Wàn"
}
# 复姓处理字典
COMPOUND_SURNAMES = {
"欧阳": "Ōu yáng", "上官": "Shàng guān",
"皇甫": "Huáng fǔ", "尉迟": "Yù chí",
"万俟": "Mò qí", "长孙": "Zhǎng sūn",
"司徒": "Sī tú", "司空": "Sī kōng",
"司马": "Sī mǎ", "诸葛": "Zhū gě",
"东方": "Dōng fāng", "独孤": "Dú gū",
"慕容": "Mù róng", "宇文": "Yǔ wén"
}
def correct_surname_pinyin(name):
"""校正姓氏拼音，处理多音字问题"""
if not name or not isinstance(name, str) or name.strip() == "":
return ""
name = name.strip()
# 先检查复姓
for surname in COMPOUND_SURNAMES:
if name.startswith(surname):
surname_pinyin = COMPOUND_SURNAMES[surname]
given_name = name[2:]
given_pinyin = ' '.join([p[0] for p in pinyin(given_name, style=Style.TONE)])
return f"{surname_pinyin} {given_pinyin}"
# 检查单姓
first_char = name[0]
if first_char in SPECIAL_SURNAMES:
surname_pinyin = SPECIAL_SURNAMES[first_char]
given_name = name[1:]
given_pinyin = ' '.join([p[0] for p in pinyin(given_name, style=Style.TONE)])
return f"{surname_pinyin} {given_pinyin}"
return ' '.join([p[0] for p in pinyin(name, style=Style.TONE)])
try:
xls = pd.ExcelFile(file_path)
class_data = {}
for sheet_name in xls.sheet_names:
df = pd.read_excel(
file_path,
sheet_name=sheet_name,
usecols=range(5),
header=None,
dtype={0: str}
)
df = df.iloc[1:]
data_list = []
for index, row in df.iterrows():
row_num = index + 2
raw_id = str(row[0]).strip() if pd.notna(row[0]) else ""
if not raw_id.isdigit():
continue
name = str(row[3]).strip() if pd.notna(row[3]) else ""
if not name:
continue
try:
id_num = int(raw_id)
formatted_id = f"{id_num:02d}"
campus = str(row[1]).strip() if pd.notna(row[1]) else ""
class_name = str(row[2]).strip() if pd.notna(row[2]) else ""
gender = str(row[4]).strip() if pd.notna(row[4]) else ""
name_pinyin = correct_surname_pinyin(name)
data_list.append({
'id_display': f"{id_num}号",
'id_file': formatted_id,
'campus': campus,
'name': name,
'name_pinyin': name_pinyin,
'class_name': class_name,
'gender': gender
})
except Exception as e:
continue
if data_list:
class_data[sheet_name] = data_list
return class_data
except Exception as e:
print(f"读取Excel文件失败：{str(e)}")
return {}
def create_word_documents(class_data, orientation, temp_dir, photo_path):
"""创建Word文档（横版或竖版）"""
if orientation == "横版":
return create_horizontal_documents(class_data, temp_dir, photo_path)
else:
return create_vertical_documents(class_data, temp_dir, photo_path)
def create_horizontal_documents(class_data, temp_dir, photo_path):
"""创建横版Word文档"""
# 模板文件路径
template_path = os.path.join(path, "横版.docx")
# 字体设置
font_name = "微软雅黑"
line_spacing_multiplier = 1
for class_name, students in class_data.items():
# 按9人一组分组
groups = [students[i:i+9] for i in range(0, len(students), 9)]
for group_num, group in enumerate(groups, 1):
# 复制模板文件
file_name = f"{class_name}_横版_{group_num:02d}.docx"
file_path = os.path.join(temp_dir, file_name)
shutil.copy(template_path, file_path)
# 打开文档
doc = Document(file_path)
if len(doc.tables) == 0:
print(f"警告: {file_path} 中没有找到表格")
continue
table = doc.tables[0]
# 填充数据
for i, student in enumerate(group):
row = i // 3
col_pair = i % 3
double_col = 1 + (col_pair * 2)
single_col = col_pair * 2
# 单列单元格（园区和班级）
single_cell = table.cell(row, single_col)
single_cell._element.clear_content()
# 先插入照片
p_photo = single_cell.add_paragraph()
p_photo.alignment = WD_PARAGRAPH_ALIGNMENT.CENTER
try:
run = p_photo.add_run()
run.add_picture(photo_path, width=Cm(2.5), height=Cm(3.5))
except Exception as e:
print(f"插入照片失败: {str(e)}")
# 如果插入照片失败，添加文字提示
run = p_photo.add_run("[照片占位符]")
run.font.name = font_name
run._element.rPr.rFonts.set(qn('w:eastAsia'), font_name)
run.font.size = Pt(10)
# 然后添加园区和班级信息
p_info = single_cell.add_paragraph()
p_info.alignment = WD_PARAGRAPH_ALIGNMENT.CENTER
p_pr = p_info._element.get_or_add_pPr()
spacing = OxmlElement('w:spacing')
spacing.set(qn('w:lineRule'), 'auto')
spacing.set(qn('w:line'), str(int(240 * line_spacing_multiplier)))
spacing.set(qn('w:before'), '0')
spacing.set(qn('w:after'), '0')
p_pr.append(spacing)
run = p_info.add_run(f"XXX幼 {student['campus']}")
run.font.name = font_name
run._element.rPr.rFonts.set(qn('w:eastAsia'), font_name)
run.font.size = Pt(15)
run.font.bold = False
# 双列单元格（拼音、姓名和学号）
double_cell = table.cell(row, double_col)
double_cell._element.clear_content()
p_double = double_cell.add_paragraph()
p_double.alignment = WD_PARAGRAPH_ALIGNMENT.CENTER
p_pr = p_double._element.get_or_add_pPr()
spacing = OxmlElement('w:spacing')
spacing.set(qn('w:lineRule'), 'auto')
spacing.set(qn('w:line'), str(int(240 * line_spacing_multiplier)))
spacing.set(qn('w:before'), '0')
spacing.set(qn('w:after'), '0')
p_pr.append(spacing)
run = p_double.add_run(f"{student['class_name']}
")
run.font.name = font_name
run._element.rPr.rFonts.set(qn('w:eastAsia'), font_name)
run.font.size = Pt(20)
run.font.bold = True
run = p_double.add_run(f"{student['name_pinyin']}
")
run.font.name = font_name
run._element.rPr.rFonts.set(qn('w:eastAsia'), font_name)
run.font.size = Pt(12) # 小4
run.font.bold = False
run = p_double.add_run(f"{student['name']}
")
run.font.name = font_name
run._element.rPr.rFonts.set(qn('w:eastAsia'), font_name)
run.font.size = Pt(25)
run.font.bold = True
run = p_double.add_run(f"{student['id_display']}")
run.font.name = font_name
run._element.rPr.rFonts.set(qn('w:eastAsia'), font_name)
run.font.size = Pt(20)
run.font.bold = True
doc.save(file_path)
print(f"已创建横版: {file_path}")
return temp_dir
def create_vertical_documents(class_data, temp_dir, photo_path):
"""创建竖版Word文档"""
# 模板文件路径
template_path = os.path.join(path, "竖版.docx")
# 字体设置
font_name = "微软雅黑"
line_spacing_multiplier = 1
for class_name, students in class_data.items():
# 按9人一组分组
groups = [students[i:i+9] for i in range(0, len(students), 9)]
for group_num, group in enumerate(groups, 1):
# 复制模板文件
file_name = f"{class_name}_竖版_{group_num:02d}.docx"
file_path = os.path.join(temp_dir, file_name)
shutil.copy(template_path, file_path)
# 打开文档
doc = Document(file_path)
if len(doc.tables) == 0:
print(f"警告: {file_path} 中没有找到表格")
continue
table = doc.tables[0]
# 填充数据
for i, student in enumerate(group):
row = i // 3
col = i % 3
cell = table.cell(row, col)
cell._element.clear_content()
# 添加学校园区信息
p_campus = cell.add_paragraph()
p_campus.alignment = WD_PARAGRAPH_ALIGNMENT.CENTER
# 设置行距
p_pr = p_campus._element.get_or_add_pPr()
spacing = OxmlElement('w:spacing')
spacing.set(qn('w:lineRule'), 'auto')
spacing.set(qn('w:line'), str(int(240 * line_spacing_multiplier)))
spacing.set(qn('w:before'), '0')
spacing.set(qn('w:after'), '0')
p_pr.append(spacing)
# 学校园区
run = p_campus.add_run(f"XXX幼 {student['campus']}")
run.font.name = font_name
run._element.rPr.rFonts.set(qn('w:eastAsia'), font_name)
run.font.size = Pt(15)
run.font.bold = False
# 插入照片
p_photo = cell.add_paragraph()
p_photo.alignment = WD_PARAGRAPH_ALIGNMENT.CENTER
try:
run = p_photo.add_run()
run.add_picture(photo_path, width=Cm(2.5), height=Cm(3.5))
except Exception as e:
print(f"插入照片失败: {str(e)}")
# 如果插入照片失败，添加文字提示
run = p_photo.add_run("[照片占位符]")
run.font.name = font_name
run._element.rPr.rFonts.set(qn('w:eastAsia'), font_name)
run.font.size = Pt(10)
# 添加其他信息
p_info = cell.add_paragraph()
p_info.alignment = WD_PARAGRAPH_ALIGNMENT.CENTER
# 设置行距
p_pr = p_info._element.get_or_add_pPr()
spacing = OxmlElement('w:spacing')
spacing.set(qn('w:lineRule'), 'auto')
spacing.set(qn('w:line'), str(int(240 * line_spacing_multiplier)))
spacing.set(qn('w:before'), '0')
spacing.set(qn('w:after'), '0')
p_pr.append(spacing)
# 班级和学号一行
run = p_info.add_run(f"{student['class_name']} ")
run.font.name = font_name
run._element.rPr.rFonts.set(qn('w:eastAsia'), font_name)
run.font.size = Pt(20)
run.font.bold = True
run = p_info.add_run(f"{student['id_display']}
")
run.font.name = font_name
run._element.rPr.rFonts.set(qn('w:eastAsia'), font_name)
run.font.size = Pt(20)
run.font.bold = True
run = p_info.add_run(f"{student['name_pinyin']}
")
run.font.name = font_name
run._element.rPr.rFonts.set(qn('w:eastAsia'), font_name)
run.font.size = Pt(15)
run.font.bold = False
run = p_info.add_run(f"{student['name']}")
run.font.name = font_name
run._element.rPr.rFonts.set(qn('w:eastAsia'), font_name)
run.font.size = Pt(25)
run.font.bold = True
doc.save(file_path)
print(f"已创建竖版: {file_path}")
return temp_dir
def convert_word_to_pdf(temp_dir):
"""将Word文档转换为PDF"""
word = win32com.client.Dispatch("Word.Application")
word.Visible = False
word_files = glob.glob(os.path.join(temp_dir, "*.docx"))
for word_file in word_files:
try:
doc = word.Documents.Open(word_file)
pdf_file = os.path.join(temp_dir, os.path.basename(word_file).replace(".docx", ".pdf"))
doc.SaveAs(pdf_file, FileFormat=17)
doc.Close()
print(f"已转换为PDF: {pdf_file}")
except Exception as e:
print(f"转换失败: {word_file}, 错误: {str(e)}")
word.Quit()
return temp_dir
def merge_pdfs(temp_dir, output_dir, orientation):
"""合并PDF文件"""
merger = PdfMerger()
pdf_files = glob.glob(os.path.join(temp_dir, f"*_{orientation}_*.pdf"))
class_files = {}
for pdf_file in pdf_files:
filename = os.path.basename(pdf_file)
class_name = filename.split("_")[0]
if class_name not in class_files:
class_files[class_name] = []
class_files[class_name].append(pdf_file)
for class_name, files in class_files.items():
files.sort()
output_file = os.path.join(output_dir, f"{class_name}_新生_{orientation}版卡片(班级姓名学号).pdf")
for pdf in files:
merger.append(pdf)
merger.write(output_file)
merger.close()
print(f"已合并{orientation}版PDF: {output_file}")
merger = PdfMerger()
merger.close()
def cleanup(temp_dir):
"""清理临时文件"""
try:
shutil.rmtree(temp_dir)
print(f"已清理临时文件夹: {temp_dir}")
except Exception as e:
print(f"清理临时文件夹失败: {str(e)}")
# 主程序
if __name__ == "__main__":
# 指定Excel文件路径
path = r"C:Usersjg2yXRZOneDrive桌面20250825幼儿长方形挂牌（照片）"
excel_file = os.path.join(path, "班级信息表.xlsx")
# 创建照片占位符
photo_path = os.path.join(path, "photo_placeholder.png")
create_photo_placeholder(photo_path)
# 创建临时目录
temp_dir = os.path.join(path, "零时文件夹")
os.makedirs(temp_dir, exist_ok=True)
# 处理横版和竖版
choices = [1, 2]
for choice in choices:
if choice == 1:
orientation = "横版"
pdf_output_dir = os.path.join(path, "20250830_托小班新生_横版_卡片")
elif choice == 2:
orientation = "竖版"
pdf_output_dir = os.path.join(path, "20250830_托小班新生_竖版_卡片")
print(f"当前处理方向: {orientation}, 输出目录: {pdf_output_dir}")
# 确保输出文件夹存在
if not os.path.exists(pdf_output_dir):
os.makedirs(pdf_output_dir)
# 调用读取函数
class_data = read_excel_data(excel_file)
if class_data:
# 创建Word文档
temp_dir = create_word_documents(class_data, orientation, temp_dir, photo_path)
# 转换为PDF（保存在同一个临时文件夹中）
temp_dir = convert_word_to_pdf(temp_dir)
# 合并PDF（从临时文件夹读取，保存到输出文件夹）
merge_pdfs(temp_dir, pdf_output_dir, orientation)
# # 清理临时文件
# cleanup(temp_dir)
# 打印汇总信息
print("
处理完成，汇总信息:")
for class_name, students in class_data.items():
group_count = (len(students) // 9 + (1 if len(students) % 9 != 0 else 0))
print(f"{class_name}: {len(students)}名学生，分成{group_count}个文档")
else:
print("未能读取到任何班级数据，请检查文件路径和内容格式。")
# 计算并显示运行时间
end_time = time.time()
total_seconds = end_time - start_time
minutes = int(total_seconds // 60)
seconds = int(total_seconds % 60)
print(f"
程序运行时间: {minutes}分{seconds}秒 (共{total_seconds:.2f}秒)")最后效果（都是虚拟名字）
横版竖版卡套卡片的效果（有照片位置）
、
群发了，我班是竖版的卡套，明天打印希望能够文字部分正好在透明位置里。
实物展示
正好嵌入横板或竖版的卡套中
总体来说，我还是喜欢横板的卡套，学号是单独一行，看起来清晰。竖版为了布局，只能把班级和学号写在一行上。要么下次也挤一挤，拼音和名字紧一点，学号单独一行。
20250902因为中班有插班生，所以中班组长要了被子园卡和点名册。应该是在中班组里群发了。
20250911中1班老师另外要了“长方卡套”（组长们讨论时，好像托小班需要，中大班不需要长方卡套）但是这位中班班主任需要更新后的长方卡套名片纸。
为了方便，我只想生成中班组（中1-中6）。
'''
1.制作20250901托小班长方形卡片，横版85*54mm和竖版54*85mm，一页9张单面
2.添加姓名拼音显示功能（拼音在姓名上方，带声调）
3.python 3.7.8
3. 横版布局：单列显示园区和班级，双列显示拼音、姓名和学号
4. 竖版布局：单列显示所有信息
5. 添加照片占位符：2.5cm宽*3.5cm高，黑色框线，中间写"贴照片"
6、只要生成中1-中6班
豆包,deepseek,阿夏
20250911
'''
# ========== 记录程序开始时间 ==========
import time
start_time = time.time()
# ========== 创建照片占位符图片 ==========
import os
from PIL import Image, ImageDraw, ImageFont
import shutil
import pandas as pd
from pypinyin import pinyin, Style
from docx import Document
from docx.shared import Cm, Pt
from docx.enum.text import WD_PARAGRAPH_ALIGNMENT
from docx.oxml.ns import qn
from docx.oxml import OxmlElement
import win32com.client
import glob
from PyPDF2 import PdfMerger
def create_photo_placeholder(image_path):
"""创建照片占位符图片"""
try:
# 确保目录存在
os.makedirs(os.path.dirname(image_path), exist_ok=True)
# 设置图片尺寸 (2.5cm x 3.5cm)
width, height = int(2.5/2.54*300), int(3.5/2.54*300) # 转换为300dpi的像素
# 创建白色背景图片
img = Image.new('RGB', (width, height), 'white')
draw = ImageDraw.Draw(img)
# 绘制黑色边框
draw.rectangle([(10, 10), (width-10, height-10)], outline='black', width=2)
# 添加文字
try:
# 尝试使用微软雅黑字体
font = ImageFont.truetype("msyh.ttc", 20)
except:
# 如果找不到字体，使用默认字体
font = ImageFont.load_default()
text = "贴照片"
text_bbox = draw.textbbox((0, 0), text, font=font)
text_width = text_bbox[2] - text_bbox[0]
text_height = text_bbox[3] - text_bbox[1]
text_x = (width - text_width) // 2
text_y = (height - text_height) // 2
draw.text((text_x, text_y), text, fill='black', font=font)
# 保存图片
img.save(image_path)
print(f"已创建照片占位符: {image_path}")
return True
except Exception as e:
print(f"创建照片占位符失败: {str(e)}")
return False
# ========== 读取Excel数据 ==========
def read_excel_data(file_path, target_classes=None):
"""读取Excel文件指定工作表数据，返回字典{班级名: 数据列表}"""
# 特殊姓氏读音字典
SPECIAL_SURNAMES = {
"乐": "Yuè", "单": "Shàn", "解": "Xiè", "查": "Zhā",
"盖": "Gě", "仇": "Qiú", "种": "Chóng", "朴": "Piáo",
"翟": "Zhái", "区": "Ōu", "繁": "Pó", "覃": "Qín",
"召": "Shào", "华": "Huà", "纪": "Jǐ", "曾": "Zēng",
"缪": "Miào", "员": "Yùn", "车": "Chē", "过": "Guō",
"尉": "Yù", "万": "Wàn"
}
# 复姓处理字典
COMPOUND_SURNAMES = {
"欧阳": "Ōu yáng", "上官": "Shàng guān",
"皇甫": "Huáng fǔ", "尉迟": "Yù chí",
"万俟": "Mò qí", "长孙": "Zhǎng sūn",
"司徒": "Sī tú", "司空": "Sī kōng",
"司马": "Sī mǎ", "诸葛": "Zhū gě",
"东方": "Dōng fāng", "独孤": "Dú gū",
"慕容": "Mù róng", "宇文": "Yǔ wén"
}
def correct_surname_pinyin(name):
"""校正姓氏拼音，处理多音字问题"""
if not name or not isinstance(name, str) or name.strip() == "":
return ""
name = name.strip()
# 先检查复姓
for surname in COMPOUND_SURNAMES:
if name.startswith(surname):
surname_pinyin = COMPOUND_SURNAMES[surname]
given_name = name[2:]
given_pinyin = ' '.join([p[0] for p in pinyin(given_name, style=Style.TONE)])
return f"{surname_pinyin} {given_pinyin}"
# 检查单姓
first_char = name[0]
if first_char in SPECIAL_SURNAMES:
surname_pinyin = SPECIAL_SURNAMES[first_char]
given_name = name[1:]
given_pinyin = ' '.join([p[0] for p in pinyin(given_name, style=Style.TONE)])
return f"{surname_pinyin} {given_pinyin}"
return ' '.join([p[0] for p in pinyin(name, style=Style.TONE)])
try:
xls = pd.ExcelFile(file_path)
class_data = {}
for sheet_name in xls.sheet_names:
# 如果指定了目标班级，且当前工作表不在目标班级列表中，则跳过
if target_classes and sheet_name not in target_classes:
print(f"跳过工作表: {sheet_name}")
continue
df = pd.read_excel(
file_path,
sheet_name=sheet_name,
usecols=range(5),
header=None,
dtype={0: str}
)
df = df.iloc[1:]
data_list = []
for index, row in df.iterrows():
row_num = index + 2
raw_id = str(row[0]).strip() if pd.notna(row[0]) else ""
if not raw_id.isdigit():
continue
name = str(row[3]).strip() if pd.notna(row[3]) else ""
if not name:
continue
try:
id_num = int(raw_id)
formatted_id = f"{id_num:02d}"
campus = str(row[1]).strip() if pd.notna(row[1]) else ""
class_name = str(row[2]).strip() if pd.notna(row[2]) else ""
gender = str(row[4]).strip() if pd.notna(row[4]) else ""
name_pinyin = correct_surname_pinyin(name)
data_list.append({
'id_display': f"{id_num}号",
'id_file': formatted_id,
'campus': campus,
'name': name,
'name_pinyin': name_pinyin,
'class_name': class_name,
'gender': gender
})
except Exception as e:
continue
if data_list:
class_data[sheet_name] = data_list
print(f"已读取工作表: {sheet_name}, 共{len(data_list)}名学生")
return class_data
except Exception as e:
print(f"读取Excel文件失败：{str(e)}")
return {}
def create_word_documents(class_data, orientation, temp_dir, photo_path):
"""创建Word文档（横版或竖版）"""
if orientation == "横版":
return create_horizontal_documents(class_data, temp_dir, photo_path)
else:
return create_vertical_documents(class_data, temp_dir, photo_path)
def create_horizontal_documents(class_data, temp_dir, photo_path):
"""创建横版Word文档"""
# 模板文件路径
template_path = os.path.join(path, "横版.docx")
# 字体设置
font_name = "微软雅黑"
line_spacing_multiplier = 1
for class_name, students in class_data.items():
# 按9人一组分组
groups = [students[i:i+9] for i in range(0, len(students), 9)]
for group_num, group in enumerate(groups, 1):
# 复制模板文件
file_name = f"{class_name}_横版_{group_num:02d}.docx"
file_path = os.path.join(temp_dir, file_name)
shutil.copy(template_path, file_path)
# 打开文档
doc = Document(file_path)
if len(doc.tables) == 0:
print(f"警告: {file_path} 中没有找到表格")
continue
table = doc.tables[0]
# 填充数据
for i, student in enumerate(group):
row = i // 3
col_pair = i % 3
double_col = 1 + (col_pair * 2)
single_col = col_pair * 2
# 单列单元格（园区和班级）
single_cell = table.cell(row, single_col)
single_cell._element.clear_content()
# 先插入照片
p_photo = single_cell.add_paragraph()
p_photo.alignment = WD_PARAGRAPH_ALIGNMENT.CENTER
try:
run = p_photo.add_run()
run.add_picture(photo_path, width=Cm(2.5), height=Cm(3.5))
except Exception as e:
print(f"插入照片失败: {str(e)}")
# 如果插入照片失败，添加文字提示
run = p_photo.add_run("[照片占位符]")
run.font.name = font_name
run._element.rPr.rFonts.set(qn('w:eastAsia'), font_name)
run.font.size = Pt(10)
# 然后添加园区和班级信息
p_info = single_cell.add_paragraph()
p_info.alignment = WD_PARAGRAPH_ALIGNMENT.CENTER
p_pr = p_info._element.get_or_add_pPr()
spacing = OxmlElement('w:spacing')
spacing.set(qn('w:lineRule'), 'auto')
spacing.set(qn('w:line'), str(int(240 * line_spacing_multiplier)))
spacing.set(qn('w:before'), '0')
spacing.set(qn('w:after'), '0')
p_pr.append(spacing)
run = p_info.add_run(f"景谷二幼 {student['campus']}")
run.font.name = font_name
run._element.rPr.rFonts.set(qn('w:eastAsia'), font_name)
run.font.size = Pt(15)
run.font.bold = False
# 双列单元格（拼音、姓名和学号）
double_cell = table.cell(row, double_col)
double_cell._element.clear_content()
p_double = double_cell.add_paragraph()
p_double.alignment = WD_PARAGRAPH_ALIGNMENT.CENTER
p_pr = p_double._element.get_or_add_pPr()
spacing = OxmlElement('w:spacing')
spacing.set(qn('w:lineRule'), 'auto')
spacing.set(qn('w:line'), str(int(240 * line_spacing_multiplier)))
spacing.set(qn('w:before'), '0')
spacing.set(qn('w:after'), '0')
p_pr.append(spacing)
run = p_double.add_run(f"{student['class_name']}
")
run.font.name = font_name
run._element.rPr.rFonts.set(qn('w:eastAsia'), font_name)
run.font.size = Pt(20)
run.font.bold = True
run = p_double.add_run(f"{student['name_pinyin']}
")
run.font.name = font_name
run._element.rPr.rFonts.set(qn('w:eastAsia'), font_name)
run.font.size = Pt(12)
run.font.bold = False
run = p_double.add_run(f"{student['name']}
")
run.font.name = font_name
run._element.rPr.rFonts.set(qn('w:eastAsia'), font_name)
run.font.size = Pt(25)
run.font.bold = True
run = p_double.add_run(f"{student['id_display']}")
run.font.name = font_name
run._element.rPr.rFonts.set(qn('w:eastAsia'), font_name)
run.font.size = Pt(20)
run.font.bold = True
doc.save(file_path)
print(f"已创建横版: {file_path}")
return temp_dir
def create_vertical_documents(class_data, temp_dir, photo_path):
"""创建竖版Word文档"""
# 模板文件路径
template_path = os.path.join(path, "竖版.docx")
# 字体设置
font_name = "微软雅黑"
line_spacing_multiplier = 1
for class_name, students in class_data.items():
# 按9人一组分组
groups = [students[i:i+9] for i in range(0, len(students), 9)]
for group_num, group in enumerate(groups, 1):
# 复制模板文件
file_name = f"{class_name}_竖版_{group_num:02d}.docx"
file_path = os.path.join(temp_dir, file_name)
shutil.copy(template_path, file_path)
# 打开文档
doc = Document(file_path)
if len(doc.tables) == 0:
print(f"警告: {file_path} 中没有找到表格")
continue
table = doc.tables[0]
# 填充数据
for i, student in enumerate(group):
row = i // 3
col = i % 3
cell = table.cell(row, col)
cell._element.clear_content()
# 添加学校园区信息
p_campus = cell.add_paragraph()
p_campus.alignment = WD_PARAGRAPH_ALIGNMENT.CENTER
# 设置行距
p_pr = p_campus._element.get_or_add_pPr()
spacing = OxmlElement('w:spacing')
spacing.set(qn('w:lineRule'), 'auto')
spacing.set(qn('w:line'), str(int(240 * line_spacing_multiplier)))
spacing.set(qn('w:before'), '0')
spacing.set(qn('w:after'), '0')
p_pr.append(spacing)
# 学校园区
run = p_campus.add_run(f"景谷二幼 {student['campus']}")
run.font.name = font_name
run._element.rPr.rFonts.set(qn('w:eastAsia'), font_name)
run.font.size = Pt(15)
run.font.bold = False
# 插入照片
p_photo = cell.add_paragraph()
p_photo.alignment = WD_PARAGRAPH_ALIGNMENT.CENTER
try:
run = p_photo.add_run()
run.add_picture(photo_path, width=Cm(2.5), height=Cm(3.5))
except Exception as e:
print(f"插入照片失败: {str(e)}")
# 如果插入照片失败，添加文字提示
run = p_photo.add_run("[照片占位符]")
run.font.name = font_name
run._element.rPr.rFonts.set(qn('w:eastAsia'), font_name)
run.font.size = Pt(10)
# 添加其他信息
p_info = cell.add_paragraph()
p_info.alignment = WD_PARAGRAPH_ALIGNMENT.CENTER
# 设置行距
p_pr = p_info._element.get_or_add_pPr()
spacing = OxmlElement('w:spacing')
spacing.set(qn('w:lineRule'), 'auto')
spacing.set(qn('w:line'), str(int(240 * line_spacing_multiplier)))
spacing.set(qn('w:before'), '0')
spacing.set(qn('w:after'), '0')
p_pr.append(spacing)
# 班级和学号一行
run = p_info.add_run(f"{student['class_name']}__ ")
run.font.name = font_name
run._element.rPr.rFonts.set(qn('w:eastAsia'), font_name)
run.font.size = Pt(20)
run.font.bold = True
run = p_info.add_run(f"{student['id_display']}
")
run.font.name = font_name
run._element.rPr.rFonts.set(qn('w:eastAsia'), font_name)
run.font.size = Pt(20)
run.font.bold = True
run = p_info.add_run(f"{student['name_pinyin']}
")
run.font.name = font_name
run._element.rPr.rFonts.set(qn('w:eastAsia'), font_name)
run.font.size = Pt(15)
run.font.bold = False
run = p_info.add_run(f"{student['name']}")
run.font.name = font_name
run._element.rPr.rFonts.set(qn('w:eastAsia'), font_name)
run.font.size = Pt(25)
run.font.bold = True
doc.save(file_path)
print(f"已创建竖版: {file_path}")
return temp_dir
def convert_word_to_pdf(temp_dir):
"""将Word文档转换为PDF"""
word = win32com.client.Dispatch("Word.Application")
word.Visible = False
word_files = glob.glob(os.path.join(temp_dir, "*.docx"))
for word_file in word_files:
try:
doc = word.Documents.Open(word_file)
pdf_file = os.path.join(temp_dir, os.path.basename(word_file).replace(".docx", ".pdf"))
doc.SaveAs(pdf_file, FileFormat=17)
doc.Close()
print(f"已转换为PDF: {pdf_file}")
except Exception as e:
print(f"转换失败: {word_file}, 错误: {str(e)}")
word.Quit()
return temp_dir
def merge_pdfs(temp_dir, output_dir, orientation):
"""合并PDF文件"""
merger = PdfMerger()
pdf_files = glob.glob(os.path.join(temp_dir, f"*_{orientation}_*.pdf"))
class_files = {}
for pdf_file in pdf_files:
filename = os.path.basename(pdf_file)
class_name = filename.split("_")[0]
if class_name not in class_files:
class_files[class_name] = []
class_files[class_name].append(pdf_file)
for class_name, files in class_files.items():
files.sort()
output_file = os.path.join(output_dir, f"{class_name}_新生_{orientation}版卡片(班级姓名学号).pdf")
for pdf in files:
merger.append(pdf)
merger.write(output_file)
merger.close()
print(f"已合并{orientation}版PDF: {output_file}")
merger = PdfMerger()
merger.close()
def cleanup(temp_dir):
"""清理临时文件"""
try:
shutil.rmtree(temp_dir)
print(f"已清理临时文件夹: {temp_dir}")
except Exception as e:
print(f"清理临时文件夹失败: {str(e)}")
# 主程序
if __name__ == "__main__":
# 指定Excel文件路径
path = r"C:Usersjg2yXRZOneDrive桌面20250825幼儿长方形挂牌（照片）"
excel_file = os.path.join(path, "班级信息表.xlsx")
# 指定要生成的班级列表（中1班到中6班）
target_classes = ["中1班", "中2班", "中3班", "中4班", "中5班", "中6班"]
# 创建照片占位符
photo_path = os.path.join(path, "photo_placeholder.png")
create_photo_placeholder(photo_path)
# 处理横版和竖版
choices = [1, 2]
for choice in choices:
# 为每个方向创建独立的临时目录
if choice == 1:
orientation = "横版"
temp_dir = os.path.join(path, "零时文件夹_横版")
pdf_output_dir = os.path.join(path, "20250830_托小班新生_横版_卡片")
elif choice == 2:
orientation = "竖版"
temp_dir = os.path.join(path, "零时文件夹_竖版")
pdf_output_dir = os.path.join(path, "20250830_托小班新生_竖版_卡片")
print(f"当前处理方向: {orientation}, 输出目录: {pdf_output_dir}")
# 确保临时文件夹存在（先清空再创建）
if os.path.exists(temp_dir):
shutil.rmtree(temp_dir)
os.makedirs(temp_dir, exist_ok=True)
# 确保输出文件夹存在
if not os.path.exists(pdf_output_dir):
os.makedirs(pdf_output_dir)
# 调用读取函数，传入目标班级列表
class_data = read_excel_data(excel_file, target_classes)
if class_data:
# 创建Word文档
create_word_documents(class_data, orientation, temp_dir, photo_path)
# 转换为PDF（保存在同一个临时文件夹中）
convert_word_to_pdf(temp_dir)
# 合并PDF（从临时文件夹读取，保存到输出文件夹）
merge_pdfs(temp_dir, pdf_output_dir, orientation)
# 清理临时文件
cleanup(temp_dir)
# 打印汇总信息
print("
处理完成，汇总信息:")
for class_name, students in class_data.items():
group_count = (len(students) // 9 + (1 if len(students) % 9 != 0 else 0))
print(f"{class_name}: {len(students)}名学生，分成{group_count}个文档")
else:
print("未能读取到指定班级的数据，请检查班级名称是否正确。")
# 计算并显示运行时间
end_time = time.time()
total_seconds = end_time - start_time
minutes = int(total_seconds // 60)
seconds = int(total_seconds % 60)
print(f"
程序运行时间: {minutes}分{seconds}秒 (共{total_seconds:.2f}秒)")
用时短。
中1班老师单独发一份
中班组长发一份
的确，如果插一个人，就要等我重新批量一次，对于班主任教师来说，效率太低（我现在带班，也不能马上用程序批量），最好也能做成PDF打印版本和word修改版，便于教师自己修改操作（word里面改名字，但是没有拼音）
© 版权声明
文章版权归作者所有，未经允许请勿转载。如内容涉嫌侵权，请在本页底部进入<联系我们>进行举报投诉!
THE END
暂无评论内容