Pandas调用worksheet.add_table报AttributeError：'int'对象无lower属性

问题场景 #

编写函数批量将DataFrame导出到同一Excel工作簿，执行时触发如下错误：

Traceback (most recent call last):
File "f:\...\weather_WPC.py", line 282, in < module > frame_to_excel(writer, exl = NS_parts, df=dfsp)
File "f:\...\df_to_excel.py", line 41, in frame_to_excel
worksheet.add_table(0, 0, df.shape[0] , df.shape[1] - 1, {'columns': [{'header': column} for column in df.columns], 'name': exl['table_name']})
File "C:\...\Python\Python310\lib\site-packages\xlsxwriter\worksheet.py", line 125, in cell_wrapper
return method(self, *args, **kwargs)
File "C:\...\Python\Python310\lib\site-packages\xlsxwriter\worksheet.py", line 3329, in add_table
name = header_name.lower()
AttributeError: 'int' object has no attribute 'lower'

将DataFrame通过.to_clipboard()复制后显示正常，用pd.read_clipboard()读取测试未报错。

相关函数代码：

import pandas as pd

def frame_to_excel(writer, exl, df):
    '''
    This function takes a DataFrame and exports it to an Excel file with specific formatting.
    '''

    # Write the DataFrame data to the Excel writer.
    df.to_excel(writer, sheet_name=exl['sht'], startrow=1, header=False, index=False)

    worksheet = writer.sheets[exl['sht']]
    worksheet.add_table(0, 0, df.shape[0] , df.shape[1] - 1, {'columns': [{'header': column} for column in df.columns], 'name': exl['table_name']})
    
# because of the error above, the rest of the formatting code does not execute. 

示例数据：

data = {
    "Rainday": [1, 2, 3, "...", 31, 32, 33, 34],
    "Day_O_M": [1, 2, 3, "...", 31, 1, 2, 3],
    "Month": ["Oct", None, None, None, None, "Nov", None, None],
    "Average": [0.01, 0.03, 0.08, "...", 2.85, 3.39, 3.56, 3.71],
    "Driest": [0, 0, 0, "...", 1.01, 1.2, 1.26, 1.31],
    "Wettest Actual": [0, 0, 0.3, "...", 11.93, 12.57, 12.69, 12.69],
    "Wettest": [0, 0, 0.27, "...", 10.92, 11.37, 11.43, 11.38],
    "2023": [0, 0, 0.01, "...", 0.01, 0, 0.93, 1.16],
    "2024": [0, 0, 0.03, "...", 0.77, 0, 0, 0],
    "Pct Yr-end": [100, 100, 175, "...", 27, 0, 0, 0],}

问题原因 #

错误根源是DataFrame存在整数类型的列名，xlsxwriter的add_table方法会自动对列头名称执行.lower()操作做内部处理，但整数对象没有该方法，因此抛出AttributeError。

剪贴板测试无报错的原因：to_clipboard()会将所有列名转为字符串格式输出，read_clipboard()读入后列名全为字符串，自然不会触发类型错误。

解决方法 #

1. 强制转换列名为字符串 #

在写入Excel前，将DataFrame的所有列名转为字符串类型，确保add_table处理时不会遇到非字符串列名：

df.columns = df.columns.astype(str)

2. 修改后的完整函数 #

import pandas as pd

def frame_to_excel(writer, exl, df):
    '''
    将DataFrame导出到Excel并添加格式化表格
    '''
    # 转换所有列名为字符串，避免类型错误
    df.columns = df.columns.astype(str)
    
    # 写入数据到Excel工作表
    df.to_excel(writer, sheet_name=exl['sht'], startrow=1, header=False, index=False)

    worksheet = writer.sheets[exl['sht']]
    # 添加Excel表格
    worksheet.add_table(
        0, 0, df.shape[0], df.shape[1]-1,
        {
            'columns': [{'header': column} for column in df.columns],
            'name': exl['table_name']
        }
    )

3. 额外检查项 #

确认exl['table_name']是合法的Excel表格名称：不能包含\ / ? * : [ ]等特殊字符，长度不超过31个字符，且不能以数字开头（虽然xlsxwriter有一定兼容性，但尽量遵循规范）。

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