Python Excel with openpyxl — Core Concepts

openpyxl is the standard Python library for reading and writing Excel .xlsx files. It supports formulas, styles, charts, images, and conditional formatting — everything you’d do manually in Excel.

Installation

pip install openpyxl

Creating a Workbook

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.title = "Sales Data"

# Write headers
headers = ["Product", "Region", "Q1", "Q2", "Q3", "Total"]
ws.append(headers)

# Write data rows
ws.append(["Widget A", "North", 12000, 15400, 18200, "=SUM(C2:E2)"])
ws.append(["Widget B", "South", 8500, 9100, 11800, "=SUM(C3:E3)"])
ws.append(["Widget C", "East", 22000, 19600, 24500, "=SUM(C4:E4)"])

wb.save("sales.xlsx")

Reading Existing Files

from openpyxl import load_workbook

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

# Read a specific cell
print(ws["A1"].value)  # "Product"

# Iterate over rows
for row in ws.iter_rows(min_row=2, values_only=True):
    product, region, q1, q2, q3, total = row
    print(f"{product}: {q1 + q2 + q3}")

# Get dimensions
print(ws.dimensions)  # "A1:F4"

Cell Styling

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

# Header styling
header_font = Font(name="Calibri", bold=True, color="FFFFFF", size=12)
header_fill = PatternFill(start_color="2C3E50", fill_type="solid")
center = Alignment(horizontal="center", vertical="center")

for cell in ws[1]:
    cell.font = header_font
    cell.fill = header_fill
    cell.alignment = center

# Number formatting
for row in ws.iter_rows(min_row=2, min_col=3, max_col=6):
    for cell in row:
        cell.number_format = "$#,##0"

# Column widths
ws.column_dimensions["A"].width = 20
ws.column_dimensions["B"].width = 15

Conditional Formatting

from openpyxl.formatting.rule import CellIsRule

# Highlight high-value cells in green
green_fill = PatternFill(start_color="27AE60", fill_type="solid")
red_fill = PatternFill(start_color="E74C3C", fill_type="solid")

ws.conditional_formatting.add(
    "C2:E4",
    CellIsRule(operator="greaterThan", formula=["15000"], fill=green_fill)
)
ws.conditional_formatting.add(
    "C2:E4",
    CellIsRule(operator="lessThan", formula=["10000"], fill=red_fill)
)

Charts

from openpyxl.chart import BarChart, Reference

chart = BarChart()
chart.title = "Quarterly Sales"
chart.y_axis.title = "Revenue ($)"
chart.x_axis.title = "Product"

data = Reference(ws, min_col=3, min_row=1, max_col=5, max_row=4)
categories = Reference(ws, min_col=1, min_row=2, max_row=4)

chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)
chart.shape = 4  # Adds some visual styling

ws.add_chart(chart, "A7")

Multiple Sheets

wb = Workbook()
ws1 = wb.active
ws1.title = "Summary"

ws2 = wb.create_sheet("Raw Data")
ws3 = wb.create_sheet("Charts")

# Copy data between sheets
ws2.append(["Source", "Value"])
for i in range(100):
    ws2.append([f"Item {i}", i * 42])

Formulas

openpyxl writes formulas as strings — Excel evaluates them when opening:

ws["F2"] = "=SUM(C2:E2)"
ws["F5"] = "=SUM(F2:F4)"       # Grand total
ws["G2"] = "=F2/F$5"            # Percentage of total
ws["H2"] = '=IF(F2>50000,"High","Normal")'

Common Misconception

“openpyxl can run Excel formulas.” It can’t — it writes formula strings into cells, but they’re only evaluated when someone opens the file in Excel or LibreOffice. If you need to compute values in Python, do the math in Python and write the result.

The one thing to remember: openpyxl handles the full Excel workflow — data, formulas, styles, charts, and conditional formatting — but remember that formulas are stored as text and only compute when opened in Excel.