Lecture

Creating Certificate Slides from Excel Data

In this lesson, we will read a PowerPoint certificate template (.pptx) and an Excel (.xlsx) file containing student information, and create new PowerPoint slides reflecting the data from the Excel file.

Note : If you are running the provided Python program on your computer, the Template PPT (input_file.pptx) and Student Information (input_file.xlsx) files must be in the same folder as the Python program.

1. Import Required Libraries

Import Libraries

import openpyxl
from pptx import Presentation
from io import BytesIO
from pptx.enum.shapes import MSO_SHAPE_TYPE

io.BytesIO : A class that allows bytes data (data composed of 0s and 1s) to be stored in memory and used as a file.
pptx.enum.shapes.MSO_SHAPE_TYPE : Constants that define various shape types in PowerPoint, used to distinguish between shapes, images, text boxes, etc.

2. Load PowerPoint and Excel Files

Load pptx and xlsx Files

presentation = Presentation("input_file.pptx")
wb = openpyxl.load_workbook("input_file.xlsx")
sheet = wb.active

Presentation("input_file.pptx") : Loads the PowerPoint file.
openpyxl.load_workbook("input_file.xlsx") : Loads the Excel file.
wb.active : Selects the first sheet of the Excel file.

3. Read Excel Data

Create data_sets List

data_sets = []

for row in sheet.iter_rows(min_row=2, values_only=True):
    data = {
        "AWARD_NAME": row[0],
        "NAME": row[1],
        "CONTENT": row[2],
        "DATE": row[3],
        "ORGANIZATION": row[4],
    }
    data_sets.append(data)

sheet.iter_rows() : Reads each row of the Excel sheet, starting from the second row (min_row=2).
values_only=True : Directly retrieves the cell values.
data_sets.append(data) : Stores each row's data in a dictionary called data and adds it to the list.

4. Select Slide Template

Select Template Slide

template_slide = presentation.slides[0]

presentation.slides[0] : Selects the first slide of the PowerPoint as the template. New slides will be created based on this slide.

5. Copy Slide and Handle Images, Text

Copy Slide Elements

for data in data_sets:
    # Create a new slide by copying the template slide
    new_slide = presentation.slides.add_slide(template_slide.slide_layout)

    for shape in template_slide.shapes:
        # Handle group shapes
        if shape.shape_type == MSO_SHAPE_TYPE.GROUP:
            for sub_shape in shape.shapes:
                if sub_shape.shape_type == MSO_SHAPE_TYPE.PICTURE:
                    # ...omission...
        
        # Handle picture shapes
        elif shape.shape_type == MSO_SHAPE_TYPE.PICTURE:
            image_stream = shape.image.blob
            # ...omission...

        # Handle text shapes
        elif shape.has_text_frame:
            new_shape = new_slide.shapes.add_textbox(shape.left, shape.top, shape.width, shape.height)
            new_shape.text_frame.clear()

            # Copy text
            for paragraph in shape.text_frame.paragraphs:
                new_paragraph = new_shape.text_frame.add_paragraph()
                # ...omission...

presentation.slides.add_slide() : Adds a new slide using the template slide.
shape.shape_type == MSO_SHAPE_TYPE.GROUP : Processes grouped shapes by handling the shapes inside the group individually.
shape.shape_type == MSO_SHAPE_TYPE.PICTURE : Processes picture shapes by copying the image to the new slide.
shape.has_text_frame : Copies text-containing shapes by creating new text boxes and copying the text, including font size, boldness, and italics.

6. Replace Text with Excel Data

Replace Text with Data

for shape in new_slide.shapes:
    if shape.has_text_frame:
        for paragraph in shape.text_frame.paragraphs:
            if "<AWARD_NAME>" in paragraph.text:
                paragraph.text = data["AWARD_NAME"]
            elif "<NAME>" in paragraph.text:
                paragraph.text = data["NAME"]
            elif "<CONTENT>" in paragraph.text:
                paragraph.text = data["CONTENT"]
            elif "<DATE>" in paragraph.text:
                paragraph.text = data["DATE"]
            elif "<ORGANIZATION>" in paragraph.text:
                paragraph.text = data["ORGANIZATION"]

Replaces specific text in the PowerPoint slide with data from the Excel file (data). For example, <AWARD_NAME> in the text is replaced with the AWARD_NAME data from Excel.

7. Remove Template Slide and Save Result

Remove Template Slide and Save

presentation.slides._sldIdLst.remove(presentation.slides._sldIdLst[0])
presentation.save("output_file.pptx")

_sldIdLst.remove() : Removes the initial template slide.
presentation.save() : Saves the final result as output_file.pptx.

Summary of the Process

Read data from Excel.
Use the first slide as a template to insert the data from Excel into new slides.
Copy text and images to the new slides, replacing text with data from Excel.
Remove the template slide and save the final result as a file.

By reading data from an Excel file and replacing placeholders in a PPT template, you can generate multiple certificate slides in the same format.

This structure can be effectively applied to automatically generate a large number of certificates, diplomas, business cards, and other template-based documents.

Mission

0 / 1

Run the code and check the results.

Code Editor

Run

Generate

Execution Result

Input/Result

The document is empty.

Try running the code.