Styling Cells

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

00:46 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 from openpyxl.styles import Font, Color, Alignment, Side, and Border. So at this point, you can go ahead and create some styles.

01:05 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 size=20.

01:23 And you’ll see that 'colors' is not defined, because it wasn’t imported. So, from openpyxl.styles import colors.

01:32 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 double_border_side.

02:01 The right will also be the double_border_side, the bottom—double_border_side, and then the left will also be double_border_side. Okay.

02:12 Just going to clear that to clean this up. So now, take your sheet["A2"] and set the .font = bold_font.

02:22 Go ahead and grab "A3", and now set the .font to big_red_text, grab "A4" change the .alignment to 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:45 Set the filename to something like "sample_styles.xlsx".

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.

03:07 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.

03:16 So, from openpyxl.styles import NamedStyle.

03:21 Okay. Set 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.

03:34 So, 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 Side(), 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[1].

04:11 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.

04:20 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.

04:53 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.

05:07 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.

05:28 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 openpyxl documentation.

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.