Automate Excel With Python - Python Excel Tutorial (OpenPyXL)

[Music] hello everybody and welcome to another youtube video so in today's video i'm going to be showing you how we can use python to automate excel specifically i'll be showing you how we can load an excel workbook how we can create a new one how we can modify the sheet names and select what sheet we're working on how we can look at the different cell values how we can create new cell values how we can style different cells merge cells and a lot of the just kind of common operations you want to do in excel now there's quite literally thousands of different things you can do with the module i'm going to show you how to use here obviously i won't be covering it all the point of this is just to get you up and running as fast as possible and show you kind of the most common and core operations that you need to know last thing i'll mention here everything that you can do in python in terms of like writing the code to automate excel you can do that manually in excel the reason you would do something like this is so that you can automate the creation of new workbooks or you can kind of search in workbooks for certain values or maybe you have an excel sheet and you want to read in that information into python and do something with that anyways you guys know your own use cases just wanted to tell you that anything you can do here you can do manually inside of excel so with that said let's get started after a quick word from our sponsor thanks to career karma for sponsoring this video career karma is a platform that helps connect learners like you to amazing boot camps and courses that help you get job ready for careers in tech i actually know some of the founders of this platform and have had the privilege of chatting with them about growing the program and community and helping people jumpstart their careers in tech if you're looking to be a web developer data scientist ux or ui designer whatever it is career karma can help you get into some great boot camps boot camps are really a great way to get hands-on experience and a lot of them are actually very flexible and don't charge you anything until you get a job you can join live audio rooms on the career karma app to speak with coaches and mentors who just got their first tech job you can download the career karma mobile app for free or check them out from the link in the description to help you get started the first thousand of my subscribers will get a free career coaching session with a career karma coach thanks again to career karma for sponsoring this video so the first thing we need to do when we want to start working with excel and python is we need to install a python module called open pi xl i'm going to assume you already have python installed on your system if you do then what you need to do is open up terminal or command prompt depending on the operating system you're on and type the following command you're going to type pip install and then open pi xl go ahead and press enter and that should install the module that you need you can see for me it's already installed now for some reason this doesn't work for you specifically if you're on mac try the command pip 3 install open pi excel linux as well try pip3 if pip3 doesn't work then what you can do is try python hyphen m pip install open pi excel and if that doesn't work you can try python 3 hyphen m pip install open pi excel if none of those work for you you're getting errors or it's saying this command doesn't exist or something then i'll leave two videos in the description that you can click on one for windows one for mac linux that will show you how to fix this command anyways i'm gonna assume at this point in time that you have now installed open pi excel so now we're just going to make sure our installation is working so open up some text editor doesn't matter what you're using i'm going to use sublime text for this video and try to import open pi xl now before i do this i'll just quickly note my kind of directory structure here so i'm inside of a folder that i've opened up in sublime text this is on my desktop inside of here i have an excel spreadsheet called grades.xlsx i have my tutorial.pi file this kind of a cheat sheet i'm looking at on my right monitor i have my working.pi file which is the one i'm going to work in and then this is kind of a temporary file that's created when i open this spreadsheet inside of excel anyways don't worry about that but just notice that all of my spreadsheets are in the same directory as my python files that's important and i'll discuss why in a second regardless though you need to run this file now once you have import.openpi excel obviously in a dot pi file i'm going to run this and notice that i get finished there was no error that popped up and that means that all was good now if this doesn't work make sure you're using the correct python interpreter and make sure again that you've installed open pi excel so now the pi excel is installed what i'm going to do is import something called workbook which we need to use to open an existing workbook so i'm going to say from and then open pi excel and actually what i'm going to do is import two things the first thing is workbook the second is i got to look at my cheat sheet here load underscore workbook okay so these are two things that we're going to use to either create a new workbook or to load an existing workbook so the first thing you need to do whenever using this module is you need to instantiate a workbook now again that workbook can be a brand new one or it can be one that already exists so what i'm going to do here is say wb standing for workbook is equal to and then i'm going to load an existing workbook so load workbook like that and then i need to put the path to the file of the workbook that i want to load so since this file is in the same directory as my python script i just need to put the name of the file so i can say grades.x lsx now it's worth noting that this module here only works with python or sorry not python excel spreadsheets that are version 10 or above so i believe that's like the 2010 version of excel or above and so the file extension must be this you cannot use the file extension xlsb which i think some people have i'm not quite sure about that but just make sure the extension is xlsx otherwise you will get an error okay so just a quick note here again the reason i can load it just this way is because that's the name of the file it's in the same directory if i created a new folder here so maybe i made a folder uh no i don't want to delete the folder i want to make a new folder make a new folder and just call it test and i put a spreadsheet inside of here then what i would have to do is test slash grades.xlsx you're typing the path from wherever your python script is now if for some reason this script was not in the same directory or any subdirectories within the current directory of the python script you would just type the absolute complete path to it so something on windows like c colon slash and then you know users slash tim slash desktop slash python whatever slash and then wherever the workbook was but here again this is in the same directory so i can access it with grades.xlsx now what i'm going to do is just close this because you cannot open a workbook if it's already open in excel you will get a problem and so now i'm going to run this and you're going to see that i don't get a problem all is good which means we were able to load this workbook now what i'm going to show you is how we can get the sheets from a workbook so this workbook right here only has one sheet but obviously excel workbooks can have multiple sheets and so you need to decide what sheet you're using and modify that sheet so i'm going to say ws standing for worksheet is equal to wb which is our workbook now that we've loaded in dot active now what this gives me is the active worksheet from this workbook so now if i print ws and i run this you're going to see that we get worksheet it's called grades there you go okay so now that we have that what i'm going to do is show you how we can access cell values from this worksheet so let me actually just open up excel again and let's see okay let's open up grades and notice that here uh we have some student names and we have their grades so the first thing i'll show you is kind of how we can access an individual cell value let's start with a1 to do that is really straightforward you can simply print out the worksheet at and then a1 now when you do this it's going to give you a cell and then if you want to actually look at the value of this cell you have to say dot value if you look at this it will give you the value which in this case is name so actually lied sorry when you're opening an existing workbook uh you can open one while it's open in excel what i meant to say is that you cannot modify the existing workbook while it's open in excel at least i'm pretty sure i think you'll probably get an error if you try to do that regardless what you can see here is that it says name obviously the value at a1 is name and so this is working just fine so now if i want to look at the value at a2 of course i could just change this to a2 and then you can see we get the value jump so there you go that's as easy as it is to actually look at the value of a cell in a workbook now if you wanted to change the value what you could say is ws at and then something like a2 and then you could say value is equal to and then change it to whatever you want so in this case maybe i'll change this to test so if i do this now when i run this you'll actually see okay it finished that it did not change in excel the reason it didn't change in excel is because i did not save this workbook so if i do make a change here i need to save this workbook manually to a file name before it will actually take effect and so what i can do to save the workbook is dot save and then the name of the file i want to save this to so i can say grades dot xlsx now you'll see what happens here when i try to save this when i run this and i try to save it notice we get an error now i can't see exactly what the error is let's say right here it doesn't quite say but the problem is that this file is already open and so i can't save to a file that's already open so i need to close this so i'm going to close that and then i'm going to try and now you can see it actually does work so now i've saved to grades xlsx if i open up excel and i look at grades we can see that we've changed this name here to be test uh in uh what do you call it a2 so there you go that's kind of the basics of opening and loading a workbook okay so now what i'm going to do is show you some operations related to sheets so what i've done is i've created a few new sheets here inside of this excel file so grade sheet 1 sheet 2 and sheet 3. now i'm going to show you how we can get these other sheets because right now i'm just showing you how to get the active sheet and obviously sometimes you want to access different sheets or maybe even create new sheets in a workbook i'll show you how to do all of that first thing to mention here though is that i kind of lied when i said that you had to use dot value to assign a value to a cell you can actually just do ws at a2 is equal to the value that will work as well however when you are accessing you do need to use dot value okay so now what i'll show you how to do is get different sheets so the first thing we can do here let me get rid of this save command is we can print all of the sheets in a workbook so i can say wb dot and then sheet names like that and what this will do is show me all of the sheets you can see we have grades sheet 1 sheet 2 and sheet 3. now if i want to access a specific sheet what i can do is say wb and then use this kind of syntax right here and inside put the name of the sheet i want in this case maybe sheet1 and when i do that you're going to see it gives me the worksheet named sheet 1. so that's how you access different worksheets i would then say ws is equal to if i didn't want the active sheet say sheet1 and that would mean now i'm doing all of my modifications or accessing with ws on sheet 1. now you can create new sheets as well so to create a new sheet what you can do is use the function which is called create sheet just have to look at my cheat sheet there so i can say wb.create underscore sheet i can say maybe we'll call this test and then what i can do is print out all the sheet names to make sure this worked so wb dot sheet names so let's run this and we can see that now we have another sheet named test now of course that's not going to update in here unless we actually save this workbook which i'm not doing right now i just wanted to show you how you can work with and access the different sheets all right so i've cleared the screen we're now going to create a brand new workbook and then what we're going to do is write in a bunch of cells and some information just so you can see how that works so to create a new workbook what you can do is say wb is equal to workbook this now initialized a new workbook now we need to get the worksheet so we're going to say ws is equal to wb.active this will just give us kind of the default sheet that's created whenever you create a workbook by default there's always one now let's change the title of this sheet so i'm going to say title is equal to data and now what i'm going to do is start writing in some information so of course what i could do is go you know ws at a1 is equal to and then ws at and you know b1 is equal to but that's not very efficient and well i don't really want to do that that's going to take a really long time so instead there's a few useful commands that we can use to insert a lot of data at once specifically rows of data so of course you can assign values like i was showing you there what you can also say is ws.append and you can append a python list of information so in this case i'll just say tim is great and then we'll do an exclamation point like that and we'll just keep the quotes consistent so if i do this now and now i decide to save the workbook so wb.save i need to give it a name let's give it tim.xlsx and i run this you're going to see all is good here and now if i go to open this worksheet or workbook i'm going to keep mixing up those names let's go browse let's open up tim you can see we have tim is great exclamation point in the uh kind of corresponding rows so that's appending meaning adding to the end of the worksheet so if i go here and i append this a few times and we can just kind of change this maybe to be like end just so that we can see the difference here now if i close this and i rerun this so i've just saved the document again let's go file let's open browse let's open tim okay we can see that we've added four rows of data and then the last thing that we added right is end and so that's going to be the last row in our spreadsheet so this is just appending to the end of the workbook now if i were to open this existing workbook and append it would add to the end of the existing data that is there but since we're creating a new workbook every time here kind of that's why what's happening is happening we only have five lines every time because while we're initializing a brand new workbook and then overriding the existing one that's already created okay so now that we know how to append rows of information to our excel spreadsheet what i'm going to do is show you how we can kind of loop through different cells in the spreadsheet so rather than actually just appending a bunch of them i'll show you how we can access and at the same time if we wanted to modify different cell values in a specific range now this example here i'm going to show you how we can loop through different rows and columns this is kind of just general python knowledge you can do this in any way that you want but i just want to show this to you in case you're unaware of how to do this so let's say instead of creating a new workbook i actually want to open the one that i just created so i'm going to have this function now load workbook and i'm going to load in what do we call this tim.x xlsx okay so now we won't bother changing the title what we will do though is we'll loop through this this cell data so to do this i'm going to say 4i in range and actually let's make this better let's say four row in range i'm going to loop in range 1 to 11. now what this really means is i want to look at row 1 through 10 because 11 is the end of the range and so we won't consider row 11. we'll start at row 1 we'll go up to 10 and then we'll stop as soon as we're done row 10. then what i want to do is pick what columns i want to loop through so if you go here right all of the rows are numeric all of the columns are letters and so i need to pick how many columns now in this case i'm going to say 4 and we'll say call in range and i just want to loop through 4 columns so i'm going to start at 1 and i'm going to go to 5. okay so now what i want to do is i actually want to get kind of the cell reference for this row and for this column so i have my row in this for loop i have a nested for loop doing the columns but this nested for loop for the columns is giving me an integer 1 through 4. it's not giving me a b c or d so how do i get it so that every time i get inside of this for loop here i have something like a2 a3 a4 so on and so forth right and then you know b2 b3 b4 because essentially what's going to happen here is for every row we're going to loop through all of the columns that we want and we need to get kind of these unique identifiers so to do that we can actually use a function here which will give us the kind of character that's represented by the column that we have now we can do this manually the manual way would be to say char is equal to and then chr of 65 plus call now what this does is gives us the character represented by an integer now 65 represents the integer uppercase a and so actually we would then have to change this range to be 0 4 because 65 plus call 0 would be a 65 plus call 1 would be b 65 plus call 2 would be capital c and so on and so forth that's what this function char does it takes some integer and gives us the character representation of it just so happens that 65 is uppercase a and then you can kind of go on from there and you can figure out what the rest of the letters are anyways that's the manual way now if we didn't want to do that manually what we could do is use the function from this open pi excel function which is the following so this function is called get column letter and the way you use it is get underscore column underscore letter you need to import it from open pi xl and actually it's not just from openpixel it's from something else so i'm going to copy this line i'm going to say from open pi excel dot and then this is utils i'm going to import get underscore column underscore letter and then what this function does is take an integer between 1 and 26 and gives you the character representation of it so 1 obviously being a and then i mean you guys know the rest so now if i change this range to 1 and 5 and i get the column letter of call this will give me the character represented by whatever this column number is so now what i can do is i can print out all the different cell values by doing the following thing i can say print and then i can say ws at and then i need to kind of combined this character with this row and there is a function in open pi excel that does this but i find it's easier just to do it manually so what i'm going to do is say char plus string of row so what this will do is give me whatever the character is so abc blah blah plus and then whatever the row is so 1 2 3 4 all the way up until 10. so this should give me all of the values that are in this excel spreadsheet and i don't want to save this let's run this and let's see what we get so notice we get a bunch of cells so we get a1 b1 c1 d1 a2 b2 c2 so on and so forth but we have not looked at the values so sorry i have to do dot value here and then this will actually give us what the value is so in this case you can see we have tim is great tim is great tim is great and then a bunch of nuns because well obviously nothing is in all those other cells that we looked at now if i change this workbook to be grades and we run this you're going to see we get some different data so what is grades empty i guess we must have accidentally cleared grades for some reason so i guess there's nothing in there uh but anyways you get the idea this does work i guess grades uh we we must have emptied somehow or maybe because it's open oh yeah it is empty uh or are we not looking at the right sheet in grades or something you know i don't know exactly why grades isn't working but for some reason i think we're on the wrong sheet and so was it giving us the wrong information okay so regardless that works that's how you can kind of loop through the different values in an excel worksheet you can loop through the rows and the columns in this kind of fashion here using this nested for loop structure you can get the column letter by importing that from open pi excel and then you can do this kind of combination of combining the character with the row and then that will actually allow you to access all of those different cells and of course we could change these cells as well in fact maybe we should do that let's say ws and then char plus string row is equal to and let's just make this equal to actually we can make it equal to this itself ws or char plus string row just so we can see how this works so let's run this we do need to save this worksheet so our workbook so wb.save and let's save this tim.x xlsx we now need to close tim okay so close that and let's run do we get any problems here no it looks like everything is good and now if i go and open this so file let's open tim you can see that we're getting kind of their corresponding positions in the cells and so that does indeed work okay so next thing i'll show you here is how we can merge cells together and then after that i'm going to show you kind of a in-depth example of how we can write some data into an excel spreadsheet and style the cells and all of that fun stuff okay so merging cells is actually really easy to do all you have to do is say ws dot merge underscore cells and then what you need to do is pass a range of cells that you want to merge so in this case i could pass a 1 2 and then i guess we would do d4 or sorry d1 and this would then merge the cells from a1 to d1 so just like you would do in excel when you're kind of typing out a range it's the exact same thing here when you're merging cells you pick the range of the cells you want to merge and then well it will merge them so let me make sure tim is closed it is so let's run this and let's see if we get any issues looks like everything is good so now if i go file open and we open tim you can see that we've merged these cells together and it kept the data from the first cell got rid of the data from the rest of the cells now let me close this to unmerge cells is kind of the just opposite of what i showed you here we can say ws dot and then on merge underscore cells and then the range of the cells you want to unmerge so a1 to d1 i think that's correct yes it is so let's run this now and all is good if we go here and we go file and we open tim we can see that we have now unmerged those cells but we did lose the data that was in them previously obviously we have no way of getting that back now you could do this to like say d2 and then it would actually merge kind of like a square region rather than just like one row or sorry rectangular region but it would be more than one row so let's just try this merge cells a1 to d2 okay now if we go file and open and we open tim you can see that now it merged kind of the first two rows together okay so now what i'm going to do is show you how we can copy and move portions of the worksheet i will also show you how we can insert empty rows and delete rows so let me get rid of this merge cells first thing i'll do is show you how we actually insert an empty row this is really easy you can say ws dot insert then you can insert this at whatever position you want so let's say i want to insert a row at position seven this means we're going to insert an empty row after row seven so we could do this twice and now if i make sure tim is close it is and i run this okay oh what's the problem here ws dot insert uh worksheet object has no attribute insert oh sorry this is insert underscore rows that's my bad insert underscore rows okay so now let's try this and okay that's all good so now we can open so file open tim and notice that we have kind of two blank rows now here because we inserted now in the same way we can delete rows we can say ws dot delete rows and we could delete at row seven so let's see what happens now if we close tim and we try to do this okay no problem let's go file and let's open tim and now you can see we deleted one of those empty rows okay so in the same way that we could insert rows we can also insert columns so i can say ws dot and then insert underscore calls i can pass to this the column at which after i want to insert an empty column so i could say b and then oops do me to do that if i run this we can see we're getting a problem here what is it saying a string object cannot be interpreted as an integer okay so that means that instead of passing b we would need to pass two i wasn't sure if this took any string or it took in the integer and so rather than giving it you know a b c d you would give it one two three so let's give it two when we do this okay all is good so let's go file let's open and let's open tim and then notice that we inserted this empty column here in column b awesome and sorry i guess i was saying after this comment inserts it sorry it inserts at that column that's my bad okay now that we've inserted the call we can do the same thing we can delete it so we could say delete calls and we can delete two so let's close this and let's run this okay now if we go file and we open tim we can see that empty column is now gone and it shifted all of those other columns after now we're sorry back to where they were before okay so now what we can do is we can actually shift a range of cells so let's say we want to take all of these cells in maybe the c and the d column and we want to move those over to be an e and f well to do that is pretty straightforward we can use the function move underscore range now move underscore range takes a range and so i guess the range that we would have here would be c1 to d11 so we're going to say c1 to d11 and then we need to pick how many columns over and how many rows we want to move it by so i can say rows equals and then i can move this say up one row to do that i would say negative 1 that means go up 1. if i made this two this would move it down two rows so let's actually leave it at two and then for columns same thing if i wanted to move it to the left i would say you know negative two to move it over two columns if i wanted to move it to the right i would just put a positive number for the number of columns the right i wanted to move it so in this case we'll just do rows two calls two let's save that let's close tim let's rerun uh we don't get any problems that's great let's open this again and then notice that we've kind of shifted this over and moved that range of cells all right so that's all i had to show you for kind of the core operations now we're going to bring all of this stuff together and i'm going to show you how we can write some data into kind of a new excel spreadsheet all right so i've just pasted a bunch of data here you can see it says data i have a bunch of names these are the names i was using in that grades spreadsheet that you saw i'm going to show you how we can create that grade spreadsheet so if you look at this here you can see we have bolded names we have all the names here we have all of the grades and then we computed the averages of all of these grades down here i'm going to show you exactly how we can create this using simply python code and so if you want to take this code i will leave a link to it in the description it should be a github repository and well it's going to have all this data that you can copy and all of the finished code as well so we're going to keep these imports up here however i am going to import one more thing that we're going to use to actually style our cells i'm going to say from open pi xl dot styles import font and i'll show you how we can use this kind of font object to modify and make you know bold and italic cells and change the color and all that okay so we have all of this data and again what i want to do is write it into the spreadsheet so the first thing i need to do is write kind of the headings right so we had name actually i guess i can just show you let's look at it here name math science english gym all of that so if we want to write all of the headings well first we need to actually create a new workbook so i'm going to say w b is equal to workbook i'm going to say ws is equal to wb dot active and then let's change the name so ws.title is equal to grids awesome so now that we have the worksheet let's write in our headings so i'm going to say headings is equal to a list and this is going to be name and then i want to get all of the different subjects here so rather than manually writing out these subjects what i'm going to do is kind of grab them from my data so i'm going to say data and then this is going to be at joe i'm just picking an arbitrary student so i'm picking joe i could pick build tim whatever i want and i'm going to get all of these keys here that are inside of this dictionary store by jump so i'm going to say data add joe dot and then keys now i'm going to convert this to a list so i'm going to say oops no this is not exactly what i wanted to do we're going to do this name and then plus data at joe dot keys we'll see if that works but what this should do is combine this list with the list returned of all of the keys that are inside of joe so that should give us math science english and gym okay so now we have our headings so let's append this into the work worksheet i'm going to say ws.append and i will append headings and now that i have all of my headings i want to start adding all of my students so i want to loop through all of my students here and i want to well add them into the workbook so i want to have their name and i want to have all of the different grades that they have for each subject so to do this i am going to say let's go for person in data and then we're going to say that their grades are equal to and this is going to be data at person now what i'm going to do is get all of the values here so i'm going to say grades is equal to data at person dot values so what this gives me is instead of all of the keys which would be math science english and gym gives me all the values so 65 78 98 and 89. now what i'm going to do is say ws dot append and i'm going to append the name of the person which in this case is going to be person plus and then their grades so what i will end up writing and we can look at this as an example is whatever the person's name is and then all of their grades will come after in all of the subsequent rows okay so let's test this first before we go any further so let's save this workbook wb.save and let's save this as new grades.xlsx okay let me just close tim we don't need that anymore let's run this and let's see if we get any problems and we says we do get a problem concatenate can only concatenate list not keys and so what that means is we need to convert this to a list it's not just my bad i guess this is not a list type by default but we can convert it to a list by doing that and then we're probably going to have the same issue here with values so let's do that again and make this a list okay so now that we've done that let's run and there we go we don't get any issues let's now open this file so file open browse and new grades okay so there you go we can see we have name math science english gym we have all of the names and we have all of the grades in the correct categories awesome and let me make sure this is in the correct category 100 160 okay 30 25 45 100 okay i'm satisfied looks good okay so now that we have that what i want to do is calculate the average of all of these grades so really of all of these columns now the columns that i want to calculate the average for is oops that's the wrong document here is column b c d and e and so what i'm going to do is set up a for loop i'm going to say 4 and we'll say call in range and then this will be in range 2 because we want to start at column b and this will go to and then rather than doing this statically by just typing in the number 6 i will actually type in what is it a dynamic thing that gives me the number of subjects that are here so i'm going to say 2 and this is going to be the len of and this will be data joe i'm just picking joe because that's going to give me all of the subjects here and then i will add 2 to this the reason i'm adding 2 to this is since we're starting at column 2 i want to go what however many subjects we have more than column 2 and so i add 2 to that at the end and what this will give me is 4 plus 2 it should be 6 so my range will be 2 6 which means we'll do 2 3 4 and 5. and if we look here that's correct 2 3 4 5. awesome so now that i have that what i'm going to do is kind of use what i did previously i'm going to say that the char is equal to and then do we have this imported yes we do get column letter i'm going to get the column letter and this is going to be from call and then i'm going to add that to whatever the row number is now the row number is going to be a static number that row that we're going to be inserting this in is seven and so what i will say is ws at char plus and i guess i don't have to do string i can just do seven so this will give us you know b7 c7 d7 e7 so on is equal to and then i want to put a formula now the formula i want is the sum so i'm going to say this is equal to the sum and then i need to put in the range of the values that i actually want to sum together so this is where it gets a little bit complicated i'm going to use something called an f string which i'll explain in one second but let me just type all this out first and then i will kind of explain what i'm doing so i'm going to say f string of equals sum and then i am going to do what should this be this should be char plus and then in single quotes this would be two and then this is going to be char plus in single quotes 6 and this is going to be divided by and then this would be the number of students that we have and so this would be the length of data okay so i know this is a little bit confusing because i just did a lot here so an f string is something you can use in python version 3.6 and above if you don't have 3.6 or above this won't work what this allows you to do is actually evaluate an expression by putting it inside of curly braces so these curly braces here will be converted automatically to a string so the char which is going to be whatever column i'm in plus 2 so this would be you know b2 and then colon to b6 this is a range b2 to b6 is an example of something that would be here and then i'm dividing that so whatever the sum of all those values is by the number of students that i have which is represented by the length of my data which in this case is 5. and so what that's going to give me is the equation of you know like b2 to b6 divided by 5 and then c2 to c6 divided by 5 and so on and so forth and so anyways i think that's actually good now if i save this and i make sure that new grades is closed and then i run this we should be good i don't think we're getting any problems so now let me open this let me go file open and let's open new grades and now notice that we're getting all of our averages calculated here right so we can see that this is equal to sum of b2 column b6 over five right and then we have sum of c2 colon c6 over five sum of d2 colon d6 over five so on and so forth now i did this kind of statically by typing 2 and typing 6. i could change this to be more by more dynamic by using the length the data and the number of subjects and all of that but for now i'm just going to keep it simple so that we don't spend too much time anyways there you go we've just added now all of the averages the last thing i want to show you is how we can actually kind of style ourselves now i won't get into this too much but i'll show you uh kind of simply how we can just make our cells bold so if i want to actually make all of my headings bold i need to do the following so i'm going to do a for loop here and what i'm going to do is loop through the very first row in my excel spreadsheet and i'm going to make every single one of those cells pull now unfortunately due to a limitation of the file type of xlsx you cannot modify an entire row or column style from python you need to modify every single individual cell so it'd be a lot easier if i could say you know row one make it all bold i can't do that instead i have to go through every cell in row one and make every cell in row one bold if that's what i wanted to do and so i'll show you how we can do that but we've kind of been doing it already i'm gonna say four and we're actually gonna say call in range then the range is gonna be 1 2 and then i guess how many columns do we have here i'm just going to manually type it in we have one two three four five so we will go up to six so i'm going to say four call and range one six i'm going to say ws at and we're going to say a and then this is going to be or sorry this shouldn't be a this is actually going to be get column letter so i'm going to say get column letter of call and then we're going to add and we'll simply add b string 1. so this is giving us you know a b c d so on and so forth and then we're looking at the first row for every single one of those columns and we're going to say dot style or sorry dot font my bad is equal to and then we're going to say font remember that i imported font up here from open pi excel dot styles and then we're going to say bold is equal to trip now there's a ton of other styles here as well what this is going to do is just change the font style to be equal to a font that is bold now this will use the default font you could change the actual font too i'm just leaving the font default but i'm changing it to be the bold version right and then i can change the color as well and say color is equal to and then there's a whole bunch of different options i'm going to refer you to the documentation if you want to do that in fact i have the documentation open here i'll leave a link to it in the description but if i go to where's styles here and there's something for changing the styles working with styles you can see here that there's like all kinds of colors we can change so let me just copy one of these let's find one that's like a nicer looking make it like a blue or something i'll copy one of these colors here and i'll show you what it looks like if we use this so if i change that to be 0099 ccff this should then change the style of our cell so let me make sure that new grade is closed let's run this see if we get any issues looks like we're all good and now i can go to excel and i go and open and let's open new grades and now notice we've changed the color of all of these values here and we've made them bold all right so i think with that that is going to wrap up this video hopefully this gave you a general idea of how to work with excel in python as i mentioned there is this documentation here which just has so much more information that i can provide you in this video i just wanted to give you guys something so you could get started you know how to open something knew how to you know read sell data write cell data and well anyways again i hope you found this useful if you did make sure to leave a like subscribe to the channel i will see you in another youtube video [Music]

Channel: Tech With Tim

Views: 1,086,089

Keywords: tech with tim, python, python programming, python projects, python excel, python excel automation, python excel tutorial, python excel manipulation, python excel data processing, excel, excel automation, python excel data, automation, openpyxl, install openpyxl, openpyxl excel formatting, installing openpyxl, openpyxl automate, excel openpyxl, python openpyxl tutorial, using python to automate excel, python automation projects, excel python automation, openpyxl tutorial

Id: 7YS6YDQKFh0

Length: 38min 1sec (2281 seconds)

Published: Thu May 13 2021