String conditional formatting "equal to" in Excel using Python's xlsxwriter

Question

I have relatively big Excel spreadsheets, where I am applying conditional formatting. However, the content of a cell is relatively short (max 3 letters). So, I need to match exactly a string. For example: 'A' should be formatted but nothing more containing 'A' ('ABC', 'BCA', 'BAC', etc.).

I tried different options using 'text' and 'cell' options but I failed miserably. Here is my test case:

import xlsxwriter

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

format1 = workbook.add_format({'bg_color': '#FFC7CE',
                               'font_color': '#9C0006'})

data = [
    ['ABC', 'BCA', 38, 30, 75, 48, 75, 66, 84, 86],
    [6, 24, 1, 84, 54, 62, 60, 3, 26, 59],
    [28, 79, 97, 13, 85, 93, 93, 22, 5, 14],
    [27, 'BAC', 40, 17, 18, 79, 90, 93, 29, 47],
    [88, 'ABC', 33, 23, 67, 1, 59, 79, 47, 36],
    [24, 'A', 20, 88, 29, 33, 38, 54, 54, 88],
    [6, 'BCA', 88, 28, 10, 26, 37, 7, 41, 48],
    [52, 78, 1, 96, 26, 45, 47, 33, 96, 36],
    [60, 54, 81, 66, 81, 90, 80, 93, 12, 55],
    [70, 5, 46, 14, 71, 19, 66, 36, 41, 21],
]

for row, row_data in enumerate(data):
    worksheet1.write_row(row, 0, row_data)


worksheet1.conditional_format('A1:J10', {'type': 'text',
                                         'criteria': 'containing',
                                         'value': 'A',
                                         'format': format1})

workbook.close()

So, I want to match only one cell. I run out of options/ideas. It seems trivial but I am getting lots of errors that my Excel files are incorrect. Hope someone found solution for this problem.

It's done in Excel 2010 (Conditional Formatting -> Highlight Cells Rules -> Equal to: type A in textbox).

Cidevant

Answer 1

The following XlsxWriter conditional format should work:

worksheet1.conditional_format('A1:J10', {'type': 'cell',
                                         'criteria': '==',
                                         'value': '"A"',
                                         'format': format1})

Note that, as in Excel, the conditional type should be cell and the value should be a string (with quotes).

Candace

Answer 2

If I understand correctly, you want Python to match a single character. So for example you want to match 'A' only and reject something like 'AB' and 'AA', before formatting.

For that you need to match character and length.

'A' in string and len('A') == len(string)

Then you can apply the formatting to the ones that match that criteria.

If you want to use xlswriter conditional_format, according to the documentation, you can imitate equal to by doing:

worksheet.conditional_format('B3:K12', {'type':     'cell',
                                        'criteria': '==',
                                        'value':    '"A"',
                                        'format':   format2})

EDITED error in value (that should be a string as pointed out by @jmcnamara)

Coit

You are right, I could crawl the table and "mark" elements of length 1. And take this table, for applying formatting to those marked elements (cells). However, I was hoping something from xlsxwriter library and .conditional_format(). If there is nothing better, I will use this solution... However, the solution does won't work, if empty cell is modified... – Cidevant

I see. I think missed your problem. So you want to create a conditional formatting that's only triggered if "A" (let's say) only appears. Is that right? – Coit

Yes. It's easily done in Excel 2010 (Conditional Formatting -> Highlight Cells Rules -> Equal to). I have added this into description. – Cidevant