Dynamic Data and Tables: Powering Up Your Documents with python-docx-template

Photo by Joshua Sortino on Unsplash

In the previous post, I gave a brief introduction to python-docx-template, a library that allows you to dynamically populate Word documents. Now, we’ll take it a step further by exploring how to create dynamic tables with datasources like Excel or Mysql databases. In this post, I’ll guide you through the process of preparing data for dynamic tables, formatting table styles, and managing complex table structures. By the end, you’ll be equipped to generate data-driven documents like reports, invoices, and more, with ease.

Introduction

One of the key advantages of python-docx-template is the ability to create dynamic tables that can be adapted to the data you provide. Dynamic tables enable you to display complex and varying data in a clear, organized manner within your Word documents. They can be especially important for documents like reports, invoices, or summaries, where the information may change frequently or comes from different sources.

Preparing Data for Tables

Before we can insert Data into the desired tables in most cases we need to extract, transform, filter and prepare data with pythons pandas library this process is pretty straight forward.

First make sure pandas is installed on your machine / virtual environment using pip or conda depending on your preference.

pip install pandas 
conda install pandas

The next step will be reading in an excel file and transforming the data into a dictionary.

 import pandas as pd

# Read data from an Excel file
 df = pd.read_excel("data.xlsx")

 # Convert the data to a list of dictionaries
 data = df.to_dict('records')

Data.xlsx looks like the following:

data.xlsx

If we have a mysql database we can use the “sqllite3” library to extract data from a database following is a sample python snippet which we can use to extract the same employee data as we did with pandas.

import sqlite3 
# Connect to the SQLite
database conn = sqlite3.connect("data.db")
# Execute a query to fetch data
cursor = conn.cursor()
cursor.execute("SELECT * FROM employees")

# Convert the data to a list of dictionaries
data = [dict(zip([column[0] for column in cursor.description], row)) for row in cursor.fetchall()]

The resulting dictionary looks as followed:

employee_data = [
    {
        "employee_id": 1001,
        "first_name": "John",
        "last_name": "Doe",
        "position": "Software Engineer",
        "department": "IT",
        "start_date": "2020-01-15",
        "salary": 75000,
    },
    {
        "employee_id": 1002,
        "first_name": "Jane",
        "last_name": "Smith",
        "position": "Data Analyst",
        "department": "IT",
        "start_date": "2019-06-01",
        "salary": 65000,
    },
    # And so on for other employees...
]

Creating the employee report template file

If we want to create our document we first need to create a word document with some jinja tags. The following picture shows the word template we’ll use to create for our employee report.

Based on the first story we now use an additional jinja tag {%tr for … in … %} which signals jinja that we want to insert a table row. For columns it would be {%tc … %}. Inside the for loop we reference the dictionary from earlier and populate each column with data. After everything is done we need to insert an additional row with {%tr endfor %}. Additionaly to our earlier code for creating the dictionary we can use the following code:

from docxtpl import DocxTemplate

# our template file containing the table
template = DocxTemplate("employee_report_template.docx")


# Create a context dictionary with employee data to be inserted
# into the template
context = {
    "employees": employee_data,
}

# Render the template with the context data
template.render(context)

# Save the generated document to a new file
template.save("Employee_Report.docx")

After running the code we receive the following table:

populated Table from Employee_Report.docx

The final template of today gets a little more complex it’s inspired by python-docx-templates.

First things first I used some shading to make the table a little bit more optical appealing. The following new tags were used compared to the previous template:

• colspan & filter operator. Colspan can be used to span the number of columns. In this case the count of col_labels. In our case 4
• “tc”. Is used like “tr” but it creates columns instead
• cellbg. Is used to set color of a table cell.

from docxtpl import DocxTemplate
from datetime import  datetime
template = DocxTemplate("dynamic_table.docx")


context = {
    'Company':'Tonys Logistics',
    'date' : datetime.now().strftime("%d.%m.%Y"),
    'col_labels': ['Product', 'Category', 'Price', 'Stock'],
'tbl_contents': [
    {'label': 'Item 1', 'cols': ['Laptop', 'Electronics', '$900', '50'], 'bg': 'cccccc'},
    {'label': 'Item 2', 'cols': ['T-shirt', 'Apparel', '$15', '200'], 'bg': 'bababa'},
    {'label': 'Item 3', 'cols': ['Coffee Mug', 'Kitchenware', '$8', '150'], 'bg': 'cccccc'},
    {'label': 'Item 4', 'cols': ['Smartphone', 'Electronics', '$700', '100'], 'bg': 'bababa'},
],
}

template.render(context)
template.save("dynamic_table_out.docx")

Company and date are used as variable in the header of our document.
col_labels has informations to the products. tbl_contents has the content of the table. Bg is used to set the cell color. The rendered table looks as followed:

Conclusion

Throughout this post, we’ve explored the power of python-docx-template in creating dynamic tables within Word documents. We learned how to prepare data for tables, whether it’s from an Excel file or a database, using libraries like pandas or sqlite3. I also showed how to use more advanced Jinja2 tags to create table rows and columns, and how to apply styling to tables, including alternating row colors for better readability.

By mastering these techniques, you can now generate data-driven documents like reports, invoices, and summaries with ease. Python-docx-template is an incredibly versatile tool that can help you automate and streamline the creation of Word documents, saving you time and ensuring consistency across your documents.

Stay tuned for the next blog post, where we’ll dive into practical examples and use cases such as generating reports with dynamic tables, creating invoices with product and pricing data, and producing data-driven summaries and overviews. You can connect with me on LinkedIn or visit my GitHub to get all the files accompanying the blog post