openpyxl Part 16 – Styling with DataBar

In the previous part of the openpyxl series we were talking about the IconSet 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, DataBar. We use it to create progress bars. 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 DataBarRule class and colors:

 >>> from openpyxl.formatting.rule import DataBarRule
 >>> from openpyxl.styles import colors 

Now let’s add the progress bars to the last column:

 >>> rule = DataBarRule(start_type='percentile', 
 ...                    start_value=10, 
 ...                    end_type='percentile', 
 ...                    end_value=90, 
 ...                    color=colors.BLUE)
 ... 
 >>> sheet.conditional_formatting.add("E2:E11", rule)
 >>> workbook.save('wb2g.xlsx') 

And here’s the wb2g.xlsx file:

In the example above we set the type to percentile and that’s how the values (start and end value) are expressed. In the next example we’ll use numbers:

 >>> rule = DataBarRule(start_type='num', 
 ...                    start_value=150000, 
 ...                    end_type='num', 
 ...                    end_value=1300000, 
 ...                    color=colors.RED)
 ... 
 >>> sheet.conditional_formatting.add("D2:D11", rule)
 >>> workbook.save('wb2h.xlsx') 

And here’s the wb2h.xlsx file:

This was the last part that deals with conditional formatting. In the next part we’ll learn how to add images.