CODEWITHSUNDEEP
pythonexcelpandasxlwings
Morten_DK
Morten_DK

Reputation: 229

XLWINGS conditional formatting based on value

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.

Upvotes: 1

Views: 3282

Answers (1)

mouwsy
mouwsy

Reputation: 1933

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

Upvotes: 1

Related Questions