openpyxl Part 15 – Styling with IconSet

In the previous part of the openpyxl series we were talking about the ColorScale built-in format, which we used to conditionally scale our workbook. We were working on the wb2.xlsx workbook again. Here’s the data it contains:

company	city	category	income	number_of_employees
Roses and Pansies	Toronto	gardening	456845.00	26
Reader’s Paradise	Sydney	publisher	911500.00	44
Dogs and Cats	Madrid	pets	458784.00	36
Enjoy Your Meal	New Orleans	restaurant	447884.00	18
Bloom Store	London	gardening	1254654.00	84
Tastes of the South	Miami	restaurant	587454.00	42
Pets for Everyone	Athens	pets	987887.00	21
Eat ‘Em Up	Helsinki	restaurant	1254477.00	53
Mr. Bunny	Vienna	pets	254478.00	12
Bookworms Rule	Rome	publisher	124474.00	40
SUM				376
MIN				12
MAX				84
AVERAGE				37.6
BIG COMPANIES				4

Today we’ll be using another built-in format, IconSet. It comes in handy if you want to add an icon to a cell depending on its value. Let’s load our workbook first:

>>> from openpyxl import load_workbook
>>> workbook = load_workbook(filename="wb2.xlsx")
>>> sheet = workbook.active 

We’ll also need the IconSetRule class:

>>> from openpyxl.formatting.rule import IconSetRule

There are quite a few icons available. You can read about them on https://openpyxl.readthedocs.io/en/stable/formatting.html#iconset.

Let’s use the 3TrafficLights2 icon for the last column. We’ll be using the percent type and the values list will be set to [0, 20, 50], which means values between 0 and 20 percent of the range will get a red light icon, values between 20 and 50 percent will get a yellow one and values over 50 percent will get a green one. Here’s the code:

>>> rule = IconSetRule('3TrafficLights2', type='percent', values=[0, 20, 50])
>>> sheet.conditional_formatting.add("E2:E11", rule)
>>> workbook.save('wb2e.xlsx') 

And here’s what it looks like:

Let’s now use another icon for the income column. This time we’re going to be working on the modified workbook so that we can see the icons in both columns. Let’s use the 5Rating icon with values with a 20-percent step:

>>> rule = IconSetRule('5Rating', type='percent', values=[0, 20, 40, 60, 80])
>>> sheet.conditional_formatting.add("D2:D11", rule)
>>> workbook.save('wb2f.xlsx') 

If you now open the wb2f.xlsx file, you will see the following:

In the next part of the series we’ll discuss the third built-in format, DataBar.