Excel column comparison using python and format the color

Question

I have two columns containing 2017 values and 2018 values. I want to color column having 2018 values based on comparison with 2017 values

2018 cell as RED if 2018 cell value <2017 cell value
2018 cell as GREEN if 2018 cell value > 2017 cell value
2018 cell as ORANGE if 2018 cell value = 2017 cell value

I was able to use conditional formatting , but for that I had to create a new column which was comparing column A and column B

worksheet.write('C' + str(rownum + 1), np.where((float(data['2018 YTD'].values[0])>float(data['2017 YTD'].values[0])),2,np.where((float(data['2018 YTD'].values[0])>float(data['2017 YTD'].values[0])),0,1)), style)


worksheet.conditional_format('C2:C5',{'type': 'icon_set','icon_style': '3_arrows' })

But using this code I had to create one more column(Column C) here, instead I am looking to highlight Column B directly with colour codes.

Edit 1## Following Wald's advice:

ws = wb.active 

redFill = PatternFill(start_color='FFFF0000', end_color='FFFF0000', fill_type='solid') 
GreenFill = PatternFill(start_color='EE1111', end_color='EE1111', fill_type='solid') 

if(ws['B2']>ws['A2']):
  ws['B2'].fill=redFill 
else(ws['B2']<ws['A2']):
  ws['B2'].fill=GreenFill

wb.save("sample.xlsx")

But it gave the below error:

TypeError: '>' not supported between instances of 'Cell' and 'Cell'

It seems in this package > or < comparison is not supported

Dan · Accepted Answer · 2018-05-11 13:19:44Z

3

Can't you just use a custom formula for the conditional formatting instead of creating a whole new row:

# First define some formats (taken directly from the docs: http://xlsxwriter.readthedocs.io/working_with_conditional_formats.html)
# Light red fill with dark red text.
red_format = workbook.add_format({'bg_color':   '#FFC7CE',
                                  'font_color': '#9C0006'})

# Light yellow fill with dark yellow text.
orange_format = workbook.add_format({'bg_color':   '#FFEB9C',
                                     'font_color': '#9C6500'})

# Green fill with dark green text.
green_format = workbook.add_format({'bg_color':   '#C6EFCE',
                                    'font_color': '#006100'})


# Red
worksheet.conditional_format('C2:C5', {'type':'formula',
                                       'criteria':'=C2<B2',
                                       'format':red_format})

# Orange
worksheet.conditional_format('C2:C5', {'type':'formula',
                                       'criteria':'=C2=B2',
                                       'format':orange_format})

# Green
worksheet.conditional_format('C2:C5', {'type':'formula',
                                       'criteria':'=C2>B2',
                                       'format':green_format})

You create three of these, one for green, one for orange and one for red, i.e. exactly how you would do it in Excel.

answered May 11, 2018 at 13:19

Dan

45.8k20 gold badges98 silver badges169 bronze badges

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

5 Comments

Sam Over a year ago

it worked. But this logic might fail when we have "n/a" or some string. Say in Column C, I have "n/a" and in Column B, we have 2; then it is actually B>C and it should be red, but in our code it wont be able to interpret "n/a" and it will give color as formatting, which is wrong!

Dan Over a year ago

@Sam well, that's a new requirement. But also, it's trivial to handle cases like that in the excel formula either using ISNA, or IF and ISNA. For example if you want no formatting for #N/A values try =AND(C2>B2, NOT(ISNA(C2)))

Arya Over a year ago

@Dan .. How can we compare complete data between two cells and I have some multiple combinations of cells need to compare, is it possible to do ?

Dan Over a year ago

@Arya I don't understand your question. It might make more sense for you to post a new question (with examples) and include a link to this answer if it's relevant.

Arya Over a year ago

@Dan.. I have posted new question. Please help me if you have any suggestion. stackoverflow.com/questions/57770813/…

Wald · Accepted Answer · 2018-05-11 13:10:02Z

2

Found this library on the internet and tested this bit which works as expected without any logic for different colors.

from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.styles import PatternFill

wb = load_workbook("sample.xlsx")

ws = wb.active

redFill = PatternFill(start_color='FFFF0000',
               end_color='FFFF0000',
               fill_type='solid')

ws['A2'].fill = redFill

wb.save("sample.xlsx")

Hope this helps.

EDIT

In order to compare the values you need to use the value within the cell not the actual cell.

Try if ws['B2'].value > ws['A2'].value:

edited May 11, 2018 at 13:10

answered May 11, 2018 at 12:19

Wald

1,0918 silver badges13 bronze badges

2 Comments

Sam Over a year ago

It seems that package doesnt support comparison logic. I have edited my post with the code

Wald Over a year ago

@Sam you are just trying to compare the actual cell if you print ws['A2'] you will get something like <Cell 'Sheet1'.B1>. You should use ws['A2'].value. Will update the answer

Collectives™ on Stack Overflow

Excel column comparison using python and format the color

2 Answers 2

5 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related