openpyxl IllegalCharacterError: cannot be used in worksheets (tested)

openpyxl.utils.exceptions.IllegalCharacterError: ... cannot be used in worksheets means a string contains an ASCII control character (NUL, bell, ESC and others below space, except tab, newline and carriage return). Remove them with openpyxl's own ILLEGAL_CHARACTERS_RE before writing.

1. Which characters trigger it

from openpyxl import Workbook
wb = Workbook(); ws = wb.active
for value in ["ok", "tab\tand newline\n are fine", "bell \x07 char", "NUL \x00 byte", "ESC \x1b[31m colour"]:
    try:
        ws.append([value])
        print(f"{value!r}: ok")
    except Exception as e:
        print(f"{value!r}: {type(e).__module__}.{type(e).__name__}: {str(e)!r}")

Output:

'ok': ok
'tab\tand newline\n are fine': ok
'bell \x07 char': openpyxl.utils.exceptions.IllegalCharacterError: 'bell \x07 char cannot be used in worksheets.'
'NUL \x00 byte': openpyxl.utils.exceptions.IllegalCharacterError: 'NUL \x00 byte cannot be used in worksheets.'
'ESC \x1b[31m colour': openpyxl.utils.exceptions.IllegalCharacterError: 'ESC \x1b[31m colour cannot be used in worksheets.'

The error is raised when the value is assigned to a cell (append, cell.value = ...), not at save(). Tab, newline and carriage return are allowed; other ASCII control characters are not, because the XML format inside .xlsx cannot store them. Typical sources: terminal colour codes (\x1b[31m) in log text, NUL bytes from binary or badly decoded data, and form feeds or bells from scraped or PDF-extracted text. The real message contains the raw character, which is why it often looks like an ordinary string in your terminal.

2. Strip them with openpyxl's own regex

from openpyxl.cell.cell import ILLEGAL_CHARACTERS_RE
print(ILLEGAL_CHARACTERS_RE.pattern)
def clean(v):
    return ILLEGAL_CHARACTERS_RE.sub("", v) if isinstance(v, str) else v
from openpyxl import Workbook, load_workbook
wb = Workbook(); ws = wb.active
ws.append([clean("bell \x07 char"), clean("ESC \x1b[31m colour"), 42])
wb.save("/tmp/clean.xlsx")
print([c.value for c in load_workbook("/tmp/clean.xlsx").active[1]])

Output:

[\000-\010]|[\013-\014]|[\016-\037]
['bell  char', 'ESC [31m colour', 42]

ILLEGAL_CHARACTERS_RE covers octal 000-010, 013-014 and 016-037: everything below space except tab (011), newline (012) and carriage return (015). Using it means you remove exactly what openpyxl rejects, nothing more. Replace with "", or with a visible marker such as "?" if you need to see where data was lost.

3. pandas to_excel raises the same error

import pandas as pd
df = pd.DataFrame({"log": ["fine", "ESC \x1b[0m reset", "NUL \x00"], "n": [1, 2, 3]})
try:
    df.to_excel("/tmp/out.xlsx", index=False)
except Exception as e:
    print(f"{type(e).__name__}: {str(e)!r}")
from openpyxl.cell.cell import ILLEGAL_CHARACTERS_RE
clean = df.map(lambda v: ILLEGAL_CHARACTERS_RE.sub("", v) if isinstance(v, str) else v)
clean.to_excel("/tmp/out.xlsx", index=False)
print(pd.read_excel("/tmp/out.xlsx").to_dict("records"))

Output:

IllegalCharacterError: 'ESC \x1b[0m reset cannot be used in worksheets.'
[{'log': 'fine', 'n': 1}, {'log': 'ESC [0m reset', 'n': 2}, {'log': 'NUL ', 'n': 3}]

DataFrame.to_excel() writes through openpyxl, so one bad cell anywhere fails the whole export. Clean every string cell with DataFrame.map before exporting (applymap on pandas older than 2.1). Non-string cells are left alone.

Better: fix it at the source

• Logs: strip ANSI escape sequences (the whole \x1b[...m, not just \x1b, or you are left with [31m as in block 2).
• NUL bytes usually mean the text was decoded with the wrong encoding (for example UTF-16 read as UTF-8); fix the decode instead of deleting NULs.
• If you need the raw data intact, write CSV instead: CSV has no such restriction.

Related: openpyxl InvalidFileException · Missing optional dependency openpyxl.

Written with AI assistance. Measured on 2026-10-23 with openpyxl 3.1.5, pandas 3.1.0 and Python 3.15.1 (python:3-slim in Docker). Each block was run verbatim and the output pasted unedited (error messages printed with repr so the control characters are visible). PlainTables is our extension; Python and openpyxl are free.