How to Conditionally Format Excel Rows With Text Phrases Using xlswriter Module-CodePudding

The script below works as intended by highlighting the Excel Rows that contain keyword1 as red.

import xlsxwriter

workbook = xlsxwriter.Workbook('conditional_format.xlsx')
worksheet1 = workbook.add_worksheet()

# Keyword
keyword1 = 'red'
keyword2 = 'yellow'

# Add a format. Light red fill with dark red text.
red_colour = workbook.add_format({'bg_color': '#FFC7CE',
                               'font_color': '#9C0006'})

data = [
    ['grape', 'Smooth skin either green, red or black in colour'],
    ['banana', 'peelable yellow skin'],
    ['apple', 'red or green skin fruit'],
    ['melon', 'refreshing yellow or red flesh fruit'],
]

# Write the data.
for row, row_data in enumerate(data):
    worksheet1.write_row(row   0, 0, row_data)# (row, column, data)

worksheet1.conditional_format('A1:B4', {'type': 'text',
                                        'criteria': 'containing',
                                        'value': keyword1,
                                        "format": red_colour})

workbook.close()

Output

Requirement

I need a way to extend on this script to search and highlight all rows when both keyword1 and keyword2 are found.

Therefore, in this example only row 4 should be highlighted as it contains instances of red and yellow as defined by keywords1 and keywords2 respectively.

Thanks in advance.

CodePudding user response：

I'm not sure you can do this with a text condition, but you should be able to do this with a formula condition, like this:

formula = '=AND(ISNUMBER(SEARCH("red",B1)),ISNUMBER(SEARCH("yellow",B1)))'
worksheet1.conditional_format('$B$1:$B$4', {'type': 'formula',
                                            'criteria': formula,
                                            "format": red_colour})

You may have to play around with absolute vs. relative references or (annoyingly) loop through all the relevant cells with an absolute reference, as xlsxwriter doesn't always handle relative references for conditional formatting correctly, but something like this should work.