xlsxwriter未将格式应用于 Dataframe 的标题行- Python Pandas

vbkedwbf 于 2023-03-11 发布在 Python

关注(0)|答案(4)|浏览(122)

我尝试获取一个 Dataframe ，然后使用xlsxwriter从该 Dataframe 创建一个电子表格
我试图对标题行进行一些格式化，但似乎唯一对该行起作用的格式化是行高，完全相同的格式化选项对 Dataframe 的其他行起作用。
请参阅下面的代码。
红色（和高度）应用于除标题行（行2）之外的所有行-红色应用于行0和行3，但仅高度应用于行2
任何帮助都将不胜感激

import numpy as np
import pandas as pd
from pandas.io.data import DataReader
from pandas import DataFrame
from IPython import display

import xlsxwriter

WorkBookName="test.xlsx"


df3=pd.read_csv("https://raw.githubusercontent.com/wesm/pydata-book/master/ch08/tips.csv", sep=',')

writer = pd.ExcelWriter(WorkBookName, engine='xlsxwriter')
df3.to_excel(writer, sheet_name="sheet",index=False,startrow=2)

workbook  = writer.book
worksheet = writer.sheets["sheet"]

worksheet.write(0,0,"text string")
worksheet.write(0,1,"text string")
worksheet.write(0,2,"text string")
worksheet.write(0,3,"text string")

color_format = workbook.add_format({'color': 'red'})
worksheet.set_row(0,50,color_format)
worksheet.set_row(2,50,color_format)
worksheet.set_row(3,50,color_format)    

writer.save()

display.FileLink(WorkBookName)

pandas

来源：https://stackoverflow.com/questions/41447606/xlsxwriter-not-applying-format-to-header-row-of-dataframe-python-pandas

4条答案

按热度按时间

fjaof16o1#

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

pd.core.format.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()

赞(0）回复(0）举报 2023-03-11

gjmwrych2#

如果你有0.22，你必须执行pd.io.formats.excel.header_style = None，看看这个git page。

赞(0）回复(0）举报 2023-03-11

1mrurvl13#

据我所知，Pandas设置索引行的格式。有一些方法可以重置它，但这些解决方案不是很可靠。实际上格式化它也很困难。
可接受的答案对所有单元格使用相同的格式，而我只想格式化索引行。

我用所需的格式写出索引列来解决这个问题：

import pandas as pd

# The data that we're feeding to ExcelWriter
df = pd.DataFrame(
    {
        "Col A": ["a", "a", "b", "b"],
        "Col B": ["a", "b", "c", "d"],
        "Col C": [1, 2, 3, 4],
    }
)

# The Excel file we're creating
writer = pd.ExcelWriter("pandas_out.xlsx", engine="xlsxwriter")
df.to_excel(writer, sheet_name="Sheet1", index=False) # Prevents Pandas from outputting an index

# The variables we'll use to do our modifications
workbook = writer.book
worksheet = writer.sheets["Sheet1"]

worksheet.set_row(0, 30) # Set index row height to 30

# Find more info here: https://xlsxwriter.readthedocs.io/format.html#format-methods-and-format-properties
header_format = workbook.add_format(
    {
        "bold": True,
        "valign": "vcenter",
        "align": "center",
        "bg_color": "#d6d6d6",
        "border": True,
    }
)

# Write the column headers with the defined format.
for col_num, value in enumerate(df.columns.values):
    worksheet.write(0, col_num, value, header_format)

# Set format of data
format1 = workbook.add_format({"align": "center"})
worksheet.set_column('A:Z', 10, format1) # Width of cell

writer.save()

赞(0）回复(0）举报 2023-03-11

ewm0tg9j4#

要更改标题的格式，应首先重置默认标题设置。
版本0.24要求：导入Pandas.io.格式.excelPandas.io.格式.excel. Excel格式设置.标题样式=无

赞(0）回复(0）举报 2023-03-11