Menu

How to Create an Excel Data Entry Form in 10 Minutes Using Python (No VBA) | Easy & Simple

Language:

Did you know you can use Python code to create an Excel Data Entry Form? This is a tutorial that will show you how to create one using the PySimpleGUI & Pandas library. No VBA or macros are 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 this code as a starter template whenever you want to build a data entry form for Excel. 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, list boxes, a file dialogue box and much more.

 

📝 Resources:

Download all the files from the tutorial:

Download Here

PySimpleGUI Documentation: https://pysimplegui.readthedocs.io/en/latest/

 

👩‍💻 Source Code:

import PySimpleGUI as sg
import pandas as pd

# Add some color to the window
sg.theme('DarkTeal9')

EXCEL_FILE = 'Data_Entry.xlsx'
df = pd.read_excel(EXCEL_FILE)

layout = [
    [sg.Text('Please fill out the following fields:')],
    [sg.Text('Name', size=(15,1)), sg.InputText(key='Name')],
    [sg.Text('City', size=(15,1)), sg.InputText(key='City')],
    [sg.Text('Favorite Colour', size=(15,1)), sg.Combo(['Green', 'Blue', 'Red'], key='Favorite Colour')],
    [sg.Text('I speak', size=(15,1)),
                            sg.Checkbox('German', key='German'),
                            sg.Checkbox('Spanish', key='Spanish'),
                            sg.Checkbox('English', key='English')],
    [sg.Text('No. of Children', size=(15,1)), sg.Spin([i for i in range(0,16)],
                                                       initial_value=0, key='Children')],
    [sg.Submit(), sg.Button('Clear'), sg.Exit()]
]

window = sg.Window('Simple data entry form', layout)

def clear_input():
    for key in values:
        window[key]('')
    return None


while True:
    event, values = window.read()
    if event == sg.WIN_CLOSED or event == 'Exit':
        break
    if event == 'Clear':
        clear_input()
    if event == 'Submit':
        df = df.append(values, ignore_index=True)
        df.to_excel(EXCEL_FILE, index=False)
        sg.popup('Data saved!')
        clear_input()
window.close()