Hey guys,
In this tutorial, we will build this Data Entry Form from scratch. After filling out the fields and pressing
the submit button, the data has been added to our Excel file. The entry form is built entirely in Python. No VBA is required. At the end of the tutorial, we will also be
converting the Python script to a standalone program. In doing so, we could share this data entry
form with colleagues & friends, and they could use our program without having to install
Python. The best part is that you could use the code
we will write in this tutorial as a starter template. If you want to add or remove columns, you
only need to change one line of code. Additionally, you could also easily add more
elements to the user form, like Multiline Input Fields, sliders, listboxes, a file dialogue
box and much more. Ok, and without further ado, let us get started. As the first step, I will be installing the
required modules. To create the Graphical User Interface, I
will be using the PySimpleGUI library. You can install the package by typing' typing'
pip install PySimpleGUI'' into your command prompt or terminal. To interact with Excel, I will be using the
pandas & openpyxl library. If you have those libraries not installed
yet, you can install them by typing "pip install pandas" & "pip install openpyxl". Once we have gotten that out of the way, I
will open a blank Python file. First things first, I will be importing the
PySimpleGUI as sg. PySimpleGUI comes with different colour themes. At the end of this tutorial, I will show you
all the available themes. But for now, I will be using the DarkTeal9
Theme. As the next step, I will create the layout. Let me shortly type out the complete structure. Because in this way, I think it might be easier
to understand how this library works. Each list within the Layout list represents
a column in the GUI. PysimpleGUI offers different Elements. For instance, in the first row, I have a simple
text as a header. After that, I have another text, with a size
of 15 & 1. It means that the text is 15 characters wide
and one character tall. In the same row, I have an input field. What is essential here is the Key. We will use the Key later to retrieve the
values from the Input form. Finally, I have also two buttons. One submit button and one Exit button. Once we have the layout, we can pass it over
to the instance of the Window. Additionally, we could also provide a window
title. After initializing the Window, we can use
a while loop to check for any events which might occur by using the Window.read() method. For instance, clicking on X will trigger the
event ''WIN_Closed''. Similarly, by clicking on the Exit button,
the ''Exit'' event gets triggered. In that case, we want to break the While loop. If we click the Submit button, I would like
to print out the event name and values. Outside our while loop, I will close the Window. Ok, and here is our code for our simple GUI
application. To run this code, I will go back to my command
prompt, navigate to the directory of the script and run the data_entry.py file. After pressing enter, our GUI appears. Let me enter my name and press the Submit
button. PySimpleGUI now prints out the event's name
in my command prompt, so ''Submit'' and the values. Note that the values are returned as Python
dictionary with the Key we have specified in the script. By clicking Exit, we should break out from
the while loop and close the Window. Now that we know how this library works, I
will add more field to our layout. As before, let me shortly type out the complete
structure. Instead of an InputText, I have inserted a
combo box with three different colours to chose from. In the next row, I would like to display three
checkboxes. For each checkbox, I am defining a separate
key. If the user ticks the checkbox, PySimpleGUI
will return True; otherwise, it returns false. Next, I have also included a Spin element. I am using here a list comprehension, which
will return a list of values from 0 to 15. By the way, you can find all available elements,
like Sliders, Listboxes, Radio Button etc., in the documentation. I will make sure to insert the link to the
documentation in the description box. Ok, and if we rerun our script, we can now
see our GUI with the new layout. After pressing the Submit button, it will
print out again our values in a python dictionary. Instead of printing the values to the screen,
I would like to return the values to Excel. Therefore I have prepared the following Excel
file. In the first row, I have inserted the headings. I intend to insert a new row with the values
from the GUI once the users press the Submit button. We can accomplish this with the help of the
pandas' libraries. Therefore, the Column Name in your Excel file
must be 100% matching with the Keys you have defined in your layout. Having said that, let me switch back to our
script. First, I will be importing pandas as pd. Then I am defining a variable called EXCEL
FILE, which holds the path to the Excel file. As my excel file is in the same directory
as the python script, I only need to type the Excel file name here. However, if your Excel file is not the same
directory, you would need to specify the exact path to the file. As the next step, I will create a pandas dataframe
by typing df = pd.read_excel followed by our Excel File. And here comes the trick. Instead of printing the values, I will append
them to our dataframe and ignore the index. Once again, this will only work if your Key
Names and the names of the columns are 100% matching. Once we have appended the new row to our dataframe,
I will overwrite our initial Excel File as follows. To let the user know that the Data have been
saved, I will also insert a popup message. Ok, and that is all there is to it. After running the script, we can see our GUI
again. And when I am clicking the submit button,
a popup window appears, saying that the data have been saved. If everything worked, we should have those
entries also in our Excel file. And indeed, our script has added two more
rows. The cool thing is that you could use this
code as a starter template for your projects. If you want to add more fields to your Excel
file, all you need to do is to add another column, for instance, "City", save the file
and adjust the layout in the Python script. For this example, I will also use an Input
text. Just remember to use the column header name
as the Key, so in my case, "city". And that's it. After rerunning the script, we have our new
Input field, and if I submit the data, we will see our new Entry in the Excel file. To polish up our application, I will also
insert a "Clear button". By clicking the button, I would like to clear
the User Input from the GUI. For this, I will define a separate function
outside the While loop. All it does is iterating over the keys in
the values and set them to an empty string. Within the While loop, I can now call this
function whenever the users press the "Clear" button. Additionally, once the user has submitted
the data, I will also clear the input. We can test this by inputting some data and
into the GUI. And as expected, after clicking "Clear", the
values in the Form have been cleared. Also, after submitting the data, we get a
fresh entry form. Now that we have our final GUI Application,
we might want to share it with colleagues or friends. Yet, perhaps they do not have Python installed
on their machine. Frankly speaking, it might be even easier
to input the values directly in Excel rather than executing our Python script from the
command prompt or terminal. Therefore we will be converting our python
file to a standalone executable file. In that case, we do not need Python to run
our application. To transform our file, I will be using the
Pyinstaller library. You can install it by typing' typing' pip
install pyinstaller'' into your command prompt or Terminal. Once done, navigate to the directory of the
Python file, which you want to convert. In my case, I am already in my Data Entry
folder. Next, you can type ''pyinstaller –onefile
--noconsole'' followed by the Python file name. Executing this line may take some time, but
once done, you will find the following folders in our project. One of the folders is called ''dist''. Here, you will find the standalone executable
file. I will copy and paste it out of the folder. You can go ahead and delete the other files,
as we do not need them. Ok, and if I execute the file, we should see
our GUI. To validate that everything is working, I
will submit more data. Once done, our program has added a new row
to our Excel file. Ok, guys, and that's pretty much it for this
tutorial. As a bonus, I will also include a folder called
DEMO in the download files. In the Demo folder, I have copied two scripts
from the official PySimpleGUI Documentation. If you run all_elements.py, it will show the
available elements, like Multiline Input Fields, sliders, listboxes and even a file picker
dialogue box. Additionally, you will find a theme script,
which shows you all the various Themes Pysimplegui offers. You could go through the list and pick your
favourite one. Ok, guys, and that's it for this video. If you have any questions, please let me know
in the comments section. Thanks for watching and see you next time.
