I have a dataframe in Pandas that I have exported to Excel and formatted headers etc. using XLWINGS. That works like a charm. But I would like to format two columns based on their value in each cell (row), so if the value is above 0 then green and below zero then red.
I can’t seem to find that option using XLWINGS, is that possible somehow?
Dummy data: Name Type Value (color intended) Boris Sale 10 Green Kurt Wareho. -5 red etc.
Advertisement
Answer
You can loop over the cells of the particular columns (note that this might not be successful, if there are empty cells in the particular columns):
import xlwings as xw path = r"test.xlsx" with xw.App(visible=False) as app: wb = xw.Book(path) ws = wb.sheets[0] for a_cell in ws["C1:D1"].expand("down"): if type(a_cell.value) in [float, int]: if a_cell.value > 0: a_cell.color = (169, 208, 142) elif a_cell.value < 0: a_cell.color = (192, 0, 0) wb.save(path) wb.close()