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.