pandas 熊猫 xlsxwriter，格式标题

pandas xlsxwriter, format header

提问by Egor Ignatenkov

I'm saving pandas DataFrame to_excel using xlsxwriter. I've managed to format all of my data (set column width, font size etc) except for changing header's font and I can't find the way to do it. Here's my example:

我正在使用 xlsxwriter 将 Pandas DataFrame 保存到 _excel。除了更改标题的字体外，我已经设法格式化了所有数据（设置列宽、字体大小等），但我找不到这样做的方法。这是我的例子：

import pandas as pd
data = pd.DataFrame({'test_data': [1,2,3,4,5]})
writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter')

data.to_excel(writer, sheet_name='test', index=False)

workbook  = writer.book
worksheet = writer.sheets['test']

font_fmt = workbook.add_format({'font_name': 'Arial', 'font_size': 10})
header_fmt = workbook.add_format({'font_name': 'Arial', 'font_size': 10, 'bold': True})

worksheet.set_column('A:A', None, font_fmt)
worksheet.set_row(0, None, header_fmt)

writer.save()

The penultimate line that tries to set format for the header does nothing.

尝试为标题设置格式的倒数第二行什么也不做。

回答by jezrael

I think you need first reset default header style, then you can change it:

我认为您需要先重置默认标题样式，然后才能更改它：

pd.core.format.header_style = None

All together:

全部一起：

import pandas as pd

data = pd.DataFrame({'test_data': [1,2,3,4,5]})
writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter')

pd.core.format.header_style = None

data.to_excel(writer, sheet_name='test', index=False)

workbook  = writer.book
worksheet = writer.sheets['test']

font_fmt = workbook.add_format({'font_name': 'Arial', 'font_size': 10})
header_fmt = workbook.add_format({'font_name': 'Arial', 'font_size': 10, 'bold': True})

worksheet.set_column('A:A', None, font_fmt)
worksheet.set_row(0, None, header_fmt)

writer.save()

Explaining by jmcnamara, thank you:

由jmcnamara解释，谢谢：

In Excel a cell format overrides a row format overrides a column format.The pd.core.format.header_styleis converted to a format and is applied to each cell in the header. As such the default cannot be overridden by set_row(). Setting pd.core.format.header_styleto Nonemeans that the header cells don't have a user defined format and thus it can be overridden by set_row().

在 Excel 中，单元格格式覆盖行格式覆盖列格式。pd.core.format.header_style将转换为格式并应用于标题中的每个单元格。因此，默认值不能被set_row(). 设置pd.core.format.header_style为None意味着标题单元格没有用户定义的格式，因此它可以被覆盖set_row()。

EDIT: In version 0.18.1you have to change

编辑：在版本中0.18.1你必须改变

pd.core.format.header_style = None

to:

到：

pd.formats.format.header_style = None

EDIT: from version 0.20 this changed again

编辑：从 0.20 版开始，这又发生了变化

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

thanks krvkir.

谢谢krvkir。

回答by Ironbeard

An update for anyone who comes across this post and is using Pandas 0.20.1.

对遇到此帖子并使用 Pandas 0.20.1 的任何人的更新。

It seems the required code is now

现在似乎需要的代码是

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

Apparently the excelsubmodule isn't imported automatically, so simply trying pandas.io.formats.excel.header_style = Nonealone will raise an AttributeError.

显然excel子模块不是自动导入的，所以pandas.io.formats.excel.header_style = None单独尝试会引发AttributeError.

回答by Chris Vecchio

Another option for Pandas 0.25 (probably also 0.24). Likely not the best way to do it, but it worked for me.

Pandas 0.25 的另一种选择（可能也是 0.24）。可能不是最好的方法，但它对我有用。

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

回答by eliu

for pandas 0.24:

对于Pandas 0.24：

The below doesn't work anymore:

以下不再起作用：

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

Instead, use the following pseudo code to be in self-control and future proof:

相反，请使用以下伪代码进行自我控制和面向未来：

# [1] write df to excel as usual
writer = pd.ExcelWriter(path_output, engine='xlsxwriter')
df.to_excel(writer, sheet_name, index=False)

# [2] do formats for other rows and columns first

# [3] create a format object for the header: headercellformat

# [4] write to the header row **one cell at a time**, with columnname and format
for columnnum, columnname in enumerate(list(df.columns)):
    worksheet.write(0, columnnum, columnname, headercellformat)

回答by Alec Hanefeld

In pandas 0.20 the solution of the accepted answer changed again.

在 pandas 0.20 中，已接受答案的解决方案再次改变。

The format that should be set to None can be found at:

可以在以下位置找到应设置为 None 的格式：

pandas.io.formats.excel.header_style

回答by divingTobi

If you do not want to set the header style for pandas entirely, you can alternatively also pass a header=Falseto ExcelWriter:

如果您不想完全为Pandas设置标题样式，您也可以将 a 传递header=False给ExcelWriter：

import pandas as pd
import numpy as np

df = pd.DataFrame(np.random.rand(3, 5), 
                  columns=pd.date_range('2019-01-01', periods=5, freq='M'))

file_path='output.xlsx'
writer = pd.ExcelWriter(file_path, engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1', header=False, index=False )
workbook = writer.book
fmt = workbook.add_format({'num_format': 'mm/yyyy', 'bold': True})
worksheet = writer.sheets['Sheet1']
worksheet.set_row(0, None, fmt)
writer.save()