openpyxl Part 14 – Styling with ColorScale

In the previous part of the openpyxl series we were talking about conditional formatting. We were working on the wb2.xlsx workbook from which we removed all the formatting. Here’s the data it contains:

And here’s what it looks like when you open the file:

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)

Then we added some conditional formatting, first to entire rows, then to single cells. We saved the formatted workbooks in separate files, wb2a.xlsx and wb2b.xlsx.

I also mentioned three built-in formats that you can use to conditionally format your workbook, ColorScale, IconSet and DataBar. Today we’ll be talking about ColorScale.

Let’s start by loading our wb2.xlsx workbook. We’ll then safe the formatted workbook to yet another separate file. Anyway, here’s the initial code:

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

We’ll also need the ColorScaleRule class:

>>> from openpyxl.formatting.rule import ColorScaleRule

Let’s also import colors:

>>> from openpyxl.styles import colors

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).

ColorScale in Action

We want to apply the coloring to the last column where the number of employees is stored. The ColorScale creates color gradients between values. Let’s first create a simple gradient between a minimum value and the maximum value, which means we have to set start_type and end_type to ‘min’ and ‘max’ respectively. And the colors should range from yellow for the minimum value to red for the maximum value. Here’s the code:

 >>> rule = ColorScaleRule(start_type='min', start_color=colors.YELLOW,
 ...                       end_type='max', end_color=colors.RED)
 >>> sheet.conditional_formatting.add("E2:E11", rule)
 >>> workbook.save('wb2c.xlsx') 

If you now open the newly created workbook, you will see that values closer to the minimum value in the last column are yellow, those near the maximum values are red and the ones in-between take all the shades of orange that you can find between yellow and red:

Python Jumpstart Course

Learn the basics of Python, including OOP.

with lots of exercises, easy to follow

The course is available on Udemy.

You can also add a third color in the middle. This time we’ll add colors to the D column. We’ll also set mid_value to 300000, which means this value will be the one near which the mid_color will be applied, so in the example below green. Let’s first load the original wb2.xlsx workbook and work on it. Here’s the code:

 >>> workbook = load_workbook(filename="wb2.xlsx")
 >>> sheet = workbook.active
 >>> rule = ColorScaleRule(start_type='min', start_color=colors.YELLOW,
 ...                       mid_type='num', mid_value=300000, mid_color=colors.GREEN,
 ...                       end_type='max', end_color=colors.RED)
 >>> sheet.conditional_formatting.add("D2:D11", rule)
 >>> workbook.save('wb2d.xlsx') 

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

That’s how we can use the ColorScale in a basic scenario. In the next part we’ll be talking about another built-in format, the IconSet.

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.