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,
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
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
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.
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.
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.
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.
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.
Become a Member to join the conversation.