Python Excel AutomationPractical guides for developers
Start learning
Guide
Getting Started With Python Excel AutomationDeep dive

Fix "Excel Found Unreadable Content" After Writing with Python

Your script saved a workbook and Excel offers to repair it. The rules Excel enforces — sheet names, table names, defined names, timezones — and how to satisfy them.

A script finishes without an error, writes report.xlsx, and the recipient sees: "We found a problem with some content in report.xlsx. Do you want us to try to recover as much as we can?" Nothing in Python reported a fault, because openpyxl and xlsxwriter validate the XML they produce, not the extra application-level rules Excel enforces on top of it. This guide lists those rules, shows the code that breaks each one, and ends with a pre-flight check that keeps a repair prompt from ever reaching a stakeholder. It belongs to Troubleshooting Common Python Excel Errors.

Two layers of validation, and the gap between them Python libraries check that the XML is well formed, while Excel additionally checks names, references and value types; anything that passes the first check but fails the second produces a repair prompt. What each layer actually checks openpyxl / xlsxwriter well-formed XML valid cell coordinates legal characters in text raises if broken the gap names, references, duplicate tables, unsupported values Excel on open checks every name resolves every reference rewrites what it cannot shows the repair prompt Everything in the middle box writes cleanly from Python and fails in Excel

Prerequisites

Bash
pip install openpyxl xlsxwriter pandas

The checks at the end use only the standard library, so they run anywhere the report job runs.

Rule 1: sheet names

Excel accepts at most 31 characters and forbids \ / ? * [ ] :. A sheet name built from data — a customer, a region, a report title — breaks both rules eventually. openpyxl truncates silently in some versions and writes the name as given in others; xlsxwriter raises. Sanitise before creating the sheet:

Python
import re

INVALID = re.compile(r"[\\/?*\[\]:]")

def safe_sheet_name(name: str, used: set[str] | None = None) -> str:
    """A name Excel accepts, unique within the workbook."""
    cleaned = INVALID.sub("-", " ".join(str(name).split()))[:31] or "Sheet"
    if used is None:
        return cleaned
    candidate, n = cleaned, 1
    while candidate.casefold() in used:
        suffix = f"_{n}"
        candidate = cleaned[: 31 - len(suffix)] + suffix
        n += 1
    used.add(candidate.casefold())
    return candidate

Uniqueness matters as much as legality: two sheets differing only in case are legal in the file format and illegal in Excel.

Rule 2: table names and ranges

Excel tables are the most common cause of a repair prompt in generated reports, because two rules apply at once. Table names must be unique across the whole workbook — not per sheet — and must not look like a cell reference. A table's range must also contain a header row plus at least one data row that actually exists.

Python
"""One table per sheet, each with a unique, legal name."""
from openpyxl import Workbook
from openpyxl.worksheet.table import Table, TableStyleInfo

wb = Workbook()
wb.remove(wb.active)
rows = {"North": [("Widget", 120)], "South": [("Widget", 90)]}

for i, (region, data) in enumerate(rows.items(), start=1):
    ws = wb.create_sheet(region)
    ws.append(["Product", "Units"])
    for row in data:
        ws.append(list(row))
    table = Table(displayName=f"tbl_{region}_{i}", ref=f"A1:B{ws.max_row}")
    table.tableStyleInfo = TableStyleInfo(name="TableStyleMedium9", showRowStripes=True)
    ws.add_table(table)

wb.save("regions.xlsx")

Three details make that safe: the name is prefixed so it can never resemble a cell address like B2, the counter guarantees uniqueness across sheets, and ref is computed from ws.max_row so it always covers real data. Writing a table over an empty range is the version of this bug that ships most often — an empty group produces A1:B1, a header with no rows, which Excel rejects. Guard with if ws.max_row > 1: before adding the table. More on tables in Create an Excel table with Python.

Rule 3: defined names and stale references

A defined name pointing at a deleted or renamed sheet leaves #REF! inside the workbook, and Excel repairs the file to remove it. This happens most often when a template is filled and sheets are then dropped:

Python
from openpyxl import load_workbook

wb = load_workbook("template.xlsx")
del wb["Scratch"]                     # a sheet other names may reference

stale = [name for name, dn in wb.defined_names.items() if "#REF" in str(dn.value)
         or "Scratch" in str(dn.value)]
for name in stale:
    del wb.defined_names[name]
    print("removed stale name:", name)

wb.save("filled.xlsx")

The same applies to charts and conditional-formatting rules whose ranges point at removed sheets — see Keep charts and images when filling an Excel template.

The five rules a generated workbook has to satisfy Sheet names, table names, defined names, cell values and formula strings each have an Excel-level rule that Python libraries do not enforce, and each has a small guard that prevents it. What Excel rejects The guard sheet name over 31 chars or containing / \\ ? * [ ] sanitise and truncate on creation two tables sharing a name, or a header-only range prefix and count; require max_row > 1 defined name pointing at a deleted sheet drop names containing #REF before saving timezone-aware datetime or NaN in a cell strip tzinfo; write None instead of NaN Each guard is two lines, and each prevents a prompt the recipient would see first

Rule 4: values Excel cannot store

Three value types cause trouble. A timezone-aware datetime has no representation in the file format and openpyxl raises on it. A float NaN writes as the text nan, which is not an error but poisons every downstream calculation. And a string beginning with = is stored as a formula, so a product code like =X12 becomes a broken formula.

Python
"""Coerce a DataFrame into values a workbook can hold."""
import math

import pandas as pd

def excel_safe(value):
    if isinstance(value, pd.Timestamp) or hasattr(value, "tzinfo"):
        value = value.tz_localize(None) if getattr(value, "tzinfo", None) else value
        return value.to_pydatetime() if isinstance(value, pd.Timestamp) else value
    if isinstance(value, float) and math.isnan(value):
        return None
    if isinstance(value, str) and value.startswith(("=", "+", "-", "@")):
        return "'" + value          # leading apostrophe forces text
    return value

Timezone handling in spreadsheet data is a topic of its own — Handle timezones in Excel timestamps with Python covers what to store instead of an offset.

Rule 5: control characters in text

XML forbids most control characters, and openpyxl raises IllegalCharacterError when it meets one. Database exports and scraped text carry them regularly — a \x00 from a fixed-width field, a vertical tab from a pasted address:

Python
import re

CONTROL = re.compile(r"[\x00-\x08\x0b\x0c\x0e-\x1f]")

def clean_text(value):
    return CONTROL.sub("", value) if isinstance(value, str) else value

Apply it at the boundary where data enters the report, not at the cell level, so the cleaning happens once per value rather than once per write.

A pre-flight check before the file leaves your script

Rather than trusting each guard individually, assert the invariants on the finished workbook. This runs in well under a second and catches everything above:

Python
"""Refuse to ship a workbook Excel would offer to repair."""
import re

from openpyxl import load_workbook

INVALID = re.compile(r"[\\/?*\[\]:]")

def preflight(path: str) -> None:
    wb = load_workbook(path)
    problems, table_names = [], set()

    for ws in wb.worksheets:
        if len(ws.title) > 31 or INVALID.search(ws.title):
            problems.append(f"illegal sheet name: {ws.title!r}")
        for name, table in getattr(ws, "tables", {}).items():
            key = name.casefold()
            if key in table_names:
                problems.append(f"duplicate table name: {name}")
            table_names.add(key)
            if table.ref.split(":")[0][1:] == table.ref.split(":")[1][1:]:
                problems.append(f"table {name} has no data rows ({table.ref})")

    for name, dn in wb.defined_names.items():
        if "#REF" in str(dn.value):
            problems.append(f"stale defined name: {name}")

    if problems:
        raise ValueError("workbook would prompt a repair:\n  " + "\n  ".join(problems))

preflight("report.xlsx")
Where the pre-flight check sits in a report job The finished workbook is validated between the write step and the delivery step, so a file that would prompt a repair fails the job instead of reaching the recipient. Validate between writing and delivering build workbook preflight() clean problems deliver fail the job inbox alert, not send

Run it as the last step of the report job, before delivery. A failure there costs a rerun; a failure in the recipient's inbox costs their confidence in the report. Wiring it into the delivery step is covered in Validate an Excel report before sending it.

Common pitfalls and gotchas

  • Editing a file Excel is holding open. Save to a temporary name and replace, or the write may land half-complete.
  • Copying a worksheet between workbooks. openpyxl's copy_worksheet works within one workbook only; a cross-workbook copy carries broken style references and prompts a repair.
  • Images with no anchor, or an anchor beyond the sheet's used range, drop out during the repair.
  • A chart referencing a sheet by an old name. Rename before you create charts, never after.
  • Assuming the prompt is cosmetic. Excel silently deletes the offending object during repair, so a "recovered" report can be missing a whole table.

Performance and scale notes

Every guard here is cheap relative to writing the file: name checks are string operations, and the pre-flight reload of a typical report costs well under a second. The exception is very large workbooks, where load_workbook for the pre-flight is genuinely expensive — use read_only=True for the sheet-name and defined-name checks (table objects are unavailable in that mode, so validate table names at construction time instead). For the write side, xlsxwriter's constant_memory mode changes the trade-off further; see Write a million rows to Excel with xlsxwriter constant memory.

Conclusion

The repair prompt lives in the gap between "valid XML" and "valid Excel". Five rules cover almost every occurrence: legal and unique sheet names, unique table names over ranges that hold data, defined names without stale references, values the format can store, and text without control characters. Enforce them where the data enters, then assert them on the finished file, and the workbook your job delivers opens cleanly every time.

Frequently asked questions

Why does Python not raise an error if the file is invalid? openpyxl and xlsxwriter validate the XML they generate, not Excel's additional application-level rules. A duplicate table name or a defined name pointing at a deleted sheet is well-formed XML, so it writes cleanly and only Excel objects.

How do I see what Excel actually removed? Let Excel repair the file, then save the repaired copy under a new name and unzip both. Diffing the two sets of XML parts shows exactly which part was rewritten or dropped.

Can a timezone-aware datetime really break a workbook? It raises an error in openpyxl rather than producing a repair prompt, because the file format has no way to store an offset. Convert to naive local time or UTC before writing, and put the zone in a separate column or the header.

Does the repair prompt mean data was lost? Usually only the offending object — a table definition, a chart, a defined name. Cell values normally survive. Never ship a file that prompts, though: recipients read it as a sign the report is untrustworthy.

Which is stricter, openpyxl or xlsxwriter? xlsxwriter validates more aggressively at write time and will raise on several things openpyxl allows, such as an over-long sheet name. Neither implements every Excel rule, so a pre-flight check of your own is still worth having.