Read Excel with openpyxl & Write to Word

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.

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.

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?

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

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

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

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

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

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

Copy
# 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)

After adding all the data, save the Word document.

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

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