Is there a way to color cells containing text using XlsxWriter?

Question

I have a pandas dataframe, which I'm exporting to an Excel file using to_excel. All of the cells have pseudorandomly generated strings of length 2 in them (either alphanumeric or just numeric). I want to color the background of some of the cells gray. I always want to color the exact same cells each time I run the .py file, but since the content of the cells is basically random, I can't use some condition on the value in the cells to color them.

I looked at the xlsx writer documentation on conditional formatting and tried to use it in conjunction with the accepted answer on this question. As a side note, if I just copy paste the first code block of the accepted answer, that runs just fine!

This is my code:

df = pd.DataFrame({'Col1': ['A1', 'B2', '3C', '4D', 'E5', '6F', 'G7'],
                   'Col2': ['00', '01', '02', '03', '04', '05', '06']})
writer = pd.ExcelWriter('shaded.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1')
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

shadedFormat  = workbook.add_format({'bg_color': 'gray'})

worksheet.conditional_format('B3:B5', {'type': 'text',
                                      'criteria': 'not equal to',
                                       'value': 'longString',
                                       'format': shadedFormat})

writer.save()

The python interpreter does not return any errors, but when I go to open the .xlsx file, a pop up window gives "We found a problem with some content in 'shaded.xlsx'. Do you want us to try to recover as much as we can?" Upon saying yes, it brings up another windows, which says "Repaired Part: /xl/worksheets/sheet1.xml part with XML error. Catastrophic failure Line 2, column 1267." and none of the columns are shaded.

I would like to do away with the error and have the specified cells shaded gray.

Thank you for any help/suggestions!

Answer 1

The error comes from the type and the criteria.

Try:

• type: 'text'
• criteria: 'containing'

Example: all cells containing "C3" are colored.

worksheet.conditional_format('B3:B5', {'type': 'text',
                                       'criteria': 'containing',
                                       'value': '3C',
                                       'format': shadedFormat})

Output:

UPDATE:

If you want to select a cell what ever there is in, you can use:

worksheet.conditional_format('B3:B5', {'type': 'text',
                                       'criteria': 'containing',
                                       'value': '',
                                       'format': shadedFormat})

Output: