xlsxwriter不将格式应用于数据帧的头行 - Python Pandas [英] xlsxwriter not applying format to header row of dataframe - Python Pandas

问题描述

我正在尝试使用数据框并使用xlsxwriter创建一个电子表格。

I am trying to take a dataframe and create a spreadsheet from that dataframe using the xlsxwriter

我正在尝试对标题行进行一些格式化，但是只有在该行中似乎正在工作的格式化用于行高。完全相同的格式化选项适用于数据帧的其他行。

I am trying to do some formatting to the header row, but the only formatting that seems to be working on that row is for the row height. The exact same formatting options work on the other rows of the dataframe.

请参阅下面的代码。

红色（和高度）应用于除标题行（行2）之外的所有行 - 红色应用于行0和行3，但仅将高度应用于行2

The red color (and the height) is applied to all rows except the header row (row 2) - the red color is applied to both row 0 and row 3, but only the height is applied to row 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)

推荐答案

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

You are trying to change the formatting of the header so you should first reset the default header settings

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()

这篇关于xlsxwriter不将格式应用于数据帧的头行 - Python Pandas的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！