Python Excel App - Excel Viewer & Data Entry Form [Tkinter, openpyxl] Python GUI Project

hi everyone and welcome back to my channel so in today's video we are going to build this Excel app with tikenter so what this app does is it connects to a certain Excel sheet that we have in this case this is going to be our people Excel sheet this is just some random data about certain people employment things like that what it's going to do is it will read this data display it here in what we call a Taken 3 view which allows us to create this table here you can see we have different columns and rows you can actually scroll and look at it and then what we're going to do is we're going to use this form right here to also insert new data into this Excel sheet so this is meant to be an interactive app in which you use both Excel with python as well as tikenter which is a python GUI library to create this desktop app we will also be using a tkinter theme to make it look like this this is why it has these green accents these colors this is all part of a tkinter theme and I'll show you more about it in the video this is to make our interface look much more modern and much more presentable so without further Ado let's get started so the theme I'm going to use is something called the forest ttk theme so this is a theme that I previously talked about in a previous video I'm going to link that down below if you're interested but basically this was created by an individual the username is Rd bendy I'm still not sure how to pronounce it and they created this theme to make it to make tikenter actually look like Excel so they use these green accents and the gray accents as well to make it look like Excel and this is how the theme looks like in light mode and how it looks like in dark mode now to use this theme we're going to need the force dark.tcl file the forestlight.tcl file so one of them is obviously for dark mode and one of them is for light mode and we're going to need both of the folders for these themes these contain the actual settings and code for the theme and the TCL files will be used to load the themes themselves so what I need to do is if I go to vs code now here this is some starter code I'll talk about this in just a second let me actually close this but as you can see see here this is my project structure these are my folders I have the force dark folder the force dash light folder and the four Stark TCL file and the forest light TCL file so as I mentioned you can get these from the theme GitHub repo so you can see here I will link this also in the description make sure to clone it and copy what you need so these are the folders and files related to the theme now the other files that we have we have the people.exl file so this is where all our data will be stored this is an Excel sheet you can open it with Microsoft Excel I can even show you how so if I open my File Explorer you see people Excel file here and if I load it you can see this is the Excel file we're going to talk a bit more about the data in just a second but for now this is how it looks like and other than that we have our main.pi file this one right here this is just a temp file you can ignore it this is what vs code uses to store temporary data now here this is our main.pi file so if I open it you can see we have some starter code now all the starter code and everything else will be linked in the description down below as well as the full source code for your reference so feel free to use that as we go along in this video so this is our starter code let me start by explaining it so let's take a look at our starter code first things first we have some imports I'm importing click into your SDK then from tikenter I'm importing ttk so ttk is a package or a sub module that you can use to get the themed widgets of teakinter this is what allows us to use these themes with teak enter and make our interface look much more modern next what I'm going to do is I will create a root widget so the root is equal to tk.tk this is our root widget or actually our window so if I actually run this right now you're going to see an empty window just like that this is our root window it's the parent widget of all other widgets in tkinter more on that later finally what I'm doing is I'm running root dot main Loop so this is the event Loop that is going to launch my application without this there would be no application it's like an infinite Loop that keeps on executing so long as your app is running when I close my app this is when this main Loop stops executing all right now that I have the basics let's start by adding the widgets obviously what we want to do is we want to go from this to this so this is a the reference of our interface this is what our end result should look like we're going to start by first creating this Frame right here and all the widgets inside of it so we're going to create the form related to inserting the row before actually creating the tree view the first thing we need to do is we need to actually use the theme that we downloaded so while we do have the folders and the files here we still haven't actually called it from our python code to do so first I will create a variable called style this will be a ttk DOT style so this is what will enable us to apply a theme to our tikenter application and we need to use ttk not TK so we're using the themed tikenter and then we say here that the parent is the root so we applied the style variable the next thing we need to do we need to actually call the TCL files that we have here to do so we just do the following we say root.tk.com and then we say source so here we're sourcing a file and this is what we're call link so the forest.light.tcl and the forest.dark.tcl after calling these I need to choose what theme I want to use so we're just going to say style dot theme use Force Dash dark alright so now we're using the Dark theme later on we'll see how we can also use light mode as well so we're using the Dark theme the next thing we want to do is we want to start creating our widgets first things first we will create something called a frame so we say ttk dot frame and the parent will be a root so why do I say parent antique inter widgets are actually organized using a hierarchy we have the root window as I told you this is the parent of everything else it's the root widget it's like the largest box and then inside of it we're going to have nested boxes so this is going to be the largest box the widget then inside it may be another box and then inside that box other boxes and things like that if we go back to our reference you can see that here this the entire window this is our route which digit now what I'm going to do is I'm going to create a frame inside of it the reason I create a frame is to keep my UI more responsive and I'll show you why later on in the video but for now we're just going to create this Frame and inside the frame we'll put all our different widgets so frame is created very easily you say ttk.frame and the parent is root like I said every widget in tkinter should have a parent so in this case the parent is the root widget and now to actually add the frame to my screen I use something called pack so this pack is what will enable my screen to be responsive and make my application more interactive and resizable so I'll show you how later on in the video because in fact if I run this right now as you can see now it's totally empty but if I do resize it you can see there's a blank window so let's start by adding the widgets and we'll see how they look alright the first thing we're going to do is we need to also group our widgets so we have created a frame as you can see here we have the root window and then we have created a frame inside of it the frame is basically transparent so you can't really see it but now what we want to do is we need to create another subframe or something that we call a label frame if you look here this insert row with the Box around it this is a label frame this Frame is actually visible It's Not Invisible and it has a title so to create it it's also very simple so what we do is we say widgets frame this is what I choose to call it ttk.label frame and then I say the parent is frame and the text is insert row so what do I mean by the parent as frame this time as I was saying before it's like you have nested boxes inside of each other so if we go back to the image you can see that you have here the largest frame which is the largest widget inside the root so you have the window the biggest box root then you have frame inside of that so this will be the frame the second box then you have the insert Row the label frame inside of the frame so it's like having different boxes of different sizes inside of each other so this is why the parent for insert row this will be the frame and not the root itself so now we specified the parent and we specify the text so the title in this case this is going to be insert row like I just showed you now if I run it you will see nothing has been added to my screen it's still blank the reason is in tkinter nothing gets added to your screen unless you do two steps the first step is to create the widget like here you say ttk.label frame the second step is to use either pack place or grid which are the geometry managers of teakinter these allow you to position your different elements on your screen and actually add them to your screen so if you don't run either pack place or grid so you can see here we have pack if you don't do that you will not see your widget on the screen and I'll demonstrate more examples as we go along in the video so now the next thing I need to do is I will say grid this time and this will be row equals 0 and column equals zero so the difference between pack and grid pack you don't really specify the row and the column things just get added sequentially to the screen however in Grid it's like you divide your screen into a grid and you have to specify the different indexes so what we did right here if I can annotate this here you can see we just split the screen into two we created a grid and now this is row 0 column zero and now this part here this will be row 0 as well but it will be column one so this is how a grid looks like and this is how we divided our application these are geometry managers you use those to better position your elements on the screen and to manage their different locations this is really important however I'm not going to go too deep into it in this video because the purpose of this video is to create this project and not just talk about geometry manager the next thing we want to do is we need to add some widget into this label frame so we can start seeing it all right the first widget we're going to add is actually going to be an entry this is where the user will type the name of the person so let's add it first things first is we do this so we say name underscore entry this is the variable name you can name this whatever you want this will be a ttk DOT entry and in this time the parent will be be the widgets frame so again going back to this hierarchy the root is the biggest widget it's the biggest box inside it we have frame inside frame we have widgets underscore frame and inside the widgets underscore frame we have the name underscore entry so this is going to be our entry and it's going to be nested inside this widget's Frame to see it of course if we run it now we won't be able to see it to see it we just do this so let's also add it inside a grid again this will say row 0 column zero why is that this is because this time the grid is inside the insert row so let me just change the color so here this is we're creating another grid and this grid will look like this and it will be inside the insert row label frame so here this will be row 0 and column 0. we only have one column so of course the index starts at zero as things usually do with coding but we only have one column so this will be column zero and the row in this case this is row zero here this is Row one this is row two and so on so we'll get to those in just a second but this is why we specify the row is equal to zero and the column is equal to zero when I say sticky equals ew here this means East West what I'm trying to say is expand it both East and West so I'll show you when we run it running it now you can see we have this insert row this is the label frame and inside it this is the entry so you can come here and you can type random things so when you press on it you can just type anything and also notice this green accent for this entry this is due to the theme that we're using which is this Forest ttk theme all right so now we have created our very first entry obviously the UI doesn't look that great but we're going to build it slowly the ew what this does the sticky equal ew it expanded it to fill up the size of the label frame now running it again you can see it takes up the entire space in the label frame both East and West so horizontally it takes up the space alright so we have created the entry but now in our app it's still empty it doesn't show name as a placeholder like we have here to do that it's very easy we just add this line of code we say insert 0 name now what 0 means is you have to insert this piece of text right here this string at a certain index so you can insert it at the end of whatever text we have you can insert it at the start you can insert it at index 5 but why do we say zero what this means is simply insert it at the very start of this entry so start it here at zero and now you can see we have name right here one actual bug let me rerun it and show you so this is a problem that we have if we press on this entry and we start typing you can see that the name part is still here which obviously isn't useful it's bad user experience because here the user still has to erase everything and write down their name all right so how can we fix it to fix it we also have a quick fix so one line of code and this is the line of code what we need to do here is we need to use the binding sorry so we need to bind a certain event with a certain function now the event that we're going to bind is focus in what focus in means it means that when you click on this entry so let me run it and show you so you can see here we have oops okay we have here the name if I press on this if I focus here I'm doing this focus in action so by pressing on it you see that the name disappeared and now I can write my name so this is what we call focus in when you put your mouse inside an entry and now you're able to type in it so we bind this focus in so that when the user does this focus in we want to call another function so we use Lambda to actually call this function you say Lambda e or you can name this anything that you want to call a certain function the function we're going to call is simply name underscore entry dot delete 0 and dot delete is very similar to name.entry.insert so here insert what it does is it inserts a certain piece of text into your entry delete what it does is it just clears the text and we say from 0 till end this means you clear everything from the start till the very finish by doing so you have cleared the entry and now the user can type whatever they want so to demo this again you run it you see you have here name as a placeholder once you press on the entry you can begin typing and the name disappears so it has been totally cleared all right so that is it for our first widget which is the entry the next thing we want to do is we want to create our second widget which is this age spin box right here so a spin box is something antique entry that you use to have different numbers so these Arrow buttons the up and down arrow button will enable you to go up and down certain numbers to insert the user's age so let's see how we're going to create it first things first let's create the spin box so I will call it age underscore spinbox and this will be ttk.spinbox widgets underscore frame again this is inside the label frame and I will specify the ages that I want so the age I want to start is 18 and up until the age of 100. so next we need to add it to our interface again if I actually just clear this and run this app you can see that the spinbox was not added yet why is that as I said before in t Contour you actually need to do two different steps first step is you create your widgets such as here ttk.spinbox and the Second Step you need to use pack or grid to actually add your widget to your screen to position the widget on the screen so here I say grid row equal 1 and column equals zero so we use these simply because as I showed you here this is our grid we call them is still zero but the row is now one and now what I do is I say sticky equal ew so again I want to expand it horizontally both sides East and West so now let's actually run it and as you can see we have a spin box if I use the arrows you can change numbers and I can insert the user's H so you have 18 and you can go up until 100. the spin box is actually a tick enter entry behind the scenes you can actually type stuff here but this would be an error and would create different problems so this is a spin box you can use the arrows to get the numbers now again notice one thing if I rerun it you can see there is no placeholder yet that says age because in our image our reference it should say h we will use a very similar approach to what we did with the name entry we just say aged underscore spinbox.insert zero age and this will give me the age here and automatically when I press on these buttons the age will go away and we're going to get the numbers so this is it for the spin box now I have the spin box and the entry the next thing I want to create is this drop down menu which is going to alert me as to whether this user is subscribed or not so we're going to have three options as you can see here from the table we're going to have subscribed not subscribed and other so this is just some random data that I was using as an example but you can put anything that you want here so to create this drop down menu we need to use something called a combo box in teakinter combo boxes are what we usually refer to as drop down menus to do so simply what you need to do is the following so I will call this status underscore combo box this will be ttk dot combo box it will be inside the widgets frame and then it will we will grid it it will be Row 2 and then column 0 because again you can see row zero one two this is Row 2 column zero so running it lets see what we have you see we have a drop down menu here obviously it's empty because we haven't given it which options we want so this is the drop down menu again it also looks like a tick enter entry you can actually type stuff here but this isn't the purpose that we want we want the drop down menu to add values to our drop down menu we need to do the following so let's actually first ignore this line of code and you see here we have values equal combo list I'll explain this line of code as well later on so values equal combo list this means that the values of this combo box the values of the drop down menu will be from this combo underscore list so I just create this variable you can see here combo underscore list this will be equal to subscribed not subscribed and other so if you want to add different options to your combo box this is where you would add them so you can write anything that you want and these will appear in your drop down menu let me actually comment this line here first and show you how this will look like so you see here we have this drop down menu if I press on this you can see we have three different options subscribe not subscribed and other so we have successfully created a combo box or a drop down menu one thing you can notice here is that it's still empty before I press on my drop down so what I want to do is I want to set this to be subscribed by default so we'll use this line of code right here to select the first option from our combo list so what this does is it says status combobox.current zero zero means the first item from the combo list in this case this is subscribe so let me show you the difference if we run it now as you can see now it says subscribe by default rather than having a blank entry so now we can select and change the value we can say other whatever we want but this is our third item so our third widget is this combo box all right now we have our first three widgets the next thing that we want to do is we want to create this checkbox right here this will specify whether or not this user is employed so we will have two options in our sheet this will be employed and unemployed and we'll get this using this checkbox so let's see how we can do it first we create the check button this will be ttk.check button and of course the parent is widgets frame we specify the text in this case the text should say employed so the text is Whatever Gets displayed here next to the check button now after creating it we need to assign this to a variable in this case this variable will store the value of whether or not this check button has been checked more on that later when we actually retrieve these values and add them to the Excel sheet so we say variable equal a I just chose to call it a and a will be equal to TK dot Boolean VAR so this will be a Boolean variable it will be either true or false depending on whether or not this check button is checked so here I created my check button what I want to do next is I want to add it to my screen so I do so using the grid this will be Row 3 column 0 and now I say sticky equal nsew this means north south east and west so it should be X founded in all four directions all right I've created it let's run it and see how it looks like and now you can see that this is my check button you can press on it check it uncheck it so by default it's unchecked okay so now we're starting to get our widgets one thing I want to note is if you resize this you can see that it stays here in the center of the screen and if I just resize it using this or using this you can see here it stays in the top Center this is because this behavior is actually due to the fact that we created this Frame so this Frame we created at the start and we packed it so pack by default allows our interface to be responsive and to remains centered in the screen so because everything is inside this Frame our entire interface remains in the center when we resize our screen so I just wanted to note this in case you were wondering why we use this Frame to begin with all right so we have all of our input widgets the next thing we want is to create this button so obviously here this is Row three sorry three and this will be row four so to create our button again very simple we say button is equal to ttk dot button the parent is widget frame as well and the text so the text displayed on the button is insert now to add it to our screen we use grid again Row 4 column 0 and we will expand this north south east west so running it you see now we have a button if we click on it nothing happens because we haven't written the code for this functionality yet but this is how it looks like I think we can note here is the fact that everything is kind of squished together and it doesn't look very nice and doesn't look very appealing of course in our reference you can see they're better spaced out obviously I annotated over them but they are better spaced out and they look better now to fix this we're going to use something called padding so padding will enable us to create spacing between these different widgets to specify padding it's very easy so we're going to start by assigning the padding to the widgets frame if I say pod x equal 20 and pod y equal 10 and if I run it now you see now there's spacing between the label frame and the rest of the window so this padding was created you can see X so horizontally here the padding is 20 on both sides and the padding is 10 vertically on the y-axis so this is how you create padding so now now I've spaced out my label frame from the rest of the screen the next thing I want to do is to add padding and space out my widgets from each other here we say pod X is equal to 5 pod Y is equal to 0 and comma 5. Now what this means is that the pad y will be different on each side so this will be 0 at the top and 5 at the bottom so now running this you see now name is spaced out it has some padding here so I'm going to go ahead and add padding to the rest of our widgets so for age we add a padding of X and Y both sides now here padding X Y again for the combo box let's do the same for the check Button as well as the button now if I run it it should look much better as you can see everything is now spaced out and it looks way more presentable and way more readable for the user who's going to be using it all right now the next thing we want to add is a separator as you can see right here so this little line right here this is a sub operator and why do we choose to add it well this part of the form is used to insert information into our Excel sheet however this is going to be used for both light and dark mode so they're not really related so this is why I choose to use a separator just to make it more clear for the user what the point of these different things are so to add my separator very simple I use ttk dot separator the parent is widgets frame I say the row is 5 column is zero I specify this padding on the X this padding on the Y and I want to expand it both East and West so as you can see this here should be row five and this is row six so now running it you can see we have a small separator right here the last thing we're going to add on this side of the screen is going to be our little switch here that's going to enable us to switch between dark and light mode so to do so what we do is the following so we say mode underscore switch this is what I choose to call it this will be a check Button as well so similar to how we had this check button before for the employed option we will use a ttk.check button as well so here the parent will be widgets frame and the text will be mode and of course I need to add it using the grid I say row equal 6 column equals 0 and I specify the padding and I expand it in all sides so running it now let's see how it looks like you see this is a check button now this doesn't really help us because how is this related to the switch that I just showed you in the reference photo now to do so simply what you specify is this property you say style equals switch and now you run it and now you can see you have this toggle button in or this switch button right here now the next thing I want to do is that when I press on the switch button I want to be able to change my UI from dark mode into light mode to do so what I first add is the following so to my mode switch the check button that I use for the switch I add this property I say command equal toggle mode now what command does is that it links your widget to a certain function here what I'm saying is when the user presses on the mode switch button when they switch or when they toggle this button I want to go ahead and execute this toggle mode function so this was going to be a function that I create myself now to create it we're just going to Simply create the function so we say def toggle underscore mode this is going to be the function again when I press on this switch I will go ahead and execute this function so this is what I'm telling python to do now what should be inside of this function simply what we're going to do is toggle between both dark mode and light mode this is very easy to do so we need to get this switch so here we say mode underscore switch we get this state so we say if it's in state selected selected means that we have pressed on it pressed on it once so if we have pressed on it once what I'm going to do is simply say the following I say style dot theme underscore use Forest light so here I'm saying I want to use the forest light theme so let's run it and try it out now here we have the very same interface now I'm going to press on my switch and as you can see my interface has been switched into light mode so this is how it looks like now this is light mode now if I want to switch it back into dark mode and I press on it nothing happens this is because we also need to add a few lines of code so we want to say else so the alternative we just switched it back I want to use Forest Dash dark now running it now if we go to light mode or we can go back to dark mode and we can do the same thing over and over again so now we have coded the part related to the theme and going from dark mode to light mode and back now we don't need to worry about this anymore so even when we add other UI elements such as the tree view we're not going to actually need to do anything related to light mode and dark mode now the next thing we need to do is we need to create this tree view right here which will allow us to see our Excel data in a table so going back to the Excel file let's first take a look at it we have four different columns we have the name age subscription where we keep track of whether or not a certain user is subscribed we even have an other option here and the employment where we keep track whether the user is employed or unemployed so this is how our data looks like it's in these four different columns and it's inside this people.xl file now we want to load it into our interface we're going to use something called the Taken 3 review to do so here's how we do it first things first is we're going to create a frame for this three view now remember how before we created a larger frame than we created here this label frame on the side we're going to need a frame here to house both our tree view as well as this scroll bar right here so to create the frame we're just going to call it tree frame and it will be equal to ttk.frame so this is how it looks like next we will say that we have to use the grid so we say row is zero column is one note that here the parent is the frame so this is the largest frame that we had it's the parent of our tree frame and like I showed you you see here the the brown colored annotations you have here two columns zero and one and one row so this is why we say row zero column one now that we've created the three frame we want to then create the tree view so first we specify the names of the columns so here Coles is going to contain all the names of our columns in this case name age subscription and employment just like here in our Excel file now that we have the columns we can go ahead and create the tree view here this will be ttk.treview the parent will be three frames so this is inside three frame we say show equal heading so that we only see the columns that we have headings for we don't want to see an additional column which is related to hierarchy and three views this isn't part of our current application next we specify the columns is equal to calls and the height is equal to 13 this means I want to show 13 different rows in my table finally I just say treeview dot pack now let's run it and see what we have now as you can see we have this table right here however there is no data in it because we haven't really loaded the data from the Excel file this is just how it looks like you can see we have one two three and four different you can see here four different columns however they don't have names yet you'll see why later on in the video so we just created D3 view the next thing I want to create is the scroll bar which we're going to attach to the three views that we're able to scroll through it going back to the reference image you see here we have this scroll bar right here which will enable us to scroll in D3 view so to create it it's very simple we just call it tree scroll this will be a ttk.scrollbar and the parent will be tree frame as well then we're just going to pack it I say side equal right to make sure that it stays on the right side and then fill equal y to make it fill up the entire vertical axis and take up the entire space vertically now let's run it again as you can see we have this scroll bar right here it's currently full because there's nothing to scroll with the last step is I actually need to link my scroll bar to my tree view so they have to be linked together so that when I scroll on the scroll bar I'm actually affecting the tree view to do that very simple you just say three scroll dot config command equal treeview.y view this means I'm configuring that the tree view moves along the y-axis with my scroll bar and also another thing I need to add is here so why scroll command this is a property we add to the tree view we say y scroll command equals 3 scroll dot set one thing you may have noticed is that the tree view is actually pretty big and these columns are pretty big as well so to reduce the width of this review we can we can just set the width as follows so using here so I can say treeview dot column so let's say the column with the name name it will be a width of 100 age will have a width of 50 subscription has width of 100 unemployment has a width of 100 you may be wondering how can we refer to name age subscription even though we can't really see them on the headings this is because internally this is what these columns are named as we just haven't yet displayed them on the tree view so now if I run this you can see the tree view has been resized and the columns are much smaller so if I press here you can see this green outline this is the First Column if I press here this is the age column which is the smallest one and we have two others here as well okay so we have created it we have the appearance of the interface next what we want to do is we want to load the data to do so we're just going to create a function that we call this will be load data this is the name of the function then I'm going to go ahead and create it so this will be def load underscore data the next thing I want to do is I want to import the package which will enable me to work with Excel files using python this package is called import Open PI Excel to install openpi Excel simply what you need to do is you pull up a terminal you had here and you just say pip install Open PI excel in my case I have it so I'm not going to run this however in her case just press enter and wait a few seconds or a few minutes and it should be installed if you don't use the integrated terminal that you have inside vs code or whatever IDE that you're using you can use a regular command prompt the CMD on your desktop to actually perform the installation after you install it you can actually import it as you can see right here and now we can start to work with our Excel file in Python okay so we have created the load underscore data function first things first I'm going to specify the path of my Excel file this is going to be people.xlsx so this is going to be my Excel file after specifying the path I will simply load the workbook so I use Open PI Excel to dot load underscore workbook to load this path into this workbook variable now that I've loaded it I need to reference this sheet so sheet will be workbook DOT active what does this mean so on any Excel file if we go back to excel here you can see that you can have multiple sheets so when we say workbook DOT active we're getting the active sheet from this workbook or the sheet that is currently open and in this case we only have one sheet so this is going to be sheet1 anyways so you don't have to worry about this really but if you had multiple sheets this is what workbook.active would mean okay so now we have this sheet we want to read the values from this sheet to do so it's very simple one line of code and this is going to be list underscore values and we say she thought values and we convert this into a list I can also print them out so you can see how it looks like let's actually run this and see what this load data function does and as you can see if you scroll here you can see in my output I have all of my data loaded as a list of topples and you can see all the information of the users is here it's not yet inside the tree view because we haven't written any code to put it here but I was just trying to show you how you can read all the values from the Excel file using Open PI Excel all right now the next thing we want to do is we want to add the column names to our tree view to do so very simple as well so we say four column name and list values Sub Zero and then we just three view dot heading the following let me explain this code bit by bit so list values Sub Zero what this is if I open up my output again you can see that this is a list of tuples so if I so list value Sub 0 is this very first element in my list so it's going to be the names of the columns so name age subscription employment these are the columns that we want so when I say list values Sub Zero this is the part that I'm getting next what I'm doing is I'm specifying The Heading of the tree view so I say treeview dot heading meaning I want to put my column names on my heading of the tree View and then I will say call call underscore name so when the name of this column internally is for example name or age I want to set the text to the same thing so now if I run it as you can see we have our column names now so you have the name column age column subscription and employment also one thing we can check is if we change the mode you can see here that it's now in like so closing this the next thing we want to do is we want to load the rest of the values to do so we just do the following so we say four value Tuple in list values and then we slice it starting at index one why do we do that this is because index 0 is the column names and everything from index 1 and onwards this is going to be the names of or actually all the information related to the users so this is list value Sub 0 and then this is list values sub 1 and everything else after it so this is what we want to load into our tree View and now we just do something very similar to what we did before we just say dot insert we say we start at blank so we start at the very start and we say TK dot end and the values are going to be equal to the value Tuple so running it now you can see all of our information has been added to this Excel file you can scroll with the scroll bar right here and you can actually see all your data you can highlight the column name here you can highlight data you can select this select that and so on all right so we have loaded our data the next step we want to do is to actually insert new information into this Excel file using the insert form right here to get started the first thing we need to do is we need to call a certain function when we click on the insert button to do so we link our button with a function using the following so we say command equal insert row so similar to before when we said command equal toggle mode here what we're doing is we're linking this insert row function to this button so when the user presses this button what's going to happen is it will call this insert row function now we go ahead and create this function we just say def insert row then what I'm going to do is divide divide my function into three different parts the first part of my function is going to be retrieving the data from the different entry widgets in my application then I'm going to insert the row into the Excel sheet then not only will I insert it into the Excel sheet but also I want to insert it into my t view so I want to also be able to see it in my tree view the one that I have created right here now to do so first let's start by getting the information from the widgets so we have here these four different widgets for the information first thing we want to get the value of the name to get the value of the name very simple we just say name equal name underscore entry dot get this will get me the value from the entry which has my name in it this will get the value as a python string next what I want to do is I will get the age here I do the same thing I say age underscore spinbox.get but then I'm going to convert it into an integer you can or cannot convert it it's no problem here you can keep it as a string but I choose to convert it into an integer now I have the name and the age what I want to get now next is the value here from this subscribed drop down so it should be either subscribed not subscribed or other how do I get the value from the drop down simply I just say status underscore combobox.get so the very same method that we're using finally we want to get the value of the check button now in this case the check button can have two different values it's either true or false so remember how we Associated it with a Boolean variable before so here this a equal TK dot Boolean VAR this Boolean means that the value is either true or false not employed or unemployed so how can we convert it to employed or unemployed simply we use this line of code we say the employment status is equal to employed if a DOT get so if this is true else just say unemployed so if it's throughput employed if it's not checked say unemployed now we have all the information from our inputs we can actually try to print it out and I will show you how this works so let's run it and now if you see here let's write for example the name John and then the age we can just select anything here using the spin box let's say he's not subscribed but he is employed if I press on insert and if I scroll down here in my output so this stuff is still related to the tree view this part right here is what we just printed we printed the name age not subscribed and employed this means we have successfully gotten all the information that we wanted we want to insert this row into the Excel sheet so to do so we're just going to connect to the Excel sheet again using Open PI Excel so the same method we specify the path we load the workbook and then we set our sheet to workbook DOT active now that we have this what we're going to do is save the values of the row inside a python list you can use a list or a tuple it's the same thing so here we're going to say name age subscription sets employment status so the same things that we printed before we're just going to save them in this variable and we're going to append to our Excel sheet like this so now we say sheet.append row underscore values for one thing I want to tell you is that you have to make sure to close your Excel file and not have it open so python cannot write to this Excel file unless it's closed otherwise the permission will be denied so I'm just going to close the file the last step I want to do is I want to save the new changes so I say workbook dot save into this path now let's run it and try to test it out so running it let's write a name here for example JT and let's specify an age and let's say other and employed so if you press on insert now it should have been inserted into my Excel sheet so if we open up the Excel here and we scroll down you can see jt23 other employed has just been added to the Excel sheet one thing you can note however is that it hasn't really been added to the tree view we need to do this ourselves as well and as an additional step now to do so very easy it's just one line of code we just say preview.insert similar to before we say here from the start till the end and the values is equal to row value let's run it and test it out make sure you have closed your Excel sheet so let's open this and let's say for example Jane uh dough and let's give her an age let's say not subscribed and let's say unemployed so if we insert it and we scroll back here you can see that Jane Doe has been inserted into the tree view let's go ahead and open up the sheet as well so she should have been inserted into the sheet and as you can see the Jane Doe data is here as well all right one very last thing I want to do is I want to make sure that after we insert our data that this information here gets cleared and reset to the default values this is simply because we don't want to keep this information it becomes very annoying for a user trying to fill in multiple multiple different people that they have to keep erasing everything and so on so let's reset this to the default values so let's say here first thing is we want to clear the name entry so we just say name entry dot delete from 0 till the end this means delete everything then we want to insert the default value which is the name so now it should write name as we had it before it should say name as a placeholder now I want to do the same thing for the age spin box so we delete everything then we insert the word age then we want the combo box to be set to the first value so we set it to combo list Sub 0 this means it will be set to subscribe again and then the employed the check box should be not selected so we uncheck it right here like so so running it let me show you an example so let's just write anything here give it any age let's change this to other and make it employed so if we press on insert now it should have been inserted you can see it at the end of the tree View and you can see here that everything was reset to the original values to the original default values all right so that is it for this video congrats on making it this far so we have created an interactive tikenter application that works with Excel we can both read from the Excel file displayed in a tree view but also insert new data into the Excel file using this form right here so please let me know if you enjoyed it and I'll see you in the next one bye bye

Channel: Code First with Hala

Views: 111,900

Keywords:

Id: 8m4uDS_nyCk

Length: 46min 56sec (2816 seconds)

Published: Wed Mar 15 2023