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:

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

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

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

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:

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.