This Python script creates a data entry form using PySimpleGUI, allowing users to input, submit, clear, and delete data in an Excel file.

excel_data_entry.py

"""
This script is based on the original code from the YouTube tutorial:
- https://youtu.be/svcv8uub0D0

The initial code can be found on GitHub at:
- https://github.com/Sven-Bo/data-entry-form-pysimplegui

Modifications made to the original code:
- Added a "Delete Last Row" button to remove the last row from the Excel file.

Author Information:
- Sven Bosau
- YouTube Channel: https://youtube.com/c/CodingIsFun
- Website: https://pythonandvba.com
"""

from pathlib import Path

import PySimpleGUI as sg
import pandas as pd

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

current_dir = Path(__file__).parent if '__file__' in locals() else Path.cwd()
EXCEL_FILE = current_dir / '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.Button('Delete Last Row'), sg.Exit()]
]

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

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

def delete_last_row():
    global df
    if len(df) > 0:
        df = df.iloc[:-1]
        df.to_excel(EXCEL_FILE, index=False)
        sg.popup('Last row deleted!')
    else:
        sg.popup('There is no data to delete!')

while True:
    event, values = window.read()
    if event == sg.WIN_CLOSED or event == 'Exit':
        break
    if event == 'Clear':
        clear_input()
    if event == 'Submit':
        new_record = pd.DataFrame(values, index=[0])
        df = pd.concat([df, new_record], ignore_index=True)
        df.to_excel(EXCEL_FILE, index=False)
        sg.popup('Data saved!')
        clear_input()
    if event == 'Delete Last Row':
        delete_last_row()
window.close()