What would you think if there was a tool that could do a week's worth of work in just 4 seconds? It's not a fantasy, it's a miracle of reality brought to us by the Python programming language!
1. Background of the story
At the end of each term, the kindergarten children receive a special "Good Child" certificate. This certificate of honor is an encouragement and recognition that every child can receive. If a child does not receive this certificate, both parents and children will be very disappointed and will even find a teacher to theorize.
At my friend's kindergarten, at the end of each term, the handing out of honorary certificates became an important activity. There are nearly 400 certificates to fill out throughout the kindergarten, which usually takes a few days to complete. But this time I solved the problem in a few seconds with Python.
2. Limitations of traditional methods
- Time-consuming: Each certificate takes at least 5 minutes to make, for a total of nearly 25 hours.
- Repetitive work: The work content is mechanical and repetitive, and the efficiency is low.
- Error-prone: Filling in information manually is prone to errors.
3. Python automated solution
Use Python automated office tools to generate PPT certificates in batches.
4. How do I automate it?
4.1 Preparation
- Collect information: Organize the list of all the winning children and related information and store them in Excel.
- Design Template: Design a certificate template in PowerPoint, including variable fields such as name, school year, time, etc.
4.2 Python script development
- Write scripts: Use Python's python-pptx library to manipulate PPT files.
- Batch Filling: Write loops that automatically populate each child's information into the template.
Python code
from pptx import Presentation
prs = Presentation('data\荣誉证书模板.pptx')
slide = prs.slides.add_slide(prs.slide_layouts[0]) # 第一个模板的第0个板式
for ph in slide.placeholders: #遍历这页PPT的所有占位符
phf = ph.placeholder_format #获取占位符的格式
print(phf.idx) #打印其ID编号
ph.text = str(phf.idx)# 将编号写入PPT对应的位置中，以便后面一一对应
# 以上读取到占位符的ID方便后面调用
prs.save('data\荣誉证书模板-占位符编号.pptx')
from openpyxl import load_workbook
wb = load_workbook("data\名单.xlsx")
ws = wb.active
data={}
for row in range(2,ws.max_row+1):
class_id = ws['A' + str(row)].value
name = ws['B' + str(row)].value
data.setdefault(class_id,[])
data[class_id].append(name)
import time
t0=time.time()# 程序开始运行的时间
prs = Presentation('data\荣誉证书模板.pptx')
slide_layout = prs.slide_layouts[0] #调用设置好的母版，因为是母版的第一版式，所以取[0]
for class_id in data:
for name in data[class_id]:
slide = prs.slides.add_slide(slide_layout) #以母版的版式为基础新增一页幻灯片
#往幻灯片中写入内容
slide.placeholders[10].text = "{} 班 {} 同学：".format(class_id,name) #此处是班级和姓名
slide.placeholders[11].text = "在2023-2024学年度第一学期获得"
slide.placeholders[12].text = "“好孩子”称号。"
slide.placeholders[13].text = "特发此证，以资鼓励。"
slide.placeholders[14].text = "市幼儿园"
slide.placeholders[15].text = "2024年4月"
prs.save('data\荣誉证书(总).pptx')
t1 = time.time()
print('程序用时：',str(round(t1-t0))+'秒。') 4.3 Automated Processes
- One-click operation: through pyinstaller to package the entire project code into EXE software, the specific method can be seen in my other article "Everyone can enjoy the convenience of office automation - three steps to convert Python programs into software", the teacher only needs to click the run button, and the program will start working.
- Rapid generation: The program quickly generates 399 certificates, each with accurate information.
5. Practical Results
- Time saving: from 25 hours to 4 seconds, 22,500 times more efficient!
- Zero errors: Automation avoids the errors that can occur when filling in manually.
- Easy to adjust: If you want to add or modify the content of the award in the future, you can simply adjust the template and script.
6. Conclusion
Through this case, we can see the strong potential of Python in the field of office automation. Not only does it help us save time and increase efficiency, but it also reduces human error and improves the quality of our work. If you're also an educator or interested in automating your office, learn Python and start a new chapter in productivity!