Generating Custom Excel Reports with Python: A Comprehensive Guide

When it comes to automating report generation and enhancing the readability of Excel files, Python provides a powerful set of libraries, including OpenPyXL and Pillow. In this post, we will dive into a Python script designed for generating custom Excel reports by populating templates with dynamic data, including text, images, and conditional formatting. This solution is particularly useful for businesses or developers who frequently work with Excel files and need a programmatic way to customize them.

What Does the Script Do?

This Python script is designed to:

Load an Excel template and populate it with dynamic data.
Customize content such as text placeholders, images, and checkboxes.
Hide or group rows conditionally based on specific rules.
Handle image resizing and alignment, including centering within specific cells.
Export the customized workbook as a new Excel file with a timestamped filename.

Let’s break down the script and demonstrate its usage.

The Complete Code

Below is the complete script for generating custom Excel reports:

from openpyxl import load_workbook, Workbook
from datetime import datetime
from openpyxl.drawing.image import Image
import re

from openpyxl.drawing.spreadsheet_drawing import AbsoluteAnchor
from openpyxl.drawing.xdr import XDRPoint2D, XDRPositiveSize2D
from openpyxl.utils.units import pixels_to_EMU
from openpyxl.utils import get_column_letter, column_index_from_string
from django.conf import settings
from PIL import Image as PILImage
import os

def set_image_with_offset(sheet, img, cell_coordinate, offset_x=0, offset_y=0):
    col_letter = ''.join(filter(str.isalpha, cell_coordinate))  # Extract column letter
    row_number = int(''.join(filter(str.isdigit, cell_coordinate)))  # Extract row number
    col_idx = column_index_from_string(col_letter) - 1
    row_idx = row_number - 1

    col_width = sheet.column_dimensions[col_letter].width or 10  # Default column width
    row_height = sheet.row_dimensions[row_number].height or 15  # Default row height

    col_pixels = col_width * 7.5  # Approximation: ~7.5 pixels per width unit
    row_pixels = row_height * 0.75  # Approximation: ~0.75 pixels per height unit

    cell_x = col_idx * col_pixels
    cell_y = row_idx * row_pixels

    final_x = cell_x + offset_x
    final_y = cell_y + offset_y

    pos = XDRPoint2D(pixels_to_EMU(final_x), pixels_to_EMU(final_y))
    size = XDRPositiveSize2D(pixels_to_EMU(img.width), pixels_to_EMU(img.height))

    img.anchor = AbsoluteAnchor(pos=pos, ext=size)
    sheet.add_image(img)

def center_image_in_cell(sheet, img, cell_coordinate):
    col_letter = ''.join(filter(str.isalpha, cell_coordinate))
    row_number = int(''.join(filter(str.isdigit, cell_coordinate)))

    col_width = sheet.column_dimensions[col_letter].width or 10
    row_height = sheet.row_dimensions[row_number].height or 15

    col_pixels = col_width * 7.5
    row_pixels = row_height * 0.75

    img_width, img_height = img.width, img.height

    offset_x = int((col_pixels - img_width) / 2 * pixels_to_EMU(1))
    offset_y = int((row_pixels - img_height) / 2 * pixels_to_EMU(1))

    img.anchor = AbsoluteAnchor(
        pos=XDRPoint2D(pixels_to_EMU(offset_x), pixels_to_EMU(offset_y)),
        ext=XDRPositiveSize2D(pixels_to_EMU(img_width), pixels_to_EMU(img_height)),
    )

    sheet.add_image(img)

def gen_xlsx(template_file, selected_sheets, prefix_filename, data):
    checked_image_path = f"{settings.BASE_DIR}/report/checkbox_checked.jpg"
    unchecked_image_path = f"{settings.BASE_DIR}/report/checkbox_unchecked.jpg"

    workbook = load_workbook(template_file)

    for sheet_name in workbook.sheetnames:
        if sheet_name not in selected_sheets:
            del workbook[sheet_name]

    for sheet_name in selected_sheets:
        sheet = workbook[sheet_name]

        for row in sheet.iter_rows():
            for cell in row:
                if cell.value and isinstance(cell.value, str) and cell.value.startswith("<") and cell.value.endswith(">"):
                    placeholder = cell.value.strip("<>")
                    value = data.get(placeholder)

                    if isinstance(value, str):
                        cell.value = value
                    elif isinstance(value, bool):
                        img = Image(checked_image_path if value else unchecked_image_path)
                        center_image_in_cell(sheet, img, cell.coordinate)
                        cell.value = None

    timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
    output_path = f"{prefix_filename}_{timestamp}.xlsx"
    workbook.save(output_path)
    return output_path

if __name__ == "__main__":
    data = {
        "customer": "Alice Johnson",
        "inspect_date": "2025-02-01",
        "lot_no": "ABC12345",
        "staff_name": "Inspector Team",
        "size": "Medium",
        "pcs": "25 pcs",
        "hardness.d1_act": "12.5",
        "hardness.acc": True,
        "hardness.spe_acc": False,
        "dimension_app.d1_act": "45.8",
        "dimension_app.acc": True,
        "dimension_app.spe_acc": True,
    }

    output_file = gen_xlsx(
        template_file="./inspection_template.xlsx",
        selected_sheets=["hardness", "dimensions"],
        prefix_filename="./reports/inspection_report",
        data=data,
    )

    print(f"Generated file: {output_file}")

Demo Data Explanation

In this example, we used a new set of data to demonstrate how the script works:

Customer Information: Contains general details like the customer’s name, inspection date, and lot number.
Measurements and Results: Dynamic data for specific cells, including numerical values, checkboxes (True or False), and placeholder text.
Checkbox Logic: Boolean values like "hardness.acc": True automatically insert a checked or unchecked checkbox image into the Excel sheet.

How to Use the Script

Prepare an Excel template with placeholders like <customer>, <inspect_date>, etc.
Define the data dictionary with keys matching the placeholders.
Call the gen_xlsx function with the template path, selected sheet names, prefix for the output file, and the data dictionary.
Run the script, and the customized Excel file will be generated.

Conclusion

This script is a versatile tool for automating Excel report generation, saving time, and reducing errors. It can be tailored for various applications, from inspection reports to financial summaries. Feel free to modify the script to suit your specific needs, and enjoy the power of Python for Excel automation! 🚀