Reading Excel Data with openpyxl and Adding Tables and Text to a Word Document

When writing a report, there are times when you need to read data from an Excel file and incorporate it into the report as tables and text.

In this lesson, we'll learn how to use the openpyxl library to read Excel data, and then use python-docx to add text and tables to a Word document.

Structure of the Excel File

First, let's look at the structure of the Excel file we'll work with in this lesson.

The Excel file contains daily sales data for each product, and the data within the sheet is as follows.

Date	Product Name	Quantity	Unit Price	Total Sales
2024-01-01	Product A	100	500	50,000
2024-01-02	Product B	150	750	112,500
2024-01-03	Product C	200	800	160,000
2024-01-04	Product A	130	500	65,000
2024-01-05	Product B	170	750	127,500

In this Excel file, the total sales for each product per day is calculated by multiplying the quantity by the unit price.

How can we convert this data in the Excel file into a Word document and include it all at once in a report?

1. Reading Data from Excel File

First, use the openpyxl library to read data from the Excel file.

Reading Data from the Excel File

# Load the Excel file
workbook = load_workbook('input_file.xlsx')

# Select the active sheet
sheet = workbook.active

# Read data
data = []

# Set min_row=2 to skip the first row (header)
for row in sheet.iter_rows(min_row=2, values_only=True):
    # Add only rows with data
    data.append(row)

• load_workbook('input_file.xlsx') loads the 'input_file.xlsx' file.

• sheet = workbook.active selects the active sheet.

• iter_rows(min_row=2, values_only=True) reads the data from the Excel sheet row by row. Here, min_row=2 is set to skip the first row (header) and start reading from the second row.

2. Creating a Word Document

Now, let's insert the fetched data into a Word document.

Creating a New Word Document

# Create a new Word document
doc = Document()

# Add a title
doc.add_heading('Sales Report', 0)

# Add a paragraph
doc.add_paragraph('The following are the recent sales data.')

• Document() is called to create a new Word document.

• add_heading('Sales Report', 0) adds a title to the document.

• add_paragraph('The following are the recent sales data.') adds a text paragraph.

3. Adding Excel Data to the Word Document as a Table

First, add the header of the table as shown below.

Adding Table Header

# Create a table
table = doc.add_table(rows=1, cols=len(sheet[1]), style='Table Grid')

# Add header to the first row of the table
hdr_cells = table.rows[0].cells

# Add the first row (header) of Excel to the first row of the table
for i, heading in enumerate(sheet[1]):
    # Add text to the header cell
    hdr_cells[i].text = str(heading.value)

• doc.add_table(rows=1, cols=len(sheet[1]), style='Table Grid') creates a table with columns as many as the number of columns in the first row of the Excel data.

• table.rows[0].cells adds the header to the first row of the table.

• for i, heading in enumerate(sheet[1]) adds the first row (header) of the Excel data to the first row of the table.

• hdr_cells[i].text = str(heading.value) adds text to the header cell.

Next, add data to each row of the table.

Adding Data to the Table

# Add data to the table
for row in data:
    # Add a new row to the table
    row_cells = table.add_row().cells

    # Add each row's data to the table
    for i, value in enumerate(row):
        # Add data to each cell
        row_cells[i].text = str(value)

4. Saving the Document

After adding all the data, save the Word document.

Saving the Word Document

# Save the document
doc.save('output_file.docx')

This code saves the created document with the name 'output_file.docx'.

Want to learn more?

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

Learn moreSign In