Making Excel Exports Open Cleanly Every Time on Any PC

"We opened it and Excel said the file is corrupt" is among the most common messages a report owner receives, and the fix is nearly always a few characters or one setting. A good export opens without a prompt on every machine, shows accents correctly, puts values in the right columns and leaves types alone. This post lists the causes of the usual failures, for XLSX and for CSV, and the habits that prevent them. It is a spoke of sending database results to Excel.

XLSX: why Excel offers to repair a file

Excel validates the package when it opens it. When something violates the format, it says it found unreadable content and offers to repair the file, which often deletes part of it. The causes in generated files are a short list.

Illegal characters. XML 1.0 forbids most control characters (code points 0 to 8, 11, 12 and 14 to 31). Text from a database or a web form can contain them: a stray backspace, a form feed from a pasted PDF, a null byte. openpyxl raises an IllegalCharacterError when you assign such a string; other writers may write it and produce a broken file. Strip them before writing:

import re

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

def clean(value):
    return ILLEGAL.sub("", value) if isinstance(value, str) else value

df = df.map(clean)        # pandas 2.1 and later; use applymap in older versions

Tabs (code 9), line feeds (10) and carriage returns (13) are legal and stay. Characters outside the basic plane, such as emoji, are legal in XML and display depending on the font.

Invalid sheet names. A name must be 1 to 31 characters, unique ignoring case, and free of : \ / ? * [ ], and must not start or end with an apostrophe. A reserved name, History, is not allowed. Writers either raise an error or write the broken name; sanitize as in building a multi-sheet Excel report from SQL queries.

Cells that are too long. A cell holds at most 32,767 characters. A larger string is truncated or corrupts the file, depending on the library. Check long text columns:

too_long = df.map(lambda v: isinstance(v, str) and len(v) > 32767).any()
print(too_long[too_long].index.tolist())

If any column is listed, truncate with a visible marker or move the text elsewhere.

Timezone-aware datetimes. Excel stores no zone. pandas raises a ValueError for aware datetimes in to_excel, and openpyxl raises one too. Convert to the reader's zone and drop the marker (exporting a query to Excel from PostgreSQL with Python).

NaN and infinity. A float NaN is not a legal Excel number. pandas writes NaN in a DataFrame as an empty cell by default, but a direct write_number(float("nan")) in XlsxWriter raises an error unless the workbook is created with nan_inf_to_errors set, which writes #NUM! and similar error cells. Replace them yourself: empty for missing values, a text marker only in text columns.

A writer that was never closed. XlsxWriter writes the file when you call close() (or leave a with block), and a file written before that is incomplete. A script that crashes between creating the workbook and closing it leaves a damaged file on disk, and a later job may deliver it. Write to a temporary name and rename on success:

import os

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

os.replace is atomic on the same filesystem, so a reader never sees a half-written file.

Too many styles or formats. Excel limits the number of distinct cell formats (about 64,000). A loop that creates a new format per cell can hit it. Create formats once and reuse them.

XLSX: values that look wrong but are not corrupt

The file opens, and the content disappoints. Mostly these are type problems covered elsewhere in the cluster, and a short list helps you recognize them.

• A date shows as a number: no date format on the cell (why an exported date shows as a number).

• An id lost its zeros or shows as 1.23E+15: written as a number; write text.

• #### in a cell: the column is too narrow for the value.

• A text starting with = calculates: the writer treated it as a formula; write it as a string.

• A percentage is 100 times too large: the cell holds 25 with a percent format instead of 0.25.

CSV: the three things that break it in Excel

CSV is plain text, so the file cannot be corrupt in the XLSX sense. It can still open wrong, for reasons that have to do with how Excel guesses.

Encoding. Excel on Windows opens a CSV with no byte order mark using the system's legacy code page, so UTF-8 accents are garbled (é instead of é). Adding the UTF-8 byte order mark at the start of the file tells Excel to read UTF-8. In Python:

df.to_csv("report.csv", index=False, encoding="utf-8-sig")

The utf-8-sig codec writes the BOM. Programs that do not expect a BOM may see three odd bytes at the start of the first header, so tell program consumers, or serve them a BOM-free copy (UTF-8 BOM in CSV files).

Separator. In locales where the decimal mark is a comma, Excel's list separator is a semicolon, and a comma-separated file opens in a single column (Excel shows a CSV in one column). If your readers are in such a locale, write semicolons (sep=";") and decimal commas (decimal=","), or give them an XLSX, which has no separator at all. Mixed audiences are the reason XLSX is the better format for people.

Type guessing. Opening a CSV by double click makes Excel guess every column. It strips zeros, converts gene names and date-like codes, and rounds long numbers (Excel drops leading zeros when opening CSV). The only reliable protections are not to use CSV for people, or to tell them to import via Data, From Text/CSV with the column set to text.

Quote fields that contain the separator, quotes or line breaks, which csv and pandas do by default (QUOTE_MINIMAL), and use the doubled quote to escape a quote (how CSV quoting works). Line endings: Windows tools tolerate \n; the CSV standard uses \r\n, which Python's csv module writes by default.

Test like the reader

The most useful test is to open the file the way a reader will. Build a fixture row that contains every awkward value you can think of:

If the export survives that row, it will survive the data. Run the fixture through the export in a test, read the file back with openpyxl, and assert the key properties. Then open it by hand once in Excel, and in LibreOffice or Google Sheets, which have different tolerances: LibreOffice often opens files that Excel rejects, so a clean open there is not proof, but a failure there is a warning.

Keep an eye on file size and on memory. A workbook near the sheet limit, or with millions of cells, may open on your machine and fail on a colleague's with a 32-bit Excel.

When no export setting will make the file open cleanly

The reader's software is old or unusual. Excel 2003 cannot open XLSX without a compatibility pack, and some viewers handle only a subset of the format. Ask what they use before debugging your file.

The data is the problem. Text with control characters or unpaired surrogates may come from a corrupted source. Cleaning on export hides the problem, so also report it.

The file passes through a system that rewrites it. Mail gateways, virus scanners and document management tools sometimes re-save attachments and alter them. Compare the hash before and after.

The reader opens CSV by double click. You can improve it with a BOM and a separator, and you cannot make Excel stop guessing. Change the format you send.

The limits are listed in Microsoft's Excel specifications and limits, and the XML character rules in the XML 1.0 specification. To look at a small extract as JSON, use the CSV to JSON converter.