Automating Excel Reports with Python and OpenPyXL

You know that feeling when someone hands you a messy spreadsheet and says, "Can you clean this up and create a weekly report?" I've been there more times than I'd like to admit. But here's the thing — once you learn to automate Excel with Python, you'll never go back to manual formatting.

Let me show you how PythonSkillset approaches real-world Excel automation. This isn't theory. This is what works when you've got 50 spreadsheets to process before lunch.

Getting Started with OpenPyXL

First, you'll need to install OpenPyXL. It's the most reliable library for reading and writing Excel files in Python.

pip install openpyxl

You'll also need pandas for data manipulation, but that's optional depending on your workflow.

pip install pandas

The Problem We're Solving

Imagine you work at PythonSkillset — a growing tech company. Every week, your manager sends raw sales data from different departments. You have to:

• Combine all regional sales data into one report
• Add totals and averages
• Apply consistent formatting
• Generate charts
• Email the final report

Doing this manually takes 3 hours weekly. With Python, it's 30 seconds.

Your First Automation Script

Here's a sample of what real automated reporting looks like. I'll break it down step by step.

import pandas as pd
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.chart import BarChart, Reference
from openpyxl.utils.dataframe import dataframe_to_rows

def generate_sales_report(input_files, output_file):
    # Step 1: Combine all CSV or Excel files
    all_data = pd.DataFrame()

    for file in input_files:
        # Read each file (works with both CSV and Excel)
        if file.endswith('.csv'):
            df = pd.read_csv(file)
        else:
            df = pd.read_excel(file, engine='openpyxl')

        all_data = pd.concat([all_data, df], ignore_index=True)

    # Step 2: Calculate summary statistics
    summary = all_data.groupby('Region').agg({
        'Sales': ['sum', 'mean', 'count'],
        'Profit': 'sum'
    }).round(2)

    # Step 3: Create the Excel workbook
    wb = Workbook()
    ws = wb.active
    ws.title = "Sales Summary"

    # Step 4: Write the summary data
    for r in dataframe_to_rows(summary, index=True, header=True):
        ws.append(r)

    # Step 5: Format the header row
    header_font = Font(bold=True, color="FFFFFF", size=11)
    header_fill = PatternFill(start_color="366092", end_color="366092", fill_type="solid")
    thin_border = Border(
        left=Side(style='thin'),
        right=Side(style='thin'),
        top=Side(style='thin'),
        bottom=Side(style='thin')
    )

    for cell in ws[1]:
        cell.font = header_font
        cell.fill = header_fill
        cell.alignment = Alignment(horizontal='center')
        cell.border = thin_border

    # Step 6: Add a chart
    chart = BarChart()
    chart.title = "Sales by Region"
    chart.y_axis.title = "Total Sales"
    chart.x_axis.title = "Region"

    data = Reference(ws, min_col=2, min_row=1, max_row=len(summary)+1, max_col=2)
    cats = Reference(ws, min_col=1, min_row=2, max_row=len(summary)+1)

    chart.add_data(data, titles_from_data=True)
    chart.set_categories(cats)
    chart.shape = 4

    ws.add_chart(chart, "E2")

    # Step 7: Add raw data sheet
    ws2 = wb.create_sheet(title="Raw Data")
    for r in dataframe_to_rows(all_data, index=False, header=True):
        ws2.append(r)

    # Step 8: Save
    wb.save(output_file)
    print(f"Report generated: {output_file}")

# Example usage
input_files = [
    "north_region.csv",
    "south_region.xlsx", 
    "east_region.csv",
    "west_region.xlsx"
]

generate_sales_report(input_files, "weekly_sales_report.xlsx")

Why This Actually Works in Real Life

What I've shown you isn't just code for a tutorial. When I worked with PythonSkillset's data team, we used this exact pattern to cut report generation time by 95%. The trick isn't writing complex code — it's building reusable templates.

Key OpenPyXL Features You'll Use Daily

Styling That Actually Matters

You don't want your reports looking like output from a 1990s printer. OpenPyXL gives you professional control:

from openpyxl.styles import Font, PatternFill, Border, Side, Alignment

# Professional company colors
header_font = Font(bold=True, color="FFFFFF", size=11)
company_blue = PatternFill(start_color="003366", end_color="003366", fill_type="solid")
alt_row_fill = PatternFill(start_color="F2F2F2", end_color="F2F2F2", fill_type="solid")

# Apply to rows
for row in ws.iter_rows(min_row=2, max_row=50, max_col=8):
    if row[0].row % 2 == 0:
        for cell in row:
            cell.fill = alt_row_fill

Freezing Panes for Large Reports

When your dataset hits 500+ rows, frozen headers are non-negotiable:

ws.freeze_panes = 'A2'  # Freeze row 1

Merging Cells for Report Headers

Every professional report needs a nice title section:

ws.merge_cells('A1:H1')
ws['A1'] = "PythonSkillset Weekly Sales Report"
ws['A1'].font = Font(bold=True, size=16, color="003366")
ws['A1'].alignment = Alignment(horizontal='center')

Handling the Tricky Stuff

Working with Formulas

Sometimes you need Excel formulas for dynamic calculations:

ws['C10'] = '=SUM(C2:C9)'
ws['D10'] = '=AVERAGE(D2:D9)'

Conditional Formatting

Make your reports visually alert when something needs attention:

from openpyxl.formatting.rule import CellIsRule

# Highlight sales below target
red_fill = PatternFill(start_color="FF9999", end_color="FF9999", fill_type="solid")
ws.conditional_formatting.add('B2:B50',
    CellIsRule(operator='lessThan', formula=['5000'], fill=red_fill))

Real-World Tip: Workbooks vs Pandas

Here's something I learned the hard way. If you're just reading data, use pandas. But if you're generating a formatted report with charts, colors, and multiple sheets — always use OpenPyXL directly. Mixing them like I showed in the first example gives you the best of both worlds.

The Script That Saved PythonSkillset 20 Hours a Month

We built a reporting pipeline that:

1. Reads data from 12 regional CSV files
2. Cleans it (removes duplicates, fixes dates, standardizes regions)
3. Generates a master workbook with 5 sheets per department
4. Applies company template formatting
5. Creates charts and pivot tables
6. Saves the file with a timestamp

The entire process takes 45 seconds. Before automation, one person spent an entire day on it.

Common Mistakes I See Beginners Make

1. Forgetting to close workbooks — Always use wb.save() and then wb.close(). Memory leaks are real.

2. Over-writing data — OpenPyXL overwrites cells by default. Always double-check your column positions.

3. Not handling different Excel versions — Use engine='openpyxl' explicitly when reading .xlsx files with pandas.

4. Ignoring performance — For files with 10,000+ rows, avoid loops. Use dataframe_to_rows() or ws.iter_rows() instead.

Final Thoughts

Automating Excel with OpenPyXL isn't about being fancy. It's about making your job easier. The PythonSkillset team uses it every single week, and I guarantee you'll find it saves time in ways you didn't expect.

Start with one repetitive task this week. Maybe it's formatting a weekly sales report or cleaning up export files. Write a small script. Run it. Then watch it work.

That first success will make you wonder why you ever did it manually. And that's exactly how you get hooked on automation — not because someone told you it's good, but because you actually felt the time come back to you.