Dynamically filling Excel template fields based on conditions in Python

Question

I am trying to generate Excel certificates from a template file, filling specific cells with data based on certain conditions. I want to dynamically fill out product details, recipient address, and check/uncheck cells that represent checkboxes.

Using openpyxl, I’ve set up a minimal example that fills in fields based on parameters like loading_name and unloading_name. The checkboxes are simulated with True and False values, which visually indicate checked/unchecked states.

Here’s what I’m looking to achieve:

1. If unloading_name is "Destination Co. Ltd", populate specific cells with address details and set cells X69 (checked) and Y69 (unchecked).
2. If loading_name matches certain values (e.g., "Source A"), populate other fields with product specifications.

Is there a better way to represent checkboxes in openpyxl, or is setting True/False values sufficient for clarity? Are there improvements to the conditional filling process or any ways to handle more complex templates effectively?

Below is the MWE code, as well as the .csv code snippet which represents template_certificate.xlsx.

from openpyxl import Workbook, load_workbook
import logging
import os

# Setup logging
logging.basicConfig(level=logging.INFO)

# Fill a cell if the value is not empty
def fill_cell(sheet, cell, value):
    if value:
        sheet[cell] = value

# Load or create a template workbook
def load_template(template_path):
    try:
        if os.path.exists(template_path):
            workbook = load_workbook(template_path)
        else:
            # Create a minimal template if it doesn't exist
            workbook = Workbook()
            sheet = workbook.active
            sheet["J4"], sheet["P13"], sheet["X69"], sheet["Y69"] = "", "", False, False  # Initialize cells
            workbook.save(template_path)
        return workbook
    except Exception as e:
        logging.error(f"Error loading template: {e}")
        return None

# Fill specific values based on "loading_name" and "unloading_name"
def conditional_fill(sheet, loading_name, unloading_name):
    if unloading_name == "Destination Co. Ltd":
        # Standard fields for "Destination Co. Ltd"
        fill_cell(sheet, "P13", "123 Example St")
        fill_cell(sheet, "P14", "12345 Sample City")
        fill_cell(sheet, "P15", "Sample Country")
        fill_cell(sheet, "P20", "ID123456")

        # Check/uncheck specific cells
        sheet["X69"] = True    # Checked
        sheet["Y69"] = False   # Unchecked

        # Conditional values based on "loading_name"
        if loading_name == "Source A":
            fill_cell(sheet, "J34", "Product Type A")
            fill_cell(sheet, "J35", "Specification A")
        elif loading_name == "Source B":
            fill_cell(sheet, "J34", "Product Type B")
            fill_cell(sheet, "J35", "Specification B")

# Main function to generate certificate
def create_certificate(template_path, loading_name, unloading_name, output_path):
    logging.info(f"Creating certificate for loading: {loading_name}, unloading: {unloading_name}")
    workbook = load_template(template_path)
    if workbook is None:
        return
    
    sheet = workbook.active
    conditional_fill(sheet, loading_name, unloading_name)

    # Save the output file
    try:
        workbook.save(output_path)
        logging.info(f"Certificate saved as {output_path}")
    except Exception as e:
        logging.error(f"Error saving certificate: {e}")

# Run example
if __name__ == "__main__":
    create_certificate(
        template_path="template_certificate.xlsx",
        loading_name="Source A",
        unloading_name="Destination Co. Ltd",
        output_path="output_certificate.xlsx"
    )

J4,P13,P14,P15,P20,J34,J35,X69,Y69
,,"","","","","",False,True

Notes

• I’m using True and False to represent checkbox states (X69 and Y69), but I’m open to suggestions if there’s a better way to visually represent checkboxes in the generated Excel file.
• I’d like feedback on structuring the conditional filling logic for clarity and maintainability.