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

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

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)