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:

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() — the chunked, low-memory approaches in Reading Excel Files with Python apply directly here.

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's 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

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.

Frequently Asked Questions

Why does openpyxl silently ignore my writes instead of raising an error?

The behaviour depends on the version and the operation. Assigning to ws["A1"] on a ReadOnlyWorksheet raises AttributeError because __setitem__ is not implemented, but some attribute-style writes (for example setting column_dimensions) hit a no-op property and are dropped without an exception. Never rely on getting an error — if you intend to write, open the workbook without read_only=True and confirm type(ws) is a plain Worksheet.

Is read-only mode actually faster for large files?

Yes, substantially, but only for pure reads. read_only=True parses the sheet XML as a lazy stream instead of building the full cell DOM in memory, so a multi-hundred-MB workbook that would exhaust RAM in normal mode iterates row-by-row with a flat memory profile. The trade-off is no random access and no write API. For read-heavy jobs that also need low memory, this is the correct mode; for anything that edits cells, it is not.

Can I convert a read-only workbook to a writable one without reloading?

No. The mode is fixed at load_workbook time and there is no in-place upgrade — a ReadOnlyWorksheet never gains the write methods. Call load_workbook(path) again without the read_only flag to get a writable Worksheet. If you already materialised the rows into a list, you can instead build a fresh Workbook() and append() the data rather than re-reading the file.

Does keep_vba=True work together with read_only=True?

You can pass both, but macro round-tripping is only meaningful when you save, and read-only workbooks cannot be saved. Use keep_vba=True with normal mode (load_workbook(path, keep_vba=True)) when you need to preserve an .xlsm file's VBA project through an edit-and-save cycle.

Related

• 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.