python - xlsxwriter 行和列相交格式

Question

我正在使用 xlsxwriter 将 pandas DataFrame 保存到 Excel。首先，我向列添加一些格式(例如，更改字体)。然后我想更改某些行的背景颜色。但是当我添加 set_row 函数时，我所有列的格式都消失了。有没有办法结合起来？

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

worksheet.set_column('A:A', None, font_fmt)

zebra = workbook.add_format({'bg_color': 'green'})

for index in range(5):
    if index % 2 == 0:
        worksheet.set_row(index+1, None, zebra)

writer.save()

Answer 1

这将有助于:

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']

formatdict = {'font_name': 'Calibri', 'font_size': 10, 'font_color':'red'}
font_fmt = workbook.add_format(formatdict)

worksheet.set_column('A:A', None, font_fmt)

zebra = workbook.add_format(formatdict)
zebra.set_bg_color('green')

for index in range(1,6,2):
        worksheet.set_row(index, None, zebra)

writer.save()

应产生以下输出: