How to change the color based on text in excel using Python?

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 ":"

If an answer resolves your problem, may I suggest you accept it? If you have further problems, please post them as separate questions. — Dhara
– Dhara, Commented May 22, 2013 at 7:43
As for the error you see, you need to add "pattern:" to the style specification. Example: xlwt.easyxf('pattern: back_color green') — Dhara
– Dhara, Commented May 22, 2013 at 7:47
@Dhara after fixing ('pattern: back_color green') thsi am getting below error : Traceback (most recent call last): File "G:\airspan_eclipse\Excel_Gen\passfail2.py", line 9, in <module> color = (passed if passorfail in ['pass','Passed','passed'] else NameError: name 'passorfail' is not defined — gmanikandan
– gmanikandan, Commented May 22, 2013 at 8:31
That's because the 2nd answer assumed you have a variable called passorfail that holds the status, instead of hard-coded "Pass"/"Fail" strings. If you don't have that, obviously the solution is not going to work for you — Dhara
– Dhara, Commented May 22, 2013 at 9:15

Dhara · Accepted Answer · 2013-05-21 15:19:43Z

2

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)

answered May 21, 2013 at 15:19

Dhara

6,8052 gold badges34 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

gmanikandan Over a year ago

Thanks a lot Dhara.The above code works well with my requirement.

Henry Schreiner · Accepted Answer · 2013-05-21 15:28:01Z

0

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)

answered May 21, 2013 at 15:28

Henry Schreiner

1,0701 gold badge10 silver badges19 bronze badges

Collectives™ on Stack Overflow

How to change the color based on text in excel using Python?

My Script

Modified code :

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

My Script

Modified code :

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related