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

score 3 · Accepted Answer · answered May 11 '18 at 13:19

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 '18 at 13:19

Dan

45,079
17
88
157

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! – Sam May 11 '18 at 13:53
@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)))` – Dan May 11 '18 at 14:05
@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 ? – Arya Aug 29 '19 at 19:02
@Arya I don't understand your question. It might make more sense for you to post a new question (with [examples](https://stackoverflow.com/help/minimal-reproducible-example)) and include a link to this answer if it's relevant. – Dan Aug 30 '19 at 12:54
@Dan.. I have posted new question. Please help me if you have any suggestion. https://stackoverflow.com/questions/57770813/excel-columns-comparison-in-generic-way-and-highlight-the-differences-with-some – Arya Sep 03 '19 at 11:31

Wald · Answer 2 · 2018-05-11T13:10:02.390

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 '18 at 13:10

answered May 11 '18 at 12:19

Wald

1,063
7
13

It seems that package doesnt support comparison logic. I have edited my post with the code – Sam May 11 '18 at 12:54
@Sam you are just trying to compare the actual cell if you print `ws['A2']` you will get something like . You should use `ws['A2'].value`. Will update the answer – Wald May 11 '18 at 13:06

Excel column comparison using python and format the color

2 Answers2