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.

Creating a Word Document with python-docx

The practice code operates in 6 steps as follows.

1. Import Required Libraries

Importing openpyxl and python-docx libraries

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.

2. Load Excel File

Loading Excel file with load_workbook function

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.

3. Create Word Document

Creating Word document with Document class

doc = Document()  # Create a new Word document

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

4. Add Report Title

Adding title with add_heading function

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.

5. Create Table and Set Column Titles

Creating table and setting column titles

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'.

6. Add Excel Data and Save Word Document

Adding Excel data and saving Word document

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

Output Result in Word Document

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

Month	Product	Quantity Sold	Unit Price	Revenue
Jan	Camera	50	500,000	25,000,000
Jan	Computer	30	1,200,000	36,000,000
Jan	Smartphone	100	900,000	90,000,000
Feb	Camera	45	480,000	21,600,000
Feb	Computer	35	1,150,000	40,250,000
Feb	Smartphone	90	850,000	76,500,000
Mar	Camera	40	470,000	18,800,000
Mar	Computer	40	1,100,000	44,000,000
Mar	Smartphone	95	800,000	76,000,000

Want to learn more?

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

Learn moreSign In