【办公类-54-11】20260201 2025学年第二学期班级点名册模版（添加“XX节放假”字样，双休国定假涂成灰色、修改标题和页眉，标题增加园区、批量导出PDF）
背景需求
【办公类-54-08】20250902 2025学年第一学期班级点名册模版（双休国定假涂成灰色、修改标题和页眉，批量导出PDF）根据新Excel模版，标题增加园区、空姓名行填充灰色
https://mp.csdn.net/mp_blog/creation/editor/150493745
修改以前的代码
今年下学期特殊，从3月开始，所以2月两个表格删除
核对放假（灰色栏）的位置
随后我还希望在灰色的地方，添加“XX节放假”
'''
下学期点名册批量（灰色）
1、修改原始模版，更改年月、标题、页眉
2、读取所有的EXCEL，批量制作EXCEL多月
3、添加“清明节放假”等汉字
4、每页标题包含园区）
5、转成PDF，便于打印
deepseek、阿夏
20250808
'''
import openpyxl
from openpyxl.styles import PatternFill, Font, Alignment
from datetime import datetime, timedelta
import os
from win32com.client import Dispatch
import traceback
import time
import psutil
# ===================== 基础配置 =====================
day = '2026年3月'
title1 = '2025学年第二学期'
title2 = '上海市XXXX第二幼儿园'
names = 'XX'
# 文件路径配置
base_path = os.path.abspath(r'C:Usersjg2yXRZOneDrive桌面60201开学点名册批量制作下学期名单')
template_file = os.path.join(base_path, '01（模版）原始点名表.xlsx')
class_info_file = os.path.join(base_path, '收集名单', '班级信息表.xlsx')
output_template = os.path.join(base_path, f'{day}点名册模版_{names}.xlsx')
output_dir = os.path.join(base_path, f'{day}{names}所有点名册')
# 节假日配置
holidays_config = {
'2026-04上': {5: '清明节放假', },
'2026-05上': {3: '劳动节放假', },
'2026-06下': {5: '端午节放假', }
}
# ===================== 样式配置（核心修复：节假日文字黑+背景灰） =====================
# 1. 双休日样式：灰色背景（原配置不变）
gray_fill = PatternFill(start_color='D3D3D3', end_color='D3D3D3', fill_type='solid')
# 2. 节假日样式：文字黑色粗体 + 灰色背景（关键修复）
holiday_font = Font(
name='宋体',
size=10,
bold=False, # 不加粗
color='000000' # 文字颜色：纯黑色（RGB代码）
)
holiday_fill = PatternFill(
start_color='D3D3D3', # 背景颜色：灰色（与双休日同色，也可改为其他灰色如'E6E6E6'）
end_color='D3D3D3',
fill_type='solid'
)
# 3. 对齐样式：文字居中（原配置不变）
center_alignment = Alignment(horizontal='center', vertical='center')
# ===================== Excel进程清理工具 =====================
def kill_excel_processes():
try:
excel_procs = []
for proc in psutil.process_iter(['name', 'cmdline']):
try:
proc_name = proc.name().lower()
if 'excel' in proc_name and any('office' in cmd.lower() for cmd in proc.cmdline()):
excel_procs.append(proc)
except (psutil.NoSuchProcess, psutil.AccessDenied, IndexError):
continue
if excel_procs:
print(f"
⚠️ 发现 {len(excel_procs)} 个残留Excel进程，开始清理...")
for proc in excel_procs:
try:
proc.terminate()
proc.wait(timeout=2)
print(f" ✅ 关闭Excel进程: PID={proc.pid}")
except (psutil.NoSuchProcess, psutil.TimeoutExpired):
print(f" ⚠️ 进程 {proc.pid} 已退出或超时")
except Exception as e:
print(f" ❌ 关闭进程 {proc.pid} 失败: {str(e)}")
else:
print("
✅ 未发现残留Excel进程")
except Exception as e:
print(f"❌ 清理Excel进程失败: {str(e)}")
# ===================== 核心工具函数 =====================
def excel_to_pdf(input_file):
if not os.path.exists(input_file):
print(f"❌ PDF转换失败：文件不存在 - {input_file}")
return False
pdf_file = os.path.splitext(input_file)[0] + '.pdf'
excel_app = None
wb = None
try:
excel_app = Dispatch("Excel.Application")
excel_app.Visible = False
excel_app.DisplayAlerts = False
excel_app.EnableEvents = False
input_abs = os.path.abspath(input_file)
wb = excel_app.Workbooks.Open(input_abs)
for ws in wb.Worksheets:
ws.PageSetup.Orientation = 1 #1=纵向 =横向
ws.PageSetup.Zoom = False
ws.PageSetup.FitToPagesTall = 1
ws.PageSetup.FitToPagesWide = 1
ws.PageSetup.CenterHorizontally = True
ws.PageSetup.CenterVertically = True
ws.PageSetup.LeftMargin = excel_app.CentimetersToPoints(1)
ws.PageSetup.RightMargin = excel_app.CentimetersToPoints(1)
ws.PageSetup.TopMargin = excel_app.CentimetersToPoints(1)
ws.PageSetup.BottomMargin = excel_app.CentimetersToPoints(1)
# 后续插入页眉汉字，页眉边距1CM
ws.PageSetup.HeaderMargin = excel_app.CentimetersToPoints(1.2)
ws.PageSetup.FooterMargin = excel_app.CentimetersToPoints(0.5)
ws.PageSetup.CenterHeader = f"&"宋体,10"{title2}"
pdf_abs = os.path.abspath(pdf_file)
wb.ExportAsFixedFormat(Type=0, Filename=pdf_abs, Quality=1)
print(f"✅ PDF转换完成（竖版+页眉）: {os.path.basename(pdf_file)}")
return True
except Exception as e:
print(f"❌ PDF转换/页眉设置失败: {str(e)}")
traceback.print_exc()
return False
finally:
if wb:
try:
wb.Close(SaveChanges=False)
print(f" ✅ 关闭Excel工作簿")
except Exception as e:
print(f" ❌ 关闭工作簿失败: {str(e)}")
if excel_app:
try:
excel_app.Quit()
del excel_app
print(f" ✅ 退出Excel应用")
except Exception as e:
print(f" ❌ 退出Excel失败: {str(e)}")
kill_excel_processes()
kill_excel_processes()
# ===================== 节假日标记函数（应用新样式） =====================
def add_holiday_marks(workbook):
print("
=== 开始添加节假日标记（文字黑+背景灰） ===")
for sheet_name in workbook.sheetnames:
ws = workbook[sheet_name]
if sheet_name in holidays_config:
holiday_days = holidays_config[sheet_name]
print(f"📅 工作表 {sheet_name}: 处理节假日 {list(holiday_days.keys())}日")
for day_num, holiday_text in holiday_days.items():
# 计算日期列（日期显示列）和状态列（节假日文字列）
date_col = 3 + (day_num - 1) * 2 # 日期列（如1日对应第3列）
status_col = date_col + 1 # 状态列（节假日文字所在列）
if date_col <= ws.max_column and status_col <= ws.max_column:
# 遍历所有学生行（第5-34行） 只要一行
for row in range(5, 6):
# 1. 处理节假日文字列（核心：应用文字黑+背景灰）
status_cell = ws.cell(row=row, column=status_col)
status_cell.value = holiday_text # 写入节假日文字
status_cell.font = holiday_font # 文字：黑色粗体
status_cell.fill = holiday_fill # 背景：灰色
status_cell.alignment = center_alignment # 文字居中
# 2. 处理日期列（可选：日期列也添加灰色背景，与文字列统一）
date_cell = ws.cell(row=row, column=date_col)
date_cell.fill = holiday_fill # 日期列背景灰
date_cell.alignment = center_alignment # 日期居中
else:
print(f"⚠️ {sheet_name}工作表 {day_num}日列位置无效，跳过处理")
print("=== 节假日标记添加完成 ===")
return workbook
# ===================== 主流程执行 =====================
def main():
print("="*50)
print("🏫 幼儿园点名册批量生成系统（节假日样式修复版）")
print(f"📅 处理周期: {day} - 2026年6月")
print(f"🏢 园区名称: {title2}")
print(f"📄 样式配置: 节假日（文字黑+背景灰）| 双休日（背景灰）")
print("="*50)
print("
=== 初始化：清理残留Excel进程 ===")
kill_excel_processes()
# 1. 创建输出目录
os.makedirs(os.path.join(base_path, '收集名单'), exist_ok=True)
os.makedirs(output_dir, exist_ok=True)
print(f"
📁 输出目录准备完成: {output_dir}")
# 2. 生成基础点名表模板
print("
=== 步骤1: 生成基础点名表模板 ===")
template_wb = None
try:
if not os.path.exists(template_file):
print(f"❌ 模板生成失败：模板文件不存在 - {template_file}")
return
template_wb = openpyxl.load_workbook(template_file)
sheet_names = template_wb.sheetnames
sheet_count = len(sheet_names)
print(f"📋 原始模板工作表: {sheet_names} (共{sheet_count}个)")
months = ['2026-03', '2026-03', '2026-04', '2026-04',
'2026-05', '2026-05', '2026-06', '2026-06']
if len(months) != sheet_count:
months = months[:sheet_count]
print(f"⚠️ 日期列表已调整为 {len(months)} 个，与工作表数量匹配")
new_sheet_names = []
for i in range(sheet_count):
suffix = sheet_names[i][-1:] if len(sheet_names[i]) >= 1 else ''
new_name = f"{months[i]}{suffix}"
new_sheet_names.append(new_name)
print(f"🔖 新工作表名称: {new_sheet_names}")
for i in range(min(len(sheet_names), len(new_sheet_names))):
old_name = sheet_names[i]
new_name = new_sheet_names[i]
template_wb[old_name].title = new_name
print(f" ✅ 重命名: {old_name} → {new_name}")
# 双休日标记
xx = [
[1,7,8,14,15,16], [21,22,28,29], # 3月
[4,5,6,11,12,16], [18,19,25,26,31], # 4月
[1,2,3,4,5,10,16], [16,17,23,24,30,31], # 5月
[6,7,13,14,16], [19,20,21,27,28,31] # 6月
]
if len(xx) < sheet_count:
while len(xx) < sheet_count:
xx.append(xx[-1])
print(f"⚠️ 双休日配置已补充至 {len(xx)} 个")
for bg_idx in range(sheet_count):
if bg_idx < len(template_wb.worksheets):
ws = template_wb.worksheets[bg_idx]
current_sheet = new_sheet_names[bg_idx] if bg_idx < len(new_sheet_names) else f"Sheet{bg_idx+1}"
if len(current_sheet) >= 7:
ws['AB3'] = current_sheet[-3:-1]
ws['W3'] = current_sheet[:4]
else:
ws['AB3'] = '03'
ws['W3'] = '2026'
ws['A2'] = f"{title1} {title2} 点名表"
ws.merge_cells('A2:AH2')
if bg_idx < len(xx):
target_days = xx[bg_idx]
else:
target_days = []
print(f"⚠️ 工作表{bg_idx+1}无双休日配置")
for day_num in target_days:
try:
if bg_idx % 2 == 0:
col_start = day_num * 2 + 1
col_end = day_num * 2 + 2
else:
col_start = (day_num - 15) * 2 + 1
col_end = (day_num - 15) * 2 + 2
if col_start <= ws.max_column and col_end <= ws.max_column:
for row in range(4, 43):
for col in range(col_start, col_end + 1):
ws.cell(row=row, column=col).fill = gray_fill
else:
print(f" ⚠️ 列超出范围: 开始{col_start}, 结束{col_end}, 最大{ws.max_column}")
except Exception as e:
print(f" ❌ 处理日期{day_num}失败: {str(e)}")
# 添加节假日标记（应用新样式）
template_wb = add_holiday_marks(template_wb)
template_wb.save(output_template)
print(f"✅ 基础模板生成完成: {os.path.basename(output_template)}")
except Exception as e:
print(f"❌ 模板生成失败: {str(e)}")
traceback.print_exc()
finally:
if template_wb:
try:
template_wb.close()
print(f"✅ 关闭模板工作簿")
except Exception as e:
print(f"❌ 关闭模板工作簿失败: {str(e)}")
kill_excel_processes()
# 3. 批量生成班级点名表
print("
=== 步骤2: 批量生成班级点名表 ===")
class_info_wb = None
try:
if not os.path.exists(class_info_file):
print(f"❌ 班级点名表生成失败：班级信息表不存在 - {class_info_file}")
return
class_info_wb = openpyxl.load_workbook(class_info_file)
class_sheets = class_info_wb.worksheets[1:] if len(class_info_wb.worksheets) > 1 else []
counter = 0
print(f"📥 读取到 {len(class_sheets)} 个班级信息")
for class_sheet in class_sheets:
current_class_wb = None
try:
class_name = class_sheet.title
print(f"
🏫 处理班级: {class_name}")
campus_info = class_sheet['B2'].value if class_sheet['B2'].value else ""
student_names = []
for row in range(2, 37):
try:
name_val = class_sheet.cell(row=row, column=4).value
student_names.append(name_val if name_val is not None else "")
except Exception as e:
print(f" ⚠️ 读取行{row}姓名失败: {str(e)}")
student_names.append("")
male_count = 0
female_count = 0
for row in range(2, 37):
try:
gender = class_sheet.cell(row=row, column=5).value
if gender == "男":
male_count += 1
elif gender == "女":
female_count += 1
except Exception as e:
print(f" ⚠️ 读取行{row}性别失败: {str(e)}")
class_sheet['E36'] = female_count
class_sheet['E37'] = male_count
print(f"👥 学生统计: 男{male_count}人, 女{female_count}人 (共{male_count+female_count}人)")
if not os.path.exists(output_template):
print(f" ❌ 加载模板失败：模板文件不存在 - {output_template}")
continue
current_class_wb = openpyxl.load_workbook(output_template)
if len(class_name) >= 2:
formatted_class = f"{class_name[0]}({class_name[1]})班"
else:
formatted_class = f"{class_name}班"
for ws in current_class_wb.worksheets:
try:
ws['A2'] = f"{title1} {title2}({campus_info}) 点名册"
ws.merge_cells('A2:AH2')
ws['C3'] = formatted_class
ws.merge_cells('C3:G3')
ws['L3'] = male_count
ws.merge_cells('L3:M3')
ws['S3'] = female_count
ws.merge_cells('S3:T3')
ws.merge_cells('W3:Z3')
ws.merge_cells('AB3:AC3')
for idx, name in enumerate(student_names[:30]):
if idx + 5 <= ws.max_row:
ws.cell(row=idx + 5, column=2, value=name)
else:
print(f" ⚠️ 行{idx+5}超出表格范围，跳过写入")
for i in range(16):
start_col = 3 + 2 * i
end_col = 3 + 2 * i + 1
if start_col <= ws.max_column and end_col <= ws.max_column:
ws.merge_cells(start_row=4, start_column=start_col,
end_row=4, end_column=end_col)
else:
print(f" ⚠️ 合并列超出范围: {start_col}-{end_col}，跳过")
except Exception as e:
print(f" ❌ 配置工作表失败: {str(e)}")
class_excel_path = os.path.join(output_dir, f"{day}{formatted_class}每月点名表_{names}.xlsx")
try:
current_class_wb.save(class_excel_path)
print(f"✅ Excel文件生成（节假日样式：文字黑+背景灰）: {os.path.basename(class_excel_path)}")
excel_to_pdf(class_excel_path)
counter += 1
except Exception as e:
print(f" ❌ 保存Excel文件失败: {str(e)}")
traceback.print_exc()
except Exception as e:
print(f"❌ 处理班级{class_name}失败: {str(e)}")
traceback.print_exc()
finally:
if current_class_wb:
try:
current_class_wb.close()
print(f" ✅ 关闭班级模板工作簿")
except Exception as e:
print(f" ❌ 关闭班级工作簿失败: {str(e)}")
kill_excel_processes()
class_info_wb.save(class_info_file)
print(f"
=== 批量处理完成 ===")
print(f"📊 总计处理班级: {counter} 个")
print(f"📁 所有文件保存路径: {output_dir}")
except Exception as e:
print(f"❌ 班级点名表批量生成失败: {str(e)}")
traceback.print_exc()
finally:
if class_info_wb:
try:
class_info_wb.close()
print(f"✅ 关闭班级信息表工作簿")
except Exception as e:
print(f"❌ 关闭班级信息表失败: {str(e)}")
print("
=== 最终清理：确认无残留Excel进程 ===")
kill_excel_processes()
print(f"
✅ 所有操作完成！")
# ===================== 执行主程序 =====================
if __name__ == "__main__":
required_packages = ['openpyxl', 'pywin32', 'psutil']
missing_packages = []
for pkg in required_packages:
try:
__import__(pkg)
except ImportError:
missing_packages.append(pkg)
if missing_packages:
print(f"⚠️ 发现缺失依赖包: {', '.join(missing_packages)}，正在自动安装...")
import subprocess
import sys
for pkg in missing_packages:
subprocess.check_call([sys.executable, "-m", "pip", "install", pkg])
print("✅ 所有依赖包安装完成")
main()
实际都是插入在，右侧列
本次的不同之处，就是添加了“XXX节放假”在灰色部分里。来说明“为什么会连着三天、五天是灰色，因为有假日”
