python excel条件格式_python – 条件格式单元格,基于它在另一列中的对应值

Excel似乎不喜欢字符串上条件格式的单引号.如果你在里面有双引号,它可以工作,即

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

与

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

我在下面给出了一个完整的可重现的示例,其中包含解决方案的屏幕截图.

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

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

worksheet.conditional_format("A2:A1001",

{"type": "formula",

"criteria": '=(B2:B1001="y")',

"format": format1

}

)

另一方面,如果你想在一个范围内设置多个条件,我认为这是可能的唯一方法是使用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()