How to Write/Update Data in Excel Using Python. Openpyxl Tutorial #4

hello everyone in previous video we have seen how we can read excel file using the python in this video we will see how we can write values in excel file using the python open y excel module first let me import open pi excel and create workbook and worksheet object you can learn about workbook and worksheet object in first video of the series so to read an excel file we have previously seen in method we can say ws and specify here the column location so if i say b8 dot value and let me add that in a print statement so we can see output in our console and if i run my python program now i can see it's giving me the value 920 so let's go back to our excel file and see that value exists in b8 cell so here i open that and i am checking the value in b8 so this is b column and this is eighth row so you can see here 920 we have extracted that successfully now to write a value all we need to do is specify the values after the equal to sign so let's go back to our excel sheet quickly and modify this value in b5 cell to something else to do that i can simply say ws b 5 is equal to let's make it something obvious so i'll just type it 9 and let me add the value tag here if i run my program again i'm expecting this value will not be reflected in actual seat right away and i'll tell you reason for that in a bit so if i run this i did not get any error but if i open my excel file again you can see that change has not been implemented now if you remember in one of our previous video we have discussed if we want to make any change in excel file we have to save the excel file so i have to write w dot save and pass my excel file name here and after sending this save command it will be reflected in the excel file so let's re-run our python program now and if this time if i go back and open my excel sheet again you can see the value in cell b5 has been changed we can do that for multiple uh cells at the same time as well so and also you don't have to specify this dot value let's say we want to add two new values in cell a9 and b9 we can do that simply by saying w as e9 let's name it something unique let's call it rick and in a b9 i want to add his balance to let's make it 1500 again i have to save my excel file to see those values reflected let me close this and let's re-run our python file now if i open my excel file again you can see that rick and 1500 has been added to the excel so this way you can make changes to as many cells you like so now i will show you one more method using the dot cell we can also use this syntax ws.cell and here we have to specify row number let's get value from row 5 and column 2 and if i print this also i have to add dot value in here rerun my python program oops i have missed bracket here so let's re-run python file now then you can see 9 is printed so if i go back to my python file again and you can see the value in cell b5 is 9 and that's what we are getting we can use this syntax as well to set the values in excel file so let me comment this out and i'll say using the same method i just have to say equal to and set it to something obvious and now if i read in my python file again this 800 should be reflected in row five and in column two so let me open my python file again and you can see that has been changed it's easy to change you know single values so i would like to show you how we can change multiple values or how we can populate either column in a row using this method so for that we will need to use a for loop and what i wanted to do is basically in the c column i want to first double the value of this b column and add that here in c call so to do that we this is what we need to do change this column uh to 3 to represent c column and change here values of rows so i can say 1 to and then set it to something 1000 and again want to keep this column 3 and then make this row 2 and another value something like that so that's how we have to assign the values for that we can use a for loop so you can see all we need to do is we want to keep the column number same the column number is 3 but we have to change the row values so in here we have to specify row 1 row 2 row 3 row 4 and up until the row 9 right so let's do that before we do that let's first populate the title here so i will use the previous syntax here call it cell c1 equal to double balance and again we have to run wb.save command to populate this value i don't want to make this change right now so let's remove that and rerun our python program if i open my balance sheet again you can see the double balance has been added in c1 cell now um the next challenge is to first grab value of this b column multiply that with 2 and add that new value in the c column to do that we can use a for loop in for loop we can use a range function so i'll say for i let me comment this out real quick so for i in a range 1 to 10 print i now if i re run my python program it will give me value from 1 to 9. we can use this value to change this row number and then extract the values for each row but the first row is for headers so i don't want to touch that so let's start from 2 and go up to if i specify here 10 it will go up to 9 this is how it will print start from 2 and go until this value minus 1 so it will be 2 to 9 and if i rerun my python program again you can see oops i forget to save it let's save it and re-run python program and you can see here how where does this eight okay it's 80 000 coming from here let me comment that out as well and try one more time and you can see here we are getting value from two till nine now we can use that numbers in our new syntax that we learn so i'll say w dot cell rho equals to i and i want to extract the values from b column so that is 2 column equal to 2 and since i want to extract the values i'll say values and here i'll name it b call if i print my b call and re-run our python program again you can see we have successfully extracted values for the b column that start from 880 to 1500 right here now my next task is to multiply this value by 2 so that is simple i'll just say b call into 2 and assign it to a variable let's call it c value so this new value that we multiplied by 2 that is c value and we will add that value in a c column so to do that i can simply say w dot cell and i want to add that for all the rows respective of that column and say i column equal to here i have to say column three because i want to add value in c column c and value equal to and that has to be this new value here so again what we are doing here is first extracting the value from column b then in here we are multiplying that column b value with 2 and then we are adding that multiplied value in a c column after we make this operation we have to save our excel file to reflect those changes so i can simply say w dot save balance xlsx and now let's close our excel file first and then re-run our program oh so i have done here w that's shave it's actually has to be wb.save so let's rerun our python file after fixing the error and no error so i'm expecting that value will be updated in the c column and open our excel file and there you go you can see the c column has been populated with this new values those are double of this p column and that's how you can add values in the excel file using the open pi excel thank you for watching and if you like this video please hit the like button and subscribe my channel thank you

Channel: Python Bits

Views: 23,946

Keywords: learn python, python programming, python, python for beginners, read excel in python, write excel in python, write excel in python openpyxl, read write excel in python, 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

Id: -g6TWzMsEWI

Length: 11min 23sec (683 seconds)

Published: Mon Jan 18 2021