CODEWITHSUNDEEP
excelpython-3.xpandasconditional-formattingxlsxwriter
Nabil Daoud
Nabil Daoud

Reputation: 221

Python XlsxWriter conditional format several cells conditioned on the value in a single cell

I would like to highlight the entire row of a table (excluding the index) if the value in the first column is more than 7. I am currently able to highlight the first cell with the following code:

import pandas as pd

df = pd.DataFrame({'A':[6, 4, 8, 2], 'B':[.3, .2, .4, .1], 'C': [12, 13, 14, 15]})

with pd.ExcelWriter('temp.xlsx', engine='xlsxwriter') as writer:
    workbook = writer.book
    df.to_excel(writer)
    worksheet = workbook.worksheets()[0]

    format_yellow = workbook.add_format({'bg_color': '#FFFF00'})

    worksheet.conditional_format('B2:B5', {'type':     'cell',
                                           'criteria': '>',
                                           'value':    7,
                                           'format':   format_yellow})

Here's what I get: get

And here is what I want: want

Upvotes: 2

Views: 2681

Answers (1)

jmcnamara
jmcnamara

Reputation: 41524

In order to figure out how to do a conditional format in XlsxWriter you need to figure out how to do it in Excel first and then apply that to XlsxWriter.

In this case you would probably need a formula conditional format, as opposed to a cell type and you would need to extend the range that the formatting applies to. Like this:

import pandas as pd

df = pd.DataFrame({'A': [6, 4, 8, 2],
                   'B': [.3, .2, .4, .1],
                   'C': [12, 13, 14, 15]})

with pd.ExcelWriter('temp.xlsx', engine='xlsxwriter') as writer:
    workbook = writer.book
    df.to_excel(writer)
    worksheet = workbook.worksheets()[0]

    format_yellow = workbook.add_format({'bg_color': '#FFFF00'})

    worksheet.conditional_format('B2:D5', {'type':     'formula',
                                           'criteria': '=$B2>7',
                                           'format':   format_yellow})

Output:

enter image description here

Note the absolute positioning of the column in $B2 so that it stays fixed over the conditional formatting range while the relative positioning of the row allows it to change.

Upvotes: 2

Related Questions