openpyxl Part 16 – Styling with DataBar

Spread the love

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

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, 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:

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

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: