How to Automate Excel Reports in Python using Openpyxl

Juggling between coding languages? Let our Code Converter help. Your one-stop solution for language conversion. Start now!

Microsoft Excel is everywhere. Finance teams live in it. Operations runs on it. Even developers who prefer databases over spreadsheets eventually need to send a report to someone who wants an .xlsx file. The manual approach — open Excel, copy-paste data, apply formatting, insert a chart, save — doesn't scale past the first request. Python can do all of that for you, reliably, in seconds.

In this tutorial, you'll learn how to use openpyxl — the most popular Python library for working with Excel .xlsx files — to build a fully automated sales report generator. We'll cover creating workbooks, reading existing files, writing formulas, applying professional formatting, building bar/line/pie charts, adding conditional formatting, and finally assembling everything into a polished multi-sheet dashboard. Every line of code is tested and ready to run.

By the end, you'll have a reusable reporting engine that takes raw data and produces a presentation-ready Excel file — with zero manual steps.

Why Openpyxl?

Python has several Excel libraries — xlrd, xlsxwriter, xlwings, pandas.ExcelWriter. Here's why openpyxl is the right default choice for report automation:

• Reads AND writes .xlsx files — xlsxwriter only writes, xlrd only reads.
• Preserves existing content — load a template, modify it, save. Pandas' ExcelWriter overwrites everything.
• Full formatting control — fonts, colors, borders, number formats, conditional formatting, data bars, color scales.
• Native chart support — bar, line, pie, scatter, area, radar, and more, all embedded in the workbook.
• Cross-platform — works on Windows, macOS, and Linux. No Excel installation required.
• MIT licensed and well-documented.

Related: Openpyxl official documentation — the definitive reference for all features covered here.

Installation & Setup

Install openpyxl with pip:

$ pip install openpyxl

That's it. No additional dependencies. The library works with Python 3.7+ and handles .xlsx files (the modern Excel format since Office 2007). It does not support the legacy .xls format — for those, use xlrd.

Part 1: Creating Your First Excel Workbook

Let's start with the fundamentals. Every Excel file is a Workbook containing one or more Worksheets. Here's how to create one from scratch, populate it with sales data, and add a formula column:

from openpyxl import Workbook

# Create a new workbook (comes with one default sheet)
wb = Workbook()
ws = wb.active
ws.title = "Sales Data"

# Write column headers
headers = ["Product", "Category", "Units Sold", "Unit Price ($)", "Revenue ($)"]
for col, header in enumerate(headers, 1):
    ws.cell(row=1, column=col, value=header)

# Write data rows
sales_data = [
    ["Wireless Mouse", "Accessories", 145, 24.99],
    ["Mechanical Keyboard", "Accessories", 98, 89.99],
    ["USB-C Hub", "Accessories", 210, 34.50],
    ["27\" Monitor", "Displays", 45, 299.99],
    ["Webcam 1080p", "Peripherals", 167, 54.99],
    ["Laptop Stand", "Accessories", 320, 39.99],
    ["Desk Lamp LED", "Office", 275, 29.99],
    ["Standing Desk", "Office", 22, 499.99],
    ["Noise Canceling Headphones", "Audio", 89, 149.99],
    ["Bluetooth Speaker", "Audio", 134, 79.99],
]

for row_idx, row_data in enumerate(sales_data, 2):
    for col_idx, value in enumerate(row_data, 1):
        ws.cell(row=row_idx, column=col_idx, value=value)

# Add a Revenue formula column (= Units Sold * Unit Price)
for row in range(2, 2 + len(sales_data)):
    ws.cell(row=row, column=5, value=f"=C{row}*D{row}")

# Save to disk
wb.save("sales_report.xlsx")
print("Workbook saved!")

Key points:

• Workbook() creates a new in-memory workbook with one default sheet accessible via wb.active.
• ws.cell(row=r, column=c, value=v) writes to a specific cell. Columns and rows are 1-indexed (Excel convention).
• Formulas are strings starting with = — openpyxl writes them as-is and Excel evaluates them when the file is opened.
• The wb.save() method writes the final .xlsx file to disk.

Part 2: Reading Existing Excel Files

Reading back data is just as straightforward. Use load_workbook() to open an existing file, then iterate through rows or access cells directly:

from openpyxl import load_workbook

wb = load_workbook("sales_report.xlsx")
ws = wb.active

print(f"Sheet: {ws.title}")
print(f"Dimensions: {ws.dimensions}")  # e.g., A1:E11
print(f"Rows: {ws.max_row}, Columns: {ws.max_column}")

# Read headers
headers = [ws.cell(row=1, column=c).value for c in range(1, ws.max_column + 1)]
print(f"Headers: {headers}")

# Iterate through data rows (skip header)
for row in ws.iter_rows(min_row=2, max_row=5, values_only=True):
    print(row)  # ('Wireless Mouse', 'Accessories', 145, 24.99, '=C2*D2')

# Access a specific cell
print(f"A2 = {ws['A2'].value}")  # Wireless Mouse

Note that values_only=True returns the computed values as Python types. Without it, you get Cell objects. Also, formula cells return the formula string — not the evaluated result — unless you open the workbook with data_only=True. The caveat: data_only=True requires Excel to have computed those formulas first, so it only works on files that were opened and saved by Excel.

Part 3: Professional Cell Formatting

Raw data in a spreadsheet looks unpolished. openpyxl gives you pixel-level control over fonts, colors, borders, alignment, and number formatting. Here's how to transform the basic sales report into a professional-looking document:

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

# Load the workbook we created earlier
wb = load_workbook("sales_report.xlsx")
ws = wb.active

# === Color palette ===
DARK_BLUE = "1F4E79"
LIGHT_BLUE = "D6E4F0"
WHITE = "FFFFFF"

# === Reusable styles ===
header_fill = PatternFill(start_color=DARK_BLUE, end_color=DARK_BLUE, fill_type="solid")
header_font = Font(name="Calibri", size=12, bold=True, color=WHITE)
header_align = Alignment(horizontal="center", vertical="center")

thin_border = Border(
    left=Side(style="thin"), right=Side(style="thin"),
    top=Side(style="thin"), bottom=Side(style="thin")
)
alt_fill = PatternFill(start_color=LIGHT_BLUE, end_color=LIGHT_BLUE, fill_type="solid")

# --- Style the header row ---
for col in range(1, ws.max_column + 1):
    cell = ws.cell(row=1, column=col)
    cell.fill = header_fill
    cell.font = header_font
    cell.alignment = header_align
    cell.border = thin_border

# --- Style data rows ---
for row in range(2, ws.max_row + 1):
    for col in range(1, ws.max_column + 1):
        cell = ws.cell(row=row, column=col)
        cell.font = Font(name="Calibri", size=11)
        cell.border = thin_border
        # Alternating row colors for readability
        if row % 2 == 0:
            cell.fill = alt_fill

    # Apply number formatting
    ws.cell(row=row, column=3).number_format = '#,##0'        # Units
    ws.cell(row=row, column=4).number_format = '$#,##0.00'    # Price
    ws.cell(row=row, column=5).number_format = '$#,##0.00'    # Revenue

# --- Set column widths ---
col_widths = {1: 28, 2: 18, 3: 14, 4: 16, 5: 16}
for col_num, width in col_widths.items():
    ws.column_dimensions[get_column_letter(col_num)].width = width

# --- Add a totals row ---
total_row = ws.max_row + 1
ws.merge_cells(f"A{total_row}:B{total_row}")
total_label = ws.cell(row=total_row, column=1, value="TOTAL")
total_label.font = Font(name="Calibri", size=12, bold=True, color=DARK_BLUE)
total_label.alignment = Alignment(horizontal="right")
total_label.fill = PatternFill(start_color="E2EFDA", end_color="E2EFDA", fill_type="solid")

for col in [3, 4, 5]:
    cell = ws.cell(row=total_row, column=col)
    cell.value = f"=SUM({get_column_letter(col)}2:{get_column_letter(col)}{total_row - 1})"
    cell.font = Font(name="Calibri", size=12, bold=True)
    cell.fill = PatternFill(start_color="E2EFDA", end_color="E2EFDA", fill_type="solid")
    cell.border = thin_border
    if col >= 4:
        cell.number_format = '$#,##0.00'

# Freeze the header row so it stays visible when scrolling
ws.freeze_panes = "A2"

wb.save("sales_report_formatted.xlsx")

The key classes from openpyxl.styles:

• Font — name, size, bold, italic, color (hex RRGGBB).
• PatternFill — solid background colors with start_color and end_color.
• Alignment — horizontal, vertical, wrap_text.
• Border + Side — thin/medium/thick borders on any edge.
• number_format — Excel format strings like '$#,##0.00' or '0.0%'.
• freeze_panes — locks rows/columns so they stay visible when scrolling (like "Freeze Top Row" in Excel).

Part 4: Conditional Formatting

Conditional formatting changes cell appearance based on their values — think data bars, color scales, and icon sets. It makes spreadsheets scannable at a glance. openpyxl supports all the major types:

from openpyxl.formatting.rule import DataBarRule, ColorScaleRule

# --- Data bars on Units Sold column ---
# Adds an in-cell horizontal bar proportional to the value
ws.conditional_formatting.add(
    "C2:C11",
    DataBarRule(start_type="min", end_type="max",
                color="2E75B6", showValue=True)
)

# --- Three-color scale on Revenue column ---
# Red for low values, yellow for middle, green for high
ws.conditional_formatting.add(
    "E2:E11",
    ColorScaleRule(
        start_type="min", start_color="F8696B",       # Red
        mid_type="percentile", mid_value=50, mid_color="FFEB84",  # Yellow
        end_type="max", end_color="63BE7B"             # Green
    )
)

You can also use CellIsRule for rule-based formatting (e.g., "highlight cells greater than 1000"):

from openpyxl.formatting.rule import CellIsRule

ws.conditional_formatting.add(
    "E2:E11",
    CellIsRule(operator="greaterThan", formula=["10000"],
               fill=PatternFill(start_color="C6EFCE", end_color="C6EFCE", fill_type="solid"),
               font=Font(color="006100"))
)

Part 5: Adding Charts (Bar, Line, Pie)

Charts are where openpyxl really shines. You can create professional charts directly in Python and embed them in the worksheet — the output looks like it was hand-crafted in Excel. Let's create all three major chart types on separate sheets.

Bar Chart — Revenue by Category

First, we aggregate sales by category on a summary sheet, then build a colored bar chart:

from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference
from openpyxl.chart.series import DataPoint

wb = Workbook()
ws = wb.active
ws.title = "Category Summary"

# Write category summary data
ws.append(["Category", "Total Units", "Total Revenue"])
category_data = [
    ["Accessories", 773, 30273.45],
    ["Displays", 45, 13499.55],
    ["Peripherals", 167, 9183.33],
    ["Office", 297, 19247.67],
    ["Audio", 223, 24075.32],
]
for row in category_data:
    ws.append(row)

# Create and configure the bar chart
bar_chart = BarChart()
bar_chart.type = "col"              # vertical columns
bar_chart.style = 10                # pre-defined Excel chart style
bar_chart.title = "Total Revenue by Category"
bar_chart.y_axis.title = "Revenue ($)"
bar_chart.x_axis.title = "Category"
bar_chart.width = 22                # in cm
bar_chart.height = 14

# Define the data and category references
data_ref = Reference(ws, min_col=3, min_row=1, max_row=len(category_data) + 1)
cats_ref = Reference(ws, min_col=1, min_row=2, max_row=len(category_data) + 1)
bar_chart.add_data(data_ref, titles_from_data=True)
bar_chart.set_categories(cats_ref)

# Color each bar individually
colors = ["2E75B6", "ED7D31", "A5A5A5", "FFC000", "4472C4"]
for i, color in enumerate(colors):
    pt = DataPoint(idx=i)
    pt.graphicalProperties.solidFill = color
    bar_chart.series[0].data_points.append(pt)

ws.add_chart(bar_chart, "E2")   # anchor chart at cell E2

Line Chart — Monthly Revenue Trend

Line charts are ideal for time-series data. Here's a year-over-year monthly comparison:

from openpyxl.chart import LineChart

ws2 = wb.create_sheet("Monthly Trend")
ws2.append(["Month", "Revenue 2024", "Revenue 2025"])

months = ["Jan", "Feb", "Mar", "Apr", "May", "Jun"]
for i, m in enumerate(months):
    ws2.append([m, 45000 + i * 3400, 48000 + i * 4100])

line_chart = LineChart()
line_chart.title = "Monthly Revenue — 2024 vs 2025"
line_chart.style = 10
line_chart.width = 24
line_chart.height = 14

data_ref = Reference(ws2, min_col=2, max_col=3, min_row=1, max_row=len(months)+1)
cats_ref = Reference(ws2, min_col=1, min_row=2, max_row=len(months)+1)
line_chart.add_data(data_ref, titles_from_data=True)
line_chart.set_categories(cats_ref)

# Style the lines (width is in EMUs — 28000 ≈ 2.2pt)
line_chart.series[0].graphicalProperties.line.width = 28000
line_chart.series[1].graphicalProperties.line.width = 28000

ws2.add_chart(line_chart, "E2")

Pie Chart — Product Distribution

from openpyxl.chart import PieChart
from openpyxl.chart.label import DataLabelList

ws3 = wb.create_sheet("Product Mix")
ws3.append(["Product", "Units Sold"])

product_mix = [
    ["Wireless Mouse", 145], ["Mechanical Keyboard", 98],
    ["USB-C Hub", 210], ["Monitor 27\"", 45],
    ["Webcam 1080p", 167], ["Laptop Stand", 320],
]
for p in product_mix:
    ws3.append(p)

pie_chart = PieChart()
pie_chart.title = "Sales Distribution by Product"
pie_chart.width = 18
pie_chart.height = 14

pie_data = Reference(ws3, min_col=2, min_row=1, max_row=len(product_mix) + 1)
pie_cats = Reference(ws3, min_col=1, min_row=2, max_row=len(product_mix) + 1)
pie_chart.add_data(pie_data, titles_from_data=True)
pie_chart.set_categories(pie_cats)

# Add percentage labels on each slice
pie_chart.dataLabels = DataLabelList()
pie_chart.dataLabels.showPercent = True
pie_chart.dataLabels.showCatName = True

# Color each slice
pie_colors = ["2E75B6", "ED7D31", "A5A5A5", "FFC000", "4472C4", "70AD47"]
for i, color in enumerate(pie_colors):
    pt = DataPoint(idx=i)
    pt.graphicalProperties.solidFill = color
    pie_chart.series[0].data_points.append(pt)

ws3.add_chart(pie_chart, "D2")

wb.save("sales_charts.xlsx")

Important chart concepts:

• Reference objects define where the chart data lives in the worksheet. They use min_col, min_row, max_col, max_row.
• DataPoint lets you style individual bars/slices/points — color, border, pattern fill.
• DataLabelList controls what labels appear: percentages, category names, values, or any combination.
• Chart dimensions (width, height) are in centimeters.

Part 6: Working with Multiple Worksheets

Real reports rarely fit on a single sheet. openpyxl makes multi-sheet workbooks trivial:

wb = Workbook()

# Default sheet
ws1 = wb.active
ws1.title = "Raw Data"

# Create additional sheets
ws2 = wb.create_sheet("Summary")       # appended at the end
ws3 = wb.create_sheet("Charts", 0)     # inserted at position 0 (first)

# List all sheets
print(wb.sheetnames)  # ['Charts', 'Raw Data', 'Summary']

# Access a sheet by name
summary = wb["Summary"]

# Copy a sheet (within the same workbook)
ws_copy = wb.copy_worksheet(ws1)
ws_copy.title = "Data Backup"

# Delete a sheet
del wb["Charts"]

wb.save("multi_sheet_report.xlsx")

Note: copy_worksheet() only works within the same workbook. It copies values, styles, and formatting — but not charts or images.

Part 7: Real-World Project — Automated Sales Report Generator

Now let's assemble everything into a complete, production-ready report generator. This script takes raw sales data and produces a polished 4-sheet Excel workbook with:

• Sheet 1 — Sales Data: Detailed transaction list with revenue/profit/margin formulas, auto-filter, frozen header, color scale on margins, data bars on units.
• Sheet 2 — Category Summary: Aggregated by product category with a colored bar chart and data bars.
• Sheet 3 — Regional Breakdown: Revenue by region with a labeled pie chart.
• Sheet 4 — Executive Dashboard: KPI cards (total revenue, units, profit, margin), summary table, polished presentation layout.

"""
Automated Sales Report Generator
Produces a 4-sheet professional Excel report from raw sales data.
"""
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.chart import BarChart, PieChart, Reference
from openpyxl.chart.label import DataLabelList
from openpyxl.chart.series import DataPoint
from openpyxl.utils import get_column_letter
from openpyxl.formatting.rule import DataBarRule, ColorScaleRule
from collections import defaultdict

# ============================================================
# CONFIGURATION — swap this with your own data source
# ============================================================
SALES_DATA = [
    # Product, Category, Region, Units, Price, Cost
    ["Wireless Mouse", "Accessories", "North", 145, 24.99, 12.50],
    ["Mechanical Keyboard", "Accessories", "North", 98, 89.99, 45.00],
    ["USB-C Hub", "Accessories", "South", 210, 34.50, 17.25],
    ["27\" Monitor", "Displays", "East", 45, 299.99, 180.00],
    ["24\" Monitor", "Displays", "West", 67, 189.99, 110.00],
    ["Webcam 1080p", "Peripherals", "North", 167, 54.99, 27.50],
    ["Laptop Stand", "Accessories", "South", 320, 39.99, 15.00],
    ["Desk Lamp LED", "Office", "East", 275, 29.99, 10.00],
    ["Standing Desk", "Office", "West", 22, 499.99, 250.00],
    ["Noise Canceling Phones", "Audio", "North", 89, 149.99, 75.00],
    ["Bluetooth Speaker", "Audio", "South", 134, 79.99, 40.00],
    ["HDMI Cable 6ft", "Accessories", "East", 450, 12.99, 4.00],
    ["Wireless Charger", "Accessories", "West", 189, 19.99, 8.00],
    ["Ergonomic Chair", "Office", "North", 15, 899.99, 450.00],
]

# ============================================================
# STYLES
# ============================================================
DARK_BLUE, MED_BLUE, LIGHT_BLUE = "1F4E79", "2E75B6", "D6E4F0"
GREEN_HEADER, LIGHT_GREEN, WHITE = "375623", "E2EFDA", "FFFFFF"

hdr_fill = PatternFill(start_color=DARK_BLUE, end_color=DARK_BLUE, fill_type="solid")
hdr_font = Font(name="Calibri", size=11, bold=True, color=WHITE)
hdr_align = Alignment(horizontal="center", vertical="center", wrap_text=True)
thin_border = Border(left=Side(style="thin"), right=Side(style="thin"),
                     top=Side(style="thin"), bottom=Side(style="thin"))
alt_fill = PatternFill(start_color=LIGHT_BLUE, end_color=LIGHT_BLUE, fill_type="solid")
curr_fmt, num_fmt, pct_fmt = '$#,##0.00', '#,##0', '0.0%'

# ============================================================
# BUILD WORKBOOK
# ============================================================
wb = Workbook()

# ----- SHEET 1: Detailed Sales Data -----
ws = wb.active
ws.title = "Sales Data"

headers = ["Product", "Category", "Region", "Units Sold",
           "Unit Price", "Unit Cost", "Revenue", "Profit", "Margin %"]
for c, h in enumerate(headers, 1):
    cell = ws.cell(row=1, column=c, value=h)
    cell.fill, cell.font, cell.alignment, cell.border = hdr_fill, hdr_font, hdr_align, thin_border

for r, row in enumerate(SALES_DATA, 2):
    for c, val in enumerate(row, 1):
        cell = ws.cell(row=r, column=c, value=val)
        cell.font = Font(name="Calibri", size=10)
        cell.border = thin_border
        if c >= 4: cell.alignment = Alignment(horizontal="right")
        if r % 2 == 0: cell.fill = alt_fill
    
    # Formulas
    ws.cell(row=r, column=7, value=f"=D{r}*E{r}")
    ws.cell(row=r, column=8, value=f"=G{r}-(D{r}*F{r})")
    ws.cell(row=r, column=9, value=f"=H{r}/G{r}")
    ws.cell(row=r, column=5).number_format = curr_fmt
    ws.cell(row=r, column=6).number_format = curr_fmt
    ws.cell(row=r, column=7).number_format = curr_fmt
    ws.cell(row=r, column=8).number_format = curr_fmt
    ws.cell(row=r, column=9).number_format = pct_fmt

# Totals row
tr = len(SALES_DATA) + 2
ws.merge_cells(f"A{tr}:C{tr}")
tc = ws.cell(row=tr, column=1, value="TOTALS")
tc.font = Font(name="Calibri", size=11, bold=True, color=DARK_BLUE)
tc.alignment = Alignment(horizontal="right")
tc.fill = PatternFill(start_color=LIGHT_GREEN, end_color=LIGHT_GREEN, fill_type="solid")
for col in [4, 7, 8]:
    cell = ws.cell(row=tr, column=col)
    cell.value = f"=SUM({get_column_letter(col)}2:{get_column_letter(col)}{tr-1})"
    cell.font = Font(name="Calibri", size=11, bold=True)
    cell.fill = PatternFill(start_color=LIGHT_GREEN, end_color=LIGHT_GREEN, fill_type="solid")
    cell.border = thin_border
    cell.number_format = curr_fmt if col >= 7 else num_fmt

# Conditional formatting + freeze + auto-filter
ws.conditional_formatting.add(f"I2:I{tr-1}", ColorScaleRule(
    start_type="num", start_value=0, start_color="F8696B",
    mid_type="percentile", mid_value=50, mid_color="FFEB84",
    end_type="num", end_value=0.6, end_color="63BE7B"))
ws.conditional_formatting.add(f"D2:D{tr-1}",
    DataBarRule(start_type="min", end_type="max", color=MED_BLUE, showValue=True))
ws.freeze_panes = "A2"
ws.auto_filter.ref = f"A1:I{tr-1}"

# Column widths
for i, w in enumerate([26, 16, 10, 12, 14, 14, 14, 14, 12], 1):
    ws.column_dimensions[get_column_letter(i)].width = w

# ----- SHEET 2: Category Summary -----
ws_cat = wb.create_sheet("Category Summary")

# Aggregate by category
cat_data = defaultdict(lambda: {"units": 0, "revenue": 0.0, "profit": 0.0})
for row in SALES_DATA:
    cat, units, price, cost = row[1], row[3], row[4], row[5]
    rev = units * price
    cat_data[cat]["units"] += units
    cat_data[cat]["revenue"] += rev
    cat_data[cat]["profit"] += rev - (units * cost)

sorted_cats = sorted(cat_data.items(), key=lambda x: x[1]["revenue"], reverse=True)

for c, h in enumerate(["Category", "Total Units", "Total Revenue", "Total Profit", "Margin %"], 1):
    cell = ws_cat.cell(row=1, column=c, value=h)
    cell.fill = PatternFill(start_color=GREEN_HEADER, end_color=GREEN_HEADER, fill_type="solid")
    cell.font = Font(name="Calibri", size=11, bold=True, color=WHITE)
    cell.alignment, cell.border = hdr_align, thin_border

for r, (cat, vals) in enumerate(sorted_cats, 2):
    ws_cat.cell(row=r, column=1, value=cat)
    ws_cat.cell(row=r, column=2, value=vals["units"])
    ws_cat.cell(row=r, column=3, value=round(vals["revenue"], 2))
    ws_cat.cell(row=r, column=4, value=round(vals["profit"], 2))
    ws_cat.cell(row=r, column=5, value=f"=D{r}/C{r}")
    for c in range(1, 6):
        cell = ws_cat.cell(row=r, column=c)
        cell.font = Font(name="Calibri", size=10)
        cell.border = thin_border
        if c >= 2: cell.alignment = Alignment(horizontal="right")
        if r % 2 == 0: cell.fill = alt_fill
    ws_cat.cell(row=r, column=3).number_format = curr_fmt
    ws_cat.cell(row=r, column=4).number_format = curr_fmt
    ws_cat.cell(row=r, column=5).number_format = pct_fmt

# Bar chart
bar = BarChart(); bar.type = "col"; bar.style = 10
bar.title = "Revenue by Product Category"
bar.width, bar.height = 22, 14
bar.add_data(Reference(ws_cat, min_col=3, min_row=1, max_row=len(sorted_cats)+1), titles_from_data=True)
bar.set_categories(Reference(ws_cat, min_col=1, min_row=2, max_row=len(sorted_cats)+1))
chart_colors = ["2E75B6", "ED7D31", "A5A5A5", "FFC000", "4472C4", "70AD47"]
for i in range(len(sorted_cats)):
    pt = DataPoint(idx=i); pt.graphicalProperties.solidFill = chart_colors[i % 6]
    bar.series[0].data_points.append(pt)
ws_cat.add_chart(bar, "G2")
ws_cat.conditional_formatting.add(f"C2:C{len(sorted_cats)+1}",
    DataBarRule(start_type="min", end_type="max", color=MED_BLUE, showValue=True))
for i, w in enumerate([20, 14, 18, 16, 12], 1):
    ws_cat.column_dimensions[get_column_letter(i)].width = w

# ----- SHEET 3: Regional Breakdown -----
ws_reg = wb.create_sheet("Regional Breakdown")
reg_data = defaultdict(lambda: {"units": 0, "revenue": 0.0})
for row in SALES_DATA:
    reg = row[2]; units = row[3]; rev = units * row[4]
    reg_data[reg]["units"] += units; reg_data[reg]["revenue"] += rev

for c, h in enumerate(["Region", "Units Sold", "Revenue"], 1):
    cell = ws_reg.cell(row=1, column=c, value=h)
    cell.fill, cell.font, cell.alignment, cell.border = hdr_fill, hdr_font, hdr_align, thin_border

for r, (reg, vals) in enumerate(sorted(reg_data.items()), 2):
    ws_reg.cell(row=r, column=1, value=reg)
    ws_reg.cell(row=r, column=2, value=vals["units"])
    ws_reg.cell(row=r, column=3, value=round(vals["revenue"], 2))
    for c in range(1, 4):
        cell = ws_reg.cell(row=r, column=c)
        cell.font = Font(name="Calibri", size=10); cell.border = thin_border
        if c >= 2: cell.alignment = Alignment(horizontal="right")
    ws_reg.cell(row=r, column=3).number_format = curr_fmt

pie = PieChart(); pie.title = "Revenue Share by Region"
pie.width, pie.height = 18, 14
pie.add_data(Reference(ws_reg, min_col=3, min_row=1, max_row=len(reg_data)+1), titles_from_data=True)
pie.set_categories(Reference(ws_reg, min_col=1, min_row=2, max_row=len(reg_data)+1))
pie.dataLabels = DataLabelList(); pie.dataLabels.showPercent = True; pie.dataLabels.showCatName = True
for i in range(len(reg_data)):
    pt = DataPoint(idx=i); pt.graphicalProperties.solidFill = chart_colors[i % 6]
    pie.series[0].data_points.append(pt)
ws_reg.add_chart(pie, "E2")
for c, w in [('A', 14), ('B', 14), ('C', 14)]:
    ws_reg.column_dimensions[c].width = w

# ----- SHEET 4: Executive Dashboard -----
ws_exec = wb.create_sheet("Executive Dashboard")
ws_exec.merge_cells("A1:F1")
title = ws_exec.cell(row=1, column=1, value="SALES PERFORMANCE DASHBOARD — Q1 2025")
title.font = Font(name="Calibri", size=16, bold=True, color=DARK_BLUE)
title.alignment = Alignment(horizontal="center", vertical="center")
ws_exec.row_dimensions[1].height = 35

total_revenue = sum(r[3] * r[4] for r in SALES_DATA)
total_units = sum(r[3] for r in SALES_DATA)
total_profit = sum((r[3] * r[4]) - (r[3] * r[5]) for r in SALES_DATA)
avg_margin = total_profit / total_revenue if total_revenue else 0

kpis = [("TOTAL REVENUE", f"${total_revenue:,.0f}", 1),
        ("TOTAL UNITS SOLD", f"{total_units:,}", 2),
        ("TOTAL PROFIT", f"${total_profit:,.0f}", 3),
        ("AVG MARGIN", f"{avg_margin:.1%}", 4)]
for kpi_title, kpi_val, col in kpis:
    for r_offset, (val, font) in enumerate([(kpi_title, Font(size=10, bold=True, color=WHITE)),
                                              (kpi_val, Font(size=22, bold=True, color=WHITE))]):
        cell = ws_exec.cell(row=3 + r_offset, column=col, value=val)
        cell.font = font
        cell.fill = PatternFill(start_color=MED_BLUE, end_color=MED_BLUE, fill_type="solid")
        cell.alignment = Alignment(horizontal="center")
    ws_exec.column_dimensions[get_column_letter(col)].width = 22
ws_exec.row_dimensions[4].height = 40

# Summary table on dashboard
ws_exec.merge_cells("A6:D6")
ws_exec.cell(row=6, column=1, value="Key Metrics by Category").font = Font(size=12, bold=True, color=DARK_BLUE)
for c, h in enumerate(["Category", "Units", "Revenue", "Profit"], 1):
    cell = ws_exec.cell(row=7, column=c, value=h)
    cell.fill, cell.font, cell.alignment, cell.border = hdr_fill, hdr_font, hdr_align, thin_border
for r, (cat, vals) in enumerate(sorted_cats, 8):
    ws_exec.cell(row=r, column=1, value=cat)
    ws_exec.cell(row=r, column=2, value=vals["units"])
    ws_exec.cell(row=r, column=3, value=round(vals["revenue"], 2))
    ws_exec.cell(row=r, column=4, value=round(vals["profit"], 2))
    for c in range(1, 5):
        cell = ws_exec.cell(row=r, column=c)
        cell.font = Font(name="Calibri", size=10); cell.border = thin_border
        if r % 2 == 0: cell.fill = alt_fill
    ws_exec.cell(row=r, column=3).number_format = curr_fmt
    ws_exec.cell(row=r, column=4).number_format = curr_fmt

# ============================================================
# SAVE
# ============================================================
wb.save("Sales_Report_Q1_2025.xlsx")
print("✅ Report generated: Sales_Report_Q1_2025.xlsx")
print(f"   Sheets: {wb.sheetnames}")
print(f"   Total Revenue: ${total_revenue:,.2f}")
print(f"   Total Profit: ${total_profit:,.2f}")
print(f"   Avg Margin: {avg_margin:.1%}")

Running this script produces a 4-sheet, fully-formatted Excel report in under a second. The output is ready to send to stakeholders — no manual formatting required.

Going Further: Ideas for Your Own Reports

The generator above is a starting point. Here are practical extensions you can add:

• Read from CSV/database: Replace the hardcoded SALES_DATA list with csv.DictReader, pandas.read_sql, or an API call.
• Load a template: Start with a pre-designed .xlsx file containing your company's branding, then use load_workbook() to populate it.
• Add images/logos: Use ws.add_image(Image('logo.png')) to insert your company logo in the header.
• Schedule it: Run the script on a cron job (Linux/macOS) or Task Scheduler (Windows) to generate reports automatically every Monday morning.
• Email the report: Combine with smtplib to email the generated .xlsx file to recipients. Check our email sending tutorial.
• Pivot tables: openpyxl supports creating pivot tables programmatically. The official docs cover this.

Openpyxl vs. Other Libraries

Knowing when to use openpyxl versus alternatives saves time:

Conclusion

You now have a complete, production-tested Excel automation toolkit. To recap what you've learned:

• ✅ Creating and reading .xlsx files with Workbook() and load_workbook()
• ✅ Writing Excel formulas as Python strings
• ✅ Professional cell formatting — fonts, fills, borders, alignment, number formats
• ✅ Conditional formatting — data bars, color scales, cell rules
• ✅ Building bar, line, and pie charts with custom colors
• ✅ Multi-sheet workbooks with sheet management
• ✅ A complete 4-sheet automated sales report with dashboard

The full code from Part 7 is ready to drop into your project — replace SALES_DATA with your own data source, adjust the colors to match your brand, and you have an automated reporting pipeline that saves hours every week.

For deeper dives, the openpyxl official documentation is excellent and covers advanced features like pivot tables, print settings, and data validation.

Happy automating! ♥

Save time and energy with our Python Code Generator. Why start from scratch when you can generate? Give it a try!

Comment panel