列中的条件格式单元格基于它在另一列中的对应值

I have created an excel file from a dataframe that looks like this:

我从数据框中创建了一个excel文件,如下所示:

In [215]: import pandas as pd

In [216]: df = pd.DataFrame({"Name": ["A", "B", "C"], "Status": ['y', 'n', 'yy']})

In [217]: df
Out[217]:
  Name  Status
0    A       y
1    B       n
2    C      yy

How can I set the bg_color for "Name" based on the value of Status? I have tried a couple of options without success:

如何根据Status的值为“Name”设置bg_color?我尝试了几个选项却没有成功:

format1 = workbook.add_format({"bg_color": "#669731"})
format2 = workbook.add_format({"bg_color": "#FFFA22"})
format3 = workbook.add_format({"bg_color": "#A43829"})

Option 1

worksheet.conditional_format("A2",
                             {"type": "formula",
                              "criteria": "=ISNUMBER(SEARCH('y', B2))",
                              "format": format1
                             }
)

Option 2

worksheet.conditional_format("A2",
                             {"type": "formula",
                              "criteria": "=$B$2='y'",
                              "format": format1
                             }
)

None of this has given the expected result and when I open the file, I get an error with the following message: unreadable content in the .xlsx
Also it will be good if I could somehow set do this without iterating the dataframe's value.

这些都没有给出预期的结果,当我打开文件时,我收到以下消息的错误:.xlsx中的不可读内容如果我能以某种方式设置这样做而不迭代数据帧的值也将是好的。

1 个解决方案

#1

Excel doesn't seem to like the single quotes for conditional formatting on a string. It works if you have the double quotes on the inside, i.e.

Excel似乎不喜欢字符串上条件格式的单引号。如果你的内部有双引号,即

"criteria": '=($B$2="y")'

versus

"criteria": "=($B$2='y')"

I've put a full reproducible example below with a screenshot of the solution.

我在下面给出了一个完整的可重现的示例,并附有解决方案的屏幕截图。

import pandas as pd

df = pd.DataFrame({"Name": ["A", "B", "C"], "Status": ['y', 'n', 'yy']})

writer = pd.ExcelWriter('test.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": "#669731"})

worksheet.conditional_format("A2",
                             {"type": "formula",
                              "criteria": '=($B$2="y")',
                              "format": format1
                             }
)

workbook.close()

If you want to set this conditional format for a range of say 1000 cells in the column this is possible using the code for the conditional format.

如果要为列中的1000个单元格设置此条件格式,则可以使用条件格式的代码。

worksheet.conditional_format("A2:A1001",
                             {"type": "formula",
                              "criteria": '=(B2:B1001="y")',
                              "format": format1
                             }
)

If on the other hand you want to set multiple conditions over a range, the only way I think this would be possible is to use a for loop, writing each cell with the format that matches a condition. I've provided the example below and it's expected output. Notice that this is a bit of a cheat since it overwrites what has already been put in the cell, if it meets any of the three conditions.

另一方面,如果你想在一个范围内设置多个条件,我认为这是可能的唯一方法是使用for循环,用匹配条件的格式写每个单元格。我已经提供了下面的示例,它是预期的输出。请注意,如果它满足三个条件中的任何一个,它会覆盖已经放入单元格中的内容,这有点作弊。

import pandas as pd

df = pd.DataFrame({"Name": ["A", "B", "C"], "Status": ['y', 'n', 'yy']}) 
writer = pd.ExcelWriter('test.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": "#669731"})
format2 = workbook.add_format({"bg_color": "#FFFA22"})
format3 = workbook.add_format({"bg_color": "#A43829"})

for i in range (0, len(df)):
    if df['Status'].ix[i] == "y":
        worksheet.write(i+1, 0, df['Name'].ix[i], format1)
    elif df['Status'].ix[i] == "n":
        worksheet.write(i+1, 0, df['Name'].ix[i], format2)
    elif df['Status'].ix[i] == "yy":
        worksheet.write(i+1, 0, df['Name'].ix[i], format3)


workbook.close()

#1