Automating Excel Reports with Python and openpyxl: A Practical Guide

Python's openpyxl library provides robust capabilities for programmatically creating, reading, and modifying Excel .xlsx files. This walkthrough demonstrates how to generate a basic report from structured data using openpyxl, focusing on data writing and fundamental formatting.

Prerequisites and Installation

Ensure Python 3 is installed on your system. The primary library required is openpyxl. Install it using pip:

pip install openpyxl

While not strictly required for this basic example, pandas is frequently used for more complex data manipulation prior to Excel export. If needed, install it:

pip install pandas

Core Report Generation Workflow

The process of automating an Excel report typically follows these steps:

• Workbook Management: Create a new workbook or load an existing one.
• Worksheet Selection: Access the active worksheet or create new ones.
• Data Population: Iterate through your data source and write values to specific cells.
• Styling and Formatting: Apply fonts, colors, borders, and adjust cell/column dimensions for readability.
• Saving: Persist the changes to a new or existing .xlsx file.

Practical Example: Generating a Sales Report

This example demonstrates creating a simple sales report from a list of dictionaries, including headers, data rows, and basic column width adjustment.

1. Import Libraries and Prepare Data

Start by importing necessary openpyxl components and defining your report data. Here, a list of dictionaries serves as our data source.

import openpyxl
from openpyxl.styles import Font, PatternFill
from openpyxl.utils import get_column_letter

# Sample data for the report
data = [
    {"Product": "Laptop", "Region": "North", "Sales": 12000, "Date": "2023-01-15"},
    {"Product": "Mouse", "Region": "South", "Sales": 250, "Date": "2023-01-16"},
    {"Product": "Keyboard", "Region": "East", "Sales": 750, "Date": "2023-01-17"},
    {"Product": "Monitor", "Region": "West", "Sales": 3000, "Date": "2023-01-18"},
]
headers = list(data[0].keys()) # Extract headers from the first dictionary

2. Create Workbook and Worksheet

Need this done fast? order an automation script on Kwork.