Automating Sales Data to Word Document

In this lesson, we will create a Word document based on the Excel data loaded in the previous session.

By running the practice code, you will see how to convert sales data into a table within a Word document using the python-docx library, and then save it as a Word document.

The practice code operates in 6 steps as follows.

Copy
import openpyxl  # Library for handling Excel files
from docx import Document  # python-docx library

• openpyxl: A library that helps read and modify Excel files.

• Document: A class that allows creating, adding content to, and modifying Word documents.

Copy
wb = openpyxl.load_workbook('input_file.xlsx')  # Load the Excel file
sheet = wb.active  # Get the currently active sheet

• openpyxl.load_workbook('input_file.xlsx'): Opens the Excel file and enables its data for use.

• wb.active: Gets the currently active sheet (the first sheet) from the loaded Excel file.

Copy
doc = Document()  # Create a new Word document

• Document(): Creates a new Word document where data will be added.

Copy
doc.add_heading('Q1 2024 Sales Performance Report', 0)

• doc.add_heading: Adds a title to the Word document. The title 'Q1 2024 Sales Performance Report' is added, and 0 indicates the largest heading size.

Copy
table = doc.add_table(rows=1, cols=5)  # Create a table with 1 row and 5 columns
table.style = 'Table Grid'  # Add grid style to the table

# Set the first row (column titles)
hdr_cells = table.rows[0].cells
hdr_cells[0].text = 'Month'
hdr_cells[1].text = 'Product'
hdr_cells[2].text = 'Quantity Sold'
hdr_cells[3].text = 'Unit Price'
hdr_cells[4].text = 'Revenue'

• doc.add_table(rows=1, cols=5): Adds a table with 1 row and 5 columns to the Word document. The first row is for column titles.

• table.style = 'Table Grid': Sets the table style to Table Grid with borderlines for a neat appearance.

• hdr_cells: Specifies each cell of the first row and adds the column titles 'Month', 'Product', 'Quantity Sold', 'Unit Price', and 'Revenue'.

Copy
# Read data from the second row onwards
for row in sheet.iter_rows(min_row=2, values_only=True):
    month, product, sales, price, revenue = row  # Store each column's data in variables
    
    # Add a new row to the table
    row_cells = table.add_row().cells
    row_cells[0].text = str(month)  # Add 'Month' data as text
    row_cells[1].text = product  # Add 'Product' data
    row_cells[2].text = str(sales)  # Add 'Quantity Sold' data as text
    row_cells[3].text = str(price)  # Add 'Unit Price' data as text
    row_cells[4].text = str(revenue)  # Add 'Revenue' data as text

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

• sheet.iter_rows(min_row=2, values_only=True): Reads data from the second row of the Excel file. Setting values_only=True retrieves only the cell values.

• Each variable (month, product, sales, price, revenue): Stores data from each column.

• table.add_row(): Adds a new row to the table and assigns the read Excel data to that row.

• doc.save('output_file.docx'): Saves the final Word document with the name 'output_file.docx'.

When this code runs, it creates the following table in the Word document.