python - Pandas DataFrame 到 Excel : Vertical Alignment of Index

Question

给定以下数据框:将 pandas 导入为 pd

d=pd.DataFrame({'a':['a','a','b','b'],
               'b':['a','b','c','d'],
               'c':[1,2,3,4]})
d=d.groupby(['a','b']).sum()
d

我想以与索引相同的对齐方式导出它(看看最左边的列是如何垂直居中的？)。问题在于，将其导出到 Excel 时，左列与每个单元格的顶部对齐:

writer = pd.ExcelWriter('pandas_out.xlsx', engine='xlsxwriter')
workbook  = writer.book
f=workbook.add_format({'align': 'vcenter'})
d.to_excel(writer, sheet_name='Sheet1')
writer.save()

...产生...

有没有办法通过 XLSX Writer 或其他库将 A 列垂直居中？

提前致谢!

Answer 1

您正在尝试更改标题的格式，因此您应该首先重置默认标题设置

from pandas.io.formats.excel import ExcelFormatter
ExcelFormatter.header_style = None

然后根据需要应用格式

format = workbook.add_format()
format.set_align('center')
format.set_align('vcenter')

worksheet.set_column('A:C',5, format)

这里是完整的工作代码

d=pd.DataFrame({'a':['a','a','b','b'],
               'b':['a','b','c','d'],
               'c':[1,2,3,4]})
d=d.groupby(['a','b']).sum()

pd.core.format.header_style = None

writer = pd.ExcelWriter('pandas_out.xlsx', engine='xlsxwriter')
workbook  = writer.book
d.to_excel(writer, sheet_name='Sheet1')

worksheet = writer.sheets['Sheet1']

format = workbook.add_format()
format.set_align('center')
format.set_align('vcenter')

worksheet.set_column('A:C',5, format)
writer.save()