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:

Learn how to make beautiful GUI apps

in Python using the Kivy framework.

Comprehensive, for Kivy beginners, easy to follow.

Get the book here (PDF) or on Amazon:

ebook / paperback (black and white) / paperback (full color)

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

Your Panda3D Magazine

Make Awesome Games and Other 3D Apps

with Panda3D and Blender using Python.

Cool stuff, easy to follow articles.

Get the magazine here (PDF).

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:

Python Jumpstart Course

Learn the basics of Python, including OOP.

with lots of exercises, easy to follow

The course is available on Udemy.

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.

Blender Jumpstart Course

Learn the basics of 3D modeling in Blender.

step-by-step, easy to follow, visually rich

The course is available on Udemy and on Skillshare.