Styling might not be the most important thing that you do to a spreadsheet, but it can definitely help to know how to in case a need pops up.
openpyxl has a ton of styling options available to perform everything that Excel can.
00:12 We’ll just cover a few topics here to get the basics down. The documentation has much more info if you’re interested in specific things that you can do. There are two main ways to apply styles to cells.
00:24 You can either apply a style directly to a cell or create a style template that can then be applied to multiple cells. Let’s open up an interpreter session and test this out on the Amazon data spreadsheet. So I’m going to open up bpython,
00:40 get the regular import, load the workbook,
and then grab that active sheet. Before you can start playing around with the styles you need to import a couple of style classes, so
import Font, Color, Alignment, Side, and
Border. So at this point, you can go ahead and create some styles.
So something like
bold_font is going to equal
Font() and then pass in
bold=True. And if you wanted to have big red text, you could say
Font(color=colors.RED)—which should actually be in all-caps—and then
And you’ll see that
'colors' is not defined, because it wasn’t imported. So,
from openpyxl.styles import colors.
Rerun that line, and there you go. If you want to have center-aligned text, you can say
Alignment(horizontal="center"). And if you wanted to have a double-border side, you could say
Side() and then pass in
border_style and then set this equal to
"double". And then for a square border, you can say
Border() and then pass in
top as the
right will also be the
double_border_side, and then the
left will also be
Just going to clear that to clean this up. So now, take your
sheet["A2"] and set the
.font = bold_font.
Go ahead and grab
"A3", and now set the
"A4" change the
center_aligned_text, and then with
"A5" set the
.border property to equal the
square_border that you made. To see how these worked, take the workbook and save it.
filename to something like
02:51 All right, it popped up in the directory. Let’s go take a look at it. So going over it, A2 has bold text, A3 has this large red text, A4 has this center alignment, and then A5 has this square border around it.
So, that’s pretty cool! If you go back, the other way to apply styles to cells is to use
NamedStyles, and these will produce, basically, a template.
from openpyxl.styles import NamedStyle.
header equal to a new
NamedStyle and the
name here, just call it
"header". And now, instead of assigning a style directly to the cell, you’ll assign them to this.
header.font and set this equal to something like
Font() and set
bold=True. And then
header.border, you can say
Border() and just do this on the bottom. Set
border_style and passing something like
"thin". And with the
.alignment, go ahead and say
Alignment(horizontal="center") and add some vertical centering, too. So, because this is headers, you want to apply this to all the cells on the first row. You can say the
header_row = sheet.
You’ll remember that this will return the first row of the sheet, so you can say that
for cell in header_row:
cell.style = header.
Okay. To make sure this worked, go ahead and say
workbook.save() and set the
filename equal to—I’m going to call it the same thing,
"sample_styles.xlsx". And no errors, so let’s go take a look! All right.
04:34 Now you can see that the header row has the new style applied.
04:42 They’re bold. They’re centered. You can make the row a little bigger—they’re centered vertically, as well, and horizontally. And they have one thin border running on the bottom of the cells.
A cool thing to note is that these
NamedStyles actually show up in Excel. If you ever looked in the Styles section in the toolbar, you can drop this down and you’ll see these default styles for, like, Bad cells, Good cells, different colors, things like that.
And you’ll notice that there’s a Custom section and there’s header! So, that’s a cool thing to keep in mind when you define these
NamedStyles, is that you may not actually apply them to any cells with your script, but they are available for a user to use in the workbook depending on what they need to do. All right, so that’s all there is to know about styles.
Like I said, we just covered a couple basic things that you can do. If you need to look at anything specific, I highly recommend taking a look at the
05:38 In the next video, we’re going to take a look at a really cool feature of Excel and you’re going to learn how to apply conditional formatting to cells using Python.
Some changes happening with colors module, this was what I ended up doing.
red_hex = '00FF0000' big_red_text = Font(color=red_hex, size=20)
I did this:
big_red_text = Font(color="ff0000", size = 20)
Become a Member to join the conversation.
Jon Nyquist on Sept. 7, 2020
Minor point: The openpyxl source code documentation says there are getting rid of some of the predefined colors, including RED openpyxl.readthedocs.io/en/stable/_modules/openpyxl/styles/colors.html
I had to add this code to get it to work: