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

• Did you know you can use Python code to create an Excel Data Entry Form and avoid duplicate records?
• Πως να δημιουργήσετε μια φόρμα εισαγωγής δεδομένων σε 10 λεπτά με τη γλώσσα προγραμματισμού PYTHON (No VBA)
• Πως να διασφαλίσετε ότι δεν θα κάνετε διπλοεγγραφές

Excel fields : Name, City, Favorite Color, German, Spanish, English, Children

Excel before data entry

Data entry form

Excel after data entry

‘Python code starts here.

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 Color’, size=(15,1)), sg.Combo([‘Green’, ‘Blue’, ‘Red’], key=’Favorite Color’)],

[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

def check_duplicate_entries(df, values):

duplicate_entries = df[

(df[‘Name’] == values[‘Name’]) &

(df[‘City’] == values[‘City’]) &

(df[‘Favorite Color’] == values[‘Favorite Color’]) &

(df[‘German’] == values[‘German’]) &

(df[‘Spanish’] == values[‘Spanish’]) &

(df[‘English’] == values[‘English’]) &

(df[‘Children’] == values[‘Children’])

]

return not duplicate_entries.empty

while True:

event, values = window.read()

if event == sg.WIN_CLOSED or event == ‘Exit’:

break

if event == ‘Clear’:

clear_input()

if event == ‘Submit’:

if check_duplicate_entries(df, values):

sg.popup(‘Duplicate entry found!’)

else:

if not check_duplicate_entries(df, values):

df = df._append(values, ignore_index=True)

df.drop_duplicates(inplace=True)

df.to_excel(EXCEL_FILE, index=False)

sg.popup(‘Data saved!’)

clear_input()

window.close()

‘Python code ends here.

Source video

This code has been created with the help of Coding is Fun. Keep in mind that additional code has been added to check for duplicate records.