Write/Update data to Excel File | OpenPyXL | Python Automation | Tutorial for Beginners

hello guys welcome to my channel so today we are going to work on a code which is specifically for this comment which is how can i write into excel workbook which is already saved in my documents folder using python auto i have written the code to open that workbook after that how can i write into cells so uh first of all python auto is a different library which can be used for specific scenarios and according to me i don't think when you are trying to automate excel workbook you should do it using five and auto there is something else that we can use it to achieve our requirement so i have this page written already so this page is all about open pi excel so this is another python library which is just to read or write excel files so this is specifically for your excel files so what this person is trying to ask is specifically being applied not to pi win auto but to open pi excel right so there are a lot of python libraries out there and we first have to figure out based on our scenarios which library suits our requirement best so according to me i think this library suits the requirement best so that is why i'm going to continue this video with this library so first let's talk about how we can install it if you don't have it installed so there is this there is this easy command that we can do which is pip install open by excel and this is roughly common to all of the installation whatever we do so if you don't have it installed you can do it pip install open pi excel and afterwards you will see something like this so once you install it there are few things that we can do beforehand just to get the hang of it but as this comment is specifically to write into excel workbook so i'll start with this part of code all right so first let me show you what excel file we have and then we will talk about what we have to modify so this is our excel file and here we have columns and few columns and few rows and this is a simple data it's not a big chunk of data so here what i will try to do is uh on this column f i'll choose this row and i'll just try to change the value of this cell from 32 to 33 right it's not a major change it's just a simple change so what we have to do first first we have to open this excel file then we have to go to this sheet because there is a possibility that there are multiple sheets here then first we have to go to the one on which we have to modify afterwards what we have to do is we have to go to that row and that column where we have to modify once we are able to find that cell then we can change the value of that cell afterwards we will just save that file to the already existing one or we can create a new file right so let's try these steps so i'm not going to use any editor it's just going to be a simple code so python idle it is so first is we have to import open pi excel then i have to load that excel file so to load that excel file with open pi excel we have a method named load so here if i provide the path then it is going to load that workbook into this wb object so wbhill stands for workbook so my file is that path temp and the name is test dot xlsx all right so once we have opened the workbook now there are possibilities that they so now there is a possibility that there can be n number of sheets so i just have to get the one which is currently active which means on which i have to modify the data so i just have one sheet but i can do something like this to get the active sheet from the workbook now coming down to which row and which column i have to get the data from so this is going to be my second row and this is my sixth column right you can count from here one two three four five six so row two and column six so i'll do the same thing here to get the data so there is this method cell here i have to provide the row and the column which is going to be 6 and i have to fetch this to into a into an object so which is going to be my c which is cell now i can print to see if it is fetching the data or not so let's see so here i have fetched the cell so it says it is f2 so coming back to our excel file here i can see that this is the f column and the data is at second column row so this is my first row this is the second row so which is good now to get the value we just have to do c dot value so now let's again run this so here i can see the data which is accurate now to change it what i have to do is c dot value equals to whatever data i want to update it with so which is 33 in my case afterwards i just have to save it so either i can provide the same name or a different name but i prefer a different name because that will give me a lot of clarity that this is my old file and this is going to be my new file right so saving it now so now it is done let's come back to my folder and this is going to be my file here you can see that it got modified yeah you cannot see the time but yeah it got modified recently so let's open this all right so here you can see that the value got modified to 33. now this was a very small example towards modifying or writing into excel file but this is the only way where you can start with like which i understood so if you have a bigger requirement you can slowly and state steadily start using this concept and make it according to your scenario so this is it for this video if you have any questions to this code please let me know in the comment section it's just pretty small code so i'm not going to put in any repository and if you want to learn more about open pi excel i'm going to keep updating this page so it has few details it's it doesn't have a lot of details but it is good enough to start with so keep an eye on this i'll just put this link to this video description so that is all for now i will hope that we will meet in some another video till then have a good day stay safe and bye

Channel: Prashant Sharma

Views: 5,610

Keywords: #python, #automation, #write, #excel, #openpyxl, #tutorial, #beginners

Id: Zvirgb08CpQ

Length: 7min 56sec (476 seconds)

Published: Sun Sep 11 2022