Creating Completion Certificate Slides from Excel Data

In this lesson, we will read a PowerPoint template (.pptx) for certificates and an Excel file (.xlsx) containing student information to generate new PowerPoint slides that incorporate data from the Excel file.

Note: If you are running the Python program provided for practice on your computer, ensure that both the template PPT (input_file.pptx) and student information (input_file.xlsx) files are in the same folder as the Python program.

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

• openpyxl: A Python library for reading and modifying Excel files.

• pptx: A library for creating and editing PowerPoint files.

• io.BytesIO: A class that allows byte data (composed of 0s and 1s) to be stored in memory and used as a file.

• pptx.enum.shapes.MSO_SHAPE_TYPE: Constants defining various shape types in PowerPoint, used to differentiate shapes, images, text boxes, etc.

Copy
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.

Copy
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(): Iterates through rows in the Excel sheet. Here, it starts from the second row (min_row=2).

• values_only=True: Directly retrieves cell values.

• data_sets.append(data): Stores each row's data as a dictionary and adds it to the list.

Copy
template_slide = presentation.slides[0]

• presentation.slides[0]: Selects the first slide in the PowerPoint file as the template. This slide is used to generate new slides.

Copy
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:
        # Processing grouped shapes
        if shape.shape_type == MSO_SHAPE_TYPE.GROUP:
            for sub_shape in shape.shapes:
                if sub_shape.shape_type == MSO_SHAPE_TYPE.PICTURE:
                    # ...omitted...
        
        # Processing picture shapes
        elif shape.shape_type == MSO_SHAPE_TYPE.PICTURE:
            image_stream = shape.image.blob
            # ...omitted...

        # Processing 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()
                # ...omitted...

• presentation.slides.add_slide(): Adds a new slide based on the template slide.

• shape.shape_type == MSO_SHAPE_TYPE.GROUP: Processes grouped shapes by handling individual shapes within the group.

• shape.shape_type == MSO_SHAPE_TYPE.PICTURE: Copies images from the template slide to the new slide.

• shape.has_text_frame: Copies text shapes by creating a new text box and transferring the text, maintaining font size, boldness, and italics.

Copy
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 placeholders (e.g., <AWARD_NAME>) in the PowerPoint slides with corresponding Excel data.

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

• _sldIdLst.remove(): Deletes the initial template slide. This is an unofficial method to remove the first slide from the PowerPoint slide list.

• presentation.save(): Saves the final result as output_file.pptx.

1. Read data from the Excel file.

2. Use the first slide as a template and insert data from the Excel file into new slides.

3. Copy text and images from the template slide, replacing text with Excel data.

4. Remove the template slide and save the final result as a file.