How to change the color based on text in excel usi

Question

In Excel cell text will vary from Pass to Fail.I have to give background color green for Pass(pass/Passed/passed) and red for Fail(fail/Failed/failed) respectively. How to change the color based on text ?

My Script

import xlwt

workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('Testing')
worksheet.write_merge(5, 5, 1, 1,'S.No')
worksheet.write_merge(5, 5, 2, 2,'Test Case Description')
worksheet.write_merge(5, 5, 3, 3,'Status')
worksheet.write_merge(5, 5, 4, 4,'Remarks')
worksheet.write_merge(6, 6, 1, 1,1)
worksheet.write_merge(7, 7, 1, 1,1)
worksheet.write_merge(6, 6, 2, 2,'Verify Transferring rate')
worksheet.write_merge(7, 7, 2, 2,'Verify Receiving rate')
worksheet.write_merge(6, 6, 3, 3,'Pass')
worksheet.write_merge(7, 7, 3, 3,'Fail')
workbook.save('testexcel.xls')

@Henry:

Modified code :

import xlwt
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('Status')

passed = xlwt.easyxf('back_color green')
failed = xlwt.easyxf('back_color red')


color = (passed if passorfail in ['pass','Passed','passed'] else
    (failed if passorfail in ['fail','Failed','failed'] else xlwt.easyxf()))

worksheet.write_merge(6, 6, 3, 3,passorfail, style = color)

workbook.save('passfail2.xls')
print "Completed"

And it's throwing error when execute ? How to resolve this error ?

Traceback (most recent call last):
  File "G:\airspan_eclipse\Excel_Gen\passfail2.py", line 5, in <module>
    passed = xlwt.easyxf('back_color green')
  File "C:\Python27\lib\site-packages\xlwt\Style.py", line 704, in easyxf
    field_sep=field_sep, line_sep=line_sep, intro_sep=intro_sep, esc_char=esc_char, debug=debug)
  File "C:\Python27\lib\site-packages\xlwt\Style.py", line 632, in _parse_strg_to_obj
    raise EasyXFCallerError('line %r should have exactly 1 "%c"' % (line, intro_sep))
xlwt.Style.EasyXFCallerError: line 'back_color green' should have exactly 1 ":"

Answer 1

You can create styles using easyxf and then pass them as arguments to your write method.

For example:

style_pass = xlwt.easyxf('pattern: pattern solid, fore_colour green;')
style_fail = xlwt.easyxf('pattern: pattern solid, fore_colour red;')
worksheet.write_merge(6, 6, 3, 3,'Pass', style=style_pass)
worksheet.write_merge(7, 7, 3, 3,'Fail', style=style_fail)

Answer 2

You'll need to put in a if statement to seperate pased on pass fail.

Then, you'll use that to make a color string, something like 'fore-colour grey25'. Look in Style.py for lists of all possible colors and options (github page: https://github.com/python-excel/xlwt/blob/master/xlwt/Style.py). Since red and green both work, and back_color also works, you can do:

passed = xlwt.easyxf('back_color green')
failed = xlwt.easyxf('back_color red')

color = (passed if passorfail in ['pass','Passed','passed'] else
    (failed if passorfail in ['fail','Failed','failed'] else xlwt.easyxf()))
worksheet.write_merge(6, 6, 3, 3,passorfail, style = color)