Unable to change font color in excel using python

Question

workb = xlsxwriter.Workbook('Newexcel1.xlsx')
worksheet1 = workb.add_worksheet("Sheet 1")

for row, row_data in enumerate(alldata):
    worksheet1.write_row(row + 1, 1, row_data)

cell_format.set_font_color('vbRed')


worksheet1.conditional_format('C2:C7', {'type':     'cell',
                                    'criteria': '==',
                                    'value': 'Data Matched!',
                                    'format': cell_format})

I'm trying to write to an excel file using python. The alldata is list of lists. The problem is that i want to change the color of text in Data Matched to green and Not Matched! to red, which is not happening. The code listed above doesn't show any error but when i open the excel file it asks me to restore previous version as the new one has xml error.

alldata =     [['My Total', 'Data Matched!', '$824,499,658', '$824,499,658'], ['Second Total', 'Data Matched!', '$824,532,682.20', '$824,532,682.20'], ['Featured Articles', 'Data Matched!', '$391,153,610.55', '$391,153,610.55'], ['Ads Revenue', 'Data Not Matched!', '$825,513,740.17', '$825,582,419.92'], ['Company 1 Revenue', 'Data Not Matched!', '$824,765,286.03', '$824,833,965.78'], ['Company 2 Revenue', 'Data Not Matched!', '$176,767,751.61', '$239,939,801.89']]

Answer 1

When using a conditional format in XlsxWriter it is best to figure out what you want to do in Excel first and then transfer it to the XlsxWriter.

In this case Excel doesn't support a cell equality with a string. Instead you have to use a "text" conditional (or possibly a formula).

Here is a simplified version of your code that fixes a few minor issues and does what you want:

import xlsxwriter

alldata = [['My Total', 'Data Matched!', '$824,499', '$824,499'],
           ['Second Total', 'Data Matched!', '$824,532', '$824,532'],
           ['Featured Articles', 'Data Matched!', '$391,153', '$391,610'],
           ['Ads Revenue', 'Data Not Matched!', '$825,513', '$825,492'],
           ['Company 1 Revenue', 'Data Not Matched!', '$824,263', '$824,965'],
           ['Company 2 Revenue', 'Data Not Matched!', '$176,711', '$239,801']]

workbook = xlsxwriter.Workbook('test.xlsx')
worksheet = workbook.add_worksheet()

worksheet.set_column('C:C', 14)

cell_format = workbook.add_format()
cell_format.set_font_color('red')

for row, row_data in enumerate(alldata):
    worksheet.write_row(row + 1, 1, row_data)

worksheet.conditional_format('C2:C7', {'type': 'text',
                                       'criteria': 'begins with',
                                       'value': 'Data Matched!',
                                       'format': cell_format})

workbook.close()

Output: