Fix openpyxl Read-Only Mode Error

Calling load_workbook(path, read_only=True) and then trying to write a cell raises an AttributeError or silently does nothing — the workbook is in an optimised read-only streaming mode that strips the write API entirely. The same problem surfaces when you try to access .active on a read-only workbook, iterate cells after calling wb.close(), or use .value on rows after the worksheet iterator is exhausted.

This page covers the root cause, the minimal diagnostic, and the correct fix for each variant.

Root Cause

openpyxl ships three workbook modes:

Mode	`load_workbook` kwargs	Can read?	Can write?	Memory use
Normal	(none)	Yes	Yes	Full DOM in RAM
Read-only	`read_only=True`	Yes	No	Streaming — low
Write-only	`write_only=True` on `Workbook()`	No	Yes	Streaming — low

read_only=True loads the XML as a lazy stream. Sheets are ReadOnlyWorksheet objects, not Worksheet objects. ReadOnlyWorksheet has no append, no cell(...).value =, no freeze_panes, no column_dimensions — writing to any of these raises AttributeError or is silently ignored depending on the openpyxl version.

Similarly, write_only=True (on a new Workbook()) produces WriteOnlyWorksheet objects that only accept append() — random-access reads are not supported.

Minimal Reproducible Diagnostic

Run this to confirm you are hitting the read-only mode restriction:

# pip install openpyxl
from pathlib import Path
from openpyxl import load_workbook

PATH = Path("workbook.xlsx")   # replace with your file

try:
    wb = load_workbook(PATH, read_only=True)
    ws = wb.active
    print(type(ws))            # → <class 'openpyxl.worksheet._read_only.ReadOnlyWorksheet'>
    ws["A1"] = "test"          # triggers the error
except AttributeError as exc:
    print(f"Caught: {exc}")
    # → 'ReadOnlyWorksheet' object has no attribute '__setitem__'
finally:
    wb.close()

If the output matches, the fix is to remove read_only=True.

Fix — Open Without `read_only` for Editing

The correct fix for any workflow that writes to an existing workbook is to open it in normal mode:

# pip install openpyxl
from pathlib import Path
from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill

PATH   = Path("workbook.xlsx")
OUTPUT = Path("workbook_updated.xlsx")   # save to a new path to preserve original

try:
    wb = load_workbook(PATH)             # no read_only kwarg — full read/write mode
    ws = wb.active

    # Now all write operations work
    ws["A1"] = "Report Title"                              # set cell value
    ws["A1"].font = Font(bold=True, size=14)              # apply style
    ws.freeze_panes = "A2"                                # freeze header row
    ws.column_dimensions["A"].width = 24                  # resize column

    wb.save(OUTPUT)
except FileNotFoundError as exc:
    raise SystemExit(f"File not found: {exc}")
except PermissionError:
    raise SystemExit(f"Close {OUTPUT} in Excel before saving")

print(f"Saved: {OUTPUT}")

The only change from the broken version: load_workbook(PATH) instead of load_workbook(PATH, read_only=True).

Variant A — `.active` Returns `None` on a Read-Only Workbook

A read-only workbook does have .active, but it returns None if the workbook contains only one sheet and that sheet's sheetState is not visible, or if active_sheet_index is not set in the XML. The safe pattern is to access the sheet by name:

# pip install openpyxl
from pathlib import Path
from openpyxl import load_workbook

PATH = Path("workbook.xlsx")

wb = load_workbook(PATH, read_only=True)

# Fragile — may return None
# ws = wb.active

# Safe — access by name
sheet_names = wb.sheetnames
print(sheet_names)           # inspect available sheets first
ws = wb[sheet_names[0]]      # or wb["Sheet1"] if you know the name

for row in ws.iter_rows(values_only=True):
    print(row)

wb.close()   # always close read-only workbooks to release the file handle

Variant B — Accessing Cell Values After `wb.close()`

In read_only mode, rows are yielded from a lazy XML stream. Once you call wb.close(), the stream is closed and any references to cells or rows become unusable. Materialise the data before closing:

# pip install openpyxl
from pathlib import Path
from openpyxl import load_workbook

PATH = Path("large_file.xlsx")

wb = load_workbook(PATH, read_only=True)
ws = wb.active

# Collect all rows into memory BEFORE closing
data = [row for row in ws.iter_rows(values_only=True)]

wb.close()   # safe to close now — data is already in the list

# Work with the data after close
headers = data[0]
for row in data[1:]:
    record = dict(zip(headers, row))
    # process record...

print(f"Loaded {len(data) - 1} records")

If the file is so large that materialising it is not feasible, process rows inside the with block or before calling wb.close().

Variant C — `write_only` Mode Raises `NotImplementedError` on Read

Workbook(write_only=True) is for streaming large files to a new path — you cannot read back cells you already wrote:

# pip install openpyxl
from openpyxl import Workbook

wb = Workbook(write_only=True)
ws = wb.create_sheet("Data")

ws.append(["Name", "Revenue"])   # correct — append only
ws.append(["North", 15000])

# ws["A1"].value  →  raises NotImplementedError in write_only mode

wb.save("output.xlsx")
wb.close()

If you need to read back values after writing, use normal mode (Workbook()) or load the saved file afterwards with load_workbook("output.xlsx").

Variant D — "I/O operation on closed file"

This ValueError appears when you call wb.save() after wb.close(), or when a context manager closes the workbook before you save:

# pip install openpyxl
from pathlib import Path
from openpyxl import load_workbook

PATH   = Path("workbook.xlsx")
OUTPUT = Path("updated.xlsx")

try:
    wb = load_workbook(PATH)
    ws = wb.active
    ws["A1"] = "updated"
    # wb.close()  ← DON'T close before saving
    wb.save(OUTPUT)    # save first
    wb.close()         # then close
except ValueError as exc:
    print(f"Caught: {exc}")   # "I/O operation on closed file"

The pattern: save, then close. If you use a with block, openpyxl does not natively support context managers on load_workbook — you must call save and close explicitly, or use pandas.ExcelWriter which handles this lifecycle automatically.

Variant E — `read_only` Workbook with `.iter_rows()` Returning Empty After Re-Iteration

The lazy row iterator in a ReadOnlyWorksheet is a one-shot generator. Once it is exhausted you cannot iterate it again — the second pass returns nothing:

# pip install openpyxl
from pathlib import Path
from openpyxl import load_workbook

PATH = Path("workbook.xlsx")

wb = load_workbook(PATH, read_only=True)
ws = wb.active

# First pass — works
for row in ws.iter_rows(values_only=True):
    pass   # process rows

# Second pass — silently empty in read_only mode
count = sum(1 for _ in ws.iter_rows(values_only=True))
print(count)   # prints 0

wb.close()

Fix: reload the workbook for the second pass, or materialise into a list on the first pass (as shown in Variant B).

Variant F — Applying openpyxl Styles When Writing with `pd.ExcelWriter`

A common mistake when using pd.ExcelWriter(path, engine="openpyxl") is applying xlsxwriter-style add_format calls — those belong to the xlsxwriter API. With the openpyxl engine, use openpyxl.styles objects instead:

# pip install pandas openpyxl
from pathlib import Path
import pandas as pd
from openpyxl.styles import Font, PatternFill, Alignment

OUTPUT = Path("output/styled.xlsx")
df = pd.DataFrame({"Region": ["North", "South"], "Revenue": [12000, 4500]})

try:
    with pd.ExcelWriter(OUTPUT, engine="openpyxl") as writer:
        df.to_excel(writer, sheet_name="Data", index=False)

        wb = writer.book
        ws = wb["Data"]

        # Style the header row
        for cell in ws[1]:
            cell.font      = Font(bold=True, color="ffffff", size=12)
            cell.fill      = PatternFill("solid", fgColor="2563eb")
            cell.alignment = Alignment(horizontal="center")

        # Number format on revenue column
        for row in ws.iter_rows(min_row=2, max_row=ws.max_row,
                                min_col=2, max_col=2):
            for cell in row:
                cell.number_format = "#,##0.00"

except Exception as exc:
    raise SystemExit(f"Export failed: {exc}")

print(f"Styled file written: {OUTPUT}")

If you need the richer format/chart API, switch to engine="xlsxwriter" and use wb.add_format(...) instead. The two engines are not interchangeable — pick one per workbook.

Common Mode Confusion Summary

Symptom	Likely cause	Correct call
`AttributeError: 'ReadOnlyWorksheet' has no attribute '__setitem__'`	`read_only=True` used for a write workflow	`load_workbook(path)`
`.active` returns `None`	read-only workbook with a non-default active sheet	Access by name: `wb["Sheet1"]`
Second `iter_rows()` loop returns nothing	One-shot generator exhausted	Materialise to `list()` on first pass
`NotImplementedError` reading a cell	`write_only=True` on a `Workbook()`	Load the saved file separately for reading
`ValueError: I/O operation on closed file`	`save()` called after `close()`	Call `save()` first, then `close()`
`add_format` raises `AttributeError`	`xlsxwriter` API called on an `openpyxl` worksheet	Use `openpyxl.styles` objects with `openpyxl` engine

Verification

After applying the fix, run the following assertion to confirm the file was written successfully:

# pip install openpyxl
from pathlib import Path
from openpyxl import load_workbook

OUTPUT = Path("workbook_updated.xlsx")

wb = load_workbook(OUTPUT, read_only=True)
ws = wb.active
first_row = next(ws.iter_rows(values_only=True))
wb.close()

assert first_row[0] == "Report Title", f"Unexpected value: {first_row[0]}"
print("Write verified successfully")

A passing assertion confirms the cell was written. If the assertion fails, check that you saved to OUTPUT and not the original PATH.

Automating Excel Report Generation — full pipeline using openpyxl and xlsxwriter for styled multi-sheet reports
Automating Monthly Sales Reports in Excel — end-to-end example that loads and fills an existing template
Reading Excel Files with Python — correct patterns for reading .xlsx files, including large-file strategies
Writing Excel Formulas and Charts with openpyxl — advanced write patterns once the workbook is open in normal mode

Part of Automating Excel Report Generation.

Fix openpyxl Read-Only Mode Error

Root Cause

Minimal Reproducible Diagnostic

Fix — Open Without read_only for Editing

Variant A — .active Returns None on a Read-Only Workbook

Variant B — Accessing Cell Values After wb.close()

Variant C — write_only Mode Raises NotImplementedError on Read