Excel Automation: openpyxl Read

By the end of this lesson you can:

• Install and import openpyxl, and open a workbook.
• List worksheets and select one by name or as the active sheet.
• Read a cell by coordinate (ws["B2"]) or row/column index.
• Iterate over rows to pull a sheet into Python data.

CSV is plain text — one table, no formatting, no formulas. A real .xlsx file is a zipped bundle of XML with multiple sheets, fonts, colours, merged cells, charts, and formulas. You can't read it with open().

pip install openpyxl     # the standard library for .xlsx in Python

Opening a workbook

from openpyxl import load_workbook

wb = load_workbook("sales.xlsx", data_only=True)
print(wb.sheetnames)            # ['Summary', 'January', 'February']

data_only=True is important: it reads the computed values of formula cells (what you see in Excel) rather than the formula text like "=SUM(B2:B10)". Use it whenever you want results, not formulas.

Selecting a sheet

ws = wb.active                  # the sheet that was open when saved
ws = wb["January"]              # by name
print(ws.title)                 # "January"
print(ws.max_row, ws.max_column)  # dimensions, e.g. 50 4

Reading single cells

# by Excel coordinate
print(ws["A1"].value)           # the header in A1
print(ws["B2"].value)           # a data cell

# by row, column number (both 1-based!)
print(ws.cell(row=2, column=2).value)   # same as B2

Iterating rows

# every row as a tuple of values
for row in ws.iter_rows(values_only=True):
    print(row)        # ('Date', 'Region', 'Amount'), then data rows…

# skip the header, read columns A and C only
for row in ws.iter_rows(min_row=2, min_col=1, max_col=3, values_only=True):
    date, region, amount = row
    print(date, amount)

values_only=True gives plain values (not cell objects). min_row=2 skips the header. min_col/max_col select a column range — exactly the controls you need to read a real report.

Turning a sheet into dicts (like DictReader)

rows = ws.iter_rows(values_only=True)
headers = next(rows)                       # first row = column names
records = [dict(zip(headers, row)) for row in rows]
print(records[0])      # {'Date': ..., 'Region': 'KL', 'Amount': 120}

This bridges Excel to the dict-of-rows shape you used for CSV in Lesson 15 — so all your CSV-era logic (filter, group, validate) applies unchanged.

Cell types come through

Unlike CSV (everything is a string), openpyxl preserves types: numbers arrive as int/float, dates as datetime objects, booleans as bool. No manual int() conversion needed — though always guard against None for empty cells.

Goal: a workbook has one sheet per month, each with Date, Region, Amount. Read them all and report the grand total plus a per-region breakdown — the report a manager currently builds by hand every quarter.

from openpyxl import load_workbook
from collections import defaultdict
import logging

logging.basicConfig(level=logging.INFO, format="%(levelname)s %(message)s")
log = logging.getLogger("xlsx")

def summarise(path: str) -> None:
    wb = load_workbook(path, data_only=True, read_only=True)
    by_region = defaultdict(float)
    grand_total = 0.0

    for name in wb.sheetnames:
        ws = wb[name]
        rows = ws.iter_rows(min_row=2, values_only=True)   # skip header
        sheet_total = 0.0
        for date, region, amount in rows:
            if amount is None:           # empty cell — skip
                continue
            amount = float(amount)
            by_region[region] += amount
            sheet_total += amount
        grand_total += sheet_total
        log.info("%s: %.2f", name, sheet_total)

    wb.close()
    print(f"\nGrand total: {grand_total:.2f}")
    for region, total in sorted(by_region.items(),
                                key=lambda kv: kv[1], reverse=True):
        print(f"  {region:12} {total:>10.2f}")

summarise("quarterly.xlsx")

INFO January: 28100.00
INFO February: 26450.50
INFO March: 29660.00

Grand total: 84210.50
  Central        31200.00
  North          28010.50
  South          25000.00

Read the code

We loop sheets via wb.sheetnames, skip each header with min_row=2, and accumulate into a defaultdict — the exact pattern from the CSV lessons, now reading native Excel. Two production touches: read_only=True streams large workbooks without loading everything into memory, and the if amount is None guard handles the blank cells real spreadsheets always have. The quarterly hand-built report is now one function call.

openpyxl opens real .xlsx files: a Workbook holds Worksheets (list via wb.sheetnames, select with wb["Name"]), each a grid of Cells read by coordinate (ws["B2"].value) or 1-based index. Use data_only=True for computed values, read_only=True for big files, and iter_rows(values_only=True) — with min_row=2 to skip headers — to pull a sheet into Python data. Zip headers with rows to get the dict-of-rows shape from CSV land, and guard against None empty cells. Next: writing styled workbooks back out.