1 00:00:00,004 --> 00:00:03,000 - [Instructor] Worksheet cells don't just contain data. 2 00:00:03,000 --> 00:00:06,003 They can also be visually styled in multiple ways. 3 00:00:06,003 --> 00:00:08,001 In this example, we'll examine some of the ways 4 00:00:08,001 --> 00:00:10,005 to apply styling to cells. 5 00:00:10,005 --> 00:00:16,006 So let's open up our sample code in cell_styles.py. 6 00:00:16,006 --> 00:00:18,002 And here in my example code, 7 00:00:18,002 --> 00:00:21,005 I already have some code that creates a workbook 8 00:00:21,005 --> 00:00:25,004 and adds some content to a few cells. 9 00:00:25,004 --> 00:00:29,001 I've also imported some classes from openpyexcel 10 00:00:29,001 --> 00:00:32,000 that I'll need for applying styles. 11 00:00:32,000 --> 00:00:34,008 So first, let's inspect the styling information 12 00:00:34,008 --> 00:00:37,004 that is applied to each of these cells 13 00:00:37,004 --> 00:00:40,006 when the data is created. 14 00:00:40,006 --> 00:00:44,002 So here I've got sheet A1, B1, and C1 15 00:00:44,002 --> 00:00:46,001 being set to these default values. 16 00:00:46,001 --> 00:00:50,006 And then I'm going to inspect the style of A1 17 00:00:50,006 --> 00:00:56,001 along with the number formats of cells B1 and C1. 18 00:00:56,001 --> 00:01:01,008 So let's go ahead and run the code that we have. 19 00:01:01,008 --> 00:01:04,008 All right, and when I run the code, you can see that here 20 00:01:04,008 --> 00:01:09,000 in the output, cell A1 has the normal style applied, 21 00:01:09,000 --> 00:01:13,000 and the number formats for B1 and C1 22 00:01:13,000 --> 00:01:14,009 are the general number format. 23 00:01:14,009 --> 00:01:17,003 And we have a date time format here. 24 00:01:17,003 --> 00:01:21,002 And this is happening because openpyexcel applies 25 00:01:21,002 --> 00:01:25,005 certain default styles when you add content to a worksheet. 26 00:01:25,005 --> 00:01:28,009 So the text content has the normal style applied to it, 27 00:01:28,009 --> 00:01:32,001 while the decimal number has the general number style 28 00:01:32,001 --> 00:01:34,008 and the date time has a date style. 29 00:01:34,008 --> 00:01:36,009 Alright, let's try changing some of these. 30 00:01:36,009 --> 00:01:39,003 Let's go ahead and close this. 31 00:01:39,003 --> 00:01:42,001 Let's try applying some of the built-in styles 32 00:01:42,001 --> 00:01:45,002 that Excel uses to our cells. 33 00:01:45,002 --> 00:01:47,009 And again, I've imported the numbers module 34 00:01:47,009 --> 00:01:51,005 from the openpyexcel.styles module, 35 00:01:51,005 --> 00:01:54,006 and I'm aliasing that just as opnumstyle here. 36 00:01:54,006 --> 00:01:57,001 So you'll see why in a second. 37 00:01:57,001 --> 00:01:59,006 Let's go ahead and scroll down a little bit. 38 00:01:59,006 --> 00:02:03,006 So what I'm going to do is write sheet A1, 39 00:02:03,006 --> 00:02:06,001 and now I'm going to set the style property 40 00:02:06,001 --> 00:02:08,005 equal to the word Title. 41 00:02:08,005 --> 00:02:11,008 This is one of the built-in styles that Excel uses. 42 00:02:11,008 --> 00:02:15,009 And then I'll do something similar for each of B1 and C1. 43 00:02:15,009 --> 00:02:22,008 So for B1 I'll set the style to Calculation. 44 00:02:22,008 --> 00:02:29,003 And then for C1 I'll set it to Accent2. 45 00:02:29,003 --> 00:02:38,004 Alright, and then for B1's number format, 46 00:02:38,004 --> 00:02:42,004 I'll set that equal to opnumstyle.. 47 00:02:42,004 --> 00:02:43,009 And you can see that there's a whole bunch 48 00:02:43,009 --> 00:02:47,002 of formatting codes here in this list. 49 00:02:47,002 --> 00:02:49,004 And, you know, you can choose any one of these 50 00:02:49,004 --> 00:02:51,003 as the formatting option. 51 00:02:51,003 --> 00:02:54,009 So I'm going to format that as, let's see, 52 00:02:54,009 --> 00:03:00,008 format currency, US dollar simple. 53 00:03:00,008 --> 00:03:05,008 And then for the C1, 54 00:03:05,008 --> 00:03:10,006 I will set the number format equal to, 55 00:03:10,006 --> 00:03:14,004 and in this case I'm going to use format date. 56 00:03:14,004 --> 00:03:21,001 And I'm going to use the DDMMYY format. 57 00:03:21,001 --> 00:03:23,008 And let's also change the width and height 58 00:03:23,008 --> 00:03:25,006 of Row1 and column A. 59 00:03:25,006 --> 00:03:27,004 And the way I'm going to do that is 60 00:03:27,004 --> 00:03:33,003 by operating on the column dimensions 61 00:03:33,003 --> 00:03:36,008 property for the column. 62 00:03:36,008 --> 00:03:43,005 And for column A, I will set the width equals to 30. 63 00:03:43,005 --> 00:03:49,003 And then for the Row dimensions 64 00:03:49,003 --> 00:03:55,009 on Row 1, I'm going to set the height of that to 50. 65 00:03:55,009 --> 00:03:56,009 All right, and you can see 66 00:03:56,009 --> 00:04:00,000 that we're saving the workbook down here. 67 00:04:00,000 --> 00:04:00,009 Okay, let's save. 68 00:04:00,009 --> 00:04:05,007 Let's run this code again. 69 00:04:05,007 --> 00:04:09,008 All right, so we can see that StyledCell.xslx 70 00:04:09,008 --> 00:04:12,006 has been created because that's what we saved it as. 71 00:04:12,006 --> 00:04:17,004 So let's go out and open that in Excel. 72 00:04:17,004 --> 00:04:20,002 Alright, let's open this up. 73 00:04:20,002 --> 00:04:23,002 Okay, so you can see that when the workbook is created, 74 00:04:23,002 --> 00:04:27,007 and now I've got it open here, the format of A1 75 00:04:27,007 --> 00:04:30,006 has been set to the title style. 76 00:04:30,006 --> 00:04:34,005 We can see that the calculation style has been set 77 00:04:34,005 --> 00:04:38,004 to cell B1 and the month day 78 00:04:38,004 --> 00:04:43,001 and two-digit year style has been set on column C. 79 00:04:43,001 --> 00:04:45,003 And the visual styles have been affected as well. 80 00:04:45,003 --> 00:04:47,006 This is the calculation visual style, 81 00:04:47,006 --> 00:04:50,003 and then this is the Accent2 style. 82 00:04:50,003 --> 00:04:53,001 And again, if you look in cell styles, 83 00:04:53,001 --> 00:04:55,008 you can see where these styles came from, right? 84 00:04:55,008 --> 00:04:58,003 So here's calculation right here, 85 00:04:58,003 --> 00:05:00,008 here's accent two down here, here's title. 86 00:05:00,008 --> 00:05:06,006 So those are all the named styles that you can use, okay. 87 00:05:06,006 --> 00:05:10,006 So you can easily change the styling of cells to be 88 00:05:10,006 --> 00:05:13,001 one of the named styles, and that's pretty useful. 89 00:05:13,001 --> 00:05:14,006 But of course we're not limited 90 00:05:14,006 --> 00:05:16,005 to the built-in styles of Excel. 91 00:05:16,005 --> 00:05:19,003 Let's try defining our own cell styles 92 00:05:19,003 --> 00:05:21,005 and then applying them to the data. 93 00:05:21,005 --> 00:05:29,001 So let's close this and let's go back to our code. 94 00:05:29,001 --> 00:05:31,002 Alright, so now I need to define some styles. 95 00:05:31,002 --> 00:05:34,001 And the way that this works is it's a two-step process. 96 00:05:34,001 --> 00:05:37,005 First you define the styles that you want to use 97 00:05:37,005 --> 00:05:40,006 and then you apply those styles to the cells 98 00:05:40,006 --> 00:05:42,002 that you want to affect. 99 00:05:42,002 --> 00:05:44,005 So I'm going to define a few different styles. 100 00:05:44,005 --> 00:05:50,005 I'm going to define a font style that has italic font. 101 00:05:50,005 --> 00:05:53,005 So I'll name that italic font. 102 00:05:53,005 --> 00:05:57,007 I'm going to create a new Font object, 103 00:05:57,007 --> 00:05:59,008 and I'm going to set italic equals to true 104 00:05:59,008 --> 00:06:02,000 and size equal to 16. 105 00:06:02,000 --> 00:06:06,004 And again, these these objects were imported up here 106 00:06:06,004 --> 00:06:08,009 from the openpyexcel styles module. 107 00:06:08,009 --> 00:06:14,000 So I've imported font, alignment, border and side. 108 00:06:14,000 --> 00:06:15,008 So let's use each of those. 109 00:06:15,008 --> 00:06:18,003 Alright, I'll create a few more, such as colored text. 110 00:06:18,003 --> 00:06:21,005 So let's make a colored text style. 111 00:06:21,005 --> 00:06:24,001 And that's going to be another Font object. 112 00:06:24,001 --> 00:06:28,003 And the name is going to be Courier New, 113 00:06:28,003 --> 00:06:31,003 and the size is going to be 20 points. 114 00:06:31,003 --> 00:06:34,004 And the color is going to be, let's see, 115 00:06:34,004 --> 00:06:36,001 it's going to be an RGB value 116 00:06:36,001 --> 00:06:37,004 and it has to have an alpha channel. 117 00:06:37,004 --> 00:06:39,005 So I'll make it 00 118 00:06:39,005 --> 00:06:46,007 and then 00 for R and then G and the B is going to be FF. 119 00:06:46,007 --> 00:06:53,000 All right, I'll make one for centered text. 120 00:06:53,000 --> 00:06:56,006 This is going to be an alignment definition. 121 00:06:56,006 --> 00:06:58,005 So the alignment is going to be 122 00:06:58,005 --> 00:07:03,006 horizontal equals center, right, so center of the line, 123 00:07:03,006 --> 00:07:10,001 and vertical alignment is going to be equal to top. 124 00:07:10,001 --> 00:07:13,003 And then let's create a border style. 125 00:07:13,003 --> 00:07:17,009 So first I'll define a border side, 126 00:07:17,009 --> 00:07:21,000 and that's going to be a new Side object, 127 00:07:21,000 --> 00:07:23,003 and that's going to be border style. 128 00:07:23,003 --> 00:07:27,006 I'll make that one mediumDashed. 129 00:07:27,006 --> 00:07:32,003 And again, these are predefined inside of Excel. 130 00:07:32,003 --> 00:07:35,000 And then I'll create a cell border, 131 00:07:35,000 --> 00:07:38,007 and that's going to be equal to a Border object. 132 00:07:38,007 --> 00:07:43,004 And so the top is going to be equal to border side. 133 00:07:43,004 --> 00:07:47,003 And then the same thing with the right 134 00:07:47,003 --> 00:07:51,002 and then the left 135 00:07:51,002 --> 00:07:52,005 and then the bottom. 136 00:07:52,005 --> 00:07:58,002 Oops. 137 00:07:58,002 --> 00:08:03,005 Okay, and then once I've defined 138 00:08:03,005 --> 00:08:05,005 the styles I want to use, 139 00:08:05,005 --> 00:08:08,002 I can now apply them directly to the cells. 140 00:08:08,002 --> 00:08:13,007 So let's make the A1 cell. 141 00:08:13,007 --> 00:08:20,004 So I'll apply the font, it's going to be my italic font. 142 00:08:20,004 --> 00:08:24,006 And then let's apply to B1. 143 00:08:24,006 --> 00:08:29,007 Let's make that font the colored text. 144 00:08:29,007 --> 00:08:34,008 And let's also assign to B1, 145 00:08:34,008 --> 00:08:37,005 I'm going to assign the alignment 146 00:08:37,005 --> 00:08:43,006 to be the centered text that we created. 147 00:08:43,006 --> 00:08:50,003 And then on the C1 cell, 148 00:08:50,003 --> 00:08:52,001 let's set the border 149 00:08:52,001 --> 00:08:57,000 equal to the cell border that we created. 150 00:08:57,000 --> 00:08:58,009 Alright, so now we've got these custom styles. 151 00:08:58,009 --> 00:09:03,009 Let's go and comment out the previous built-in styles, 152 00:09:03,009 --> 00:09:07,005 but we'll leave the dimensions alone. 153 00:09:07,005 --> 00:09:10,002 Alright, so let's save. 154 00:09:10,002 --> 00:09:17,002 And now let's run our updated code. 155 00:09:17,002 --> 00:09:19,008 Alright, looks like everything worked. 156 00:09:19,008 --> 00:09:27,009 Let's go ahead and open up our Excel file. 157 00:09:27,009 --> 00:09:29,004 Alright, so let's see. 158 00:09:29,004 --> 00:09:32,001 It looks like, okay, we've got in cell A1, 159 00:09:32,001 --> 00:09:35,006 we have the italic font, that's good. 160 00:09:35,006 --> 00:09:39,006 Here we have the alignment 161 00:09:39,006 --> 00:09:42,004 set to top and centered within the cell. 162 00:09:42,004 --> 00:09:43,005 Okay, that's pretty good. 163 00:09:43,005 --> 00:09:46,001 And it's blue text, that's also good. 164 00:09:46,001 --> 00:09:49,000 And then finally we have the date formatting 165 00:09:49,000 --> 00:09:52,008 here in the last cell. 166 00:09:52,008 --> 00:09:54,005 Alright, so now I've only touched on 167 00:09:54,005 --> 00:09:55,006 a few of the examples here. 168 00:09:55,006 --> 00:09:59,003 So I encourage you to refer to the openpyexcel docs. 169 00:09:59,003 --> 00:10:03,004 In fact, let me just bring those up real quick. 170 00:10:03,004 --> 00:10:06,004 So here at this link in the openpyexcel documentation, 171 00:10:06,004 --> 00:10:10,001 you can learn more about applying some cell styles. 172 00:10:10,001 --> 00:10:12,005 I would suggest maybe trying some of your own experiments 173 00:10:12,005 --> 00:10:14,005 here to get a better feel 174 00:10:14,005 --> 00:10:18,000 for how the styling functions work before continuing on.