hello Hello, everyone I was white this video to share with everyone how to use a third party called the openpyxl Python module to automate the operation of Excel are often so if you need to use Excel's people in this movie might be very good for you we then read on Ok to remind you if you have not learned Python, then you can see me on the first description field inside Python good instructional videos we'll just start where we need to use a third-party module called openpyxl so let's take him directly enter install pip install openpyxl installed after we first introduced him directly to the test execution can come in and found no error occurs it means that we really installed in the terminal here which we do not need to whole modules come in both introducing only need to use inside the two things we write from openpyxl import workbook and then there is a load_workbook this workbook is an Excel file meant so if we want to read an Excel file can be written like this load_workbook then a pair of small brackets can write it on the Excel file we want to read his path in this pair of brackets here I have prepared an Excel file with us that he is a Python file on the same data inside the folder inside so I can directly fill his profile name excel.xlsx good to remind everybody here about Openpyxl he only supports Excel 2010 or later, so if you are here in Excel format your Excel file format by 2010 after a good little wrong he might remind you where I put him inside a variable called a workbook wb is the abbreviation of us now read Excel files also tell him what we want to use a worksheet which does that mean we have a good look at him Excel file is like this long before I was in there already populated with some of the information he has seen bottom left corner of a worksheet in an Excel file which you can have a lot of worksheets so now after reading the Excel file also tell him what we want to use a worksheet here because now I only have a worksheet so I'll use the default we will be able to read the default worksheet also wrote wb.active that he is as we opened the Excel worksheet in which one he will choose which one to use that as well and I put him inside a variable named ws is a good acronym worksheet directly to him, we printed out on execution You can see that he told us that this ws is a w orksheet is a worksheet his name is called a worksheet that is now Ok to use this worksheet good he is already using this worksheet of how to write that if we want to read the information inside it is assumed that 177 Well he was in the 177 field B3 do so here we can write ws then a pair of brackets written inside the cell that you want to read, he was a field in which his fields so here is B3 we wrote B3 good execution can see him telling us ws then B3 cell is a cell that is a cell of a cell is a cell B3 and then he was in this position a worksheet inside of us want to read him inside our wrote a .value execution can see he told us this inside information is that 177 177 B3 B3 well if we want to A5 A5 good information here in this cell A5 and then do write he told us A5 a small blue again if we want to modify the information inside suppose we want to A5 of the grid put him into a small gray well, what we want to put into a small blue-gray small that it can be very easy to write how we just read the data directly to the wording and then followed by the equal and you want to put into it is what he meant to write like this is what we want to A5 of the value of his cell into a small gray good execution success we can look at clearly found that the successful implementation of the A5 the grid but he did not was a little blue into a small gray reason why it is this way because we are here to do a modification of the action but did not do his store so do not store, then he would not really modify up ok if we do store, then you can add a wb.save in the back and then write on a pair of parentheses in this pair of brackets we want to save the file name here I'll just copy him up good and then executed once found him wrong reason why it is wrong because we are now open this Excel file which we have to do this if you change in open Excel files stored conflict situation he happens Oh, we first have to turn off good and then he executed once on the implementation of our success can be found then he opened it he was A5 into a small gray ok then if we assume that more than one worksheet my side more new there are several good worksheet by 1234 if we want to know what words like this worksheet can write wb.sheetnames ok this way he will return now to write all the names of the worksheet I took him to our printed out execution he told us now have a worksheet 1234 if we do not want to use the default worksheet like this, we can then write wb a bracket a pair of brackets and then write what you are using a worksheet in which I suppose now I use to work in table II table II good work here write me again He ws printed out once he told us now that he is using the worksheet worksheet two because we use now is working Table II ok if we want to create a worksheet, then you can write like this we can add then write wb.create_sheet your name to see what the name of the worksheet you want to create a hypothesis called me to write a qq well then after you have created I then printed out all the worksheets wb.sheetnames performed once found more than a qq myself that we open this the Excel can be found just did not read it that this qq reason why is because we have not done so he would not really modify stored up so if we want to add qq also go up if you want to do and then put in the back of a wb.save he Tieshanglai the same time we perform error occurred because I did not perform well once he closed the successful implementation of our then he was open to look at can be found here more than a qq thing so we now understand how to read like Excel file and then how to choose which one we want to use, or you want to create a worksheet worksheet can be and how we want to kind of read inside information or change information inside well then if we do not want to read Excel files we want to own to create a new workbook that can write like this and then a pair of small brackets like this writing is to create an Excel file and then put him inside variables just have to tell him what we want to use a worksheet here I with preset like good we can put the name of the worksheet suppose I want to get rid of him into qq well we write ws.title equal qq ok this way he will put this forecast worksheet of course, we also changed the name qq can go inside information to fill the grid suppose I want to be 123 A1 and A2 is 456 and so here I can not fill the last course we want him to do that is stored in action wb.save then a pair of small brackets can write it in this small brackets we want to put this Excel file is called what name I called him new_excel.xlsx well we perform can find him the more out of us an Excel file open to look at him can be found in this Excel file that we just created, he called the new_excel then we put him into a worksheet his name to qq qq can see here is good then if we want a new by a horizontal well data that can be written like this ws.append then a pair of parentheses that we can write in this small brackets I wrote a list of assumptions is a 1234567890 so this whole string meaning is to create an Excel file and then use the default worksheet to worksheet name into contact qq With a new horizontal information 1234567890 finally put his name into the store just the name we have used yet have created an Excel file of his name is this right so we are now creating a new Excel file saved as name him again he will direct coverage of this fall we had a good Excel files directly we look at the implementation well then he opened the successful implementation of the original Excel file that he was overwritten coverage to us this is a newly created then we have to add a horizontal information I gave him a little amplification 1234567890 this is a list of Oh and then there are his worksheet name into qq good thing if we want to add a lot of horizontal information on the more than a few here and I can write a few more and then I suppose a more than 66 over 55 a 77 well we execute then he opened then take a look at him you can see more than the four horizontal then there is information 55 66 77 ok then if we want to read a data range suppose we want to read this new_excel him inside a range of information we can how we can use it to write a nested for him to go back to read here I put him into this workbook load_workbook then we have to read the new_excel him to come in and then stick to delete this title also delete the save good we can use a nested for loop thing we wrote for row in range first read his every horizontal we look at him a few of his 1234 four horizontal so here we write 1,5 then we write for col that is, each one straight row col in range of his every straight row he has 12345 5 so here we write 1,6 Okay, let's hope that inside this for loop is run it like this he went to run the grid and ran A1 B1 C1 D1 E1 again this grid is the first a horizontal Well A1 B1 C1 D1 E1 then followed by A2 B2 and so we want to run this for loop is run like this we can use other things to help us to come in so he introduced us to write from openpyxl.utils import get_column_letter we're in here for good loop you can write like this we write char = get_column_letter inside the col and then pass into the column letter which he needs inside the parentheses suppose we need to pass a number inside pass 1 is the number 1 he will back to our first inline his letters that is a 2 so we pass he will return to us us write B 3 C and so he is back pass 4 is D 5 so that E We now put our col is inline digital pass in the letter he will pass next to us this way we will be able to read our write ws in parentheses because now and then char char is to represent each letter and then a straight row plus on each of us a horizontal his number is str turned him into a string of row printed out on him and he can see from A1 B1 C1 then read all the way to E4 is read first and then followed by a horizontal 2nd 3rd 4th if we are to value, then it followed by the .value he will return each grid inside information to us can see that the first horizontal is blank so 1,234,567,890 is 123 456 789 0 none then if we want to change the scope of inside information like this, we can also write directly to him equal to suppose we put him into his plaid name well we like this as he should write directly to the final do good actions saved his store Tieshanglai because to do so he turned off well before the implementation ok then we opened it he can see his data was changed to the name of his grid A1 A2 A3 A4 ok so well we can go to read or modify a sub-range of data again if we want to merge grid like this way select a range and then put him up and he will merge into a grid can if we want to do this kind of action ws.merge_cells write like this then a pair of small brackets can then write our range to merge in this small brackets suppose I write here is A1 to E1 then we write A1: E1 Oh A1 in this thing that E1 in the scope of this thing we like this better because here are making changes so do store to store to do, then I would put him close ok executed successfully re-open it into a grid that can be found on the A1 to E1 combined into a lattice myself if we want to make his reinstatement, then you can write ws.unmerge_cells as we want to write the reply that the range A1 to E1, like I want to put him back on to close the good execution A1 to E1 can see him but we will reinstate the original information he does not respond well then if we write here is E2 good that is in the range A1 to E2 is so good like this we try once again to open ok you can see A1 E2 this range he would be merged into a lattice Oh good so we can do the combined grid with merge_cells with unmerge_cells action ok if we want to do insert a horizontal or insert a straight row like action like this if we want to to insert a straight row like this, or insert a horizontal like this how can we do assume that we can write ws.insert insert horizontal well to write insert_ rows and a pair of small brackets can write it in this small brackets on the first of several horizontal insert suppose we want to do that I wrote 3 is inserted in the third horizontal movements do have to do that here because store to store the change so if I turn off after successful implementation of our good and then he opened it can be seen that we do in the third horizontal insertion of the action yet if we want to do is inserted in the B-th straight row of words as we wrote here just insert him into cols column is here that we can not write can not write B-th row so we take him straight to digital conversion is written 2 B 2 a word is a word is 1 C the word is 3 D 4 and so on so that if we want to do B-th row straight insert wrote 2 then closed again to perform the same then he opened here to see the B-th row straight we do insert action that we can insert of course, you can delete it if our side is written and then delete column is cols2 is deleted B-th straight line up to see the implementation of open straight row we just inserted as if it was deleted we want to remove a third horizontal crossbar, then it would just mean writing delete_rows 3 so we delete_rows then 3 then he opened it his third horizontal was ok we deleted this course, you want to If you want to insert one side a lot a lot a lot a straight horizontal row you have to write a few insert_rows (3) 333 once we insert four horizontal rows so you can delete a number like this can also write directly many good that I will not demonstrate here for everyone to see everyone play their own ok so we can insert rows or cols do insert inline or horizontal movements that can put him next if we delete ok you want to move data looks like this if we want to bring here to the lower right corner of the information so he shifted a good move here we can how do we write ws.move_range then the first one pair of parentheses inside parentheses we write the information on the parameters you want to move his range here is we want to move A3 ~ E4 so we wrote this range A3: E4 then we have to tell him how we want to move the crossbar with a straight row I write here rows = 2 means that we want to shift down two horizontal grid Oh, you can also write a negative write-2, then that is a good move up two squares mean here is to write a straight row cols If I write 2 if that is the right to go straight row of two cells that can be written as a negative write negative words that we write to the left two grid 22 is to the right with each move down two spaces, like oh good that I put him close and then executed after the execution is over and then he opened to see our range of data he went to the right of each shift with the following two Georgia ok so we Move_range can be used to move data ok then let's suppose to practice what we've got an information that this information is stored inside five of their respective Name Height Weight older now that we have to how to write these five personal information to an excel file which looks like this five names of their respective age, height and weight with that we also do their last five height calculated from the average age, average height and weight are also calculated average Oh, how we can do that Oh ok it as a start we create an excel file and then it then to tell him what a worksheet we want to use it then I will use the default active then we have to put first horizontal is the title name age, height and weight with the first thing came to write it so we created a list called the name of his title he was tall and young and finally the weight ok then what we should remember it we came directly to this list append that he would write directly in the first a horizontal yet directly written in the first horizontal and then we give this information to write five people came Well so do we use a for loop to run him to run the data because the data that he was a list of do after this list which it had five each dictionary is a dictionary so we do on behalf of a person with a for loop to run the data that this person does it represent that every dictionary that is, everyone Oh, that made us each a dictionary which is to get the value we want to do is we want the value of name value as well as the weight value age of tall do that is with 74 white 18023 Well then this value is written in a horizontal Well with 74 white 18023 that here we can do like this, oh we write person.values that this way he is to write the dictionary in which each value taken out oh and then we then convert it into a list like this to do that he would become the equivalent of Oh, so we take the first example of a dictionary when it is equivalent to the value of all the inside will be taken out and then turn it into a list to convert it into a list of 18,023 with 741,802,374 equivalent would become like oh ok So it has become a list, so it’s easy. Let’s just append it in. Ok. Is that done? Then we store it in wb.save . Here I call it data.xlsx and execute it. the successful implementation of that we opened it to see Oh good we enlarge it a bit and then it can be seen than what we put on the title and then there are five people have written information should not come in yet well ok it wrong should be no problem yo we going to do then calculate the average of these five individuals age, height and weight of the thing is, there are the following 168.4 35.4 74.6 168.4 Oh that fact, We do not really write the value of 168.4 in the lattice points inside Oh yeah, he can see twice fact, we wrote inside excel is a formula oh we write is equal to the average then add a short range Oh, that this average he would be to calculate its average value in this range oh this is written, B2-B6 B2-B6 do it so the calculation of the average range inside of it here as we really do not need to go to the average write-only in the above, as long as we write this formula came in on it ok so this way we can write here that I write a for loop to let him run every column, oh that is, each one straight row that here we are I hope he straight platoon from B and then went straight row of D Well that is run straight row of three BCD is 234 Oh so here we write range 2,5 to get_column_letter then use as our previous post write oh from openpyxl.utils import get_column_letter I put it up, oh this data does not compare such a mess and then we write here as a char = get_column_letter our col and then pass that then we can go in this way I wrote char plus look at the first few horizontal oh it is the seventh so here we add a string write 7 meaning that this way he would be the equivalent of us ran with B7 and C7 B7 C7 D7 Well, right D7 is the three data we need to fill in. Its position is ok . Then we want to make it equal to the excel formula. Let’s see what it looks like. This is equal to average. Just paste it and copy it. paste it here because we each straight row loop to do with the change Well here so we can not write B write char that because we have no way to write string variable in there so I do here with the f word string fstring then we can write directly to a brace so that you can use inside the string variable that the 2 floor, then we put it into a string here is the sum of it plus 6 ok ok then what is the problem that here I changed it to single quotes single or double quotation marks that there is a conflict like this should be no problem then we execute it once was because it had to modify so I put it off Oh execution and success that we then it opens to see this average will go up as we are calculated using a formula excel to write this average oh well that we have to come back again to this place becomes the title in bold because you can see the bold headline here is that of how to do it right here, we need to introduce another thing Oh, we write from openpyxl.styles import Font Font object to the introduction of this that we are following Oh, you can write like this I use a for loop to run through each column is a straight row every time that if we are to go from the beginning of the end so we write this as 1,5 char = get_column_letter col Biography here we go then write char + 1 string 1 Why is it because we are now put this side of the A1 B1 C1 D1 as well as their words into bold we do it this way so that he would write ran A1 B1 C1 D1 with it and then we put it equal Font of this object and then we put bold set to True then it becomes a bold word to myself as I put it off well put it off and then do ok then opened it to see it become bold letters that do well then if you also want to change the color, then we can more behind a color Oh, that he can fill in the value of what we can to find openpyxl network above and then into his official left in this column it we can find a good walking with styles that here he explained in detail how we can write our colors to find what color oh oh you can find your favorite color here assume I want turning it into a blue well I'll copy this blue good as I came to write it and then put it off and then turn it on good execution you can see the text color of the text becomes a blue ok then we introduce this in python to operate almost to excel on the side of the course, I have a lot of things not to introduce If you are interested, then you can own the official to learn about this openpyxl here he has described in great detail Then I hope this video will help everyone, then we will see you in the next video. Bye
