尝试设置DataFrame导出Excel表头自动换行触发AttributeError错误

我从Inv_report2中选取指定列生成Final_Inv DataFrame，尝试用pd.ExcelWriter导出到Excel并设置表头自动换行，执行代码如下：

Final_Inv = Inv_report2[["Product ID","Product","Net Activations","Books(at warehouse)","Books(Received by Retailer)","In Transit","Activated","Lost/Stolen (After Activation)","Lost/Stolen(Before Activation)","Total stock at Retailer","Activations(Books)","Stock in Network(Weeks)","Sell Through(%)","Stock at warehouse(Week)","Game Start Date"]]


with pd.ExcelWriter('K:\SnW Project\Inventory Report\Output Excels\Output.xlsx') as writer:
    Final_Inv.style.set_properties(**{'text-align': 'center'}).to_excel(writer,sheet_name='Inv_report',index = 0,startrow=0, startcol=0)
    workbook  = writer.book
    worksheet = writer.sheets['Inv_report']
    format.set_text_wrap
    worksheet.set_row('A:Z',30,format)
    write.save()

执行后触发如下错误：

AttributeError                            Traceback (most recent call last)
Input In [29], in <cell line: 64>()
     66 workbook  = writer.book
     67 worksheet = writer.sheets['Inv_report']
---> 68 format.set_text_wrap
     69 worksheet.set_column('A:Z',30,format)
     70 write.save()

AttributeError: 'builtin_function_or_method' object has no attribute 'set_text_wrap'

错误原因 #

1. 变量名冲突：format是Python内置函数，直接用它作为格式对象变量会覆盖内置方法，导致无法调用Excel格式的set_text_wrap属性
2. 未创建格式对象：没有通过workbook.add_format()创建Excel格式实例，直接调用方法会报错
3. 方法使用错误：set_row用于设置行高，不能接收列范围A:Z，设置列宽需用set_column
4. 笔误：write.save()应为writer.save()

修正后的代码 #

Final_Inv = Inv_report2[["Product ID","Product","Net Activations","Books(at warehouse)","Books(Received by Retailer)","In Transit","Activated","Lost/Stolen (After Activation)","Lost/Stolen(Before Activation)","Total stock at Retailer","Activations(Books)","Stock in Network(Weeks)","Sell Through(%)","Stock at warehouse(Week)","Game Start Date"]]

# 路径前加r转为原始字符串，避免反斜杠被解析为转义字符
with pd.ExcelWriter(r'K:\SnW Project\Inventory Report\Output Excels\Output.xlsx') as writer:
    # 导出数据，后续用xlsxwriter API处理样式
    Final_Inv.to_excel(writer, sheet_name='Inv_report', index=0, startrow=0, startcol=0)
    
    workbook = writer.book
    worksheet = writer.sheets['Inv_report']
    
    # 创建格式对象：开启自动换行+居中对齐
    wrap_format = workbook.add_format({'text_wrap': True, 'align': 'center', 'valign': 'vcenter'})
    
    # 设置A-Z列宽度为30，应用自动换行格式
    worksheet.set_column('A:Z', 30, wrap_format)
    
    # 单独给表头设置加粗样式（可选）
    header_format = workbook.add_format({'text_wrap': True, 'align': 'center', 'valign': 'vcenter', 'bold': True})
    worksheet.set_row(0, 30, header_format)
    
    # with语句会自动处理保存，无需手动调用save()

关键说明 #

• 用workbook.add_format()创建自定义格式，避免和Python内置format冲突
• 原始字符串路径可防止转义字符导致的路径错误
• set_column批量设置列宽和格式，set_row单独配置表头行样式
• with语句管理ExcelWriter时，代码块结束后会自动完成文件保存

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