Inserting Excel Data into a Slide as a Table

By combining the openpyxl and python-pptx libraries learned in Chapter 1, you can easily insert data from an Excel sheet into a PowerPoint table with simple code.

In this lesson, we will learn how to load data from an Excel file and create a table in a PowerPoint slide.

1. Creating a Presentation and Inserting a Table

First, create a PowerPoint presentation and add a table to the slide.

Creating a Presentation and Inserting a Table

from pptx import Presentation
from openpyxl import load_workbook

# Create a PowerPoint file
prs = Presentation()

# Add a new slide (Layout 5: Blank slide)
slide = prs.slides.add_slide(prs.slide_layouts[5])

# Load the Excel file
wb = load_workbook('input_file.xlsx')
ws = wb.active

Code Explanation

• Creating a presentation and adding a slide : A presentation object is created using Presentation(), and a slide is added.

• Adding a new slide : A new slide using layout 5 (blank slide) is added with prs.slides.add_slide(prs.slide_layouts[5]).

• Loading the Excel file : The Excel file with input data is loaded using load_workbook('input_file.xlsx'), and the active sheet is selected with wb.active.

2. Adding a Table to the Slide

Based on the Excel data loaded with openpyxl, set the size of the rows and columns, and add the table to the PowerPoint slide.

Adding Data from Excel to the Table

# Check Excel data and set row and column sizes
rows, cols = ws.max_row, ws.max_column

# Add a table (adjust position and size to fit the slide)
table = slide.shapes.add_table(rows, cols, Inches(0.5), Inches(0.5), Inches(9), Inches(5.5)).table

Code Explanation

• Checking Excel data : The size of the Excel sheet's rows and columns is checked using ws.max_row and ws.max_column.

• Adding a table : A table is added to the slide with slide.shapes.add_table(), and the table object is assigned to the table variable.

3. Adding Excel Data to the Table

Now, use openpyxl to add Excel data to the table.

When adding Excel data to the table, use the enumerate function to get the row and column indices.

The enumerate function takes a list, tuple, string, or other types of data as input and returns the index and value sequentially.

Example of Using the Enumerate Function

data = ['apple', 'banana', 'cherry']

for idx, value in enumerate(data):
    print(f'Index: {idx}, Value: {value}')

# Output:
# Index: 0, Value: apple
# Index: 1, Value: banana
# Index: 2, Value: cherry

To get the data from the Excel sheet, use iter_rows() to retrieve the data by rows and columns.

For example, if the Excel data is as follows:

Header 1	Header 2
Data 1	Data 2
Data 3	Data 4

The data retrieved with the iter_rows function is as follows:

Example of Using the iter_rows Function

for row in ws.iter_rows(max_row=rows, max_col=cols, values_only=True):
    for value in row:
        print(value)

# Output:
# Header 1
# Header 2
# Data 1
# Data 2
# Data 3
# Data 4

Now, use the enumerate function and iter_rows to add the Excel data to the table.

Adding Data to the Table

# Specify table style (set the first row as the header and apply style)
for i, row in enumerate(ws.iter_rows(values_only=True)):
    for j, value in enumerate(row):
        cell = table.cell(i, j)
        # Insert data into each cell
        cell.text = str(value)
        
        # Set font size and text wrapping
        for paragraph in cell.text_frame.paragraphs:
            paragraph.font.size = Pt(12)  # Adjust font size
            paragraph.font.bold = True if i == 0 else False  # Bold for the first row (header)
            paragraph.font.color.rgb = RGBColor(255, 255, 255) if i == 0 else RGBColor(0, 0, 0)  # White text for header
            cell.text_frame.word_wrap = True  # Enable text wrapping

Code Explanation

• Specify table style : The Excel data is retrieved by rows with enumerate(ws.iter_rows(values_only=True)) and added to the table.

• Insert data into each cell : The data is added to each cell with cell.text = str(value).

• for loop : Each cell's text in the table is styled by accessing it via for paragraph in cell.text_frame.paragraphs.

• Set font size and text wrapping : The table's style is specified using paragraph.font.size, paragraph.font.bold, paragraph.font.color.rgb, and cell.text_frame.word_wrap.

Want to learn more?

Join CodeFriends Plus membership or enroll in a course to start your journey.

Learn moreSign In