Automate Excel Reports with Python and openpyxl

Every week, you compile data from multiple sources, paste it into Excel, apply formatting, create charts, and email the report. It takes hours. And you do it again the next week.

What if Python could do this while you grab coffee?

Today we're building an Excel report generator that creates professional, formatted spreadsheets from raw data—automatically.

What You'll Learn

Reading and writing Excel files with Python
Applying formatting (colors, fonts, borders)
Creating charts programmatically
Building reusable report templates

Prerequisites

Python 3.8 or higher
openpyxl library (pip install openpyxl)
Basic understanding of Excel concepts

The Problem

Manual Excel reporting is painful because:

It's repetitive (same steps every time)
It's error-prone (copy-paste mistakes)
It's time-consuming (hours that could be spent elsewhere)
Formatting is tedious (applying styles to hundreds of cells)

The Solution

We'll create a Python script that:

Reads data from various sources
Creates a professionally formatted Excel workbook
Adds summary calculations and charts
Saves a ready-to-share report

Step 1: Installing openpyxl

First, install the openpyxl library:

bash

1pip install openpyxl

openpyxl is the go-to library for Excel automation in Python. It can read, write, and modify Excel files without needing Excel installed.

Step 2: Creating a Basic Workbook

Let's start with the fundamentals:

python

1from openpyxl import Workbook
2
3# Create a new workbook
4wb = Workbook()
5
6# Get the active worksheet
7ws = wb.active
8
9# Give it a name
10ws.title = "Sales Report"
11
12# Write data to cells
13ws['A1'] = "Product"
14ws['B1'] = "Sales"
15ws['C1'] = "Revenue"
16
17# Write a row of data
18ws.append(["Widget A", 150, 4500])
19ws.append(["Widget B", 200, 8000])
20ws.append(["Widget C", 75, 2250])
21
22# Save the workbook
23wb.save("sales_report.xlsx")
24print("Report created!")

Step 3: Reading Data from Excel

Often you need to read existing data:

python

1from openpyxl import load_workbook
2
3def read_excel_data(filepath, sheet_name=None):
4    """
5    Read data from an Excel file into a list of dictionaries.
6    
7    Args:
8        filepath: Path to the Excel file
9        sheet_name: Name of sheet to read (default: active sheet)
10    
11    Returns:
12        List of dictionaries with column headers as keys
13    """
14    wb = load_workbook(filepath)
15    
16    # Get the specified sheet or active sheet
17    ws = wb[sheet_name] if sheet_name else wb.active
18    
19    # Get headers from first row
20    headers = [cell.value for cell in ws[1]]
21    
22    # Read data rows
23    data = []
24    for row in ws.iter_rows(min_row=2, values_only=True):
25        # Skip empty rows
26        if any(row):
27            row_dict = dict(zip(headers, row))
28            data.append(row_dict)
29    
30    return data
31
32# Usage
33sales_data = read_excel_data("raw_data.xlsx")
34for record in sales_data:
35    print(record)

Step 4: Applying Professional Formatting

This is where Python really shines—consistent formatting across hundreds of cells:

python

1from openpyxl.styles import Font, Fill, PatternFill, Border, Side, Alignment
2
3def apply_header_style(ws, row=1):
4    """Apply professional styling to header row."""
5    
6    # Define styles
7    header_font = Font(
8        name='Calibri',
9        size=12,
10        bold=True,
11        color='FFFFFF'  # White text
12    )
13    
14    header_fill = PatternFill(
15        start_color='4472C4',  # Blue background
16        end_color='4472C4',
17        fill_type='solid'
18    )
19    
20    thin_border = Border(
21        left=Side(style='thin'),
22        right=Side(style='thin'),
23        top=Side(style='thin'),
24        bottom=Side(style='thin')
25    )
26    
27    center_alignment = Alignment(
28        horizontal='center',
29        vertical='center'
30    )
31    
32    # Apply to header row
33    for cell in ws[row]:
34        cell.font = header_font
35        cell.fill = header_fill
36        cell.border = thin_border
37        cell.alignment = center_alignment
38
39
40def apply_data_style(ws, start_row=2):
41    """Apply styling to data rows."""
42    
43    thin_border = Border(
44        left=Side(style='thin'),
45        right=Side(style='thin'),
46        top=Side(style='thin'),
47        bottom=Side(style='thin')
48    )
49    
50    # Alternate row colors
51    light_fill = PatternFill(
52        start_color='D9E2F3',
53        end_color='D9E2F3',
54        fill_type='solid'
55    )
56    
57    for row_num, row in enumerate(ws.iter_rows(min_row=start_row), start=start_row):
58        for cell in row:
59            cell.border = thin_border
60            
61            # Apply alternating colors
62            if row_num % 2 == 0:
63                cell.fill = light_fill

Step 5: Adding Formulas and Calculations

Python can insert Excel formulas that calculate when the file is opened:

python

1def add_summary_section(ws, data_end_row):
2    """Add summary calculations below the data."""
3    
4    summary_row = data_end_row + 2
5    
6    # Labels
7    ws.cell(row=summary_row, column=1, value="Summary")
8    ws.cell(row=summary_row + 1, column=1, value="Total Sales:")
9    ws.cell(row=summary_row + 2, column=1, value="Total Revenue:")
10    ws.cell(row=summary_row + 3, column=1, value="Average Sale:")
11    
12    # Formulas (assuming column B is sales, column C is revenue)
13    ws.cell(row=summary_row + 1, column=2, value=f"=SUM(B2:B{data_end_row})")
14    ws.cell(row=summary_row + 2, column=2, value=f"=SUM(C2:C{data_end_row})")
15    ws.cell(row=summary_row + 3, column=2, value=f"=AVERAGE(B2:B{data_end_row})")
16    
17    # Format summary section
18    bold_font = Font(bold=True)
19    for row in range(summary_row, summary_row + 4):
20        ws.cell(row=row, column=1).font = bold_font
21    
22    return summary_row

Step 6: Creating Charts

Add visual charts to your reports:

python

1from openpyxl.chart import BarChart, Reference, PieChart
2
3def add_bar_chart(ws, data_range, title="Sales by Product"):
4    """Add a bar chart to the worksheet."""
5    
6    chart = BarChart()
7    chart.type = "col"  # Column chart (vertical bars)
8    chart.title = title
9    chart.y_axis.title = "Sales"
10    chart.x_axis.title = "Product"
11    
12    # Data reference (values)
13    data = Reference(ws, min_col=2, min_row=1, max_row=data_range, max_col=2)
14    
15    # Category reference (labels)
16    categories = Reference(ws, min_col=1, min_row=2, max_row=data_range)
17    
18    chart.add_data(data, titles_from_data=True)
19    chart.set_categories(categories)
20    
21    # Size and position
22    chart.width = 15
23    chart.height = 10
24    
25    # Add chart to worksheet
26    ws.add_chart(chart, "E2")
27    
28    return chart
29
30
31def add_pie_chart(ws, data_range, title="Revenue Distribution"):
32    """Add a pie chart to the worksheet."""
33    
34    chart = PieChart()
35    chart.title = title
36    
37    # Data reference
38    data = Reference(ws, min_col=3, min_row=1, max_row=data_range, max_col=3)
39    categories = Reference(ws, min_col=1, min_row=2, max_row=data_range)
40    
41    chart.add_data(data, titles_from_data=True)
42    chart.set_categories(categories)
43    
44    # Size and position
45    chart.width = 12
46    chart.height = 10
47    
48    # Add chart to worksheet
49    ws.add_chart(chart, "E15")
50    
51    return chart

The Complete Script

python

1#!/usr/bin/env python3
2"""
3Excel Report Generator - Create professional Excel reports automatically.
4Author: Alex Rodriguez
5
6This script generates formatted Excel reports with data, styling, and charts.
7"""
8
9from datetime import datetime
10from openpyxl import Workbook
11from openpyxl.styles import Font, PatternFill, Border, Side, Alignment, NamedStyle
12from openpyxl.chart import BarChart, PieChart, Reference
13from openpyxl.utils import get_column_letter
14
15
16def create_styles():
17    """Create reusable styles for the workbook."""
18    
19    styles = {
20        'header_font': Font(name='Calibri', size=12, bold=True, color='FFFFFF'),
21        'header_fill': PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid'),
22        'alt_row_fill': PatternFill(start_color='D9E2F3', end_color='D9E2F3', fill_type='solid'),
23        'border': Border(
24            left=Side(style='thin'),
25            right=Side(style='thin'),
26            top=Side(style='thin'),
27            bottom=Side(style='thin')
28        ),
29        'center': Alignment(horizontal='center', vertical='center'),
30        'currency': '#,##0.00',
31        'number': '#,##0',
32    }
33    
34    return styles
35
36
37def apply_formatting(ws, styles, data_end_row):
38    """Apply formatting to the worksheet."""
39    
40    # Format header row
41    for cell in ws[1]:
42        cell.font = styles['header_font']
43        cell.fill = styles['header_fill']
44        cell.border = styles['border']
45        cell.alignment = styles['center']
46    
47    # Format data rows
48    for row_num in range(2, data_end_row + 1):
49        for col_num, cell in enumerate(ws[row_num], start=1):
50            cell.border = styles['border']
51            
52            # Alternate row colors
53            if row_num % 2 == 0:
54                cell.fill = styles['alt_row_fill']
55            
56            # Format numbers in specific columns
57            if col_num == 2:  # Sales column
58                cell.number_format = styles['number']
59            elif col_num == 3:  # Revenue column
60                cell.number_format = styles['currency']
61    
62    # Auto-adjust column widths
63    for column in ws.columns:
64        max_length = 0
65        column_letter = get_column_letter(column[0].column)
66        
67        for cell in column:
68            try:
69                if len(str(cell.value)) > max_length:
70                    max_length = len(str(cell.value))
71            except:
72                pass
73        
74        adjusted_width = min(max_length + 2, 50)
75        ws.column_dimensions[column_letter].width = adjusted_width
76
77
78def add_charts(ws, data_end_row):
79    """Add charts to the worksheet."""
80    
81    # Bar Chart for Sales
82    bar_chart = BarChart()
83    bar_chart.type = "col"
84    bar_chart.title = "Sales by Product"
85    bar_chart.y_axis.title = "Units Sold"
86    bar_chart.x_axis.title = "Product"
87    bar_chart.style = 10
88    
89    data = Reference(ws, min_col=2, min_row=1, max_row=data_end_row, max_col=2)
90    categories = Reference(ws, min_col=1, min_row=2, max_row=data_end_row)
91    
92    bar_chart.add_data(data, titles_from_data=True)
93    bar_chart.set_categories(categories)
94    bar_chart.width = 15
95    bar_chart.height = 10
96    
97    ws.add_chart(bar_chart, "E2")
98    
99    # Pie Chart for Revenue
100    pie_chart = PieChart()
101    pie_chart.title = "Revenue Distribution"
102    pie_chart.style = 10
103    
104    data = Reference(ws, min_col=3, min_row=1, max_row=data_end_row, max_col=3)
105    categories = Reference(ws, min_col=1, min_row=2, max_row=data_end_row)
106    
107    pie_chart.add_data(data, titles_from_data=True)
108    pie_chart.set_categories(categories)
109    pie_chart.width = 12
110    pie_chart.height = 10
111    
112    ws.add_chart(pie_chart, "E14")
113
114
115def add_summary(ws, styles, data_end_row):
116    """Add summary section below data."""
117    
118    summary_start = data_end_row + 3
119    
120    # Summary header
121    ws.cell(row=summary_start, column=1, value="SUMMARY")
122    ws.cell(row=summary_start, column=1).font = Font(bold=True, size=14)
123    
124    # Summary rows
125    summary_items = [
126        ("Total Units Sold:", f"=SUM(B2:B{data_end_row})"),
127        ("Total Revenue:", f"=SUM(C2:C{data_end_row})"),
128        ("Average Units per Product:", f"=AVERAGE(B2:B{data_end_row})"),
129        ("Average Revenue per Product:", f"=AVERAGE(C2:C{data_end_row})"),
130        ("Best Selling Product:", f"=INDEX(A2:A{data_end_row},MATCH(MAX(B2:B{data_end_row}),B2:B{data_end_row},0))"),
131    ]
132    
133    for i, (label, formula) in enumerate(summary_items):
134        row = summary_start + 1 + i
135        ws.cell(row=row, column=1, value=label)
136        ws.cell(row=row, column=2, value=formula)
137        ws.cell(row=row, column=1).font = Font(bold=True)
138        
139        # Format currency cells
140        if "Revenue" in label:
141            ws.cell(row=row, column=2).number_format = styles['currency']
142
143
144def add_report_header(ws, report_title):
145    """Add a title header above the data."""
146    
147    # Insert rows at top
148    ws.insert_rows(1, 3)
149    
150    # Title
151    ws.cell(row=1, column=1, value=report_title)
152    ws.cell(row=1, column=1).font = Font(name='Calibri', size=18, bold=True, color='4472C4')
153    ws.merge_cells('A1:D1')
154    
155    # Date
156    ws.cell(row=2, column=1, value=f"Generated: {datetime.now().strftime('%B %d, %Y at %H:%M')}")
157    ws.cell(row=2, column=1).font = Font(italic=True, color='666666')
158
159
160def generate_report(data, output_path, report_title="Sales Report"):
161    """
162    Generate a complete Excel report.
163    
164    Args:
165        data: List of dictionaries with 'product', 'sales', 'revenue' keys
166        output_path: Path to save the Excel file
167        report_title: Title for the report
168    
169    Returns:
170        Path to the generated report
171    """
172    
173    # Create workbook and styles
174    wb = Workbook()
175    ws = wb.active
176    ws.title = "Sales Data"
177    styles = create_styles()
178    
179    # Write headers
180    headers = ["Product", "Units Sold", "Revenue ($)"]
181    ws.append(headers)
182    
183    # Write data
184    for record in data:
185        ws.append([
186            record.get('product', ''),
187            record.get('sales', 0),
188            record.get('revenue', 0)
189        ])
190    
191    data_end_row = len(data) + 1  # +1 for header
192    
193    # Apply formatting
194    apply_formatting(ws, styles, data_end_row)
195    
196    # Add charts
197    add_charts(ws, data_end_row)
198    
199    # Add summary
200    add_summary(ws, styles, data_end_row)
201    
202    # Add report header (this shifts rows, so do it last)
203    add_report_header(ws, report_title)
204    
205    # Save
206    wb.save(output_path)
207    print(f"✅ Report generated: {output_path}")
208    
209    return output_path
210
211
212def main():
213    """Main entry point with example data."""
214    
215    # Example sales data
216    sales_data = [
217        {"product": "Widget Alpha", "sales": 1250, "revenue": 31250.00},
218        {"product": "Widget Beta", "sales": 890, "revenue": 26700.00},
219        {"product": "Widget Gamma", "sales": 2100, "revenue": 42000.00},
220        {"product": "Widget Delta", "sales": 560, "revenue": 19600.00},
221        {"product": "Widget Epsilon", "sales": 1800, "revenue": 45000.00},
222        {"product": "Widget Zeta", "sales": 420, "revenue": 14700.00},
223        {"product": "Widget Eta", "sales": 3200, "revenue": 64000.00},
224        {"product": "Widget Theta", "sales": 750, "revenue": 22500.00},
225    ]
226    
227    # Generate the report
228    output_file = f"sales_report_{datetime.now().strftime('%Y%m%d')}.xlsx"
229    generate_report(
230        data=sales_data,
231        output_path=output_file,
232        report_title="Q4 2025 Sales Report"
233    )
234    
235    print(f"\nOpen {output_file} in Excel to view your report!")
236
237
238if __name__ == "__main__":
239    main()

How to Run This Script

Install openpyxl:
bash
```
1pip install openpyxl
```
Save the script as excel_report_generator.py
Run the script:
bash
```
1python excel_report_generator.py
```

Expected output:

Prompt

✅ Report generated: sales_report_20251107.xlsx

Open sales_report_20251107.xlsx in Excel to view your report!

Open the generated file in Excel to see:
- Formatted data table with headers
- Alternating row colors
- Bar and pie charts
- Summary calculations

Customization Options

Read Data from CSV

python

1import csv
2
3def read_csv_data(filepath):
4    """Read data from a CSV file."""
5    data = []
6    with open(filepath, 'r') as f:
7        reader = csv.DictReader(f)
8        for row in reader:
9            data.append({
10                'product': row['Product'],
11                'sales': int(row['Sales']),
12                'revenue': float(row['Revenue'])
13            })
14    return data

Add Multiple Sheets

python

1def add_detailed_sheet(wb, data):
2    """Add a detailed breakdown sheet."""
3    ws = wb.create_sheet(title="Detailed Analysis")
4    
5    # Add your detailed content here
6    ws['A1'] = "Detailed Analysis"
7    # ...

Custom Color Schemes

python

1# Corporate blue theme
2COLORS = {
3    'primary': '0066CC',
4    'secondary': '003366',
5    'accent': 'FF9900',
6    'light': 'E6F0FF',
7}
8
9header_fill = PatternFill(
10    start_color=COLORS['primary'],
11    end_color=COLORS['primary'],
12    fill_type='solid'
13)

Email the Report Automatically

Combine with email automation (see our email automation guide):

python

1import smtplib
2from email.mime.multipart import MIMEMultipart
3from email.mime.base import MIMEBase
4from email import encoders
5
6def email_report(filepath, recipient):
7    """Send the report via email."""
8    # See our email automation tutorial for full implementation
9    pass

Common Issues & Solutions

Issue	Solution
"ModuleNotFoundError: openpyxl"	Run `pip install openpyxl`
Charts not appearing	Ensure data references are correct (row/column numbers)
Formatting not applied	Check that you're formatting after writing data
File won't open in Excel	Ensure .xlsx extension; check for corruption
Numbers showing as text	Set number_format on cells containing numbers

Taking It Further

Schedule Weekly Reports

Combine with task scheduling:

python

1# Windows Task Scheduler or cron job
2# python excel_report_generator.py

Connect to Database

python

1import sqlite3
2
3def get_data_from_database():
4    """Fetch data from SQLite database."""
5    conn = sqlite3.connect('sales.db')
6    cursor = conn.cursor()
7    
8    cursor.execute("""
9        SELECT product_name, SUM(quantity), SUM(revenue)
10        FROM sales
11        GROUP BY product_name
12    """)
13    
14    data = []
15    for row in cursor.fetchall():
16        data.append({
17            'product': row[0],
18            'sales': row[1],
19            'revenue': row[2]
20        })
21    
22    conn.close()
23    return data

Add Data Validation

python

1from openpyxl.worksheet.datavalidation import DataValidation
2
3def add_dropdown(ws, cell_range, options):
4    """Add a dropdown list to cells."""
5    dv = DataValidation(
6        type="list",
7        formula1=f'"{",".join(options)}"',
8        allow_blank=True
9    )
10    ws.add_data_validation(dv)
11    dv.add(cell_range)

Conclusion

You've built a complete Excel report generator. Every component is modular—swap out the data source, change the styling, add new charts—the foundation handles it all.

The real power here is consistency. Every report looks identical, every formula is correct, and you never have to manually format cells again. What used to take hours now takes seconds.

Start with this template, then customize it for your specific reports. Add your company's color scheme, connect it to your data sources, and watch your weekly reporting time drop to nearly zero.

Your Excel spreadsheets, automated.

Automate Excel Reports with Python and openpyxl

What You'll Learn

Prerequisites

The Problem

The Solution

Step 1: Installing openpyxl

Step 2: Creating a Basic Workbook

Step 3: Reading Data from Excel

Step 4: Applying Professional Formatting

Step 5: Adding Formulas and Calculations

Step 6: Creating Charts

The Complete Script

How to Run This Script

Customization Options

Read Data from CSV

Add Multiple Sheets

Custom Color Schemes

Email the Report Automatically

Common Issues & Solutions

Taking It Further

Schedule Weekly Reports

Connect to Database

Add Data Validation

Conclusion

Share this article