Python Excel Data Entry Form with Tkinter tutorial for beginners - Python GUI project [openpyxl]

hello everyone and welcome back to my channel so today's video is a continuation of a previous video I've done in which I've created this tea Contour data entry form so the purpose of this form is to facilitate data entry for professionals who are maybe saving some user data in Excel files in SQL databases and in the previous video what we did is that we created this interface I walked you through every single step of creating the interface creating different elements in the interface and then how the enter data function Works however we didn't really save the data in any place in any database or Excel file now the purpose of today's tutorial is to show you how you can save the data you collect using this form in an Excel file so maybe in a future video I'll do it showing you how you can add it to an SQL database but for today's video we're going to add it to an Excel file now let me first stress one thing you do not need to be a taken are expert to watch this video nor do you have to have seen the previous one so this video is a self-contained video Simply about how you can use Python to write to Excel files so if you're not interested in the interface part that is totally fine we're just going to talk pure excel in today's video alright so without further Ado let's get started and let's see what we can do to actually write this data into an Excel sheet okay so I'm going to close this interface and let's start talking about the code now last time what we did is we collected all these different information so the first name the last name the title we collected them in numerous variables you can see you have a variable here called first name you can see here this is where we collected we collect last name we collected title age nationality and so on so we collected all of this information into python variables So today we're not going to talk at all about the tikenter part we're just going to think okay I have these python variables they contain strings of where of the information that I have I just want to put it in an Excel file now as of now what happens is we're simply printing them out using these few line kinds of code that you see right here so after I enter the data I print out first name print out last name print out title and so on so let me actually show you how this works now we've seen it in the previous video but we're going to do it for the sake of demonstration so let's say I have a user whose name is John Smith and his his title is Mister and then his age is like 24 and his nationality last time for simplicity's sake we just put the continents rather than putting individual countries but anyways let's say he's European um he is currently registered and he accepts the terms so this is just some sample data and then I'm going to come here I'm going to press on enter data and as you can see nothing happens except the fact that this information was printed so you can see first name John last name Smith and then all of the other information has also been printed here so this is what we were able to do last time we collected this information we simply printed it out to the console so we didn't really do anything with it now let's see how we can write it to excel all right let me actually stop this and let me show you what library we're going to use in order to actually use Excel with python so the library we're going to use is called open Pi XL so this is a very famous Library commonly used for different Excel tools using python so the way you're going to install it is simply you go to your terminal or your command line your CMD wherever you are here I'm using an integrated terminal in vs code however you could be using any text editor and any terminal that you like so depending on what you're comfortable with all right so now I'm going to Simply pip install open by Excel again any terminal and also if you're using a virtual environment make sure to install it inside your virtual environment here I'm not using any for the sake of Simplicity but anyways this is the command that you need to execute so pip install Open PI Excel so now when I press enter in my case it's going to say requirement already satisfied so that's totally fine this is because I already have it but in your case this will probably install it it will take about a minute and then you will have Open PI Excel on your desktop all right now that we've installed it let's actually start by importing it so I'm simply going to import Open PI Excel next I'm going to import OS so why do we need os I'll show you later on where we're going to use it but for now you need to know that you also need to import OS okay so now that we have Open PI Excel and Os imported let's start by writing some code so as you can see right here currently in my folder so I'm inside the folder tick enter data entry there is no Excel file in which I want to write to so here we're going to take the case where maybe you're starting off and you haven't created your Excel file manually now of course you could already have this Excel file created manually you could have created it copy paste it into this folder and then it's super easy to just write the information to it but I'm trying to show you how you can do the whole process from within python so how can I create my Excel file with python so this is our first step creating the Excel file let's see how we're going to do it so first things first we need to specify a file path so our file path in my case is going to be the following so this directory slash taken3 data entry which is my current folder slash data dot xlsx so this is how you specify that this is an Excel file in your case this path is going to be totally different depending on your machine your current working directory where you want to store store this file it's all up to you but this in my case this is my file path now why do I have the file path here firstly I want to check if the file already exists so let's say for example I already had the file I wouldn't need to create it but if I don't have the file I need to use Python to create this Excel file so let's go ahead and do that so let's say if not OS dot path dot exists file path what this does and this is the reason we actually import OS it checks does this file path exist Does this data file exist if not let's see what we're going to do we're going to actually create it and we're going to use Open PI Excel to do so okay so now we have the following I have workbook equals open Pixel dot workbook so what this does is it will open up an Excel workbook using Open PI Excel so an Excel workbook is this thing that you see right here usually workbooks look like this in Excel this is an example of an empty workbook you can have multiple sheets so this is sheet 1 you can have another sheet but essentially this is how it works and also this is not the file we're going to use I'm just showing you for reference in case you maybe haven't used excel in a while or you just want a visual representation of what I'm talking about so this is Excel this is an Excel workbook that's open and there are two different sheets that you see right here I can maybe even create a third one and so on all right going back to vs code so now that I have actually referred to my workbook and opened up a workbook using Open PI Excel what's the next thing I'm going to do I need to actually talk to my sheet so as you can see there are usually multiple sheets in an Excel file or there can be multiple sheets sometimes you're going only going to have one but anyways what we need to do is we need to specify sheet equals workbook DOT active now what does active mean active means get me the active sheet so get me the sheet that's currently open now going back to my blank Excel file if I do this and I stop and tell you okay what is the active sheet in this scenario you're going to tell me that the active sheet is sheet 3 so sheet 3 is the active one because it's the one that's currently open so it's active so if I were talking to this workbook using python sheet3 would be returned when we do sheet oh sorry workbook DOT active Okay so in our case we're only going to use one sheet so workbook.active is going to open up the very first sheet in that workbook all right perfect now that we've done so we need to create a heading usually most Excel files when they store data so let's say I'm storing some user data and I go say first name last name and then here I just start typing up a bunch of names you know you're going to use this as your heading this is would contain the names of the different columns that you're using so we're going to actually create the headings as soon as we create the file so we only need to do that once you only create the file once and you add the headings once now that I've specified them in our case they're going to be first name last name title age also note that they're inside a python list so this is how openpi Excel works you write stuff using python lists so now that we've done that next I'm simply going to sheet dot append The Heading so this dot append function what it does is it takes this python list and it attaches it it appends it to your sheet so inside your sheet so it writes this as a row inside the sheet so there's a brand new row in the sheet now that we've done all of this you might think okay is that enough do we now have the Excel file not really because we haven't really saved anything now the way Open PI Excel works is that you first open up a workbook using something like this then you may be right to it you do some things you append rows but then you actually have to save it if you don't save it it doesn't really get saved anywhere and you can actually stop and try it yourself and you'll see that you will have no results because it will not be saved anywhere and especially not in the file that we want so then what we're supposed to do is simply workbook dot save and this is where you specify the file path and the file name so you can see the file path contains the name of the file as well I use workbook dot save and this should save it in a brand new Excel file all right let's actually test the theory out so we're going to go ahead and run it and this should open up our application now let's say here we have John Smith again and not going to give him a title any nationality is he registered maybe accept the terms and let's actually try to enter the data Note that for now we haven't coded anything related to actually entering the data in the database but for now what we want is simply to see this file created and to have the heading printed inside the file so let's press on enter data now as you can see data.xlsx has been created right here inside our Target folder so I'm just going to shut off the application go to my folder so as you can see this is my directory in my data this is the same directory that we were using we have the main.pi file and we have the data Excel file let me launch it as you can see you have here the first name last name title and this is actually the information that we entered and these are the headings for our columns so now we have successfully created the file using python added a heading so we added the first row and then we save the file in a desired location with the desired file name so for now this has been a success I'm just going to close it now and let's head back to vs code so this one was the old file that we were using just for the sake of demonstration let me actually delete these just so that we don't get confused all right so let me actually head back to vs code so now that we were able to actually create this workbook and have the heading and save the workbook this should not execute again this if statement and all the code within it if the file already exists and let's actually try it out so let's go ahead do the same thing let's go with Jane Doe and let's accept the terms and let's enter data so you see the data itself was printed but are there any changes to the file let's open it up again as you can see there we go so as you can see no information so it's just the same headings the same thing we didn't add anything this is simply because the actual if statement the contents of this if statement were not executed this is because the file already exists all right so now that we've done that what we want to do next is we want to enable python to actually save the data that we have and actually save it in this Excel file so other than the heading we want to save the actual data how do we do that so outside of my if statement I'm going to execute something else and here this is load workbook so this is very different from this workbook right here here we were actually creating a workbook object we were opening a workbook object here we're calling a function called load workbook and we specify the path and we're saying hey python open up this file path now what should happen is if the file path doesn't exist it will first go through this entire process of creating it after that it will load the workbook now if the file does exist it's going to Simply ignore this entire if statement right here and it will just execute this part immediately by loading the workbook all right so now we've loaded the workbook what do we want to do next we actually want to write the data the variable's first name last name title age nationality and so on we want to write an inside our sheet the same the same thing follows you need to actually specify the sheet is workbook DOT active to get the active sheet then you will just simply do sheet dot append and you create a python list and this python list what it does it will simply contain the first name last name and all the different variables that we have so these variables we put them inside a list and this list is we just say she dot append finally the very last step that you need to do is you need to actually save this inside the workbook so just workbook dot save and you specify the file path so you're saying please save me inside this Vic very same file path okay so this is all the code that we need let's go ahead and test it out and let's try to add an additional row or two or three rows to our Excel sheet so let me run it and let's go now and just say John Smith and let's actually give him a title so we're going with Mr let's say his ages 23 and his nationality is from Asia is he currently registered yes he's finished four courses in one semester and he accepted the terms and conditions so let's press on enter data data was printed now was it actually added to the Excel file let's open it back up so it's loading there you go so you have John Smith and then you have all the information that we just entered has been added to the database now what happens if I add another piece of data so let's go ahead with Jane Smith and this time Jane is 26 and she's from Europe and she's not registered and she hasn't taken any courses or any semesters and let's just press enter data and again let's go back and open up the file and as you can see the information for Jane Smith has been added and this is the data entry that I was talking about so we were able to get the information from the python code from our application and Export it added to the Excel sheet now one last test I want to do just to show you that everything is working fine I'm going to close the app and rerun it from scratch so what happens if the app and let's actually close the file as well so what happens if the app stops running and we execute it again should this also work will it overwrite the existing code so let's go with Max and say his last name is something s and then let's say he's a doctor and he is let's say 28 and his nationality is European and he's registered and he accepts so let's enter the data close the app again even though you don't really need to close it go back open up the file now as it's loading there you go you have the information that Max entered inside the sheet so this can be built incrementally no matter how many times you run or execute the program it doesn't all need to happen in one execution of the program as you saw we actually shut it down we closed it we stopped running then we read Around the program and as you can see we were able to add information on top of the existing information so that's all I wanted to show you today I just wanted to add this additional part to the T Contour data entry form in which we captured this data and then added it to the Excel sheet as you saw in this video thank you so much for watching and I do hope you enjoyed if you did please leave a like And subscribe to my channel for future videos like this and let me know what else you'd like to see me do with this data entry form alright thank you so much and bye bye

Channel: Code First with Hala

Views: 69,267

Keywords:

Id: fvIThtPt6Nc

Length: 18min 48sec (1128 seconds)

Published: Wed Sep 07 2022