Read Excel with openpyxl & Write to Word

Converting Excel data into Word documents is a common task in report generation and document automation workflows.

In this lesson, we will use the openpyxl library to read data from an Excel file and the python-docx library to add text and tables to a Word document.

Let’s first look at the structure of the Excel file we’ll be working with.

The Excel file contains sales data with the following structure:

The file calculates the total sales for each product by multiplying the quantity by the unit price.

Our goal is to transform this data into a Word document and include it in a report.

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

This library provides features for reading and writing Excel files and allows you to extract data with simple code.

Copy
from openpyxl import load_workbook

# Load the Excel file
workbook = load_workbook('sales_data.xlsx')
sheet = workbook.active

# Read data
data = []
for row in sheet.iter_rows(min_row=2, values_only=True):
    data.append(row)

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

• sheet = workbook.active selects the active sheet.

• iter_rows(min_row=2, values_only=True) reads data row by row, starting from the second row (min_row=2) to skip the header.

This code retrieves the Excel data as a list.

Next, we use python-docx to create a new Word document and add a title and paragraph.

Copy
from docx import Document

# Create a new Word document
doc = Document()
doc.add_heading('Sales Report', 0)
doc.add_paragraph('Below is the recent sales data.')

• Document() creates a new Word document.

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

• add_paragraph('Below is the recent sales data.') adds a paragraph to the document.

We insert the data read from Excel into the Word document as a table.

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

# Add headers to the first row of the table
hdr_cells = table.rows[0].cells
for i, heading in enumerate(sheet[1]):
    hdr_cells[i].text = str(heading.value)

# Add data to the table
for row in data:
    row_cells = table.add_row().cells
    for i, value in enumerate(row):
        row_cells[i].text = str(value)

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

• The first row of the table is populated with headers (e.g., Date, Product Name, etc.).

• The data is added row by row to the table using table.add_row().cells.

After adding all the data, save the Word document.

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

This code saves the completed document as 'sales_report.docx'.