CODEWITHSUNDEEP
pythonexcelpandasxlsxwriter
Uqhah
Uqhah

Reputation: 67

Conditional format a row from python based on values in two columns

I want to use python to save my dataframe as excel and conditional format rows. I want to color the rows green if the values in two columns match my threshold. Suppose I have a table with columns ID, name_product, price and quantity. Now from this dataframe when I am exporting it to excel file I want to color the entire rows green if price >10 and quantity greater than 5. I am able to add single condition using the following code :

styled.to_excel(writer, sheet_name="Feuil1", index = False)

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

format1 = workbook.add_format({'bg_color': '#FFC7CE','font_color': '#9C0006'})
worksheet.conditional_format('A2:O5', {'type': 'formula','criteria': '=(INDIRECT("F"&ROW())>10)',"format": format1})
workbook.close()
writer.save()

But simply adding this does not do the trick:

worksheet.conditional_format('A2:O7336', {'type': 'formula','criteria': '=(INDIRECT("F"&ROW())>10) & (INDIRECT("B"&ROW())>5)',"format": format1})

Upvotes: 1

Views: 727

Answers (1)

Uqhah
Uqhah

Reputation: 67

This seems to have worked and is giving me the desired result:

writer = pd.ExcelWriter("Placing.xlsx")
styled.to_excel(writer, sheet_name="Feuil1", index = False)

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

format1 = workbook.add_format({'bg_color': '#FFC7CE','font_color': '#9C0006'})
worksheet.conditional_format('A2:O7336', {'type': 'formula','criteria': '=AND((($F2)>10),($B2>5))',"format": format1})
workbook.close()
writer.save()

Let me know if you all have a better solution.

Upvotes: 1

Related Questions