Fix "Excel Cannot Open the File Because the File Format or Extension Is Not Valid"

Your script writes report.xlsx without complaint, but Excel will not open it:

Excel cannot open the file 'report.xlsx' because the file format or file extension
is not valid. Verify that the file has not been corrupted and that the file
extension matches the format of the file.

The message means exactly what its last sentence says. An .xlsx file is a zip archive of XML parts with a manifest that declares what kind of workbook it is. When the bytes are not a zip archive, the archive is damaged, or the manifest declares a different kind of workbook than the extension promises, Excel stops. Python produces each of those states in a handful of recognisable ways. This page, part of Troubleshooting Common Python Excel Errors, shows how to tell which one you have and how to fix the code that caused it.

Prerequisites

pip install pandas openpyxl

The examples use a set of small files, each broken in one of the ways described below, plus one good file for comparison.

Step 1 — Check the file with a script, not by eye

The fastest diagnosis is a function that walks through the same layers Excel does and says where the file fails. It needs only the standard library:

import zipfile
import re
from pathlib import Path

MACRO = "application/vnd.ms-excel.sheet.macroEnabled.main+xml"
PLAIN = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"

def check_xlsx(path):
    path = Path(path)
    head = path.read_bytes()[:8]
    if not head:
        return "empty file (0 bytes) — the writer was probably never closed"
    if not head.startswith(b"PK\x03\x04"):
        return f"not a zip archive — starts with {head!r} (CSV, HTML or text?)"
    try:
        with zipfile.ZipFile(path) as z:
            bad = z.testzip()
            names = set(z.namelist())
            manifest = z.read("[Content_Types].xml").decode() if "[Content_Types].xml" in names else ""
    except zipfile.BadZipFile as exc:
        return f"damaged zip archive: {exc} — bytes altered in transit or text mode?"
    if bad:
        return f"damaged member: {bad}"
    if "xl/workbook.xml" not in names or not manifest:
        return "zip archive, but not a workbook"
    declared = re.search(r'PartName="/xl/workbook.xml" ContentType="([^"]+)"', manifest)
    declared = declared.group(1) if declared else ""
    if declared == MACRO and path.suffix.lower() == ".xlsx":
        return "macro-enabled workbook saved with .xlsx — rename to .xlsm or drop the macros"
    if declared == PLAIN and path.suffix.lower() == ".xlsm":
        return "plain workbook saved with .xlsm — rename to .xlsx"
    return "ok"

for name in ("good.xlsx", "csv_named.xlsx", "html_named.xlsx", "empty.xlsx", "mangled.xlsx"):
    print(f"{name:16} {check_xlsx(name)}")

Each file in the output fails at a different layer, and each layer points at a different mistake in the writing code. The steps below take them in turn.

Step 2 — Text saved under an Excel name

csv_named.xlsx starts with b'region,s' and html_named.xlsx with b'<table b'. Both are text files wearing an .xlsx extension. In a script this is usually a one-word slip:

df.to_csv("report.xlsx")        # writes CSV text, whatever the name says
df.to_html("report.xlsx")       # writes an HTML table

The same state arrives from outside: a download whose server returned an HTML error or login page instead of the file, saved under the name you asked for. Fix the writer call, or keep the extension honest:

import pandas as pd

df = pd.DataFrame({"region": ["North", "South"], "sales": [410, 388]})
df.to_excel("report.xlsx", index=False)     # a real workbook
df.to_csv("report.csv", index=False)        # or a CSV with a CSV name
print(check_xlsx("report.xlsx"))

For downloads, check the response before saving: a Content-Type of text/html, or a body that does not start with PK, means the server did not send a workbook. Read an Excel File from a URL or Bytes in Python shows the checks.

Step 3 — An empty file from a writer that was never closed

empty.xlsx is zero bytes. pandas' ExcelWriter and xlsxwriter build the workbook in memory and write it only when they are closed. The file appears on disk as soon as the writer opens it, so a script that crashes, returns early or simply forgets close() leaves an empty .xlsx behind:

import os

writer = pd.ExcelWriter("forgot_to_close.xlsx", engine="openpyxl")
df.to_excel(writer, index=False)
print(os.path.getsize("forgot_to_close.xlsx"))   # 0 — nothing written yet
writer.close()
print(os.path.getsize("forgot_to_close.xlsx") > 0, check_xlsx("forgot_to_close.xlsx"))

Use a with block so the writer is closed however the code exits. If the writing can fail half-way, write to a temporary name and rename it into place only after the block completes, so a failed run never leaves a broken file where a reader expects a good one:

tmp = "report.tmp.xlsx"
with pd.ExcelWriter(tmp, engine="openpyxl") as writer:
    df.to_excel(writer, index=False)
os.replace(tmp, "report.xlsx")
print(check_xlsx("report.xlsx"))

Keep .xlsx at the end of the temporary name. pandas checks the extension even when you pass engine=, and refuses a name like report.xlsx.part with ValueError: Invalid extension for engine. os.replace is atomic on the same filesystem, so a reader sees either the old report or the complete new one, never half of each.

Step 4 — Bytes that went through text mode

mangled.xlsx starts with the right signature but the archive is damaged. That pattern — correct start, broken body — is what happens when binary data passes through code that treats it as text: a file opened with "w" instead of "wb", bytes decoded to a string and encoded back, or a newline conversion that turns every \n byte into \r\n. The setup for this page produced mangled.xlsx exactly that way.

Every step that touches workbook bytes must handle them as bytes:

import io

buffer = io.BytesIO()
df.to_excel(buffer, index=False)
data = buffer.getvalue()                 # bytes, not str

with open("from_bytes.xlsx", "wb") as fh:   # "wb", never "w"
    fh.write(data)
print(check_xlsx("from_bytes.xlsx"))

Web frameworks are the usual suspects in practice: a handler that returns the bytes as a str, or an HTTP client that reads the response with .text instead of .content. Return an Excel File from a Flask Download Endpoint shows a handler that gets this right. When a file is fine on your machine and broken after transfer, compare sizes and a checksum (hashlib.sha256(data).hexdigest()) on both ends to find the step that changed it.

Step 5 — A macro workbook saved as .xlsx

The last layer is a mismatch between what the workbook says it is and what its name says. Loading an .xlsm with keep_vba=True keeps the macro project and marks the workbook as macro-enabled; saving it as .xlsx writes macro-enabled content under a macro-free name, and Excel refuses:

from openpyxl import load_workbook

wb = load_workbook("macro_src.xlsm", keep_vba=True)
wb.save("macro_as.xlsx")
print(check_xlsx("macro_as.xlsx"))

wb = load_workbook("macro_src.xlsm", keep_vba=True)
wb.save("macro_ok.xlsm")
print(check_xlsx("macro_ok.xlsm"))

Keep the .xlsm extension when the macros should survive. If they should not, load without keep_vba and save as .xlsx; the macro project is dropped and the workbook is declared as a plain one. Work with Macro-Enabled .xlsm Files in openpyxl covers both paths.

Step 6 — Check files before they leave the job

Put the checker at the end of every job that sends or publishes a workbook, and fail the run if it reports anything but ok. A report that fails loudly at 06:00 is fixed before anyone notices; one that reaches forty inboxes broken is not. A final load with openpyxl adds a check that the XML inside is readable, which the zip-level checks cannot see:

def verify_before_sending(path):
    status = check_xlsx(path)
    if status != "ok":
        raise RuntimeError(f"{path}: {status}")
    load_workbook(path, read_only=True).close()
    return path

print(verify_before_sending("report.xlsx"))

Validate an Excel Report Before Sending It extends this with checks on the report's contents.

Common pitfalls

• Trusting the extension. It is only a name. Check the first bytes of anything you did not write yourself.
• Catching exceptions around the writer and carrying on. The file is then left empty or partial. Let the error stop the job, or write to a temporary name.
• Opening files in text mode "because it worked for CSV". Binary formats need "rb" and "wb".
• Renaming .xlsm to .xlsx to hide macros. The content still declares macros; rename changes nothing inside.

Performance notes

The checker reads the whole file once and decompresses each member for testzip(), which takes milliseconds for typical reports and about a second for a 50 MB workbook. For very large files in a hot path, skip testzip() and rely on the signature, manifest and a read-only load, which touch far less data.

Conclusion

Excel's "file format or file extension is not valid" means the bytes do not match the .xlsx name: text written by to_csv or an error page, an empty file from a writer that was never closed, an archive damaged by text-mode handling, or macro-enabled content saved with the wrong extension. Check the file layer by layer, fix the writing code that the failing layer points to, write through a with block and a temporary name, and verify every workbook before it leaves the job.

Frequently asked questions

My .xlsx file is 0 bytes. What happened? The writer was created but never closed. pandas ExcelWriter and xlsxwriter only write the workbook when they are closed, so a script that exits early or forgets close() leaves an empty file behind. Use a with block.

Can I just rename a CSV to .xlsx? No. The extension has to match the content. A CSV is plain text and an .xlsx is a zip archive of XML parts; renaming changes neither. Write a real workbook with to_excel, or keep the .csv extension.

Why does Excel reject a workbook I saved with keep_vba=True? Loading with keep_vba=True keeps the macro project and marks the workbook as macro-enabled. Saving that as .xlsx produces a file whose content says .xlsm, and Excel refuses the mismatch. Save it with an .xlsm extension, or load without keep_vba to drop the macros.

The file opens on my machine but not after it is emailed or downloaded. Why? Something between you and the recipient treated the bytes as text — a web handler returning a str, a file opened in text mode, or a mail library encoding it wrongly. Compare the file size and a checksum on both ends, and make every step handle the data as bytes.

Related

• Up one level: Troubleshooting Common Python Excel Errors — every common read and write error.
• Fix "Excel Found Unreadable Content" After Writing with Python — files Excel opens but has to repair.
• Fix BadZipFile When Reading an Excel File in Python — the same damage seen from the reading side.
• Build an Excel Workbook in Memory with BytesIO — handling workbook bytes safely.