- [Instructor] Worksheet cells don't just contain data.
They can also be visually styled in multiple ways.
In this example, we'll examine some of the ways
to apply styling to cells.
So let's open up our sample code in cell_styles.py.
And here in my example code,
I already have some code that creates a workbook
and adds some content to a few cells.
I've also imported some classes from openpyexcel
that I'll need for applying styles.
So first, let's inspect the styling information
that is applied to each of these cells
when the data is created.
So here I've got sheet A1, B1, and C1
being set to these default values.
And then I'm going to inspect the style of A1
along with the number formats of cells B1 and C1.
So let's go ahead and run the code that we have.
All right, and when I run the code, you can see that here
in the output, cell A1 has the normal style applied,
and the number formats for B1 and C1
are the general number format.
And we have a date time format here.
And this is happening because openpyexcel applies
certain default styles when you add content to a worksheet.
So the text content has the normal style applied to it,
while the decimal number has the general number style
and the date time has a date style.
Alright, let's try changing some of these.
Let's go ahead and close this.
Let's try applying some of the built-in styles
that Excel uses to our cells.
And again, I've imported the numbers module
from the openpyexcel.styles module,
and I'm aliasing that just as opnumstyle here.
So you'll see why in a second.
Let's go ahead and scroll down a little bit.
So what I'm going to do is write sheet A1,
and now I'm going to set the style property
equal to the word Title.
This is one of the built-in styles that Excel uses.
And then I'll do something similar for each of B1 and C1.
So for B1 I'll set the style to Calculation.
And then for C1 I'll set it to Accent2.
Alright, and then for B1's number format,
I'll set that equal to opnumstyle..
And you can see that there's a whole bunch
of formatting codes here in this list.
And, you know, you can choose any one of these
as the formatting option.
So I'm going to format that as, let's see,
format currency, US dollar simple.
And then for the C1,
I will set the number format equal to,
and in this case I'm going to use format date.
And I'm going to use the DDMMYY format.
And let's also change the width and height
of Row1 and column A.
And the way I'm going to do that is
by operating on the column dimensions
property for the column.
And for column A, I will set the width equals to 30.
And then for the Row dimensions
on Row 1, I'm going to set the height of that to 50.
All right, and you can see
that we're saving the workbook down here.
Okay, let's save.
Let's run this code again.
All right, so we can see that StyledCell.xslx
has been created because that's what we saved it as.
So let's go out and open that in Excel.
Alright, let's open this up.
Okay, so you can see that when the workbook is created,
and now I've got it open here, the format of A1
has been set to the title style.
We can see that the calculation style has been set
to cell B1 and the month day
and two-digit year style has been set on column C.
And the visual styles have been affected as well.
This is the calculation visual style,
and then this is the Accent2 style.
And again, if you look in cell styles,
you can see where these styles came from, right?
So here's calculation right here,
here's accent two down here, here's title.
So those are all the named styles that you can use, okay.
So you can easily change the styling of cells to be
one of the named styles, and that's pretty useful.
But of course we're not limited
to the built-in styles of Excel.
Let's try defining our own cell styles
and then applying them to the data.
So let's close this and let's go back to our code.
Alright, so now I need to define some styles.
And the way that this works is it's a two-step process.
First you define the styles that you want to use
and then you apply those styles to the cells
that you want to affect.
So I'm going to define a few different styles.
I'm going to define a font style that has italic font.
So I'll name that italic font.
I'm going to create a new Font object,
and I'm going to set italic equals to true
and size equal to 16.
And again, these these objects were imported up here
from the openpyexcel styles module.
So I've imported font, alignment, border and side.
So let's use each of those.
Alright, I'll create a few more, such as colored text.
So let's make a colored text style.
And that's going to be another Font object.
And the name is going to be Courier New,
and the size is going to be 20 points.
And the color is going to be, let's see,
it's going to be an RGB value
and it has to have an alpha channel.
So I'll make it 00
and then 00 for R and then G and the B is going to be FF.
All right, I'll make one for centered text.
This is going to be an alignment definition.
So the alignment is going to be
horizontal equals center, right, so center of the line,
and vertical alignment is going to be equal to top.
And then let's create a border style.
So first I'll define a border side,
and that's going to be a new Side object,
and that's going to be border style.
I'll make that one mediumDashed.
And again, these are predefined inside of Excel.
And then I'll create a cell border,
and that's going to be equal to a Border object.
And so the top is going to be equal to border side.
And then the same thing with the right
and then the left
and then the bottom.
Oops.
Okay, and then once I've defined
the styles I want to use,
I can now apply them directly to the cells.
So let's make the A1 cell.
So I'll apply the font, it's going to be my italic font.
And then let's apply to B1.
Let's make that font the colored text.
And let's also assign to B1,
I'm going to assign the alignment
to be the centered text that we created.
And then on the C1 cell,
let's set the border
equal to the cell border that we created.
Alright, so now we've got these custom styles.
Let's go and comment out the previous built-in styles,
but we'll leave the dimensions alone.
Alright, so let's save.
And now let's run our updated code.
Alright, looks like everything worked.
Let's go ahead and open up our Excel file.
Alright, so let's see.
It looks like, okay, we've got in cell A1,
we have the italic font, that's good.
Here we have the alignment
set to top and centered within the cell.
Okay, that's pretty good.
And it's blue text, that's also good.
And then finally we have the date formatting
here in the last cell.
Alright, so now I've only touched on
a few of the examples here.
So I encourage you to refer to the openpyexcel docs.
In fact, let me just bring those up real quick.
So here at this link in the openpyexcel documentation,
you can learn more about applying some cell styles.
I would suggest maybe trying some of your own experiments
here to get a better feel
for how the styling functions work before continuing on.