使用 pandas xlswriter 模块使用 conditional_format 格式化整行 [英] Format entire row with conditional_format using pandas xlswriter module

问题描述

我正在使用 pandas xlswriter 模块创建一个 excel 报告.下面是相同的代码片段.

number_rows = len(df.index)//df是数据框writer = pd.ExcelWriter("Report_1.xlsx",engine='xlsxwriter')df.to_excel(作家，报告")工作簿 = writer.bookworksheet = writer.sheets['report']# 定义颜色格式的范围color_range = "A2:F{}".format(number_rows+1)format1 = workbook.add_format({'bg_color': '#FFC7CE','font_color': '#9C0006'})worksheet.conditional_format(color_range, {'type': 'text','标准':'包含'，'价值':'成功'，'格式':格式1})

我想根据单元格值 (=SUCCESS) 突出显示一行 ('bg_color': '#FFC7CE', 'font_color': '#9C0006').但是当我使用conditional_format"时，它仅适用于该特定单元格.如果标准"与单元格值匹配，有没有办法将格式应用于整行?

解决方案

我在下面提供了一个完全可重现的示例，该示例利用 Excel 中的 INDIRECT() 函数

I am creating an excel report using pandas xlswriter module. Below the code-snippet for the same.

number_rows = len(df.index)
//df is dataframe

writer = pd.ExcelWriter("Report_1.xlsx",engine='xlsxwriter')
df.to_excel(writer,"report")

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

# Define range for the color formatting
color_range = "A2:F{}".format(number_rows+1)


format1 = workbook.add_format({'bg_color': '#FFC7CE',
                               'font_color': '#9C0006'})

worksheet.conditional_format(color_range, {'type': 'text',
                                           'criteria' : 'containing',
                                           'value': 'SUCCESS',
                                           'format': format1})

I want to highlight a row ('bg_color': '#FFC7CE', 'font_color': '#9C0006') based on a cell value(=SUCCESS). But when I use the "conditional_format" it is applying only on that particular cell. Is there any way to apply the format to the entire row if the "criteria" matches cell value?

解决方案

I've provided a fully reproducible example below that makes use of the INDIRECT() function from Excel (Link here). Please note that I've set the range as $A$1:$B$6 but you can extend this to a different range if you have more columns.

import pandas as pd

df = pd.DataFrame({"Name": ['A', 'B', 'C', 'D', 'E'], 
"Status": ['SUCCESS', 'FAIL', 'SUCCESS', 'FAIL', 'FAIL']})

number_rows = len(df.index) + 1

writer = pd.ExcelWriter('Report_1.xlsx', engine='xlsxwriter')

df.to_excel(writer, sheet_name='Sheet1', index=False)

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

format1 = workbook.add_format({'bg_color': '#FFC7CE',
                              'font_color': '#9C0006'})

worksheet.conditional_format("$A$1:$B$%d" % (number_rows),
                             {"type": "formula",
                              "criteria": '=INDIRECT("B"&ROW())="SUCCESS"',
                              "format": format1
                             }
)

workbook.close()

with expected output in excel:

这篇关于使用 pandas xlswriter 模块使用 conditional_format 格式化整行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！