Automating Excel Reports with pandas and openpyxl: Formatted Workbooks Without Manual Work

Introduction

Picture your Monday morning ritual: you pull last week's sales data from the database, paste it into Excel, manually bold the headers, apply a currency format to the revenue column, add conditional formatting to flag underperformers, and then do the same thing for five regional tabs. It takes ninety minutes every single week. By the time you finish, you've already missed the 9am standup and your coffee is cold.

This is exactly the problem that pandas combined with openpyxl was built to eliminate. You already know how to load, clean, and aggregate data in pandas — and if you've followed along with this learning path, you've built up real competence in grouping and aggregating data and reshaping it. What's been missing is the final mile: writing that processed data back to Excel as a professional, formatted workbook that looks like someone spent hours on it. By the end of this lesson, you'll be able to produce exactly that — programmatically, in seconds, with a script you run once and then forget about.

This lesson is about deep, practical mastery of the pandas + openpyxl integration, including formatting individual cells, applying conditional formatting, writing multi-sheet workbooks, embedding charts, and structuring your code so that the report generation is reusable and maintainable. We're going to cover the parts that tutorials skip: how the openpyxl object model actually works, where pandas' ExcelWriter hands off control, and how to avoid the subtle bugs that will silently corrupt your output.

What you'll learn:

• How pandas and openpyxl interact internally, and why understanding that boundary matters
• Writing multi-sheet workbooks with per-sheet formatting using ExcelWriter and openpyxl's object model
• Applying cell-level formatting: fonts, fills, borders, number formats, and alignment
• Building rule-based conditional formatting programmatically
• Embedding charts directly into Excel workbooks without leaving Python
• Structuring reusable report generation code that handles real-world data

---

Prerequisites

You should be comfortable with:

• pandas DataFrames — loading, filtering, grouping, and aggregating data (Your First pandas DataFrame is a good baseline)
• Python functions, loops, and dictionaries (Python Basics for Excel Users covers the fundamentals if you need a refresher)
• A working Python environment with pandas, openpyxl, and matplotlib installed (Setting Up Python for Data Analysis walks through environment setup)

Install dependencies if you haven't:

pip install pandas openpyxl matplotlib

---

Understanding the Architecture: How pandas and openpyxl Actually Fit Together

Before writing a single cell format, you need a mental model of how these two libraries interact. Most tutorials treat to_excel() as magic and then bolt on openpyxl as an afterthought. That leads to code that breaks in surprising ways.

When you call df.to_excel('report.xlsx'), pandas internally creates an openpyxl workbook, writes your DataFrame into it, and saves the file. That's it — pandas is done, and the file is closed. You cannot go back and format it afterward through the same write pipeline without reopening the file.

The right pattern is pandas.ExcelWriter used as a context manager:

import pandas as pd

with pd.ExcelWriter('report.xlsx', engine='openpyxl') as writer:
    df.to_excel(writer, sheet_name='Sales', index=False)
    # At this point, the workbook is still open in memory
    workbook = writer.book
    worksheet = writer.sheets['Sales']
    # Now you can use openpyxl directly to format anything

When the with block exits, ExcelWriter saves and closes the workbook. This is the critical insight: the writer.book attribute gives you direct access to the underlying openpyxl workbook object, and writer.sheets is a dictionary mapping sheet names to openpyxl worksheet objects. Once you have those, every formatting capability openpyxl provides is available to you.

Setting Up a Realistic Dataset

We'll work with a realistic sales dataset throughout this lesson. Let's build it:

import pandas as pd
import numpy as np
from datetime import date, timedelta

np.random.seed(42)

regions = ['Northeast', 'Southeast', 'Midwest', 'West', 'Southwest']
products = ['Enterprise License', 'Pro Subscription', 'Starter Pack', 'Add-on Services']
reps = {
    'Northeast': ['Alice Huang', 'Marcus Webb'],
    'Southeast': ['Dani Torres', 'James Okafor'],
    'Midwest': ['Sara Lindqvist', 'Ben Hartmann'],
    'West': ['Priya Nair', 'Cole Easton'],
    'Southwest': ['Lucia Morales', 'Ryan Park'],
}

rows = []
start_date = date(2024, 1, 1)

for _ in range(500):
    region = np.random.choice(regions)
    rep = np.random.choice(reps[region])
    product = np.random.choice(products)
    units = np.random.randint(1, 20)
    price = {'Enterprise License': 4500, 'Pro Subscription': 1200,
             'Starter Pack': 350, 'Add-on Services': 800}[product]
    revenue = units * price * np.random.uniform(0.85, 1.0)
    sale_date = start_date + timedelta(days=np.random.randint(0, 365))
    rows.append({
        'Date': sale_date,
        'Region': region,
        'Sales Rep': rep,
        'Product': product,
        'Units': units,
        'Revenue': round(revenue, 2),
    })

df = pd.DataFrame(rows).sort_values('Date').reset_index(drop=True)
print(df.shape)
print(df.head())

This gives us 500 rows of sales records across five regions, ten reps, and four products — enough variety to make aggregations meaningful.

---

Writing a Multi-Sheet Workbook

Real reports rarely live on a single tab. You typically need a summary sheet and detail sheets broken out by some dimension — region, product line, time period. Let's build that structure.

# First, build our aggregations
monthly_summary = (
    df.assign(Month=pd.to_datetime(df['Date']).dt.to_period('M').astype(str))
      .groupby('Month')
      .agg(Total_Revenue=('Revenue', 'sum'),
           Total_Units=('Units', 'sum'),
           Deals=('Revenue', 'count'))
      .reset_index()
)

regional_summary = (
    df.groupby('Region')
      .agg(Total_Revenue=('Revenue', 'sum'),
           Total_Units=('Units', 'sum'),
           Deals=('Revenue', 'count'),
           Avg_Deal_Size=('Revenue', 'mean'))
      .reset_index()
      .sort_values('Total_Revenue', ascending=False)
)

rep_summary = (
    df.groupby(['Region', 'Sales Rep'])
      .agg(Total_Revenue=('Revenue', 'sum'),
           Deals=('Revenue', 'count'),
           Avg_Deal_Size=('Revenue', 'mean'))
      .reset_index()
      .sort_values(['Region', 'Total_Revenue'], ascending=[True, False])
)

with pd.ExcelWriter('sales_report.xlsx', engine='openpyxl') as writer:
    monthly_summary.to_excel(writer, sheet_name='Monthly Summary', index=False)
    regional_summary.to_excel(writer, sheet_name='Regional Summary', index=False)
    rep_summary.to_excel(writer, sheet_name='Rep Performance', index=False)
    df.to_excel(writer, sheet_name='Raw Data', index=False)

    workbook = writer.book
    sheets = writer.sheets

At this point you have a four-tab workbook. It's functional but ugly — default Calibri 11pt, no column width adjustments, no number formatting. Let's fix all of that.

---

Cell-Level Formatting: Fonts, Fills, Borders, and Alignment

openpyxl's formatting model works by creating immutable style objects and assigning them to cells. The style classes live in openpyxl.styles. Let's import everything we'll need:

from openpyxl.styles import (
    Font, PatternFill, Border, Side, Alignment, numbers
)
from openpyxl.utils import get_column_letter

Building a Reusable Header Formatter

The single most impactful thing you can do to a report is format the headers properly. Let's write a function that applies a consistent header style to any worksheet:

def format_headers(worksheet, header_fill_hex='1F4E79', font_color_hex='FFFFFF'):
    """
    Apply professional header formatting to the first row of a worksheet.
    Assumes pandas wrote the DataFrame starting at row 1 with index=False.
    """
    header_fill = PatternFill(
        start_color=header_fill_hex,
        end_color=header_fill_hex,
        fill_type='solid'
    )
    header_font = Font(
        name='Calibri',
        bold=True,
        color=font_color_hex,
        size=11
    )
    header_alignment = Alignment(horizontal='center', vertical='center', wrap_text=False)
    
    for cell in worksheet[1]:  # Row 1 is the header row
        cell.fill = header_fill
        cell.font = header_font
        cell.alignment = header_alignment
    
    # Set the header row height
    worksheet.row_dimensions[1].height = 22

Column Width Auto-Fitting

pandas doesn't set column widths. That means every column comes out as the default 8 characters wide, which truncates most real data. Here's a robust auto-fit function:

def autofit_columns(worksheet, min_width=8, max_width=45, padding=2):
    """
    Set column widths based on the maximum content length in each column.
    Inspects both the header and the data rows.
    """
    for col_idx, column_cells in enumerate(worksheet.columns, 1):
        max_length = 0
        col_letter = get_column_letter(col_idx)
        
        for cell in column_cells:
            if cell.value is not None:
                # Formatted numbers display longer than raw floats
                try:
                    display_length = len(str(cell.value))
                except:
                    display_length = 0
                max_length = max(max_length, display_length)
        
        adjusted_width = min(max(max_length + padding, min_width), max_width)
        worksheet.column_dimensions[col_letter].width = adjusted_width

Applying Number Formats

Number formats in openpyxl use the same format string syntax as Excel. You assign them to individual cells or ranges:

def apply_number_formats(worksheet, format_map):
    """
    Apply number formats to specific columns.
    
    format_map: dict mapping column index (1-based) to format string
    Example: {3: '#,##0.00', 4: '#,##0', 5: '0.00%'}
    """
    for row in worksheet.iter_rows(min_row=2):  # Skip header
        for cell in row:
            if cell.column in format_map:
                cell.number_format = format_map[cell.column]

Now let's apply all of this to our regional summary sheet:

with pd.ExcelWriter('sales_report.xlsx', engine='openpyxl') as writer:
    monthly_summary.to_excel(writer, sheet_name='Monthly Summary', index=False)
    regional_summary.to_excel(writer, sheet_name='Regional Summary', index=False)
    rep_summary.to_excel(writer, sheet_name='Rep Performance', index=False)
    df.to_excel(writer, sheet_name='Raw Data', index=False)

    workbook = writer.book

    # Format Regional Summary
    ws_regional = writer.sheets['Regional Summary']
    format_headers(ws_regional)
    autofit_columns(ws_regional)
    
    # Column indices: 1=Region, 2=Total_Revenue, 3=Total_Units, 4=Deals, 5=Avg_Deal_Size
    apply_number_formats(ws_regional, {
        2: '"$"#,##0.00',
        4: '#,##0',
        5: '"$"#,##0.00'
    })

    # Format Rep Performance
    ws_rep = writer.sheets['Rep Performance']
    format_headers(ws_rep)
    autofit_columns(ws_rep)
    apply_number_formats(ws_rep, {
        3: '"$"#,##0.00',
        4: '#,##0',
        5: '"$"#,##0.00'
    })
    
    # Format Monthly Summary
    ws_monthly = writer.sheets['Monthly Summary']
    format_headers(ws_monthly)
    autofit_columns(ws_monthly)
    apply_number_formats(ws_monthly, {
        2: '"$"#,##0.00',
        3: '#,##0',
        4: '#,##0'
    })

Adding Alternating Row Colors (Zebra Stripes)

This is one of those things that makes a spreadsheet instantly more readable. openpyxl doesn't have a built-in table style applier that works well programmatically, so we do it cell by cell:

def apply_zebra_stripes(worksheet, even_color='DCE6F1', odd_color='FFFFFF'):
    """
    Apply alternating row fills starting from row 2 (data rows).
    """
    even_fill = PatternFill(start_color=even_color, end_color=even_color, fill_type='solid')
    odd_fill = PatternFill(start_color=odd_color, end_color=odd_color, fill_type='solid')
    
    for row_idx, row in enumerate(worksheet.iter_rows(min_row=2), start=2):
        fill = even_fill if row_idx % 2 == 0 else odd_fill
        for cell in row:
            cell.fill = fill

---

Conditional Formatting: Flagging What Matters

Conditional formatting is where your reports go from "nice" to "actually useful for decision-making." openpyxl implements conditional formatting through rule objects, not through cell fills — which is the right way to do it, because Excel evaluates these rules dynamically when the file is opened.

from openpyxl.formatting.rule import (
    ColorScaleRule, DataBarRule, IconSetRule, CellIsRule, FormulaRule
)
from openpyxl.styles import PatternFill

Color Scale on Revenue

Color scales are the most visually intuitive way to show distribution across a numeric column:

def apply_color_scale(worksheet, col_letter, min_row, max_row):
    """
    Apply a green-yellow-red color scale to a numeric column range.
    """
    cell_range = f'{col_letter}{min_row}:{col_letter}{max_row}'
    
    color_scale_rule = ColorScaleRule(
        start_type='min',
        start_color='F8696B',   # Red for low values
        mid_type='percentile',
        mid_value=50,
        mid_color='FFEB84',     # Yellow for middle
        end_type='max',
        end_color='63BE7B'      # Green for high values
    )
    
    worksheet.conditional_formatting.add(cell_range, color_scale_rule)

Highlighting Below-Quota Performers

For the rep performance sheet, let's flag anyone whose revenue is below a threshold:

def highlight_below_threshold(worksheet, col_letter, threshold, min_row, max_row):
    """
    Apply a red fill to cells in a column where the value is below the threshold.
    """
    red_fill = PatternFill(start_color='FFC7CE', end_color='FFC7CE', fill_type='solid')
    red_font = Font(color='9C0006', bold=True)
    
    cell_range = f'{col_letter}{min_row}:{col_letter}{max_row}'
    
    rule = CellIsRule(
        operator='lessThan',
        formula=[str(threshold)],
        fill=red_fill,
        font=red_font
    )
    
    worksheet.conditional_formatting.add(cell_range, rule)

Let's also add a top-performer highlight using a formula-based rule, which is more powerful because it can reference other cells:

def highlight_top_n_percent(worksheet, col_letter, data_col_letter, 
                             min_row, max_row, pct=10):
    """
    Highlight the top N% of rows based on values in data_col_letter.
    Uses a PERCENTILE formula rule to identify top performers.
    
    highlight_col_letter: column whose entire row gets highlighted
    data_col_letter: column containing the numeric values to rank
    """
    green_fill = PatternFill(start_color='C6EFCE', end_color='C6EFCE', fill_type='solid')
    green_font = Font(color='276221', bold=True)
    
    data_range = f'{data_col_letter}{min_row}:{data_col_letter}{max_row}'
    highlight_range = f'{col_letter}{min_row}:{col_letter}{max_row}'
    
    # Formula must be written as if evaluating from the first cell in the range
    formula = f'${data_col_letter}{min_row}>=PERCENTILE(${data_range},{(100-pct)/100})'
    
    rule = FormulaRule(formula=[formula], fill=green_fill, font=green_font)
    worksheet.conditional_formatting.add(highlight_range, rule)

Now let's apply everything to the Rep Performance sheet:

# Inside the ExcelWriter context, after writing sheets...
ws_rep = writer.sheets['Rep Performance']
data_row_count = len(rep_summary)
last_data_row = data_row_count + 1  # +1 for header

# Color scale on revenue column (col 3 = Total_Revenue)
apply_color_scale(ws_rep, 'C', 2, last_data_row)

# Flag anyone below $50,000 in total revenue
highlight_below_threshold(ws_rep, 'C', 50000, 2, last_data_row)

# Highlight top 10% earners across the whole row
# We'll apply to the rep name column so the entire row stands out
highlight_top_n_percent(ws_rep, 'B', 'C', 2, last_data_row, pct=10)

---

Freezing Panes and Setting Sheet Views

Frozen panes are one of those quality-of-life features that separates a report someone will use from one they'll close immediately. When you have 500 rows, you need the header locked:

def freeze_header_row(worksheet):
    """Freeze the top row so headers remain visible while scrolling."""
    worksheet.freeze_panes = 'A2'

def set_print_settings(worksheet, orientation='landscape', fit_to_page=True):
    """Configure print settings for professional output."""
    from openpyxl.worksheet.page import PageMargins
    
    worksheet.page_setup.orientation = orientation
    worksheet.page_setup.fitToPage = fit_to_page
    worksheet.page_setup.fitToWidth = 1
    worksheet.page_setup.fitToHeight = 0  # Allow multiple pages vertically
    
    worksheet.page_margins = PageMargins(
        left=0.5, right=0.5, top=0.75, bottom=0.75
    )
    
    # Repeat header row when printing
    worksheet.print_title_rows = '1:1'

The freeze_panes attribute takes the cell address of the first unfrozen cell. 'A2' means row 1 is frozen. 'B2' would freeze both the first row and the first column.

---

Embedding Charts in the Workbook

This is where things get powerful. You can build a chart using matplotlib or openpyxl's native charting API and embed it directly in the workbook. Both approaches have trade-offs worth understanding.

Approach 1: openpyxl Native Charts

openpyxl can generate charts that use the workbook's own data as their source. These charts are live — if someone updates the data in Excel, the chart updates. The downside is that openpyxl's charting API is less flexible than matplotlib's.

from openpyxl.chart import BarChart, Reference

def add_revenue_bar_chart(workbook, data_worksheet, chart_worksheet_name,
                           data_start_row, data_end_row,
                           label_col, value_col, title='Revenue by Region'):
    """
    Add a bar chart to a dedicated chart sheet using data from a data sheet.
    
    label_col, value_col: 1-based column indices
    """
    chart = BarChart()
    chart.type = 'col'          # Vertical bars
    chart.grouping = 'clustered'
    chart.title = title
    chart.style = 10            # Built-in Excel style
    chart.y_axis.title = 'Revenue ($)'
    chart.x_axis.title = 'Region'
    chart.y_axis.numFmt = '"$"#,##0'
    chart.shape = 4
    
    # Define the data reference (values)
    data_ref = Reference(
        data_worksheet,
        min_col=value_col,
        min_row=data_start_row,
        max_row=data_end_row
    )
    
    # Define category labels
    cats = Reference(
        data_worksheet,
        min_col=label_col,
        min_row=data_start_row + 1,  # Skip header
        max_row=data_end_row
    )
    
    chart.add_data(data_ref, titles_from_data=True)
    chart.set_categories(cats)
    chart.series[0].graphicalProperties.solidFill = '1F4E79'
    
    # Size the chart
    chart.width = 20   # cm
    chart.height = 12  # cm
    
    # Add to a dedicated chart sheet or existing sheet
    if chart_worksheet_name not in workbook.sheetnames:
        chart_sheet = workbook.create_sheet(chart_worksheet_name)
    else:
        chart_sheet = workbook[chart_worksheet_name]
    
    chart_sheet.add_chart(chart, 'B2')
    return chart_sheet

Approach 2: Embedding a matplotlib Figure as an Image

For complex, highly customized charts, create them in matplotlib and insert them as images:

import matplotlib.pyplot as plt
import matplotlib.ticker as mticker
from openpyxl.drawing.image import Image as XLImage
import io

def embed_matplotlib_chart(workbook, sheet_name, dataframe,
                            x_col, y_col, title, cell_position='B2'):
    """
    Create a matplotlib bar chart and embed it as an image in a worksheet.
    Uses an in-memory buffer — no temporary files needed.
    """
    fig, ax = plt.subplots(figsize=(10, 5))
    
    colors = ['#1F4E79' if v >= dataframe[y_col].median() else '#F4B942'
              for v in dataframe[y_col]]
    
    bars = ax.bar(dataframe[x_col], dataframe[y_col], color=colors, edgecolor='white')
    
    ax.set_title(title, fontsize=14, fontweight='bold', pad=15)
    ax.set_xlabel(x_col, fontsize=11)
    ax.set_ylabel('Revenue', fontsize=11)
    ax.yaxis.set_major_formatter(mticker.FuncFormatter(
        lambda x, _: f'${x:,.0f}'
    ))
    ax.spines['top'].set_visible(False)
    ax.spines['right'].set_visible(False)
    plt.xticks(rotation=30, ha='right')
    plt.tight_layout()
    
    # Write to an in-memory buffer
    buf = io.BytesIO()
    fig.savefig(buf, format='png', dpi=150, bbox_inches='tight')
    buf.seek(0)
    plt.close(fig)
    
    # Insert into the workbook
    if sheet_name not in workbook.sheetnames:
        ws = workbook.create_sheet(sheet_name)
    else:
        ws = workbook[sheet_name]
    
    img = XLImage(buf)
    img.anchor = cell_position
    ws.add_image(img)
    return ws

---

Building the Complete Report Pipeline

Now let's pull everything together into a single, well-structured function that produces the complete report:

def generate_sales_report(df, output_path='sales_report.xlsx'):
    """
    Generate a fully formatted multi-sheet Excel sales report.
    
    Parameters
    ----------
    df : pd.DataFrame
        Raw sales data with columns: Date, Region, Sales Rep, Product, Units, Revenue
    output_path : str
        File path for the output workbook
    """
    # ── 1. Compute aggregations ──────────────────────────────────────────────
    monthly = (
        df.assign(Month=pd.to_datetime(df['Date']).dt.to_period('M').astype(str))
          .groupby('Month')
          .agg(Total_Revenue=('Revenue', 'sum'),
               Total_Units=('Units', 'sum'),
               Deals=('Revenue', 'count'))
          .reset_index()
    )
    
    regional = (
        df.groupby('Region')
          .agg(Total_Revenue=('Revenue', 'sum'),
               Total_Units=('Units', 'sum'),
               Deals=('Revenue', 'count'),
               Avg_Deal_Size=('Revenue', 'mean'))
          .reset_index()
          .sort_values('Total_Revenue', ascending=False)
    )
    
    rep_perf = (
        df.groupby(['Region', 'Sales Rep'])
          .agg(Total_Revenue=('Revenue', 'sum'),
               Deals=('Revenue', 'count'),
               Avg_Deal_Size=('Revenue', 'mean'))
          .reset_index()
          .sort_values(['Region', 'Total_Revenue'], ascending=[True, False])
    )
    
    product_mix = (
        df.groupby('Product')
          .agg(Total_Revenue=('Revenue', 'sum'),
               Total_Units=('Units', 'sum'))
          .reset_index()
          .sort_values('Total_Revenue', ascending=False)
    )
    
    # ── 2. Write sheets ──────────────────────────────────────────────────────
    with pd.ExcelWriter(output_path, engine='openpyxl') as writer:
        monthly.to_excel(writer, sheet_name='Monthly Summary', index=False)
        regional.to_excel(writer, sheet_name='Regional Summary', index=False)
        rep_perf.to_excel(writer, sheet_name='Rep Performance', index=False)
        product_mix.to_excel(writer, sheet_name='Product Mix', index=False)
        df.to_excel(writer, sheet_name='Raw Data', index=False)
        
        wb = writer.book
        
        # ── 3. Format each sheet ─────────────────────────────────────────────
        sheet_configs = {
            'Monthly Summary': {
                'number_formats': {2: '"$"#,##0.00', 3: '#,##0', 4: '#,##0'},
                'color_scale_cols': ['B'],
            },
            'Regional Summary': {
                'number_formats': {2: '"$"#,##0.00', 3: '#,##0',
                                   4: '#,##0', 5: '"$"#,##0.00'},
                'color_scale_cols': ['B'],
                'threshold_col': 'B',
                'threshold': 100000,
            },
            'Rep Performance': {
                'number_formats': {3: '"$"#,##0.00', 4: '#,##0',
                                   5: '"$"#,##0.00'},
                'color_scale_cols': ['C'],
                'threshold_col': 'C',
                'threshold': 50000,
            },
            'Product Mix': {
                'number_formats': {2: '"$"#,##0.00', 3: '#,##0'},
                'color_scale_cols': ['B'],
            },
        }
        
        for sheet_name, config in sheet_configs.items():
            ws = writer.sheets[sheet_name]
            
            # Get actual data row count from the written sheet
            last_row = ws.max_row
            
            format_headers(ws)
            apply_zebra_stripes(ws)
            autofit_columns(ws)
            freeze_header_row(ws)
            set_print_settings(ws)
            
            if 'number_formats' in config:
                apply_number_formats(ws, config['number_formats'])
            
            for col_letter in config.get('color_scale_cols', []):
                apply_color_scale(ws, col_letter, 2, last_row)
            
            if 'threshold_col' in config:
                highlight_below_threshold(
                    ws, config['threshold_col'],
                    config['threshold'], 2, last_row
                )
        
        # Raw Data sheet — minimal formatting, just headers and freeze
        ws_raw = writer.sheets['Raw Data']
        format_headers(ws_raw)
        freeze_header_row(ws_raw)
        autofit_columns(ws_raw, max_width=30)
        apply_number_formats(ws_raw, {6: '"$"#,##0.00'})
        
        # ── 4. Embed charts ──────────────────────────────────────────────────
        embed_matplotlib_chart(
            wb,
            sheet_name='Charts',
            dataframe=regional,
            x_col='Region',
            y_col='Total_Revenue',
            title='Revenue by Region — Full Year 2024',
            cell_position='B2'
        )
        
        add_revenue_bar_chart(
            workbook=wb,
            data_worksheet=writer.sheets['Product Mix'],
            chart_worksheet_name='Charts',
            data_start_row=1,
            data_end_row=len(product_mix) + 1,
            label_col=1,
            value_col=2,
            title='Revenue by Product'
        )
        
        # ── 5. Reorder sheets ────────────────────────────────────────────────
        desired_order = [
            'Monthly Summary', 'Regional Summary', 'Rep Performance',
            'Product Mix', 'Charts', 'Raw Data'
        ]
        for i, name in enumerate(desired_order):
            if name in wb.sheetnames:
                wb.move_sheet(name, offset=wb.sheetnames.index(name) - i)
    
    print(f"Report saved to {output_path}")

# Run it
generate_sales_report(df, 'q4_sales_report.xlsx')

This function is genuinely production-ready. It's parameterized, handles variable data sizes via ws.max_row, applies a consistent visual language across all sheets, and reorders the tabs to match the logical flow of the report.

---

Advanced Patterns and Performance Considerations

Styling Performance: Avoid Per-Cell Style Object Creation

One of the most common performance problems in openpyxl code is creating new style objects inside inner loops. Every time you write Font(bold=True) inside a loop over 10,000 cells, Python allocates a new object. With large datasets, this is measurably slow.

The fix is to create style objects once and reuse them:

# SLOW — creates new objects on every iteration
for row in worksheet.iter_rows(min_row=2):
    for cell in row:
        cell.font = Font(bold=True, color='FF0000')  # ← New object every time!

# FAST — create once, reuse everywhere
error_font = Font(bold=True, color='FF0000')
for row in worksheet.iter_rows(min_row=2):
    for cell in row:
        cell.font = error_font  # ← Same object, no allocation overhead

For a sheet with 500 rows and 6 columns, this is a 3000x difference in object creation. At 50,000 rows, it's the difference between a five-second script and a two-minute one.

Handling the `datetime` vs. `date` Serialization Issue

pandas will write datetime64 columns to Excel correctly, but Python's native date objects (from the datetime.date class) can cause problems:

# If your DataFrame has Python date objects instead of pandas Timestamps,
# explicitly convert before writing
df['Date'] = pd.to_datetime(df['Date'])

If you skip this and write native date objects, they'll appear as numbers in Excel (specifically, they'll render as the integer number of days since 1900-01-01, which is Excel's internal date representation). The cells won't have a date number format applied, so they'll just look like large integers to the end user.

Writing Large DataFrames Efficiently

For very large datasets (100,000+ rows), consider whether you actually need to write everything to Excel. Excel's practical limit is 1,048,576 rows, but performance degrades significantly above 100,000 rows. For large raw data, consider:

1. Writing a filtered or sampled subset to the "Raw Data" sheet
2. Using write_only mode in openpyxl for the raw data sheet while using normal mode for summary sheets — though note that write_only worksheets don't support formatting after writing

# For genuinely large datasets, filter before writing
top_deals = df.nlargest(1000, 'Revenue')
top_deals.to_excel(writer, sheet_name='Top 1000 Deals', index=False)

Making the Report Rerunnable: File Locking Gotchas

If the output file is already open in Excel when your script tries to write it, you'll get a PermissionError. This is a workflow hazard in environments where the report is open on someone's screen while the scheduled task tries to regenerate it. Handle it gracefully:

import os
from datetime import datetime

def safe_output_path(base_path):
    """
    If the target file is locked (open in Excel), write to a timestamped version.
    """
    try:
        # Test write access
        with open(base_path, 'a'):
            pass
        return base_path
    except PermissionError:
        stem, ext = os.path.splitext(base_path)
        timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
        return f'{stem}_{timestamp}{ext}'

---

Hands-On Exercise

Build a self-contained report generator for a new scenario. Here are your requirements:

Scenario: You manage inventory for a retail chain with five store locations. You have a DataFrame with columns: Date, Store, Category, SKU, Units_Sold, Units_Remaining, Unit_Cost, Unit_Price.

Your task:

1. Generate synthetic data for 12 months, 5 stores, 4 product categories, and 20 SKUs. Include at least 2,000 rows.

2. Build three aggregation tables:

   • Inventory Status: per-SKU summary showing Units_Remaining, Units_Sold, Gross_Margin (calculated as (Unit_Price - Unit_Cost) / Unit_Price), and a Days_of_Supply estimate (assume 30 days and use Units_Remaining / (Units_Sold / 30))
   • Store Performance: total revenue and margin by store
   • Category Trends: monthly revenue by category

3. Apply the following formatting:

   • Professional dark header row with white text
   • Currency format on all revenue and cost columns
   • Percentage format on margin columns ('0.0%')
   • Red conditional formatting on any SKU with fewer than 10 Units_Remaining
   • Green highlighting on SKUs with Gross_Margin > 0.35
   • Frozen headers on all sheets

4. Embed a matplotlib bar chart showing gross margin by category

5. Structure your code as a function generate_inventory_report(df, output_path) that can be called from a scheduled job

Stretch goal: Add a --date-range argument using Python's argparse module so the report can be run for any calendar month from the command line.

---

Common Mistakes & Troubleshooting

"My conditional formatting isn't appearing"

The most common cause is writing the conditional formatting rule to the wrong cell range. Double-check that your range string uses the correct column letter and that min_row=2 skips the header. A related issue: if you applied a cell-level fill after the conditional formatting, the fill will visually override the conditional format in Excel (because Excel applies conditional formats on top of base cell formats — but openpyxl writes them as separate XML elements, and Excel displays whichever was set last via openpyxl as the base, with the conditional format on top). Apply fills first, then conditional formatting rules.

"The file opens but the formatting is all wrong"

This almost always means you called to_excel() inside the context manager, but then applied formatting before pandas wrote the data, or you applied it to a different worksheet object than the one pandas used. Always call to_excel() first, then get the worksheet via writer.sheets['Sheet Name'] — not by creating a new one.

"Numbers are showing as text"

If a numeric column is stored as Python str objects in your DataFrame (which happens more often than you'd think — see Cleaning Messy Data with pandas for how to catch this), pandas will write them as text cells and no number format will apply. Always verify dtypes with df.dtypes before writing.

"Column widths aren't updating"

autofit_columns iterates over worksheet.columns, which only exists for normal mode worksheets. If you somehow ended up with a write-only worksheet, this will fail silently or raise an error. Also: the get_column_letter import from openpyxl.utils is required — if you forget it, you'll get a NameError that's easy to overlook.

"The chart position is wrong"

openpyxl chart anchor positions are specified as Excel cell addresses (e.g., 'B2'). The chart's top-left corner is anchored at that cell. If two charts overlap, the second one will obscure the first. Use a large enough cell offset — for a chart that's 20cm wide, anchor the second chart at something like 'B22' or 'N2'.

"My script is slow on large datasets"

Profile with cProfile to find the bottleneck. Usually it's either style object creation inside loops (fix: create objects once, reuse them), or calling worksheet.iter_rows() multiple times for different operations (fix: combine operations into a single pass). For truly large workbooks, consider writing raw data to a separate file and keeping the formatted report to summary tables only.

---

Summary & Next Steps

You now have a complete, production-grade workflow for generating formatted Excel reports from pandas DataFrames. Let's recap the architecture:

1. pd.ExcelWriter is the bridge. Use it as a context manager. Write all DataFrames first with to_excel(), then access writer.book and writer.sheets to apply openpyxl formatting.

2. Build reusable style functions. format_headers(), autofit_columns(), apply_number_formats(), and apply_zebra_stripes() are utilities you'll use on every report. Put them in a module and import them.

3. Conditional formatting uses rule objects, not cell fills. Excel evaluates rules at display time. The API is worksheet.conditional_formatting.add(range_string, rule_object).

4. Charts can be native openpyxl (live, updatable) or embedded matplotlib images (more control, static). For dashboards, use native charts. For heavily customized visuals, use matplotlib with io.BytesIO.

5. Performance matters at scale. Create style objects once, reuse them everywhere. Apply conditional formatting after cell fills.

The natural next step from here is scheduling this script. On Linux/macOS, add it to cron. On Windows, use Task Scheduler. In cloud environments, trigger it from a Lambda function, a GitHub Action, or a Cloud Run job. The script already handles all the formatting logic — the infrastructure wrapper is straightforward.

You might also explore combining this with the data pipeline skills from Selecting and Filtering Data in pandas and Working with Dates and Time Series in pandas to build reports that automatically filter to the most recent time period, compare period-over-period, and flag statistical anomalies — all without a human touching a keyboard.

The Monday morning ritual dies here.