Speed Up openpyxl with read_only Mode

Reading a large workbook with plain load_workbook builds every cell as a Python object before you touch a single value. read_only=True replaces that with a stream: rows are parsed as you ask for them and discarded afterwards. The speed-up is substantial, and so is the list of things you give up. This guide, part of Working with Large Excel Files in Python, covers both sides.

Prerequisites

pip install openpyxl

The basic streaming read

from openpyxl import load_workbook

wb = load_workbook("large.xlsx", read_only=True, data_only=True)
try:
    ws = wb["Orders"]
    rows = ws.iter_rows(values_only=True)
    header = list(next(rows))
    print("columns:", header)

    count = 0
    for row in rows:
        count += 1
    print(f"{count:,} data row(s)")
finally:
    wb.close()

Three arguments are doing the work. read_only=True selects the streaming reader. data_only=True returns Excel's cached results rather than formula text, which is what you want when harvesting numbers — see reading formula results for the caveats. And values_only=True on the iterator yields plain tuples instead of cell objects.

The try/finally is not decoration. A read-only workbook keeps the .xlsx archive open for as long as it might read from it, so a loop over a folder of files without close() exhausts the process's file handles after a few hundred iterations.

What values_only saves

The flag looks minor and is the single biggest lever in the whole mode:

import time
from openpyxl import load_workbook

def timed(label, values_only):
    wb = load_workbook("large.xlsx", read_only=True, data_only=True)
    ws = wb["Orders"]
    start = time.perf_counter()
    total = 0
    for row in ws.iter_rows(min_row=2, values_only=values_only):
        cell = row[4]
        total += (cell if values_only else cell.value) or 0
    wb.close()
    print(f"{label:22} {time.perf_counter() - start:5.1f}s  total={total:,.0f}")

timed("values_only=True", True)
timed("values_only=False", False)

With values_only=False, openpyxl constructs a ReadOnlyCell for every cell in every row — millions of short-lived objects, each triggering allocation and garbage collection. With it True, each row is a tuple of plain Python values. The only reason to leave it off is when you need a cell's coordinate or number format, which read-only mode exposes only partially anyway.

Index the header, then use positions

Streaming gives you tuples, so column names have to be resolved once and used as indexes:

from openpyxl import load_workbook

def stream_records(path, sheet, wanted):
    wb = load_workbook(path, read_only=True, data_only=True)
    try:
        ws = wb[sheet]
        rows = ws.iter_rows(values_only=True)
        header = [str(h).strip() if h is not None else "" for h in next(rows)]
        missing = [c for c in wanted if c not in header]
        if missing:
            raise ValueError(f"{path}: missing column(s) {missing}")
        picks = {c: header.index(c) for c in wanted}

        for row in rows:
            yield {name: row[i] for name, i in picks.items()}
    finally:
        wb.close()

revenue = 0.0
for record in stream_records("large.xlsx", "Orders", ["Quantity", "Unit_Price"]):
    revenue += (record["Quantity"] or 0) * (record["Unit_Price"] or 0)

print(f"revenue: {revenue:,.2f}")

Building a small dict per row costs something, but it keeps the calling code readable and is still far cheaper than cell objects. On the hottest loops, drop the dict and use the integer indexes directly — the difference shows up above a million rows.

The limits you are accepting

The right-hand column decides whether the mode fits. A job that reads values and produces a summary loses nothing. A job that reads a sheet, edits three cells and saves cannot use it at all — and the usual answer there is two handles: a read-only one for the scan and a normal one for the edit.

Dimensions are declared, not measured

ws.max_row in read-only mode comes from the sheet's declared dimension, which some writers omit and others overstate after rows have been deleted:

from openpyxl import load_workbook

wb = load_workbook("large.xlsx", read_only=True)
ws = wb["Orders"]

print("declared:", ws.max_row, "x", ws.max_column)
print("recalculated:", ws.calculate_dimension())

real_rows = sum(1 for row in ws.iter_rows(values_only=True) if any(v is not None for v in row))
print("rows with content:", real_rows)
wb.close()

calculate_dimension() forces a scan, which costs a pass over the file. If you are going to iterate anyway, count as you go instead — and never size a progress bar or an allocation on max_row in this mode without checking it first.

Reading several sheets safely

Each sheet is a separate stream, and a read-only sheet cannot be rewound. Iterate one sheet to completion before moving on, and re-open the workbook if you need a second pass:

from openpyxl import load_workbook

def sheet_totals(path, column="Unit_Price"):
    totals = {}
    wb = load_workbook(path, read_only=True, data_only=True)
    try:
        for ws in wb.worksheets:
            rows = ws.iter_rows(values_only=True)
            header = list(next(rows, ()))
            if column not in header:
                continue
            idx = header.index(column)
            totals[ws.title] = sum((row[idx] or 0) for row in rows)
    finally:
        wb.close()
    return totals

print(sheet_totals("large.xlsx"))

Calling iter_rows twice on the same read-only sheet re-parses it from the beginning, which is correct but doubles the work. Where two passes are genuinely needed — a scan to find the header row, then a scan to read it — accumulate what you need in the first pass instead.

Two handles when you need to read and write

The common real-world job is "scan a big sheet, then write a small summary into the same workbook". Read-only mode cannot save, so use two handles with clearly separated roles:

from openpyxl import load_workbook

# 1. Stream the detail sheet to compute the numbers
reader = load_workbook("large.xlsx", read_only=True, data_only=True)
try:
    ws = reader["Orders"]
    rows = ws.iter_rows(values_only=True)
    header = list(next(rows))
    qty, price = header.index("Quantity"), header.index("Unit_Price")
    revenue = sum((r[qty] or 0) * (r[price] or 0) for r in rows)
finally:
    reader.close()

# 2. Re-open normally, but only to write a handful of cells
writer = load_workbook("large.xlsx")
summary = writer.create_sheet("Summary", 0)
summary["A1"], summary["B1"] = "Total revenue", round(revenue, 2)
summary["B1"].number_format = "#,##0.00"
writer.save("large_with_summary.xlsx")

The second load is not free — it materialises the whole workbook — so this pattern helps most when the writing step can target a different, smaller file. Where the output must live beside 400,000 rows of detail, consider generating the summary workbook separately and leaving the detail file untouched, which is faster and keeps the source unmodified.

Common pitfalls and gotchas

A reusable streaming helper

Wrapping the mode in a context manager removes every chance of forgetting close(), and gives the rest of your code one obvious way to read a big sheet:

from contextlib import contextmanager
from openpyxl import load_workbook

@contextmanager
def streamed_rows(path, sheet, first_row=2, min_col=None, max_col=None):
    wb = load_workbook(path, read_only=True, data_only=True)
    try:
        ws = wb[sheet]
        yield ws.iter_rows(min_row=first_row, min_col=min_col, max_col=max_col, values_only=True)
    finally:
        wb.close()

with streamed_rows("large.xlsx", "Orders", max_col=6) as rows:
    highest = max((r[5] or 0) for r in rows)
print("largest unit price:", highest)

Because the generator is yielded inside the with, the workbook stays open exactly as long as the caller is iterating and is closed the moment the block ends — including when the body raises. Bounding max_col in the helper's signature also nudges every caller towards reading only the columns it needs.

Performance and scale notes

Streaming turns memory use from "proportional to the sheet" into "proportional to one row", which is what lets a 300 MB workbook run inside a small container. Time still scales with the number of cells parsed, so bound the columns with min_col and max_col and skip rows you do not need with min_row. The remaining cost is per-value Python work: keep the loop body vectorised where you can by batching rows into DataFrames, as the chunked reading pattern does.

Conclusion

read_only=True with values_only=True is the fastest way openpyxl can hand you the contents of a large sheet, and it costs you writing, random access, styles and a trustworthy max_row. For extraction and aggregation that trade is almost always worth making. Resolve column positions from the header once, bound the range you iterate, count rows as you go, and close the workbook in a finally so a folder-sized job does not run out of file handles.

Frequently asked questions

What exactly does read_only mode change? The worksheet becomes a lazy iterator over the sheet XML. Cells are lightweight read-only objects created as you pass them, nothing is cached, and the sheet cannot be modified or saved.

Why must I call wb.close()? A read-only workbook keeps the underlying zip archive open so it can keep reading. Without close(), file handles accumulate until the process runs out of them.

Why is ws.max_row sometimes wrong? The dimension comes from the sheet's declared range, which some writers omit or overstate. Count rows as you iterate, or call ws.calculate_dimension() to force a scan.

Can I combine read_only with data_only? Yes, and for harvesting numbers you usually should — data_only=True returns Excel's cached results instead of formula text.

Related

Up to the parent guide:

• Working with Large Excel Files in Python — where streaming sits among the other levers.

Related guides:

• Read a Large Excel File in Chunks with pandas — batching these streamed rows into DataFrames.
• Write Large DataFrames to Excel with write_only Mode — the same idea on the way out.
• Read a Cell Value from Excel with openpyxl — normal-mode reading, for comparison.
• Read Formula Results with openpyxl data_only — what data_only actually returns.