使用Pandas与XlsxWriter生成Excel文件报错求助

Let’s work through this Excel repair error issue— I’ve dealt with similar XlsxWriter workbook corruption problems before, so let’s break down actionable fixes:

1. Clean Invalid Characters in Your Data #

Excel’s underlying XML structure gets easily tripped up by hidden control characters, unescaped XML symbols (like &, <, >), or overly long strings. Try sanitizing your data first:

import re
import pandas as pd

def clean_string(s):
    if isinstance(s, str):
        # Escape XML special characters to avoid breaking the file structure
        s = s.replace('&', '&amp;').replace('<', '&lt;').replace('>', '&gt;')
        # Remove non-printable control characters (like \x00-\x1F)
        return re.sub(r'[\x00-\x1F\x7F]', '', s)
    return s

# Apply cleaning logic to all cells in your DataFrame
df = df.applymap(clean_string)

2. Ensure Proper Workbook Closure #

A super common culprit is forgetting to properly close the workbook, or attempting to write to it after closing. Double-check your code ends with this critical step:

workbook = xlsxwriter.Workbook(WORKBOOK_NAME, {'strings_to_formulas': False})

# Add worksheets, write data, apply formatting... all your workbook operations here

workbook.close()  # This finalizes the XML structure— don't skip it!

Never modify the workbook after calling close()— that will corrupt the file instantly.

3. Avoid Duplicate Worksheet Names #

Excel doesn’t allow duplicate sheet names (even case-insensitive duplicates like "Sheet1" and "sheet1"). If you’re generating sheets dynamically, add a check to rename duplicates:

used_sheet_names = set()
for idx, sheet_data in enumerate(your_sheet_data_list):
    base_name = f"Sheet{idx+1}"
    sheet_name = base_name
    counter = 1
    while sheet_name in used_sheet_names:
        sheet_name = f"{base_name}_{counter}"
        counter += 1
    used_sheet_names.add(sheet_name)
    worksheet = workbook.add_worksheet(sheet_name)
    # Write data to the worksheet...

4. Check Version Compatibility #

Mismatched versions of Pandas and XlsxWriter often cause XML generation bugs. Try upgrading both to their latest stable versions, or roll back to a known compatible pair:

# Upgrade to latest stable versions
pip install --upgrade pandas xlsxwriter

# Or roll back to a proven stable combination (example versions)
pip install pandas==1.5.3 xlsxwriter==3.0.9

5. Isolate Complex Elements #

If you’re using conditional formatting, charts, or pivot tables, these advanced features can introduce XML errors. Test by creating a minimal workbook with just raw data (no formatting or extras). If that opens without issues, add back elements one by one to find the problematic feature.

Bonus: Test with a Different Engine #

To rule out data-specific issues, try exporting your data using openpyxl instead:

df.to_excel(WORKBOOK_NAME, engine='openpyxl')

If this file opens without errors, the problem is likely tied to XlsxWriter’s handling of your specific formatting or data patterns.

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