使用 XlsxWriter 在 Pandas 中导出到“xlsx”时应用样式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24065547/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Apply styles while exporting to 'xlsx' in pandas with XlsxWriter
提问by Nikita
I use the .to_excel method of pandas to write a DataFrame as an Excel workbook. This works nice even for multi-index DataFrames as index cells become merged. When using the pure XlsxWriter I can apply formats to cells what also works nice.
我使用 pandas 的 .to_excel 方法将 DataFrame 编写为 Excel 工作簿。即使对于多索引数据帧，当索引单元格合并时，这也能很好地工作。使用纯 XlsxWriter 时，我可以将格式应用于单元格，这也很好用。
However I couldn't find a way to do the same with the pandas method. Just passing a dict with column names and styles would be most intuitive.
但是我找不到用Pandas方法做同样的事情的方法。仅传递带有列名和样式的 dict 将是最直观的。
Is there any way to do so?
有什么办法吗？
采纳答案by jmcnamara
Is there any way to do so
有没有办法这样做
Currently no. There isn't a formatting mechanism like that in Pandas for formatting the Excel output (apart from a few hard-coded formats).
目前没有。没有像 Pandas 那样的格式化机制来格式化 Excel 输出（除了一些硬编码格式）。
However, even if it was XlsxWriter doesn't currently support formatting cells after data is added. It is on TODO list.
但是，即使是 XlsxWriter 目前也不支持在添加数据后格式化单元格。它在待办事项清单上。
Update:
更新：
As a workaround I recommend getting a reference to the underlying workbook and worksheet and overwriting any cells that you wish to be formatted with the same data from the Pandas dataframe and a XlsxWriter format.
作为一种解决方法，我建议获取对基础工作簿和工作表的引用，并使用 Pandas 数据框和 XlsxWriter 格式中的相同数据覆盖您希望格式化的任何单元格。
回答by Tim Hoffmann
If you just want to style the header, you can modify pandas.io.formats.excel.header_style. Of course, this is no general solution, but is an easy workaround for a common use-case.
如果您只想设置标题的样式，可以修改pandas.io.formats.excel.header_style. 当然，这不是通用的解决方案，而是针对常见用例的简单解决方法。
import pandas.core.format
header_style_backup = pandas.io.formats.excel.header_style
try:
pandas.io.formats.excel.header_style = {"font": {"bold": True},
"borders": {"top": "thin", "right": "thin", "bottom": "thin", "left": "thin"},
"pattern": {"pattern": "solid", "fore_colour": 26},
"alignment": {"horizontal": "center", "vertical": "top"}}
df.to_excel(writer, sheet_name=sheetname, startrow=table_startrow)
finally:
pandas.formats.format.header_style = header_style_backup
Note:The location of header_style has been changing multiple times in prior pandas versions. Use the following for older versions:
注意：header_style 的位置在之前的 Pandas 版本中已经多次更改。对旧版本使用以下内容：
version < 0.20.0 pandas.formats.format.header_style
版本 < 0.20.0 pandas.formats.format.header_style
version < 0.18.0 pandas.core.format.header_style
版本 < 0.18.0 pandas.core.format.header_style
回答by chrisp
The following approach allows me to use xlsxwriter formatting on the dataframe index and column names (though I can't guarantee it's validity):
以下方法允许我在数据框索引和列名上使用 xlsxwriter 格式（尽管我不能保证它的有效性）：
import pandas as pd
import xlsxwriter as xl
# remove pandas header styles
# this avoids the restriction that xlsxwriter cannot
# format cells where formatting was already applied
pd.core.format.header_style = None
# write dataframe to worksheet
writer = pd.ExcelWriter(sumfile, engine='xlsxwriter')
df.to_excel(writer, sheet_name='test')
# create desired xlsxwriter formats
workbook = writer.book
worksheet = writer.sheets['test']
header = workbook.add_format({'bold': True})
index = workbook.add_format({'align': 'left'})
# apply formats to header and index
worksheet.set_row(0, None, header)
worksheet.set_column(0,0, None, index)回答by joeln
The next version of Pandas (2.0) will include experimental support for exporting styled DataFrames direct to Excel using openpyxl: http://pandas-docs.github.io/pandas-docs-travis/style.html#Export-to-Excel
下一版本的 Pandas (2.0) 将包括使用 openpyxl 将带样式的 DataFrames 直接导出到 Excel 的实验性支持：http://pandas-docs.github.io/pandas-docs-travis/style.html#Export-to-Excel
