Fix ReadOnlyWorksheet Has No Attribute Errors in openpyxl

Read-only mode is the standard advice for big workbooks: load_workbook(path, read_only=True) streams rows instead of building every cell in memory, so a file that would need gigabytes as a normal workbook can be read in a few megabytes. Then the script that worked a minute ago fails with AttributeError: 'ReadOnlyWorksheet' object has no attribute 'iter_cols', or 'merged_cells', or 'column_dimensions' — or, further in, 'EmptyCell' object has no attribute 'coordinate'. Read-only worksheets are a different, much smaller class than normal worksheets: they can stream cell values and styles, and nothing else. This page lists what is missing, and shows how to rewrite each common use. It belongs to Troubleshooting Common Python Excel Errors.

Prerequisites

pip install openpyxl

stock.xlsx has a header row, seven items, a merged range, a column width, a comment, a hyperlink and frozen panes — and gaps: some rows have no location, and only one has a note.

Step 1 — Reproduce the errors

from openpyxl import load_workbook

wb = load_workbook("stock.xlsx", read_only=True)
ws = wb["Stock"]
print(type(ws).__name__, type(ws["A2"]).__name__)

attempts = {
    "iter_cols": lambda: list(ws.iter_cols()),
    "merged_cells": lambda: ws.merged_cells,
    "column_dimensions": lambda: ws.column_dimensions["A"].width,
    "cell comment": lambda: ws["A2"].comment,
    "write a cell": lambda: setattr(ws.cell(2, 3), "value", 99),
    "save": lambda: wb.save("copy.xlsx"),
}
for name, attempt in attempts.items():
    try:
        attempt()
        print(f"{name:18} works")
    except (AttributeError, TypeError) as exc:
        print(f"{name:18} {type(exc).__name__}: {exc}")

Every one fails. iter_cols, merged_cells and column_dimensions do not exist on ReadOnlyWorksheet; ReadOnlyCell has no comment (or hyperlink); assigning a value raises AttributeError: Cell is read only; and saving raises TypeError: Workbook is read-only. These are not bugs: in read-only mode openpyxl parses each sheet's XML as a stream of rows and keeps nothing else.

Step 2 — Replace column access with rows

iter_cols and columns need random access to the whole grid, which a stream cannot give. Read the rows once and transpose them in Python:

rows = list(ws.iter_rows(values_only=True))
header, *data = rows
columns = dict(zip(header, zip(*data)))
print(columns["Qty"])
print(sum(columns["Qty"]))

zip(*data) turns a list of row tuples into a tuple per column. This holds the sheet's values in memory, which defeats part of the purpose for very large sheets; when you only need a few columns, select them while streaming instead, with iter_rows(min_col=3, max_col=3, values_only=True). For column-wise analysis of a big sheet, pandas with usecols is usually simpler — see Read Specific Columns from Excel with pandas.

Step 3 — Handle EmptyCell in gaps

In read-only mode, a position with no cell in the file is returned as an EmptyCell: it has value = None but no coordinate, row or column attributes. Code that builds addresses from cells breaks on the first gap:

first_rows = list(ws.iter_rows(min_row=2, max_row=3))
print([type(c).__name__ for c in first_rows[1]])
try:
    print([c.coordinate for c in first_rows[1]])
except AttributeError as exc:
    print("AttributeError:", exc)

for r, row in enumerate(ws.iter_rows(min_row=2, max_row=3), start=2):
    print([f"{chr(64 + c)}{r}={cell.value!r}" for c, cell in enumerate(row, start=1)])

Row 3 has no location, so its second position is an EmptyCell, and asking for its coordinate fails. Track row and column numbers yourself with enumerate (starting at the min_row you asked for), or use values_only=True and ignore cell objects altogether. get_column_letter(c) from openpyxl.utils converts the column number to a letter beyond column Z.

Step 4 — Get the metadata from a normal load

Merged ranges, widths, comments and hyperlinks are worksheet-level information that read-only mode never parses. If you need them, load the workbook a second time without read_only, take what you need, and keep using the fast stream for the cell values:

meta_wb = load_workbook("stock.xlsx")
meta = meta_wb["Stock"]
info = {
    "merged": [str(r) for r in meta.merged_cells.ranges],
    "width A": meta.column_dimensions["A"].width,
    "comments": {c.coordinate: c.comment.text for row in meta.iter_rows() for c in row if c.comment},
    "links": {c.coordinate: c.hyperlink.target for row in meta.iter_rows() for c in row if c.hyperlink},
    "freeze": meta.freeze_panes,
}
print(info)

A normal load of a large file costs the memory and time you were trying to avoid, so do it only when the metadata matters, and only for the sheets that need it. For merged cells in particular, a common reason to need them is to fill their values down — Handle Merged Cells When Reading Excel with pandas shows the approach.

Step 5 — Writing means a different mode

A read-only workbook cannot be modified or saved. If the job reads a large file and writes results, combine read-only input with write-only output — the streaming counterpart for writing:

from openpyxl import Workbook

out = Workbook(write_only=True)
summary = out.create_sheet("Low stock")
summary.append(["SKU", "Qty"])
for sku, _, qty, _ in ws.iter_rows(min_row=2, values_only=True):
    if qty < 40:
        summary.append([sku, qty])
out.save("low_stock.xlsx")
wb.close()
meta_wb.close()
print(load_workbook("low_stock.xlsx").active.max_row - 1, "rows written")

Both modes stream, so memory stays flat even for files with hundreds of thousands of rows. If you need to edit an existing workbook in place, read-only mode is simply the wrong tool; use a normal load. Write-only mode is covered in Write Large DataFrames to Excel with Write-Only Mode.

Step 6 — Close the workbook

A read-only workbook keeps the file open while you iterate, so that it can stream. Call wb.close() when finished — the example above does — or the file handle stays open until the process exits, which on Windows prevents anyone from moving or overwriting the file. Recent openpyxl versions do not support using the workbook as a context manager, so try/finally around the work is the reliable pattern.

Step 7 — Check that read-only mode is worth it

It is easy to switch modes out of habit and then fight the missing features. Measure on a file of realistic size before rewriting code around the stream:

import time
import tracemalloc

big = Workbook(write_only=True)
sheet = big.create_sheet("Data")
for i in range(50_000):
    sheet.append([i, f"S-{i:05}", i % 97, i * 0.5, "A1"])
big.save("big.xlsx")

for mode in (False, True):
    tracemalloc.start()
    t0 = time.perf_counter()
    book = load_workbook("big.xlsx", read_only=mode)
    total = sum(row[2] for row in book["Data"].iter_rows(values_only=True))
    elapsed = time.perf_counter() - t0
    peak = tracemalloc.get_traced_memory()[1] / 2**20
    tracemalloc.stop()
    book.close()
    print(f"read_only={mode!s:5}  {elapsed:5.2f} s  peak {peak:6.1f} MiB  total={total}")

On this 50,000-row, five-column sheet the normal load peaked at about 95 MiB and the read-only load at about 6 MiB: the normal load builds every cell object up front, the read-only load keeps only the current row. Time did not improve — the read-only pass was a little slower in this measurement (about 5 versus 5.5–6 seconds across runs, both inflated by tracemalloc's bookkeeping) — because both modes parse the same XML, and the advice that read-only mode is "faster" really means it avoids running out of memory. The memory difference is what matters for files with millions of cells. For a few thousand rows, a normal load costs little and keeps every feature — there is no reason to give them up.

Common pitfalls

• Expecting a smaller copy of the normal API. Read-only worksheets stream values and styles; nothing else exists.
• Relying on max_row. It comes from the sheet's stored dimension, which some writers omit or get wrong; ws.reset_dimensions() makes openpyxl compute it while iterating, at the cost of a full pass.
• Using ws["A2"] in loops. Random access works but re-reads the stream each time; iterate once instead.
• Building addresses from EmptyCell. Count rows and columns yourself.
• Forgetting close(). The file stays locked until the process ends.
• Expecting a speed-up. Read-only mode saves memory, not parsing time; for raw speed on large files, the calamine engine in pandas is the bigger lever.

Conclusion

'ReadOnlyWorksheet' object has no attribute … means the code uses a feature read-only mode does not parse. Stream rows with iter_rows and transpose them instead of iter_cols, count positions yourself because gaps are EmptyCell objects without coordinates, take merged ranges, widths, comments and links from a separate normal load when you need them, write results with a write-only workbook, and close the read-only workbook when done.

Frequently asked questions

Why does openpyxl say 'ReadOnlyWorksheet' object has no attribute 'iter_cols'? Read-only worksheets stream the sheet row by row and do not support column access. Use iter_rows and transpose the rows with zip(*rows), or read only the columns you need with min_col and max_col.

Why does 'EmptyCell' object have no attribute 'coordinate'? In read-only mode, positions with no cell in the file are returned as EmptyCell objects, which only carry value = None. Track row and column numbers with enumerate instead of reading them from the cell.

How do I read merged cells or column widths in read-only mode? You cannot; read-only mode does not parse that information. Load the workbook a second time without read_only and read merged_cells.ranges and column_dimensions from that worksheet.

Can I save a workbook opened with read_only=True? No. Saving raises TypeError: Workbook is read-only. Load it normally to edit it, or write results to a new workbook, ideally created with Workbook(write_only=True) for large outputs.

Related

• Up one level: Troubleshooting Common Python Excel Errors — fixes for the errors you are most likely to meet.
• Speed Up openpyxl with Read-Only Mode — when and how to use the mode.
• Iterate Over Rows and Columns with openpyxl — iteration in normal mode.
• Fix openpyxl max_row Counting Empty Rows — when row counts look wrong.