PyQt5 QTableWidget tutorial: Load Excel data into Table Widget [Python, openpyxl, PyQT5]

hello everyone and welcome back to my channel in today's video we're going to take an Excel file and load all of the data from it into a pi qd5 application and more specifically AQ table widget so the Q table widget is basically this type of table that you see right here on my screen so this is within a pi qd5 application and we're going to take all of this data from an Excel file so this one that I'm showing you right now and we're going to load this data in the piquity 5 table so this is the goal of the tutorial the tutorial is definitely beginner friendly so you may follow along if you are a beginner the only thing I ask of you is that you have Pi qd5 installed I do have a separate video very short that you can refer to if you want to see the instructions on how to install by qt5 and without further Ado let's get started so I'm going to pull up vs code I'm actually going to close the application so I have vs code open with a blank python file so inside my folder called piqt5 Excel two Q table widget I have two main files the main.pi which is the python file where we're going to write our code as well as the Excel file containing all our data now do note that I'm using vs code because it's my preference however you can use any text editor that you like or that you're comfortable with so long as that you can run Python and you're able to actually be comfortable working with it alright so this is my blank file let me actually first show you the Excel file so just a disclaimer this isn't my file I didn't write this or create this I downloaded it so the source link for the main file will be in the description now I think I did remove one of the columns when I downloaded the file so it may not be exactly the same thing however this file will be inside the GitHub repository Linked In the description which will contain the source code as well as the main Excel file alright so we're using this file we obviously want to load this Excel data into our program how do we do that we're going to use a popular python module called Open PI Excel so to install it you need to pull up a CMD or a terminal so here I'm using the integrated terminal in vs code and simply you're going to type pip install Open PI Excel now this is going to take about a minute for you to install in my case I already have it so it says requirement already satisfied so in your case just wait a minute it should be there all right now that we've installed Open PI Excel we can go ahead and start using it to load this data into Pi qd5 so first things first when we write our code we want to actually import everything that we need what we want to import is the following so from piqd5.qt widgets so if you have bike 85 installed you should be able to import this QT widgets you want to import Q application Q widget QV box layout Q table widget and queue table widget item to make things easier you can go ahead and replace all of this with an asterisk this way you would import the entire QT widgets module but for the sake of specificity I chose to type each of them out and you'll see where we're going to use each single one as I show you throughout the code okay next thing you want to do is you want to import sys again I'll show you why we need it in just a second finally you want to import Open PI Excel so Open PI Excel is super important this is how we're going to actually access all of the data in the Excel file and how we're going to load it all right so let's get started by actually creating the most basic Pi qd5 application so let's write down the starter code for a basic PI qt5 app then we'll see how we can add a q table widget and load the Excel content okay so you want to define a class the class in this case will inherit from Q widget so this is where we're using this Q widget import so this will create the window that we're going to launch when we launch the application then you want to fill in the Constructor for this class so the init function you want to call the init function of the superclass so using this line of code we're actually calling the init function of the superclass in this case this is the EQ widget all right next what we want to do is we want to set the window title of our application so here this will be the title of the window I'll show you when we run it the title will be load Excel data to queue table widget so this is the title of our application now that we've done this what we want to do next is actually execute the application to do so we need this block of code right here inside if name equal main so this is where we're actually executing the python code what we want to do is Define a queue application inside a variable called app every python app needs AQ application to actually be able to be executed so this is how we can actually execute the application and see what we developed we pass sys.orgb this is actually saying that we're passing the command line arguments to this queue application in our case we don't really have any but we keep it here regardless next what we do is we create a window variable which has an instance of the main class so this main class we create an instance of it and what we want to do next is to say window.show maximized show maximize will ensure the application launches and it's almost full screen so it's totally maximized to the size of your screen finally we execute the application using app.exec so let's run it and see what we have so far and as you can see it launches a big application it's definitely maximized and it's totally empty so it's totally blank the title as you can see we set the window title to be load Excel data to Q table widget and you can see it right here so now we've created the most basic Pi qt5 application what we want to do next is to actually add a q table widget to our app we can't do that before first adding a layout for our application so for Simplicity I'm going to use a QV box layout this will essentially lay everything out in one vertical column so everything will be under each other stacked under each other in our case we only have one thing this is the Q table widget so it doesn't really matter whether it's a vertical box or a horizontal box so I say layout is QV box layout then I set the self.set layout to be layout itself so the layout that I previously defined after doing so here I can create my Q table widget to do so it's very simple using one line of code I say self.table widget dot Q table widget so here I'm creating an instance of this Q table widget class and I'm saving it inside a variable called self.table widget finally what I want to do is I say layout dot add widget and here is where I'm going to say self dot table widget so I'm adding my widget into this layout now if I run it I should be able to see you see this is the table widget obviously it's blank there are no rows or columns there is no data there is nothing it's just a blank table widget that we just added to our application okay perfect so we have the Q table widget what we want to do next is to actually load this Excel data to do so I'm going to create a function called load data and it's going to look like this so and then I'm going to go Define it let's say load data and of course we have the past self so this is where I'm going to load all of the Excel data into this Q table widget all right how am I actually going to do this I need to utilize Open PI Excel first things first I need the path of my Excel file so I'm going to come here to my Excel file I'm going to right click and I'm going to copy the path I'm going to create a string and copy paste the absolute path into my python code so now we have the path inside a variable next thing I want to do is to say workbook so this is a variable and I'm going to say open by Excel dot load workbook and then I have to pass the path so here I'm saying go to this path and load me this workbook using Open PI Excel I'm saving this in a variable now the way Open PI Excel works is that load workbook will actually open up this workbook that we have right here however you do know that in Excel you might have multiple sheets so in case we have one sheet however usually in Excel there may be multiple sheets so we need to refer to this sheet using so sheet it will be workbook Dot active so active means the currently open sheet so now list of countries is active because it's currently open inside the Excel file alright now that I have the sheet I am able to actually load all of the different values and to do so I'm actually going to show you how I can load them it's pretty simple so let's say values is equal to list so you'll see why I'm converting a list in just a second sheet dot values okay so sheet itself refers to my active sheet in my Excel file and then dot values will get me all of the different values inside of this sheet I convert it to a list and now this will produce a list of tuples containing my values what I'm going to do next is just for the sake of showing you I'll say four value in values print value and I'll show you what each value is so let's run it and see what it prints out you can see it still launches the blank application we still haven't put everything inside the qtable widget but going back to the code you can see these are the values so you have the first Tuple so this is a python Tuple these parentheses they show you a python Tuple you have rank country population and then the percent of the world population and then in each one you have the rank and the value of the country and the value of the population as well so as you can see this actually enabled us to retrieve all the values from the Excel sheet and load them in Python so this is where Open PI Excel did its job we opened up the workbook we accessed the sheet and then we access the value of this sheet what we did was simply just print these values however the main goal of this tutorial is to take these values and to put them in the queue table which so let's do that I'm going to close this and I'm going to actually shut this off and I'm going to rename this to be list underscore values just to signify that this is a list of all the values and the values themselves are python topples as we just saw okay you notice that the very first one let me actually pull up the output again let me actually run it again so here as you can see this first value in the values list so the first Tuple is just the header of the Excel file so you have rank country population percent of world Pub these are the header this is the main part of the Excel file we want this to be set as the header in the queue table widget so the way I'm going to do that is very simple I'll say self dot table widget dot set horizontal header labels so this will set the labels for the header and then I'm going to pass this values Sub Zero so here take the very first list the very first Tuple inside this list give it to the header so let's rerun it and see how it looks like we still actually don't find anything in the table why is that this is because we haven't set a row or a column count for the table in pi qt5 you always have to set the row count and the column count before you start populating your Q table widget so let's do that so we close this and come back to the code so let's go here and say self dot table widget dot set row count and what I'm going to do is say sheet dot Max sorry Max row so sheet.max row will get me the maximum number of rows with actual values in my Excel sheet and then self.table widget again dot set column count and here we'll say sheet.max column so this is the maximum number of columns in this case we have four columns so this Max column value will actually be 4. so after doing this let's rerun and as you can see we now can see the header of this table and you can actually see the actual physical table right here so you can see all of the rows the four columns and you have rank country population and the percentage as the header so they're not actually one of the rows this is the header of the table so now we've added the main information the header as well as we've added the number of rows and number of columns the very last thing we need to do is to actually add the data all right let's add the data so to do so I'm going to create a for Loop and this will Loop over my data inside this list dot values variable so let's say four value topple so here I'm indicating that this is a tuple however you can name it anything you want in list values okay so now let's just maybe print this out for now because I want to show you one main thing let's run it and go back here to see what we printed out you can see the very first one is this rank country population percent of world population so how do we get rid of this simply we will just slice the list so I will say list values and we'll go here so one and so on so we're saying take the list values list and start from position one and go until the end so here we're skipping out on the very first value in this list the reason is we don't want to put the header again a second time inside the table all right so this value Tuple will return this row it will return a single Row the values of a row let me show you again so let's run it and go to the print you can see it starts like this so zero world the values so these are the values for each separate row and notice that we no longer have the header being printed out here so this was our goal to get rid of the header so you can see the values are printed here now this value represents one value Tuple so each time I print a value Tuple I get this row right here before I show you how you can actually insert this value Tuple into the Q table widget let me show you first how to actually insert any value in the Q table widget so I'm going to comment these out and go to a new line all you need to do is run this line of code you say self.table widget dot set item then you specify the number of the row as well as the number of the columns so if I say row 0 column 1 or let's say column two and then we say Q table widget item you create a new Q table widget item this represents a single item inside the queue table widget so one cell let's say hello so I'm just doing this for example just so that you can understand how we set a value to the Q table widget let's rerun it now you can see in row 0 which is the first row because everything in Python starts at index 0 and then column two which is the third column we just added Hello here and now I can change these values any way that I want so coming back here I can say in row one and column one uh let's say row seven let's rerun it you can see it right here so it changed the position so now what we want to do is to actually create this Loop and fill in all of the information in the respective cells okay so let me just comment this one out now and go back to where we were writing our code so we have value Tuple we want to Loop over this value Tuple and insert every single value into the Q table widget so I'll say four value in value and now what I'll say is self.table widget dot set item the way we said before now we're going to need something for the row and the column let's get back to it in just a second but the queue table widget item itself will be the value that we said right here so this will be one of the values inside the topple we convert it to a string just to stay safe in case there are integers that are too big to be represented as integers in Python so we keep everything as a string so what is going to be the index of the row and the index of the column so these need to be set dynamically we can't do this we can't hard code it and say 0 1 or 7 the way we were doing so before so let me clear this we need to set it dynamically so for the column what I'm going to do is come here and I'll say column index equal zero so the column index will be where which column we're putting our insert this table item so the column index it starts at zero and it's going to be incremented every single time this Loop occurs so as we Loop over the values we're going to insert this item in a different column okay so we have the column index now what we need is a row index so let me clear the Sprint statement and come here outside of this loop we're going to have a row index it's going to be equal to 0 and then every time we Loop this outer loop we're going to increment the row index by 1. so let me show you now whether this works and I'll go back and explain it so let's see so now I run it and as you can see all of the information that was present in the Excel sheet now appears inside my table so the way this works is that this row index will loop from 0 till the maximum amount of rows and then each row we Loop over every single individual item inside this row so every single individual column and this is how we were able to get all of this information using Open PI Excel and add it to our Q table widget as you can see right here so now we have all of our data in our piquity 5 application inside our queue widget alright so that's really it for this video again the source code will be available in the comments down below thank you so much for watching and I'll see you in the next one bye bye

Channel: Code First with Hala

Views: 16,602

Keywords:

Id: cTqP7xLk7j4

Length: 18min 55sec (1135 seconds)

Published: Mon Sep 19 2022