使用pandas.ExcelWriter与openpyxl生成的xlsx文件UTF-8错误及损坏问题

我尝试根据数组中的名称在单个Excel文件中创建多个工作表，每个工作表复用同一列数据，但运行代码后生成的Test.xlsx文件无法被Excel打开，提示文件损坏，同时出现“is not UTF-8 encoded”错误，保存功能被禁用。

is not UTF-8 encoded

Saving disabled.

See Console for more details.

import pandas as pd
import openpyxl
wb = openpyxl.Workbook() 
wb.save(filename='Test.xlsx')

import openpyxl
# Read names from Y.csv
y_data = ['a','b','c']

# Iterate through each row in Y.csv
for i in range(len(y_data)):
    sheet_name = y_data[i]

    data = {
    'Column1': [
        2016.1, 2016.2, 2016.3, 2016.4, 2017.1, 2017.2, 2017.3, 2017.4,
        2018.1, 2018.2, 2018.3, 2018.4, 2019.1, 2019.2, 2019.3, 2019.4,
        2020.1, 2020.2, 2020.3, 2020.4, 2021.1, 2021.2, 2021.3, 2021.4,
        2022.1, 2022.2, 2022.3, 2022.4
    ]
    }
    df = pd.DataFrame(data)
    print(df)
    
    # Write the sheet to the Excel file
    with pd.ExcelWriter('Test.xlsx', mode='a',engine='openpyxl') as writer:
        df.to_excel(writer, sheet_name=y_data[i], index=False,encoding='utf8')

print(f"Created {len(y_data)} spreadsheets in output.xlsx.")

问题原因 #

1. 重复打开ExcelWriter：循环内每次创建新的ExcelWriter实例追加写入，会导致文件结构损坏，openpyxl无法正确处理多次追加的文件句柄。
2. 无效的encoding参数：to_excel的encoding参数仅对旧版xlwt引擎有效，使用openpyxl引擎时该参数会引发编码冲突，导致文件无法识别。
3. 冗余操作与文件名混淆：重复导入openpyxl无意义；最后打印的文件名是output.xlsx，与实际生成的Test.xlsx不一致。

修复后的代码 #

import pandas as pd
from openpyxl import Workbook

# 初始化Excel文件
wb = Workbook()
wb.save('Test.xlsx')

# 工作表名称列表
y_data = ['a', 'b', 'c']

# 准备复用的数据（仅创建一次）
data = {
    'Column1': [
        2016.1, 2016.2, 2016.3, 2016.4, 2017.1, 2017.2, 2017.3, 2017.4,
        2018.1, 2018.2, 2018.3, 2018.4, 2019.1, 2019.2, 2019.3, 2019.4,
        2020.1, 2020.2, 2020.3, 2020.4, 2021.1, 2021.2, 2021.3, 2021.4,
        2022.1, 2022.2, 2022.3, 2022.4
    ]
}
df = pd.DataFrame(data)

# 仅打开一次ExcelWriter，批量写入所有工作表
with pd.ExcelWriter('Test.xlsx', mode='a', engine='openpyxl', if_sheet_exists='replace') as writer:
    for sheet_name in y_data:
        df.to_excel(writer, sheet_name=sheet_name, index=False)
        print(f"已写入工作表: {sheet_name}")

print(f"已在Test.xlsx中创建{len(y_data)}个工作表。")

关键修改说明 #

• 将数据初始化移到循环外，避免重复创建DataFrame，提升效率。
• 仅创建一次ExcelWriter实例，在循环内依次写入所有工作表，确保文件结构完整。
• 移除无效的encoding='utf8'参数，消除编码冲突。
• 添加if_sheet_exists='replace'参数，防止同名工作表引发错误（可根据需求调整为'new'）。
• 修正打印的文件名，保持与实际生成文件一致。

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