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:

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:

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

Python Jumpstart Course

Learn the basics of Python, including OOP.

with lots of exercises, easy to follow

The course is available on Udemy.

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.