Add Conditional Formatting (Icon Sets) using python win32com

Question

I would like to add conditional formatting in an excel sheet using python win32com with the conditions mentioned below: Arrow Up: Cell value >= 0 Arrow Down: Cell value < 0

Expected Excel output

This is my code but it is showing me 'Traffic Lights' instead of the 'Arrows' set.

format_condition = worksheet.Cells(start_row + 1, col).FormatConditions.AddIconSetCondition()

Actual Excel output

What did I miss out?

Answer 1

I think the default is to use traffic lights if not specified even though arrows it at the top of the list.
You need to specify that 'xl3Arrows (Colored)' is to be used and set the criteria.

import win32com.client as win32


### Constants [Can be imported if available in Module(s)]
xlConditionValueNumber = 0
xlIconNoCellIcon = -1
xlIconGreenUpArrow = 1
xlIconRedDownArrow = 3
xlGreater = 5
xlGreaterEqual = 7
xl3Arrows = 1  # xl3Arrows (Colored) = 1 

### Create Excel object
excel = win32.gencache.EnsureDispatch('Excel.Application')
# excel.Visible = True

### Open Excel Workbook and required Worksheet
wb = excel.Workbooks.Open(r'<path>\foo.xlsx')
ws = wb.Worksheets(1)

### Conditional Format range
ws.Range("E5:S5").Select()

### Set Conditional Formatting to use IconSet
excel.Selection.FormatConditions.AddIconSetCondition()
excel.Selection.FormatConditions(excel.Selection.FormatConditions.Count).SetFirstPriority()

### Set Conditional Format basics including the icon style
cf1 = excel.Selection.FormatConditions(1)
cf1.ReverseOrder = False
cf1.ShowIconOnly = False
cf1.IconSet = excel.ActiveWorkbook.IconSets(xl3Arrows)  # 3 Arrows Coloured

### Disable yellow arrow
cf2 = excel.Selection.FormatConditions(1).IconCriteria(2)
cf2.Type = xlConditionValueNumber
cf2.Value = 0
cf2.Operator = xlGreaterEqual 
cf2.Icon = xlIconNoCellIcon

### Set Conditional Format Criteria for Green Arrow
### >= 0, Type: Number, Value:0
cf3 = excel.Selection.FormatConditions(1).IconCriteria(3)
cf3.Type = xlConditionValueNumber
cf3.Value = 0
cf3.Operator = xlGreaterEqual 
cf3.Icon = xlIconGreenUpArrow

### Set Conditional Format Criteria for Red Arrow
### Default condition < 0, Type: Number, Value:0
excel.Selection.FormatConditions(1).IconCriteria(1).Icon = xlIconRedDownArrow

### Save and Quit
ws.Range("A1").Select()
wb.SaveAs(r'<path>\foo_out.xlsx')
excel.Application.Quit()

The CF rule shows icon style as custom but is set to use arrows