使用Openpyxl写入Excel触发IllegalCharacterError问题求助

Hey there! Let's break down why you're hitting that IllegalCharacterError and how to fix it.

What's Causing the Error? #

The IllegalCharacterError from openpyxl pops up when you try to write characters that Excel doesn't support. These are mostly ASCII control characters (like \x00 to \x08, \x0B, \x0C, \x0E to \x1F, and \x7F). Your example code uses plain numbers, but since you mentioned working with "processed content", those hidden invalid characters are almost certainly present in your real dataset.

Solution: Clean Invalid Characters Before Writing #

The easiest fix is to filter out those illegal characters before assigning values to Excel cells. Here's how to modify your code with a simple cleaning function:

from openpyxl import Workbook
import re

def clean_excel_illegal_chars(content):
    # Regex pattern to match all Excel-prohibited control characters
    # (excluding \t, \n, \r which Excel allows)
    illegal_pattern = re.compile(r'[\x00-\x08\x0B\x0C\x0E-\x1F\x7F]')
    # Convert content to string first to handle non-string data (like numbers)
    cleaned_content = illegal_pattern.sub('', str(content))
    return cleaned_content

book = Workbook()
sheet = book.active
sheet.title = "test"

# Replace this with your actual processed content list
processed_data = [x for x in range(7729)]

for idx, item in enumerate(processed_data):
    # Clean the content before writing to Excel
    cleaned_item = clean_excel_illegal_chars(item)
    sheet.cell(row=1, column=idx+1).value = cleaned_item

book.save('test.xlsx')

Extra Quick Tip #

If you meant to write your 7729 entries as rows instead of columns (which is more common for datasets), just swap the row/column parameters: use row=idx+1 and column=1 instead of row=1 and column=idx+1.

内容的提问来源于stack exchange，提问作者EHM