current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

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 ":"
share|improve this question
    
If an answer resolves your problem, may I suggest you accept it? If you have further problems, please post them as separate questions. –  Dhara May 22 '13 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 May 22 '13 at 7:47
    
@Dhara Sure I accepted the answer –  gmanikandan May 22 '13 at 8:30
    
@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 May 22 '13 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 May 22 '13 at 9:15

2 Answers 2

up vote 1 down vote

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)
share|improve this answer
    
Thanks a lot Dhara.The above code works well with my requirement. –  gmanikandan May 22 '13 at 4:17
up vote 0 down vote

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)
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.