How to Apply Font Style in Excel using Python. Openpyxl Tutorial #5

hello everyone in this video we will learn how to apply text formatting to excel file using open by excel to begin with first we need to import our open pi excel module and then create a workbook and worksheet object so i'll say open pixel.nodeworkbook and here i have to pass my excel file and create a worksheet object as well that i can say wb and i want to grab seat one now you may wonder where this coming from so i have balance dot xl excel sheet already created and in that i have two sheets sheet one and score so i have imported my excel book here and created a worksheet object for that particular sheet1 if you want to learn about how to import this workbook and worksheet object please go back to first video in this series to work with axle styling open pi excel provide us two styling modules so we need to import that first i can say from open pi excel dot styles import font and color we can use this too to create a specific style of the text and apply it to a excel sheet so first thing we will do is create a font style so to do that we can use this font class provided by open pi excel and you can see in here it takes number of argument and we will use some of the most important argument from this such as name color is commonly used a size represent font size we can use this athletic bold underline and i think sometime we use shadow and condense so we will use some of this style define that and apply that to our excel sheet now let's go back to our excel sheet for a moment in here i want to apply style to this particular cell a4 what i wanted to do is change this font size and change font family so in open pi excel we can do that using a name property and size property also change color of that font so currently it's black i will make it something lighter we can use that using a color in a name property we can here enter any font name that is defined in this uh dropbox so let's pick this shock box so i'll just say chalk box chalkboard actually let's make sure i didn't miss palette yep chalkboard and also i want to increase this font size to 14 so i'll specify that here 14 and this color we have to pass the hex code so you can just google it and find the hex code for any color actually here i am using this website rapidtable.com this will provide us a hex code and rbg code for particular color here we you can basically toggle you know go up and down and select the shade and here also you can you know scroll this circle and select any color and that will give this hex code for that particular color so i will select that hex code and add that in my code now this is font to style we defined let's save it in a variable i'll say it font style is equal to this here we just define a style we have to apply this to particular set to see the change so in our excel sheet first you have to grab this a4 cell we know how we can do that uh we can simply say ws and here just specify the cell location and let's save that in variable called a4 now we have font style and our cell location so i can simply say here a4 dot style is equal to font style and since we are making a change in actual c we always have to send w dot save now let's run our python code outside python and file name is font format dot pi if i run it oops we get an error oh i get the error actually this needs to be e4 dot font not style now let's rerun our python file again so let's go back to our excel sheet and see if that change is applied and looks like it does so if i select a4 cell you can see here we have style of chalkboard and font size is applied to 14. uh we can make more changes here we can also add italic so i can say italic is equal to true and now if i rerun my code and let's close this one and reopen our balance sheet you can see it's italic so if i click on this one you will see this italic symbol is now selected we can also make it bold so right now it's just italic so let's make it bold as well so let's close this actual seat and pass another parameter it's getting a little bit cramped here so i'll write that in a new line and i'll say intellect and bold equal to true as well let's read another code did not get any error again so let's go back open our excel sheet and select that a4 cell and you can see now it's a font style is chalkboard it's 14 point bold and italic and that's what we have defined now again uh this is for one particular cell but we can apply tiling to entire excel sheet enter column or rows let's apply a particular style to this uh double balance row all of this element first we need to define a style i will uh comment this out first and let's first define our style so i'll say font what font we want to use so let's go back in here and use something uh yep let's use this rim kofi so let me select that i'll make this regular font again so now i select this it says ariel i'll pass the name size let's make it tell a color we can go back to our hex code website and select any color so let me open chrome and in here let's make it reddish yep let's make it right select this hex code and i can directly pass that in here so i can say underline equal to true let me come back to this next line let's do a strike through strikethrough equal to true now what i can do is create a for loop and apply that style to and try column we have seen how can we select entire column using a loop in here we want to select a value from this third column start from second row until the ninth row so we can use on that for range function and we want the variable from second column to nine column but if you want to use nine we have to add here 10 all right that's good and in here we can use our dot cell method and w dot s cell i can specify the row number here so let's say row one and column number here column we want to make change in a one two and three third column in here and i want to apply the style is equal to so we have defined the style but we haven't applied the variable so let's quickly give variable here i'll say style equal to and again this supposed to be a font so i can see here column call style and apply it here now we don't want to just apply this to first row we want to apply to all of the row so instead of using one i will change this to i so when loop goes through every time this value will change and uh vice versa it will apply style to all of the columns so it will apply to first second third fourth and up to ninth all right one last step before we do this we have to save our excel sheet otherwise we will not able to see the change so i'll go in here and add w dot save let's run our python file now and we get an error it's in line 13 oh i get it so in underline we have to specify the actual underlying type so in here we can only specify this many types uh let me copy and paste it here so you can see it better we can only specify either double single double accounting or single accounting let's use single and i will comment this out and i will add this in description so you don't run into same issue let's clear the console and read a number of python file oops i forget to save my python file again so let's save it and re-run it this time we did not get any error so let's go back to our excel sheet and see if that change is applied i'll open it and there you go it's really ugly to see so let me make it a little bit bigger and you can see the textile is rim roofie that's what we specify the size is 12 the color that is one we took from that website is really reddish underlying it has single underline and it has strike through so we can see here all of those changes are applied so this is removing 12 single underline there is strike two and there is an underline so that's how you can apply font style using open pi excel that's all for this video i'll see you next one thank you

Channel: Python Bits

Views: 12,221

Keywords: python tutorial, python programming, learn python, python for beginners, write excel in python openpyxl, excel read and write in python, openpyxl tutorials, read excel files in python, python how to read microsoft excel files, openpyxl, microsoft excel python, excel python, python xlsx, python openpyxl, openpyxl tutorial, font style in excel

Id: fYVmsoqUWyQ

Length: 11min 30sec (690 seconds)

Published: Mon Jan 18 2021