Working with Cell Styles Using Python

Python 3: Automating Your Job Tasks Superhero Level: Automate Excel Tasks with Python 3
9 minutes
Share the link to this page
Copied
  Completed
You need to have access to the item to view this lesson.
One-time Fee
$99.99
List Price:  $139.99
You save:  $40
€91.90
List Price:  €128.67
You save:  €36.76
£78.70
List Price:  £110.19
You save:  £31.48
CA$136.14
List Price:  CA$190.60
You save:  CA$54.46
A$149.21
List Price:  A$208.90
You save:  A$59.69
S$134.58
List Price:  S$188.42
You save:  S$53.84
HK$780.18
List Price:  HK$1,092.29
You save:  HK$312.10
CHF 90.89
List Price:  CHF 127.25
You save:  CHF 36.36
NOK kr1,073.95
List Price:  NOK kr1,503.58
You save:  NOK kr429.62
DKK kr686.39
List Price:  DKK kr960.97
You save:  DKK kr274.58
NZ$162.99
List Price:  NZ$228.20
You save:  NZ$65.20
د.إ367.26
List Price:  د.إ514.18
You save:  د.إ146.92
৳11,707.96
List Price:  ৳16,391.62
You save:  ৳4,683.65
₹8,329.22
List Price:  ₹11,661.24
You save:  ₹3,332.02
RM468.70
List Price:  RM656.20
You save:  RM187.50
₦146,985.30
List Price:  ₦205,785.30
You save:  ₦58,800
₨27,706.17
List Price:  ₨38,789.74
You save:  ₨11,083.57
฿3,624.43
List Price:  ฿5,074.35
You save:  ฿1,449.91
₺3,224.01
List Price:  ₺4,513.75
You save:  ₺1,289.73
B$510.40
List Price:  B$714.59
You save:  B$204.18
R1,807.25
List Price:  R2,530.23
You save:  R722.97
Лв179.86
List Price:  Лв251.81
You save:  Лв71.95
₩135,425.45
List Price:  ₩189,601.05
You save:  ₩54,175.60
₪370.41
List Price:  ₪518.60
You save:  ₪148.18
₱5,770.97
List Price:  ₱8,079.59
You save:  ₱2,308.62
¥15,565.94
List Price:  ¥21,792.94
You save:  ¥6,227
MX$1,660.69
List Price:  MX$2,325.03
You save:  MX$664.34
QR362.75
List Price:  QR507.87
You save:  QR145.11
P1,354.73
List Price:  P1,896.68
You save:  P541.94
KSh13,098.69
List Price:  KSh18,338.69
You save:  KSh5,240
E£4,690.53
List Price:  E£6,566.93
You save:  E£1,876.40
ብር5,727.42
List Price:  ብር8,018.62
You save:  ብር2,291.20
Kz84,733.22
List Price:  Kz118,629.90
You save:  Kz33,896.68
CLP$89,440.82
List Price:  CLP$125,220.73
You save:  CLP$35,779.90
CN¥722.24
List Price:  CN¥1,011.17
You save:  CN¥288.92
RD$5,826.59
List Price:  RD$8,157.46
You save:  RD$2,330.87
DA13,436.03
List Price:  DA18,810.98
You save:  DA5,374.95
FJ$222.79
List Price:  FJ$311.92
You save:  FJ$89.12
Q776.81
List Price:  Q1,087.56
You save:  Q310.75
GY$20,917.88
List Price:  GY$29,285.87
You save:  GY$8,367.99
ISK kr13,827.61
List Price:  ISK kr19,359.21
You save:  ISK kr5,531.60
DH990.33
List Price:  DH1,386.51
You save:  DH396.17
L1,760
List Price:  L2,464.07
You save:  L704.07
ден5,654.52
List Price:  ден7,916.55
You save:  ден2,262.03
MOP$803.51
List Price:  MOP$1,124.95
You save:  MOP$321.43
N$1,819.78
List Price:  N$2,547.77
You save:  N$727.98
C$3,674.63
List Price:  C$5,144.63
You save:  C$1,470
रु13,329.38
List Price:  रु18,661.67
You save:  रु5,332.28
S/371.45
List Price:  S/520.05
You save:  S/148.59
K388.01
List Price:  K543.23
You save:  K155.22
SAR374.96
List Price:  SAR524.96
You save:  SAR150
ZK2,538.86
List Price:  ZK3,554.51
You save:  ZK1,015.64
L457.57
List Price:  L640.62
You save:  L183.04
Kč2,271.57
List Price:  Kč3,180.29
You save:  Kč908.72
Ft35,609.88
List Price:  Ft49,855.26
You save:  Ft14,245.38
SEK kr1,071.59
List Price:  SEK kr1,500.27
You save:  SEK kr428.67
ARS$88,666.13
List Price:  ARS$124,136.13
You save:  ARS$35,470
Bs690.83
List Price:  Bs967.19
You save:  Bs276.36
COP$380,678.73
List Price:  COP$532,965.46
You save:  COP$152,286.72
₡51,169.76
List Price:  ₡71,639.71
You save:  ₡20,469.95
L2,462.78
List Price:  L3,448
You save:  L985.21
₲749,130.70
List Price:  ₲1,048,812.96
You save:  ₲299,682.25
$U3,853.49
List Price:  $U5,395.05
You save:  $U1,541.55
zł392.01
List Price:  zł548.83
You save:  zł156.82
Already have an account? Log In

Transcript

Hi, and welcome to this lecture. Inside this lecture we are going to talk about applying different styles to the cells inside our sheet. In order to do that, first of all, I'm going to select a specific cell inside this sheet that we are going to work on from within the Python interpreter. So let's say that we choose cell B eight, which is Richard right here. What we want to do is to use the open pi Excel module and change the font color of the cell. Also, we want to make the text bold and italic as well.

We are also going to insert a background color for this cell. On top of that, we are going to modify the border for the cell and we're going to add a different border from the default that you're seeing right now on the screen. And we're also going to edit the alignment of the text inside the cell from center which is the setting we're seeing right now. All the texts in the cells inside this ship centered to, for example, let's say Left Alignment. So the text inside this cell B eight will be aligned to the left. And we are going to do all that using Python and the open pi Excel module.

Okay, so remember that we are going to use the cell B eight in order to apply all the changes all the style changes I have already mentioned. And now I'm going to close the file. And I'm going into the Python interpreter. First of all, I'm going to import open bi Excel. And we're going to have a look at open pi Excel that styles. So you can see that here we have multiple classes and attributes that we can use in order to change and edit the style within our sheets within our cells.

We are going to use some of these classes and attributes inside this lecture. For example, we are going to use alignment, border color, also font and pattern fill. For example, To perform the desired style changes on our cell, okay, now since we are going to access each of these classes in the code that follows, and because we want to save some space into our code, and not be required to always specify open pi Excel dot styles dot, and now the name of the class, let's say border. In order to avoid this into our code, I'm just going to use from open Vy XL dot styles, import all the names within this namespace. Okay? And now we can just use the name of the class for example, border whenever we want to access and use it.

Okay, one more thing before moving on to the code. I have attached a link to this lecture to the official documentation of open pi Excel regarding styles. And you should check that out in order to learn how to use each of these classes. Each of these attributes regarding style editing in Excel, okay? Now, before anything else, we should load our workbook. And in order to do that, I'm going to use the same line of code as we did earlier in this section, including the full path to the file.

The next thing we should do is to reference our sheet, and we're going to consider the fourth sheet inside the workbook, which is employee data. So let me reference this sheet. Okay, and finally, we should reference we should select the cell within the sheet that we want to work on. In our case, as I said, that would be B eight, so cell equals sheet of B eight. And at this point, we are ready to start applying the classes from within open pi Excel dot styles. And first of all, we are going to change the color of the font inside this cell.

And also we are going to make the text bold and italic at the same time. In order to do that. I'm going to create this variable let's call it font equals the name of the class inside open pi Excel dot style. That would be font. So font, and in between parentheses we are going to insert several arguments. For example, color equals colors, dot red.

And this is the way in which to set the color of the font. Comma, we said we want the text to be bold, so bold equals true, and also italic equals true. Okay, let's hit Enter. And now we should apply these changes to our cell. So we have cell dot font, where font is a predefined attribute of a cell inside the open pi Excel module. So cell dot font equals our variable right here, font.

Okay, so we just set the font color and bold and italic for the text within the cell. The next thing we should do is to also add a pattern fill a background color for our cell. And in order to do that, I'm going to create a new variable called field. equals the name of the class from within the open pi Excel module. And that would be pattern fill right here. So pattern fill, and in between its parentheses, we have fill underscore type equals solid.

So we want a solid color in the background, comma, and now BG color equals, and now we will input a color code for a random color I already picked. So this is it. Okay, Enter. And now in order to apply this pattern to our cell, we should simply use cell dot fill, which is another attribute of a cell equals fill. Okay, next on our list is the border. For the border of a cell, we can change both the border style, which refers to how thick the border is, if it's a double border, or a simple border and so on, and also the border color by also specifying color code as we did before for the pattern fill, okay, in order to do that, we are going to use the border class from within open pi Excel dot styles.

So let me show you how to do that. Since this is a longer command, I will copy and paste it right into the interpreter. So we have border, this variable right here equals border the class. And now left equals So the left border border style double. So we are going to change the border style to a double border and color this color right here, which is referenced by this code. Next we have the right border, also a double border and with the same color, and finally we are going to apply the same settings on the top border right here, and also for the bottom border of this cell.

Okay, let's hit Enter. And now let's apply these border settings to our cell. So cell dot border, we are using the border attribute equals border, the variable that we just defined. Okay? Finally, we also said that we want to set the alignment of the text inside the cell. So for that, I'm going to create another variable, align equals.

And now we are going to use alignment from up here. So alignment, and I'm going to specify horizontal equals left instead of the Center Alignment that we currently have inside the sheet. Okay, of course, we should apply this setting as well to the cell. So cell dot alignment, equals align. Okay, great. Now, the last thing we should do before checking the result is, of course, saving the workbook by using workbook dot save and the full path to the file, Enter.

And now before opening up the file and checking the results, let's do a short recap and see what changes have we applied to ourselves. So first of all, we should have a different font color which is red, the font inside the cells should be bold. italic as well, the background pattern for this cell should be solid. And also the color should be changed as well. We should also have a double border for this cell with a different color. And also the alignment of the text inside the cell should be left.

Okay, now let's open up the file. So font color red checked, text is bold checked. Text is italic checked. Pattern fill is solid checked, background color has changed checked, the border style is double. Let's zoom in and check that as well. I don't know if you can see this, but the thickness of this border is greater than for the rest of the cells and also the color of the border has changed.

And finally, the alignment of the text inside this cell is to the left which is also correct. Okay. Again, you can check out the link attached to this lecture in order to learn more about Cell Styles using open pi Excel. And I will see you in the next lecture.

Sign Up

Share

Share with friends, get 20% off
Invite your friends to LearnDesk learning marketplace. For each purchase they make, you get 20% off (upto $10) on your next purchase.